Is there a better way to get the product of values from all column combinations from two dataframes?

user_7751265 0
Viewed 57

I have two dataframes :

       (A0, B0, C0)  (A1, B1, C1)  (A2, B2, C2)
Item0             6             6             4
Item1             2             3             9

       (D0, E0)  (D1, E1)
Item0         3         3
Item1         7         5

I would like to get the product between values of each column from the first dataframe with every other column from the second dataframe, like this:

       (A0, B0, C0, D0, E0)  (A0, B0, C0, D1, E1)  (A1, B1, C1, D0, E0)  (A1, B1, C1, D1, E1)  (A2, B2, C2, D0, E0)  (A2, B2, C2, D1, E1)
Item0                    18                    18                    18                    18                    12                    12
Item1                    14                    10                    21                    15                    63                    45

I have come up with the following solution using pandas.concat and itertools.product (last two lines before the last prints, everything else is to setup the dataframes):

import pandas as pd
import numpy as np
import itertools
from time import time

t=time()

nb_line=150
nb_col1=6000
nb_col2=50

ind=["Item"+str(i) for i in range(nb_line)]
col1=[("A"+str(i),"B"+str(i),"C"+str(i)) for i in range(nb_col1)]
col2=[("D"+str(i),"E"+str(i)) for i in range(nb_col2)]

df1=pd.DataFrame(np.random.randint(10, size=(nb_line, nb_col1)),index=ind,columns=col1)
df2=pd.DataFrame(np.random.randint(10, size=(nb_line, nb_col2)),index=ind,columns=col2)

print(df1)
print(df2)

df=pd.concat([df1[i[0]]*df2[i[1]] for i in itertools.product(df1.columns,df2.columns)],axis=1)
df.columns=[i[0]+i[1] for i in itertools.product(df1.columns,df2.columns)]

print(df)
print(time()-t)

This code runs in less than one minute on my machine which is acceptable but since I am new to pandas I am wondering if there is a better way to solve my problem.

pandas python
1 Answers

With the dataframes you provided, here is how your code performs on my computer:

import statistics
import time

elapsed_time = []
for _ in range(10):
    start_time = time.time()
    df = pd.concat(
        [df1[i[0]] * df2[i[1]] for i in itertools.product(df1.columns, df2.columns)],
        axis=1,
    )
    df.columns = [i[0] + i[1] for i in itertools.product(df1.columns, df2.columns)]
    elapsed_time.append(time.time() - start_time)

print(f"--- {round(statistics.mean(elapsed_time), 1):2} seconds in average ---")
# --- 39.3 seconds in average ---

Here is another way to achieve the same result, with the advantages of a code being a bit more idiomatic (thus easier to read, at least for me) and ~18% faster:

from itertools import product

elapsed_time = []
for _ in range(10):
    start_time = time.time()
    columns = list(product(df1.columns, df2.columns))
    df = pd.concat([df1[cols[0]] * df2[cols[1]].values for cols in columns], axis=1)
    df.columns = columns
    elapsed_time.append(time.time() - start_time)

print(f"--- {round(statistics.mean(elapsed_time), 1):2} seconds in average ---")
# --- 32.0 seconds in average ---
user_112460560
Related