How to merge dataframes to every row in a second dataframe?

user_16693387 0
Viewed 81

The array of data will come from other source, so in this example I have declared them as array and with minimal data. But the combinations of the entries in lists will be a lot more over million combinations. It's like the length of ar1 * arr2 * arr3 etc.

arr1 = [1, 2]
arr2 = [10, 20]
arr3 = [0.1, 0.2]

df1 = pd.DataFrame(arr1, columns=["col1"])
df2 = pd.DataFrame(arr2, columns=["col2"])
df3 = pd.DataFrame(arr3, columns=["col3"])

Final result of the new DataFrame should be all of posible combinations of the given arrays:

col1 col2 col3
1    10   0.1
1    10   0.2
1    20   0.1
1    20   0.2
2    10   0.1
2    10   0.2
2    20   0.1
2    20   0.2
pandas python
3 Answers
user_94414040

functools.reduce and pd.merge

Kind of slow.

import pandas as pd
from functools import reduce

reduce(
    pd.merge,
    [d.assign(dummy=1)
     for d in [df1, df2, df3]
    ]).drop('dummy', axis=1)

   col1  col2  col3
0     1    10   0.1
1     1    10   0.2
2     1    20   0.1
3     1    20   0.2
4     2    10   0.1
5     2    10   0.2
6     2    20   0.1
7     2    20   0.2

itertools.product and pd.DataFrame.itertuples

Definitely faster

import pandas as pd
from itertools import product

def tupify(d): return d.itertuples(index=False, name=None)
def sumtup(t): return sum(t, start=())

pd.DataFrame(
    list(map(sumtup, product(*map(tupify, [df1, df2, df3])))),
    columns = sum(map(list, [df1, df2, df3]), start=[])
)

   col1  col2  col3
0     1    10   0.1
1     1    10   0.2
2     1    20   0.1
3     1    20   0.2
4     2    10   0.1
5     2    10   0.2
6     2    20   0.1
7     2    20   0.2
user_23366540

expand_grid from pyjanitor is a fast implementation of cartesian product and uses np.meshgrid under the hood.

# pip install pyjanitor
import pandas as pd
import janitor as jn

# expand_grid requires a dictionary:

others = {"df1": df1, "df2": df2, "df3": df3}

jn.expand_grid(others = others).droplevel(1,1)

   col1  col2  col3
0     1    10   0.1
1     1    10   0.2
2     1    20   0.1
3     1    20   0.2
4     2    10   0.1
5     2    10   0.2
6     2    20   0.1
7     2    20   0.2

expand_grid can also be extended to cartesian product of dataframe and series, and even non pandas objects. It's end product though is a dataframe.

user_71757130
Related