Panda multiply dataframes using dictionary to map columns-CodePudding

I am looking to multiply element-wise two dataframes with matching indices, using a dictionary to map which columns to multiply together. I can only come up with convoluted ways to do it and I am sure there is a better way, really appreciate the help! thx!

df1:

Index	ABC	DEF	XYZ
01/01/2004	1	2	3
05/01/2004	4	7	2

df2:

Index	Echo	Epsilon
01/01/2004	5	10
05/01/2004	-1	-2

Dictionary d = {'ABC': 'Echo', 'DEF': 'Echo', 'XYZ': 'Epsilon'}

Expected result:

Index	ABC	DEF	XYZ
01/01/2004	5	10	30
05/01/2004	-4	-7	-4

CodePudding user response：

You can use:

# only if not already the index 
df1 = df1.set_index('Index')
df2 = df2.set_index('Index')

df1.mul(df2[df1.columns.map(d)].set_axis(df1.columns, axis=1))

or:

df1.mul(df2.loc[df1.index, df1.columns.map(d)].values)

output:

            ABC  DEF  XYZ
Index                    
01/01/2004    5   10   30
05/01/2004   -4   -7   -4