I have two dataframes as below
df1 = pd.DataFrame( {
"names": ['alpha', 'bravo', 'charlie', 'delta', 'echo', 'foxtrot', 'golf'],
"Debit": [0, 5000, 0, 5000, 3000, 0, 700],
"Credit": [1000, 0, 2000, 0, 0, 8000, 0],
} )
and
df2 = pd.DataFrame( {
"names": ['alpha', 'bravo', 'charlie', 'delta', 'echo', 'foxtrot'],
"db_head": [1, 1, 1, 1, 1, 1],
"cr_head": [2, 2, 2, 2, 2, 2],
} )
The output I want is:
names Debit Credit head
0 alpha 0 1000 2
1 bravo 5000 0 1
2 charlie 0 2000 2
3 delta 5000 0 1
4 echo 3000 0 1
5 foxtrot 0 8000 2
I tried to merge but did not understand how to get the value from two of the last columns based on the current df’s column values
if merging without condition
print(df1.merge(df2, how="inner", on="names"))
by simply merging both of the dataframes resulted like this
names Debit Credit db_head cr_head
0 alpha 0 1000 1 2
1 bravo 5000 0 1 2
2 charlie 0 2000 1 2
3 delta 5000 0 1 2
4 echo 3000 0 1 2
5 foxtrot 0 8000 1 2
tried these two methods but both of methods are giving error
df1['acchead'] = [df2[df2['names'] == x].db_head.item() if y > 0 else df2[df2['names'] == x].cr_head.item() for x, y in [df1['names'], df1['Debit']]]
df1['acchead'] = [df2[df2['names'] == x].db_head.item() if df1.Debit.item() > 0 else df2[df2['names'] == x].cr_head.item() for x in [df1['names']]]
Any Help would be appreciated.
>Solution :
You can merge, then post-process the output with pop and where:
out = df1.merge(df2, on='names', how='inner')
out['head'] = out.pop('db_head').where(out['Debit'].ne(0), out.pop('cr_head'))
Output:
names Debit Credit head
0 alpha 0 1000 2
1 bravo 5000 0 1
2 charlie 0 2000 2
3 delta 5000 0 1
4 echo 3000 0 1
5 foxtrot 0 8000 2
Other approach with a reshaping before the merge:
(df1.assign(variable=np.where(df1['Debit'].ne(0), 'db_head', 'cr_head'))
.merge(df2.melt('names', value_name='head'), on=['names', 'variable'])
#.drop(columns='variable')
)
Output:
names Debit Credit variable head
0 alpha 0 1000 cr_head 2
1 bravo 5000 0 db_head 1
2 charlie 0 2000 cr_head 2
3 delta 5000 0 db_head 1
4 echo 3000 0 db_head 1
5 foxtrot 0 8000 cr_head 2