I have two dataframes, df1 and df2, both with different number of rows.
df1 has a column 'NAME', a short string; and df2 has a column 'LOCAL_NAME', a much longer string that may contain the exact contents of df1.NAME.
I want to compare every entry of df1.NAME with every entry in df2.LOCAL_NAME, and if df1.NAME appears in a particular entry of df2.LOCAL_NAME, I want to create add an entry in a new column df2.NAME_MAP = df1.NAME. If it doesn't appear in the long string df2.LOCAL_NAME, the corresponding entry in df2.NAME_MAP will be df2.LOCAL_NAME
For now, efficiency is not an issue. Here are sample datasets.
df1 = pd.DataFrame({
"NAME" : ['222', '111', '444', '333'],
"OTHER_COLUMNS": [3, 6, 7, 34]
})
df2 = pd.DataFrame({
"LOCAL_NAME": ['aac111asd', 'dfse222vdsf', 'adasd689as', 'asdv444grew', 'adsg243df', 'dsfh948dfd']
})
df1:
NAME | OTHER_COLUMNS |
---|---|
'222' | 3 |
'111' | 6 |
'444' | 7 |
'333' | 34 |
df2:
LOCAL_NAME |
---|
'aac111asd' |
'dfse222vdsf' |
'adasd689as' |
'asdv444grew' |
'adsg243df' |
'dsfh948dfd' |
The goal is to create another column in df2 called NAME_MAP which has the value of df.NAME if that string is contained exactly in the larger df2.LOCAL_NAME string. df2 would now look like this:
LOCAL_NAME | NAME_MAP |
---|---|
'aac111asd' | '111' |
'dfse222vdsf' | '222' |
'adasd689as' | 'adasd689as' |
'asdv444grew' | '444' |
'adsg243df' | 'adsg243df' |
'dsfh948dfd' | 'dsfh948dfd' |
Then I can join the two dataframes on NAME_MAP:
LOCAL_NAME | NAME_MAP | NAME (from df1) | OTHER_COLUMNS (from df1) |
---|---|---|---|
'aac111asd' | '111' | '111' | 6 |
'dfse222vdsf' | '222' | '222' | 3 |
'adasd689as' | 'adasd689as' | NaN | NaN |
'asdv444grew' | '444' | '444' | 7 |
'adsg243df' | 'adsg243df' | NaN | NaN |
'dsfh948dfd' | 'dsfh948dfd' | NaN | NaN |
How do I go about trying to do this string comparison in two datasets of different sizes?