chris chris - 3 months ago 19
Python Question

Multiplying every column of one pandas df with another df

I'm trying to multiply two pandas dataframes with each other. Specifically, I want to multiply every column with every column of the other df.

The dataframes are one-hot encoded, so they look like this:

col_1, col_2, col_3, ...
0 1 0
1 0 0
0 0 1
...


I could just iterate through each of the columns using a for loop, but in python that is computationally expensive, and I'm hoping there's an easier way.

One of the dataframes has 500 columns, the other has 100 columns.

This is the fastest version that I've been able to write so far:

interact_pd = pd.DataFrame(index=df_1.index)
df1_columns = [column for column in df_1]
for column in df_2:
col_pd = df_1[df1_columns].multiply(df_2[column], axis="index")
interact_pd = interact_pd.join(col_pd, lsuffix='_' + column)


I iterate over each column in df_2 and multiply all of df_1 by that column, then I append the result to interact_pd. I would rather not do it using a for loop however, as this is very computationally costly. Is there a faster way of doing it?

EDIT: example

df_1:

1col_1, 1col_2, 1col_3
0 1 0
1 0 0
0 0 1


df_2:

2col_1, 2col_2
0 1
1 0
0 0


interact_pd:

1col_1_2col_1, 1col_2_2col_1,1col_3_2col_1, 1col_1_2col_2, 1col_2_2col_2,1col_3_2col_2

0 0 0 0 1 0
1 0 0 0 0 0
0 0 0 0 0 0

Answer

You can use numpy.

Consider this example code, I did modify the variable names, but Test1() is essentially your code. I didn't bother create the correct column names in that function though:

import pandas as pd
import numpy as np

A = [[1,0,1,1],[0,1,1,0],[0,1,0,1]]
B = [[0,0,1,0],[1,0,1,0],[1,1,0,0],[1,0,0,1],[1,0,0,0]]

DA = pd.DataFrame(A).T
DB = pd.DataFrame(B).T

def Test1(DA,DB):
  E = pd.DataFrame(index=DA.index)
  DAC = [column for column in DA]
  for column in DB:
    C = DA[DAC].multiply(DB[column], axis="index")
    E = E.join(C, lsuffix='_' + str(column))
  return E

def Test2(DA,DB):
  MA = DA.as_matrix()
  MB = DB.as_matrix()
  MM = np.zeros((len(MA),len(MA[0])*len(MB[0])))
  Col = []
  for i in range(len(MB[0])):
    for j in range(len(MA[0])):
      MM[:,i*len(MA[0])+j] = MA[:,j]*MB[:,i]
      Col.append('1col_'+str(i+1)+'_2col_'+str(j+1))
  return pd.DataFrame(MM,dtype=int,columns=Col)

print Test1(DA,DB)
print Test2(DA,DB)

Output:

   0_1  1_1  2_1  0  1  2  0_3  1_3  2_3  0  1  2  0  1  2
0    0    0    0  1  0  0    1    0    0  1  0  0  1  0  0
1    0    0    0  0  0  0    0    1    1  0  0  0  0  0  0
2    1    1    0  1  1  0    0    0    0  0  0  0  0  0  0
3    0    0    0  0  0  0    0    0    0  1  0  1  0  0  0
   1col_1_2col_1  1col_1_2col_2  1col_1_2col_3  1col_2_2col_1  1col_2_2col_2  \
0              0              0              0              1              0   
1              0              0              0              0              0   
2              1              1              0              1              1   
3              0              0              0              0              0   

   1col_2_2col_3  1col_3_2col_1  1col_3_2col_2  1col_3_2col_3  1col_4_2col_1  \
0              0              1              0              0              1   
1              0              0              1              1              0   
2              0              0              0              0              0   
3              0              0              0              0              1   

   1col_4_2col_2  1col_4_2col_3  1col_5_2col_1  1col_5_2col_2  1col_5_2col_3  
0              0              0              1              0              0  
1              0              0              0              0              0  
2              0              0              0              0              0  
3              0              1              0              0              0  

Performance of your function:

%timeit(Test1(DA,DB))
100 loops, best of 3: 11.1 ms per loop

Performance of my function:

%timeit(Test2(DA,DB))
1000 loops, best of 3: 464 ┬Ás per loop

It's not beautiful, but it's efficient.