CODEWITHSUNDEEP
pythonpandasdataframejoin
Yun Tae Hwang
Yun Tae Hwang

Reputation: 1471

how to join two dataframe with a key and duplicate the matching value to fill in

how can I join two data frames by column "ID" and fill the blanks with the matching value. Since it is complicated to explain, here is my code to show what I want for the result.

import pandas as pd    
df = pd.DataFrame({'id': [1, 1, 1, 2, 2, 3, 4, 4, 4], 'col1': [3, 0, -1, 3.4, 4, 5, 6, 7, 8]})
df2 = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6, 7, 8, 9], 'col2': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']})

Now, I want to join these two dataframes with "id" and duplicate the values in col2 to fill in the blank col2 column after join.

please help me. Thanks

Upvotes: 0

Views: 36

Answers (3)

Ala Tarighati
Ala Tarighati

Reputation: 3817

If you outer join them:

df3 = pd.merge(df, df2, on='id', how='outer')

Then you can replace NaN values in col1 by corresponding values in col2: 

df3.loc[df3.col1.isnull(), 'col1'] = df3.loc[df3.col1.isnull(), 'col2']
print(df3)

Output:

    id col1 col2
0   1   3   A
1   1   0   A
2   1   -1  A
3   2   3.4 B
4   2   4   B
5   3   5   C
6   4   6   D
7   4   7   D
8   4   8   D
9   5   E   E
10  6   F   F
11  7   G   G
12  8   H   H
13  9   I   I

Upvotes: 0

Zito Relova
Zito Relova

Reputation: 1041

Are you looking for merge?

df.merge(df2, on='id')

    id  col1    col2
0   1   3.0     A
1   1   0.0     A
2   1   -1.0    A
3   2   3.4     B
4   2   4.0     B
5   3   5.0     C
6   4   6.0     D
7   4   7.0     D
8   4   8.0     D

Upvotes: 1

jezrael
jezrael

Reputation: 863281

I believe you need map:

df['col2'] = df['id'].map(df2.set_index('id')['col2'])

Or left join with merge:

df = df.merge(df2, on='id', how='left')

print (df)
   id  col1 col2
0   1   3.0    A
1   1   0.0    A
2   1  -1.0    A
3   2   3.4    B
4   2   4.0    B
5   3   5.0    C
6   4   6.0    D
7   4   7.0    D
8   4   8.0    D

Upvotes: 0

Related Questions