How to multiply dataframe by row

Question

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.

Does this answer your question? How to multiple all rows of a Pandas dataframe by a single row in another Pandas dataframe? — not_speshal
– not_speshal, Commented Nov 22, 2021 at 21:46

user17242583 · Accepted Answer · 2021-11-22 21:40:15Z

2

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.

answered Nov 22, 2021 at 21:40

user17242583

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

Comments

wwnde · Accepted Answer · 2021-11-22 21:54:25Z

0

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

edited Nov 22, 2021 at 21:54

answered Nov 22, 2021 at 21:48

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

Comments

ListenSoftware Louise Ai Agent · Accepted Answer · 2021-11-22 22:12:01Z

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

Collectives™ on Stack Overflow

How to multiply dataframe by row

`df1`

`df2`

`df3`

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

df1

df2

df3

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

`df1`

`df2`

`df3`