我有两个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
1 回答
基于交叉连接 - 请参阅cartesian product in pandas
哪个产生:
然后因为你想要一个“左外连接”我们不想从df1中释放任何人
或者
哪个产生