Merge two pandas dataframes but print all possible matches-CodePudding

I have two pandas dfs that looks like:


NAME        GROUP
Thomas      1
Jan         1
Thomas      2
Marco       2
Antonio     3
Julio       3
Thomas      3


NAME        STREET
Thomas      Pine
Jan         Park
Thomas      Main
Marco       Fifth
Antonio     Sixth
Julio       Crown
Thomas      King

Desired output:


NAME        GROUP      STREET
Thomas      1          Pine,Main,King
Jan         1          Park
Thomas      2          Pine,Main,King
Marco       2          Fifth
Antonio     3          Sixth
Julio       3          Crown
Thomas      3          Pine,Main,King

So essentially I would want to merge the two dataframes on “NAME” but I would want to have it print all the possible combinations for street.

What I tried is:

df3 = pd.merge(df1, df2, on = 'NAME’)

But this gives me just:


NAME        GROUP      STREET
Thomas      1          Pine
Jan         1          Park
Thomas      2          Pine
Marco       2          Fifth
Antonio     3          Sixth
Julio       3          Crown
Thomas      3          King

Is it possible at all to get the output I want using the merge? I have tried reading the documentation but couldn’t get to the right output…

CodePudding user response：

We can do groupby before merge

out = df1.merge(df2.groupby('NAME',as_index=False)[['STREET']].agg(','.join),how='left')
Out[374]: 
      NAME  GROUP          STREET
0   Thomas      1  Pine,Main,King
1      Jan      1            Park
2   Thomas      2  Pine,Main,King
3    Marco      2           Fifth
4  Antonio      3           Sixth
5    Julio      3           Crown
6   Thomas      3  Pine,Main,King