How to multiply dataframe by row-CodePudding

Hello I am trying to multiply a dataframe with a row from another dataframe.

`df1`

Date	ABC	DEF	XYZ
2020-01-31	3	4	2
2020-02-31	1	3	5
2020-03-31	4	2	6
2020-04-31	2	2	7

`df2`

Year	ABC	DEF	XYZ
2020	.5	.4	.3

Is there a way to multiple DF2 across all values of DF1 to get DF3 like below.

`df3`

Date	ABC	DEF	XYZ
2020-01-31	1.5	1.6	.6
2020-02-31	.5	1.2	1.5
2020-03-31	2	.8	1.8
2020-04-31	1	.8	2.1

I have tried

df3.loc[:,['ABC','DEF','XYZ']] = df1.mul(df2, level=1,axis='columns')

But with that I end up with df3 being full of NaN.

CodePudding user response：

cols = ['ABC', 'DEF', 'XYZ']
df3 = df1.copy()
df3[cols] = df3[cols].mul(df2[cols].values)

Output:

>>> df3
          Date  ABC  DEF  XYZ
0  2020-01-31   1.5  1.6  0.6
1  2020-02-31   0.5  1.2  1.5
2  2020-03-31   2.0  0.8  1.8
3  2020-04-31   1.0  0.8  2.1

The trick here is to multiply not by df2[cols], but df2[cols].values.

CodePudding user response：

df1.update(pd.DataFrame(df1.set_index('Date').values*df2.set_index('Year').values,columns=df1.iloc[:,-3:].columns))

output

        Date  ABC  DEF  XYZ
0  2020-01-31  1.5  1.6  0.6
1  2020-02-31  0.5  1.2  1.5
2  2020-03-31  2.0  0.8  1.8
3  2020-04-31  1.0  0.8  2.1

How it works

df1.set_index('Date').values gives you an array, say array a

df2.set_index('Year').values gives you array b

pd.DataFrame(a*b, columns=,columns=df1.iloc[:,-3:].columns) gives you a new df

df1.update(new df), updates original df

CodePudding user response：

you have to copy df1 before multipling the columns abc,def,xyz by df2. update the values of those columns with the multiplied value

df1="""
Date    ABC DEF XYZ
2020-01-31  3   4   2
2020-02-31  1   3   5
2020-03-31  4   2   6
2020-04-31  2   2   7
"""
df2="""
Year    ABC DEF XYZ
2020    .5  .4  .3
"""

def create_df(df):
    df=df.strip().split('\n')
    df=list(map(lambda x: x.strip().split('\t'),df))
    df=pd.DataFrame(df[1:],columns=df[0])
    return df

df1=create_df(df1)
df1['ABC']=df1['ABC'].astype(float)
df1['DEF']=df1['DEF'].astype(float)
df1['XYZ']=df1['XYZ'].astype(float)
df2=create_df(df2)
df2['ABC']=df2['ABC'].astype(float)
df2['DEF']=df2['DEF'].astype(float)
df2['XYZ']=df2['XYZ'].astype(float)

cols = ['ABC', 'DEF', 'XYZ']
df3 = df1.copy()
df3[cols]=df1[cols].mul(df2[cols].values)
print(df3)

output

         Date  ABC  DEF  XYZ
0  2020-01-31  1.5  1.6  0.6
1  2020-02-31  0.5  1.2  1.5
2  2020-03-31  2.0  0.8  1.8
3  2020-04-31  1.0  0.8  2.1