combine a Data frame to specific Row Value (for n Rows, append a Data frame)

Question

I have 2 data frame, i need to combine like below

This is my Data frame 1

This is my Data frame 2

i want my output as below :

Potentially it might be like this https://stackoverflow.com/questions/13269890/cartesian-product-in-pandas. — Anshuman Kumar, Jul 29 '19 at 10:11
Yes. Cartesian product concept worked for me after converting Column into a list. Thanks — Shiv948, Jul 29 '19 at 10:19

help-ukraine-now · Accepted Answer · 2019-07-29T10:37:29.833

4

index = pd.MultiIndex.from_product(
    [df1['symbol'], df2['symbol']], 
    names=['col1', 'col2'])

pd.DataFrame(index=index).reset_index()

    col1    col2
0   L160    K-AL
1   L160    K-PL
2   L170    K-AL
3   L170    K-PL
4   L180    K-AL
5   L180    K-PL

Edit:

even better now thanks to @Andy L.:

pd.MultiIndex.from_product([df1['symbol'], df2['symbol']], names=['col1', 'col2']) \
    .to_frame(index=False)

edited Jul 29 '19 at 10:37

answered Jul 29 '19 at 10:17

help-ukraine-now

3,850
4
19
36

2

you may use `to_frame` to chain it `pd.MultiIndex.from_product([df1['symbol'], df2['symbol']], names=['col1', 'col2']).to_frame(index=False)` – Andy L. Jul 29 '19 at 10:32
1

@AndyL. Thanks for this solution – Shiv948 Jul 29 '19 at 10:42

score 3 · Answer 2 · answered Jul 29 '19 at 10:15

Assign an artificial key column and merge on it to get all the matches:

df1.assign(key=1).merge(df2.assign(key=1), on='key', suffixes=['_1', '_2']).drop('key', axis=1)

Output

  symbol_1 symbol_2
0     L160     K-AL
1     L160     K-PL
2     L170     K-AL
3     L170     K-PL
4     L180     K-AL
5     L180     K-PL

score 2 · Answer 3 · answered Jul 29 '19 at 10:23

df1 = DataFrame({'key':[1,1,1],'symbol':['L160','L170','L180']})
df2 = DataFrame({'key':[1,1],'symbol':['K-PL','K-AL']})
merge(df1, df2,on='key')[['col1', 'col2']]

Basically its a cartesian product. LINK

OUTPUT

  key symbol_x symbol_y
0    1     L160     K-PL
1    1     L160     K-AL
2    1     L170     K-PL
3    1     L170     K-AL
4    1     L180     K-PL
5    1     L180     K-AL

virgilus · Answer 4 · 2019-07-31T13:54:23.120

Using Numpy can also work:

import numpy as np
import pandas as pd

df1 = pd.DataFrame(['L160', 'L170', 'L180'],columns=['symbol'])
df2 = pd.DataFrame(['K-AL', 'K-PL'],columns=['symbol'])

np.transpose([np.tile(df1['symbol'], len(df2['symbol'])), np.repeat(df2['symbol'], len(df1['symbol']))])

Or directly in a Pandas dataframe:

pd.DataFrame(np.transpose([np.tile(df1['symbol'], len(df2['symbol'])), np.repeat(df2['symbol'], len(df1['symbol']))]))

combine a Data frame to specific Row Value (for n Rows, append a Data frame)

4 Answers4