CODEWITHSUNDEEP
pythonpython-3.xpandas
Grendel
Grendel

Reputation: 783

Merge two dataframe with groupby AND a list of names

Hel lo I have 2 dataframes such as :

Dataframe1

GroupName weigth Name
Group1 34 Baby1
Group1 43 Baby2
Group1 34 Baby3
Group2 32 Baby4
Group2 32 Baby2
Group3 33 Baby9

Dataframe2

GroupName Size ListNames
Group1 89 ['Baby1','Baby2']
Group1 87 ['Baby3']
Group2 78 ['Baby4','Baby2']
Group3 80 ['Baby9']

and I would like to merge the two dataframes by GroupName and by ListNames Here I should get for instance :

Dataframe3

GroupName weigth Name Size ListNames
Group1 34 Baby1 89 ['Baby1','Baby2']
Group1 43 Baby2 89 ['Baby1','Baby2']
Group1 34 Baby3 87 ['Baby3']
Group2 32 Baby4 78 ['Baby4','Baby2']
Group2 32 Baby2 78 ['Baby4','Baby2']
Group3 33 Baby9 80 ['Baby9']

I know we shoult use groupby 'GroupName' but I do not know how to deal with tha ListNames in pandas. Does someone have an idea? Thank you for your help.

Upvotes: 1

Views: 45

Answers (1)

jezrael
jezrael

Reputation: 862511

First create column Name with same values like ListNames and repeat rows by DataFrame.explode (pandas 0.25+), then merge together:

#if necessary convert strings to lists
import ast
#df2['ListNames'] = df2['ListNames'].apply(ast.literal_eval)

df = df1.merge(df2.assign(Name=df2['ListNames']).explode('Name'), on=['GroupName','Name'])
print (df)
  GroupName  weigth   Name  Size       ListNames
0    Group1      34  Baby1    89  [Baby1, Baby2]
1    Group1      43  Baby2    89  [Baby1, Baby2]
2    Group1      34  Baby3    87         [Baby3]
3    Group2      32  Baby4    78  [Baby4, Baby2]
4    Group2      32  Baby2    78  [Baby4, Baby2]
5    Group3      33  Baby9    80         [Baby9]

Upvotes: 1

Related Questions