1

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

MathMan 99
  • 665
  • 1
  • 7
  • 19

1 Answers1

1

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
Shubham Sharma
  • 68,127
  • 6
  • 24
  • 53
  • It works, however I am getting a row of 0s at index=1 for some reason in the original problem I have – MathMan 99 Feb 13 '22 at 06:06
  • @MathMan99 Check if your original dataframe have other columns also? If so, you can use `df2.set_index('currency')[['balance']].T.append(df1, ignore_index=True).fillna(0)` – Shubham Sharma Feb 13 '22 at 06:15
  • That did not work. In my original problem, both df1 and df2 have columns that do not overlap except for the timestamp column name. However, all I care about is adding the USD and CAD columns and set everything else to 0 when appending – MathMan 99 Feb 13 '22 at 06:28
  • @MathMan99 Please update your question to include more details. Currently, the solution presented here works for the OP that you have posted. – Shubham Sharma Feb 13 '22 at 07:01
  • I edited the question to better represent my current problem. Thank you! – MathMan 99 Feb 13 '22 at 19:06
  • 1
    @MathMan99 Happy coding! – Shubham Sharma Feb 14 '22 at 16:31