Python: concat rows of two dataframes where not all columns are the same-CodePudding

I have two dataframes:

EDIT:

df1 = pd.DataFrame(index = [0,1,2], columns=['timestamp', 'order_id', 'account_id', 'USD', 'CAD'])
df1['timestamp']=['2022-01-01','2022-01-02','2022-01-03']
df1['account_id']=['usdcad','usdcad','usdcad']
df1['order_id']=['11233123','12313213','12341242']
df1['USD'] = [1,2,3]
df1['CAD'] = [4,5,6]
df1:
    timestamp   account_id order_id  USD CAD
0   2022-01-01  usdcad     11233123  1   4
1   2022-01-02  usdcad     12313213  2   5
2   2022-01-03  usdcad     12341242  3   6

df2 = pd.DataFrame(index = [0,1], columns = ['timestamp','account_id', 'currency','balance'])
df2['timestamp']=['2021-12-21','2021-12-21']
df2['account_id']=['usdcad','usdcad']
df2['currency'] = ['USD', 'CAD']
df2['balance'] = [2,3]
df2:
    timestamp  account_id currency  balance
0   2021-12-21 usdcad     USD       2
1   2021-12-21 usdcad     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:
    timestamp   account_id order_id  USD CAD
0   0           0          0         2   3
1   2022-01-01  usdcad     11233123  1   4
2   2022-01-02  usdcad     12313213  2   5
3   2022-01-03  usdcad     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)