Populate column value with corresponding data from another dataframe (merge??)

Question

I have two dataframes. First:

    country city
0   Norway    a
1   Norway    a
2   Norway    a
3   Norway    b
4   Norway    b
5   Norway    b
6   Sweden    c
7   Sweden    c
8   Sweden    c
9   Sweden    c
10  Sweden    d
11  Sweden    d
12  Sweden    d

and second:

  city  region
0    a       1
1    a       2
2    b       3
3    b       4
4    c       5
5    c       6
6    c       7
7    d       8

How can I get the following dataframe? We just need to assign a correct country to the corresponding city as a new column in the second dataframe.

  country city  region
0  Norway    a       1
1  Norway    a       2
2  Norway    b       3
3  Norway    b       4
4  Sweden    c       5
5  Sweden    c       6
6  Sweden    c       7
7  Sweden    d       8

I have tried to use merge and mapping, but could not succeed.

Djaouad · Accepted Answer · 2019-09-25 12:21:47Z

2

You can create a dict that maps city names to country names, and then use that as a map for the pd.Series.map method:

df2['country'] = df2['city'].map(dict(zip(df1.city, df1.country)))

print(df2)

Output:

  city  region country
0    a       1  Norway
1    a       2  Norway
2    b       3  Norway
3    b       4  Norway
4    c       5  Sweden
5    c       6  Sweden
6    c       7  Sweden
7    d       8  Sweden

answered Sep 25, 2019 at 12:21

Djaouad

22.8k4 gold badges37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mykola Zotko · Accepted Answer · 2019-09-25 13:52:35Z

1

You can merge both dataframes, but first you need to drop duplicates in the first dataframe:

pd.merge(df1.drop_duplicates(), df2)

Output:

  country city  region
0  Norway    a       1
1  Norway    a       2
2  Norway    b       3
3  Norway    b       4
4  Sweden    c       5
5  Sweden    c       6
6  Sweden    c       7
7  Sweden    d       8

answered Sep 25, 2019 at 13:52

Mykola Zotko

18.2k7 gold badges88 silver badges91 bronze badges

1 Comment

anarz Over a year ago

That is exactly how I ended up solving the problem

abheet22 · Accepted Answer · 2019-09-25 12:21:12Z

0

Try this:

pd.merge(df1,df2, how='inner', left_on='city',right_on='city')

answered Sep 25, 2019 at 12:21

abheet22

4705 silver badges12 bronze badges

Collectives™ on Stack Overflow

Populate column value with corresponding data from another dataframe (merge??)

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related