how to convert income from one currency into another using a histroical fx table in python with pand-CodePudding

I have two dataframes, one is a income df and the other is a fx df. my income df shows income from different accounts on different dates but it also shows extra income in a different currency. my fx df shows the fx rates for certain currency pairs on the same date the extra income came into the accounts.

I want to convert the currency of the extra income into the same currency as the account so for example, account HP on 23/3 has extra income = 35 GBP, i want to convert that into EUR as that's the currency of the account. Please note it has to use the fx table as i have a long history of data points to fill and other accounts so i do not want to manually code 35 * the fx rate. Finally i then want to create another column for income df that will sum the daily income extra income in the same currency together

im not sure how to bring both df together so i can get the correct fx rate for that sepecifc date to convert the currency of the extra income into the currency of the account

my code is below

import pandas as pd

income_data = {'date': ['23/3/22', '23/3/22', '24/3/22', '25/3/22'], 'account': ['HP', 'HP', 'JJ', 'JJ'],
        'daily_income': [1000, 1000, 2000, 2000], 'ccy of account': ['EUR', 'EUR', 'USD', 'USD'],
        'extra_income': [50, 35, 10, 12.5], 'ccy of extra_income': ['EUR', 'GBP', 'EUR', 'USD']}

income_df = pd.DataFrame(income_data)

fx_data = {'date': ['23/3/22', '23/3/22', '24/3/22', '25/3/22'], 'EUR/GBP': [0.833522, 0.833522, 0.833621, 0.833066],
        'USD/EUR': [0.90874, 0.90874, 0.91006, 0.90991]}

fx_df = pd.DataFrame(fx_data)

the final df should look like this (i flipped the fx rate so 1/0.833522 to get some of the values)

Would really appreicate if someone could help me with this. my inital thpought was merge but i dont have a common column and not sure if map function would work either as i dont have a dictionary. apologies in advance if any of my code is not greate - i am still self learning, thanks!

CodePudding user response：

Consider creating a common column for merging in both data frames. Below uses assign to add columns and Series operators (over arithmetic ones: , -, *, /).

# ADD NEW COLUMN AS CONCAT OF CCY COLUMNS
income_data = income_data.assign(
    currency_ratio = lambda df: df["ccy of account"]   "/"   df["ccy of extra_income"]
)

# ADD REVERSED CURRENCY RATIOS
# RESHAPE WIDE TO LONG FORMAT
fx_data_long = pd.melt(
    fx_data.assign(**{
        "GBP/EUR": lambda df: df["EUR/GBP"].div(-1),
        "EUR/USD": lambda df: df["USD/EUR"].div(-1)
    }),
    id_vars = "date",
    var_name = "currency_ratio",
    value_name = "fx_rate"
)

# MERGE AND CALCULATE
income_data = (
    income_data.merge(
        fx_data_long,
        on = ["date", "currency_ratio"],
        how = "left"
    ).assign(
        total_income = lambda df: df["daily_income"].add(df["extra_income"].mul(df["fx_rate"]))
    )
)