Left outer Join Pandas Data Frame using contains

Question

I have two Pandas Data Frames df1 and df2. I want df1 joined with df2 using a left outer join but with a contains function like "df2.Partial_key" in "df2.Full_Key".

Select df1.data_id1, df1.Full_Key, df1.text_field
, df2.data_id2, df2.text_field
from df1
LEFT OUTER JOIN df2 on "df1.Full_Key contains df2.Partial_key"

Is there a way to do this without for loops? Given

df1 = pd.DataFrame.from_items([('data_id1' , ['bzx_0001','bzx_0002','bzx_0003','bzx_0004'])
, ('Full_Key_1',['AAAA-BBBB-20150101-NS237890', 'BBBB-CCCC-21050101-MS18546', 'CCCC-CCCC-20150101-MS34567', 'CCCC-CCCC-20150101-MS34568'])
, ('text_field',['aaaaa', 'bbbbb', 'cccccc', 'ddddd'])])

df2 = pd.DataFrame.from_items([('data_id2',['dm_0001', 'dm_0002', 'dm_0003', 'dm_0004'])
,('Partial_key',['AAAA-BBBB-20150101-', 'AAAA-BBBB-20150101-', 'BBBB-CCCC-21050101-', 'XXXX-XXXX-20150101-'])
])

DataFrame expected after joining:

df_exp_res = pd.DataFrame.from_items([
('data_id1', ['bzx_0001', 'bzx_0001', 'bzx_0002', 'bzx_0003', 'bzx_0004'])
,('Full_Key_1',['AAAA-BBBB-20151005-NS237890', 'AAAA-BBBB-20151005-NS237890', 'BBBB-CCCC-21050101-MS18546', 'CCCC-CCCC-20150101-MS34567', 'CCCC-CCCC-20150101-MS34568'])
,('text_field',['aaaaa', 'aaaaa', 'bbbbb', 'cccccc', 'ddddd'])
,('data_id2', ['dm_0001', 'dm_0002', 'dm_0003', np.nan, np.nan])
,('Partial_key',['AAAA-BBBB-20151005-', 'AAAA-BBBB-20151005-', 'BBBB-CCCC-21050101-', np.nan, np.nan])
])

My solution using for loops:

s = [['data_id1' , 'Full_Key_1', 'text_field', 'Partial_key', 'data_id2']]
for indx1, row1 in df1.iterrows():
    fnd = False
    for indx2, row2 in df2.iterrows():
        if row2['Partial_key'].strip() in row1['Full_Key_1'].strip():
            s.append([row1['data_id1'],row1['Full_Key_1'], \
            row1['text_field'], row2['Partial_key'], \
            row2['data_id2']])
            fnd = True
        else:
            pass
    else:
        if not fnd:
            s.append([row1['data_id1'],row1['Full_Key_1'], \
            row1['text_field'], np.nan, np.nan])

pd_result_calc = pd.DataFrame(s[1:],columns=s[0])
print df1
print df2
print pd_result_calc

Are the `Partial_key`s always truncations of `Full_key`s? and are they always 19 characters in length? — unutbu, Jan 14 '16 at 17:59
The Partial_Keys and Full_Keys have no fixed lengths. As longs as the entire Partial_Key is contained in Full_Key that is considered a match. But yes, the Partial_Key will always be Truncation of the Full_Key of the form: Partial_key = Full_Key[0:some_n] where 0 < some_n <= Len(Full_Key) — Partha Sarkar, Jan 14 '16 at 18:46

score 1 · Accepted Answer · edited May 23 '17 at 10:27

based on a cross join - see cartesian product in pandas

df1 = pd.DataFrame.from_items([('data_id1' , ['bzx_0001','bzx_0002','bzx_0003','bzx_0004'])
, ('Full_Key_1',['AAAA-BBBB-20150101-NS237890', 'BBBB-CCCC-21050101-MS18546', 'CCCC-CCCC-20150101-MS34567', 'CCCC-CCCC-20150101-MS34568'])
, ('text_field',['aaaaa', 'bbbbb', 'cccccc', 'ddddd'])])

df2 = pd.DataFrame.from_items([('data_id2',['dm_0001', 'dm_0002', 'dm_0003', 'dm_0004'])
,('Partial_key',['AAAA-BBBB-20150101-', 'AAAA-BBBB-20150101-', 'BBBB-CCCC-21050101-', 'XXXX-XXXX-20150101-'])
])

df1['key'] =1
df2['key'] =1

merged_cross_join = pd.merge(df1, df2,on='key')

# we don't need this helper column 'key' any longer
merged_cross_join.drop('key', axis=1, inplace=True)
df1.drop('key', axis=1, inplace=True)

contains_criteria = merged_cross_join[['Full_Key_1','Partial_key']].apply(lambda x: x['Partial_key'] in x['Full_Key_1'],axis=1)
print merged_cross_join[contains_criteria]

Which produces:

   data_id1                   Full_Key_1 text_field  key data_id2          Partial_key
0  bzx_0001  AAAA-BBBB-20150101-NS237890      aaaaa    1  dm_0001  AAAA-BBBB-20150101-
1  bzx_0001  AAAA-BBBB-20150101-NS237890      aaaaa    1  dm_0002  AAAA-BBBB-20150101-
6  bzx_0002   BBBB-CCCC-21050101-MS18546      bbbbb    1  dm_0003  BBBB-CCCC-21050101-

and then because you want like a "left outer join" we don't want to loose anyone from df1

not_matched_in_df1 = set(df1['data_id1']) - set(merged_cross_join['data_id1'])
final = pd.concat([merged_cross_join,df1[df1['data_id1'].isin(not_matched_in_df1)]],axis=0)

or alternatively

merged_cross_join.combine_first(df1)

which produces

   data_id1                   Full_Key_1 text_field data_id2          Partial_key
0  bzx_0001  AAAA-BBBB-20151005-NS237890      aaaaa  dm_0001  AAAA-BBBB-20151005-
1  bzx_0001  AAAA-BBBB-20151005-NS237890      aaaaa  dm_0002  AAAA-BBBB-20151005-
2  bzx_0002   BBBB-CCCC-21050101-MS18546      bbbbb  dm_0003  BBBB-CCCC-21050101-
3  bzx_0003   CCCC-CCCC-20150101-MS34567     cccccc      NaN                  NaN
4  bzx_0004   CCCC-CCCC-20150101-MS34568      ddddd      NaN                  NaN

This drops the left outer join part where df1.Full_Keys have no matches found. — Partha Sarkar, Jan 14 '16 at 19:13

Left outer Join Pandas Data Frame using contains

1 Answers1