Home > Mobile >  Pandas: Sum and aggregate by daily amounts after multiplying exchange rates look-up by same dates fr
Pandas: Sum and aggregate by daily amounts after multiplying exchange rates look-up by same dates fr

Time:10-14

thank you in advance for your help.

I am completely new to Python, and I tried different things to achieve what is required (mostly using groupby()) but everything has failed so far.

I have a data frame that includes multiple transactions in different currencies for the same day (116200 rows):

Index Account No Withdrawal AMT Deposit AMT Dates Currency
0 12345567 100 300 2015-01-01 eur
1 12345567 100 300 2015-01-01 usd
2 12345567 100 300 2015-01-01 gbp
3 12345567 100 300 2015-01-01 eur
4 34334123 100 300 2015-01-02 usd
5 34334123 100 300 2015-01-02 gbp

I have two separate data frames with the exchanges rates for each day (one for EUR to GBP and one for USD to GBP):

Index EURO-GBP Dates
0 1.634 2015-01-01
1 1.6676 2015-01-02
2 1.4554 2015-01-03
3 1.23455 2015-01-04
Index USD-GBP Dates
0 0.934 2015-01-01
1 0.943 2015-01-02
2 0.834 2015-01-03
3 0.945 2015-01-04

First, I need to figure out a way to convert the first values of the data frame to GBP. As you have noticed each day includes transactions in different currencies, so any tip on how to do that would be more than appreciated!

Then, I want to create a data frame with just one day per row for the same day, i.e., merge each row with the corresponding daily sum of Withdrawal and deposit columns:

Index Withdrawal AMT Deposit AMT Dates Currency
0 1000 600 2015-01-01 GBP
1 3000 500 2015-01-02 GBP
2 2000 700 2015-01-03 GBP

Thank you again for taking the time to read my post!

P.S. All numbers are random!

CodePudding user response:

You can do it as follows:

(Assuming your main dataframe is named df1, the exchange rate dataframes are df_xr_eur and df_xr_usd):

# Split the main dataframe by currency
df1_eur = df1[df1['Currency'] == 'eur'].copy()
df1_usd = df1[df1['Currency'] == 'usd'].copy()
df1_gbp = df1[df1['Currency'] == 'gbp'].copy()

# Calculate GBP equivalent of currency values
df1_eur['Withdrawal AMT'] *= df1_eur['Dates'].map(df_xr_eur.set_index('Dates')['EURO-GBP'])
df1_eur['Deposit AMT'] *= df1_eur['Dates'].map(df_xr_eur.set_index('Dates')['EURO-GBP'])

df1_usd['Withdrawal AMT'] *= df1_usd['Dates'].map(df_xr_usd.set_index('Dates')['USD-GBP'])
df1_usd['Deposit AMT'] *= df1_usd['Dates'].map(df_xr_usd.set_index('Dates')['USD-GBP'])

# Assemble the previously split datrframes after exchange rate calculation
df2 = pd.concat([df1_eur, df1_usd, df1_gbp]).assign(Currency='GBP')

# Aggregate by `Dates`
df_final = df2.groupby('Dates').agg({'Withdrawal AMT': 'sum',
                                     'Deposit AMT': 'sum',
                                     'Currency': 'first'
                                    }).reset_index()

Result:

print(df_final)


        Dates  Withdrawal AMT  Deposit AMT Currency
0  2015-01-01           520.2       1560.6      GBP
1  2015-01-02           194.3        582.9      GBP
  • Related