Multiplying series across two dataframes via a lookup table (third dataframe)-CodePudding

I have stock prices in a dataframe called 'stock_data' as hown here:

stock_data = pd.DataFrame(np.random.rand(5,4)*100, index=pd.date_range(start='1/1/2022', periods=5),columns = "A B C D".split())
stock_data

    A   B   C   D
2022-01-01  50.499862   65.011650   91.563112   45.107004
2022-01-02  53.218393   86.534942   54.575897   28.154673
2022-01-03  96.827564   49.782633   19.894127   47.529094
2022-01-04  18.226396   27.908952   67.141263   66.101363
2022-01-05  1.061750    29.833253   94.161190   85.542529

I have currency exchange rates here in a series called 'currency_list'. Note the index is same as column names in stock_data for reference

currency_list=pd.Series(['USD','CAD','EUR','CHF'], index="A B C D".split())
currency_list

A    USD
B    CAD
C    EUR
D    CHF
dtype: object

I have currency exchange rates here in a dataframe called 'forex_data'

Forex_data=pd.DataFrame(np.random.rand(5,3), index=pd.date_range(start='1/1/2022', periods=5),columns = "USD CAD EUR".split())
Forex_data

    USD CAD EUR
2022-01-01  0.194238    0.996759    0.900205
2022-01-02  0.366476    0.054540    0.474838
2022-01-03  0.709269    0.723097    0.655717
2022-01-04  0.557701    0.878100    0.824146
2022-01-05  0.865796    0.432785    0.222463

Now I want to convert the prices to my base currency (let's say CHF) by the following logic -

2022-05-01 price of stock A is 50.499*0.194 , and so forth.

I am just stuck and don't know what to do - could someone help?

CodePudding user response：

Example

import numpy as np
df1 = pd.DataFrame(np.random.randint(5, 20,(5,4)), index=pd.date_range(start='1/1/2022', periods=5),columns = list("ABCD"))
s1 = pd.Series(['USD','CAD','EUR','CHF'], index=list("ABCD"))
df2 = pd.DataFrame(np.random.randint(10,40, (5, 3)) / 10, index=pd.date_range(start='1/1/2022', periods=5),columns = "USD CAD EUR".split())

df1

            A   B   C   D
2022-01-01  8   19  6   12
2022-01-02  15  8   18  6
2022-01-03  9   11  14  17
2022-01-04  17  13  17  17
2022-01-05  11  12  10  19

s1

A    USD
B    CAD
C    EUR
D    CHF
dtype: object

df2

            USD CAD EUR
2022-01-01  2.7 1.0 1.4
2022-01-02  3.6 3.1 1.2
2022-01-03  2.7 2.1 1.0
2022-01-04  3.8 2.4 3.6
2022-01-05  2.0 1.6 3.6

Code

mapping columns of df1 and use mul

out  = df1.set_axis(df1.columns.map(s1), axis=1).mul(df2).reindex(df2.columns, axis=1)

out

            USD     CAD     EUR
2022-01-01  21.6    19.0    8.4
2022-01-02  54.0    24.8    21.6
2022-01-03  24.3    23.1    14.0
2022-01-04  64.6    31.2    61.2
2022-01-05  22.0    19.2    36.0