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):

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

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:

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