perform calculation on column of a dataframe using data from another dataframe

Question

I have two dataframes, one called order_values & the other fx_rates. They look like below.

What I need to do is convert the value_loc value in the order_values dataframe into EUR's using the fx_rates dataframe.

In reality I won't know how many different currency there are & its likely to be more than shown below.

My current solution I believe is far from optimal. I loop through each different currency (i.e. USD, JPY etc) and merge that currency column (EURUSD, EURJPY) to the order_values dataframe & perform the calculation to convert the value_loc into value_eur & then drop the currency column from the order_values dataframe.

  order_values                             
  order_num   value_loc  currency   date
  1           2,345      USD        2-12-2021
  2           104        EUR        2-12-2021
  3           20,000     JPY        2-15-2021
  4           550        USD        3-06-2021
  

  fx_rates
  date       pair    rate
  2-12-2021  EURUSD  1.5
  2-12-2021  EURJPY  5
  2-12-2021  EUREUR  1
  ...
  3-06-2021  EURUSD  1.56
  3-06-2021  EURJPY  5.6
  3-06-2021  EUREUR  1

jezrael · Accepted Answer · 2022-02-08 12:15:51Z

2

You can merge by currency with dates and then multiple by new Series, which has same number of values like order_num, because used left join.

For currency helper column is stripped first 3 letters of column pair and column value_loc is converted to numeric.

df = fx_rates.assign(currency = fx_rates['pair'].str[3:])
df1 = order_values.assign(value_loc = order_values['value_loc'].str.replace(',','').astype(float))

s = df1.merge(df, how='left', on=['date','currency'])['rate']
df1['value_loc'] *= s
print (df1)
   order_num  value_loc currency       date
0          1     3517.5      USD  2-12-2021
1          2      104.0      EUR  2-12-2021
2          3        NaN      JPY  2-15-2021 <- no match create NaN
3          4      858.0      USD  3-06-2021

Details:

print (s)
0    1.50
1    1.00
2     NaN
3    1.56
Name: rate, dtype: float64

edited Feb 8, 2022 at 12:15

answered Feb 8, 2022 at 12:08

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Corralien · Accepted Answer · 2022-02-08 12:25:40Z

0

FYI, a one-line version of the solution of @jezrael:

order_values['value_loc'] = (
    order_values['value_loc'].str.replace(',', '').astype(float)
    * order_values[['date', 'currency']].merge(fx_rates.assign(currency=fx_rates['pair'].str[3:]), how='left')['rate']
)
print(order_values)

# Output
   order_num  value_loc currency       date
0          1     3517.5      USD  2-12-2021
1          2      104.0      EUR  2-12-2021
2          3        NaN      JPY  2-15-2021
3          4      858.0      USD  3-06-2021

answered Feb 8, 2022 at 12:25

Corralien

121k8 gold badges44 silver badges69 bronze badges

Comments

baines · Accepted Answer · 2022-02-08 23:03:12Z

0

I tried to reproduce the data you presented in the question.

you can find my solution below using 'pandas.Series.str'1

import pandas as pd
fx_rates = pd.DataFrame({'date':['2-12-2021','2-12-2021','2-12-2021','3-06-2021','3-06-2021','3-06-2021'],
                         'pair':['EURUSD','EURJPY','EUREUR','EURUSD','EURJPY','EUREUR'],'rate':[1.5,5,1,1.56,5.6,1]})
order_values=pd.DataFrame({'order_num':[1,2,3,4],'value_loc':[2345,104,2000,550],  'currency':['USD','EUR','JPY','USD'], 
                           'date':['2-12-2021','2-12-2021','2-15-2021','3-06-2021']})

fx_rates['pair_split']=fx_rates['pair'].str.split('EUR',expand=True)[1].replace('','EUR')
df=pd.merge(order_values,fx_rates,right_on=['date','pair_split'],left_on=['date','currency'],how='inner')

edited Feb 8, 2022 at 23:03

answered Feb 8, 2022 at 12:41

baines

313 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

perform calculation on column of a dataframe using data from another dataframe

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related