CODEWITHSUNDEEP
pythonpandas-groupbycartesian-product
msksantosh
msksantosh

Reputation: 399

Cartesian Product on Pandas Groupby

I have a dataframe

df:

col1    col2    col3
JHGK    B       name1
JHGK    B       name2
JHGK    C       name3
OPDR    B       name4
ERTH    B       name5
ERTH    C       name6

After doing a groupby on col1, I would like to do cartesian product between( distinct col3 values where col2 = B and distinct col3 values where col2 = C)

Result dataframe:

final:

col1   n1     n2
JHGK   name1  name3
JHGK   name2  name3
ERTH   name5  name6

Upvotes: 0

Views: 993

Answers (1)

Scratch'N'Purr
Scratch'N'Purr

Reputation: 10429

Take subset of df where col2 is B, and take subset of df where col2 is C. Then do a join on col1, drop some extra columns, and rename. :)

>>> import pandas as pd
>>> df = pd.DataFrame({'col1': ['JHGK','JHGK','JHGK','OPDR','ERTH','ERTH'], 'col2': ['B','B','C','B','B','C'], 'col3': ['name1','name2','name3','name4','name5','name6']})
>>> df
   col1 col2   col3
0  JHGK    B  name1
1  JHGK    B  name2
2  JHGK    C  name3
3  OPDR    B  name4
4  ERTH    B  name5
5  ERTH    C  name6
>>> df_joined = df[df['col2'] == 'B'].merge(df[df['col2'] == 'C'], on='col1').drop(['col2_x', 'col2_y'], axis=1).rename(columns={'col3_x': 'n1', 'col3_y': 'n2'})
>>> df_joined
   col1     n1     n2
0  JHGK  name1  name3
1  JHGK  name2  name3
2  ERTH  name5  name6

Upvotes: 3

Related Questions