python - 使用包含左外连接 Pandas 数据框

Question

我有两个 Pandas 数据框 df1 和 df2。我希望 df1 使用左外连接与 df2 连接，但包含像“df2.Full_Key”中的“df2.Partial_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"

有没有不用 for 循环的方法？给定

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:

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])
])

我使用 for 循环的解决方案:

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

Answer 1

基于交叉连接 - 参见 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]

产生:

   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-

然后因为你想要一个“左外连接”，我们不想从 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)

或者替代地

merged_cross_join.combine_first(df1)

产生

   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