Merge dataframes with multiple combinations in Python

Question

I am trying to merge Dataframe 1 and 2 into the expected output.

Dataframe 1

Parent  Family Name

Jack    Everdeen
Rita    Lawrence
Amy     Donnie
Eric    Everdeen
Nash    Donnie
Andrew  Paul
Lily    Donnie

Datafram 2

Child   Family Name

Vivian  Everdeen
Alex    Donnie
Rose    Evans
Annie   Paul
Hugh    Lawrence
Billy   Donnie
Marc    Paul

Expected Output

Note that "Vivian", "Alex" and "Billy" have more that 1 row because there are more than one parent who have the same family name with the child.

Child   Family Name  Possible Parent
Vivian  Everdeen     Jack
Vivian  Everdeen     Eric
Alex    Donnie       Amy
Alex    Donnie       Nash
Alex    Donnie       Lily
Rose    Evans        0
Annie   Paul         Andrew
Hugh    Lawrence     Rita
Billy   Donnie       Amy
Billy   Donnie       Nash
Billy   Donnie       Lily
Marc    Paul         Andrew

My code is as follows.

import pandas as pd

data_1 = [['Jack','Everdeen'],['Rita','Lawrence'],['Amy','Donnie'],['Eric','Everdeen'],['Nash','Donnie'],['Andrew','Paul'],['Lily','Donnie']]
df_1 = pd.DataFrame(data_1,columns=['Parent','Family Name'],dtype=float)
data_2 = [['Vivian','Everdeen'],['Alex','Donnie'],['Rose','Evans'],['Annie','Paul'],['Hugh','Lawrence'],['Billy','Donnie'],['Marc','Paul']]
df_2 = pd.DataFrame(data_2,columns=['Child','Family Name'],dtype=float)

pd.concat([df_1,df_2])

However, the output is as follows.

    Parent  Family Name Child

0   Jack    Everdeen    NaN
1   Rita    Lawrence    NaN
2   Amy     Donnie      NaN
3   Eric    Everdeen    NaN
4   Nash    Donnie      NaN
5   Andrew  Paul        NaN
6   Lily    Donnie      NaN
0   NaN     Everdeen    Vivian
1   NaN     Donnie      Alex
2   NaN     Evans       Rose
3   NaN     Paul        Annie
4   NaN     Lawrence    Hugh
5   NaN     Donnie      Billy
6   NaN     Paul        Marc

Please help me to merge the 2 dataframes into the expected output.

Why not simply `merge` on *Family Name*? – Parfait Jul 24 '22 at 03:37 — Parfait, Jul 24 '22 at 03:37

score 1 · Accepted Answer · answered Jul 24 '22 at 03:36

You could get that done with a left merge.

df_2.merge(df_1, how='left').fillna(0)

Result

     Child Family Name  Parent
0   Vivian    Everdeen    Jack
1   Vivian    Everdeen    Eric
2     Alex      Donnie     Amy
3     Alex      Donnie    Nash
4     Alex      Donnie    Lily
5     Rose       Evans       0
6    Annie        Paul  Andrew
7     Hugh    Lawrence    Rita
8    Billy      Donnie     Amy
9    Billy      Donnie    Nash
10   Billy      Donnie    Lily
11    Marc        Paul  Andrew

Merge dataframes with multiple combinations in Python

1 Answers1