How to get a scalar product of rows in dataframe with matching indexes

user_17264115 0
Viewed 24

Let's say I have two dataframes with same columns, first one has unique index, second has not unique index,

      column1  column2
a        1       2
b        4       5
c        3       3

      column1  column2
a        1       2
a        4       5
c        3       3
b        1       2
b        4       5
a        3       3

Now how can I make a scalar product of rows where index match, the result would be a dataframe with one column (with values of scalar product, for example first row: 1*1+2*2=5) and index as in second dataframe:

      result
a        5  
a        14 
c        18  
b        14 
b        41  
a        9
pandas python
1 Answers

Multiple and then sum DataFrames:

df = df2.mul(df1).sum(axis=1).to_frame('result')
print (df)
   result
a       5
a      14
a       9
b      14
b      41
c      18

If ordering is important in ouput:

df = (df2.assign(a=range(len(df2)))
         .set_index('a', append=True)
         .mul(df1, level=0)
         .sum(axis=1)
         .droplevel(1)
         .to_frame('result'))
print (df)
   result
a       5
a      14
c      18
b      14
b      41
a       9
user_29010020
Related