Python: concat rows of two dataframes of different shapes and set value to 0 where no data-CodePudding

I have two dataframes:

df1 = pd.DataFrame(index = [0,1,2], columns=['order_id', 'USD', 'CAD'])
df1['order_id']=['11233123','12313213','12341242']
df1['USD'] = [1,2,3]
df1['CAD'] = [4,5,6]
df1:
    order_id  USD   CAD
0   11233123  1     4
1   12313213  2     5
2   12341242  3     6

df2 = pd.DataFrame(index = [0,1], columns = ['currency','balance'])
df2['currency'] = ['USD', 'CAD']
df2['balance'] = [2,3]
df2:
    currency    balance
0   USD         2
1   CAD         3

I would like to add a row to df1 at index 0, and fill that row with the balance of df2 based on currency. So the final df should look like this:

df:
    order_id USD CAD
0       0    2   3
1   11233123 1   4
2   12313213 2   5
3   12341242 3   6

How can I do this in a pythonic way? Thank you

CodePudding user response：

Set the index of df2 to currency then transpose the index to columns, then append this dataframe with df1

df_out = df2.set_index('currency').T.append(df1, ignore_index=True).fillna(0)

print(df_out)

   USD  CAD  order_id
0    2    3         0
1    1    4  11233123
2    2    5  12313213
3    3    6  12341242

CodePudding user response：

Try the following code:

df = pd.concat([df2.set_index('currency').T, df1], axis=0, ignore_index=True)[df1.columns].fillna(0)