CODEWITHSUNDEEP
pythonpandas
Sanoj
Sanoj

Reputation: 1497

Merge on pandas data frames with multiple values

I have pandas data frames like: 

_data_orig = [
        [1, 3.2],
        [3, 3.9],
        [4, 1.2],
        [5, 2.2]
    ]
_columns1 = ["ID", "GPA"]

_data_new = [
    [1, "Bob"],
    [2, "Sam"],
    [3, "Jane"],
    [3, "Sanoj"]
]
_columns2 = ["ID", "Name"]


df_orig = pd.DataFrame(data=_data_orig, columns=_columns1)
df_new = pd.DataFrame(data=_data_new, columns=_columns2)

When I do:

df_merge = pd.merge(df_orig, df_new, how='left')

I get:

    ID  GPA Name
0   1   3.2 Bob
1   3   3.9 Jane
2   3   3.9 Sanoj
3   4   1.2 NaN
4   5   2.2 NaN

You can see that ID: 3 is repeated. I want in this format so that ID:3 is not repeated from df_orig:

    ID  GPA Name    Name_1
0   1   3.2 Bob 
1   3   3.9 Jane    Sanoj
2   4   1.2 NaN 
4   5   2.2 NaN

Upvotes: 1

Views: 125

Answers (2)

Parfait
Parfait

Reputation: 107697

Consider pivot off a groupby().cumcount with merge:

df_new['IDcount'] = "Name_" + (df_new.groupby("ID").cumcount() + 1).astype(str)
df_wide = df_new.pivot(index="ID", columns="IDcount", values="Name").reset_index()

df_merge = pd.merge(df_orig, df_wide, on='ID', how='left')

#    ID  GPA Name_1 Name_2
# 0   1  3.2    Bob   None
# 1   3  3.9   Jane  Sanoj
# 2   4  1.2    NaN    NaN
# 3   5  2.2    NaN    NaN

Upvotes: 1

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210932

Try this:

let's create the following helper DF

In [279]: x = (df_new.groupby('ID')['Name']
     ...:            .apply(';'.join)
     ...:            .str.split(';', expand=True)
     ...:            .add_prefix('Name_')
     ...:            .reset_index())
     ...:

In [280]: x
Out[280]:
   ID Name_0 Name_1
0   1    Bob   None
1   2    Sam   None
2   3   Jane  Sanoj

now we can simply merge it with the df_orig DF

In [281]: pd.merge(df_orig, x, how='left').fillna('')
     ...:
Out[281]:
   ID  GPA Name_0 Name_1
0   1  3.2    Bob
1   3  3.9   Jane  Sanoj
2   4  1.2
3   5  2.2

Upvotes: 1

Related Questions