Pandas - Merge on columns containing a few null values

Question

I have 2 data frames - cities and stadiums. I want to merge them

cities
     Name   Stadium
0     c1      a
1     c2     NaN
2     c3     NaN
3     c4      b

stadiums
      Name     Capacity Type Col4 Col5
0       a        1       1    1     4
1       b        3       2    2     5
2       c        3       1    1     6

Output:
     Name   Stadium Capacity Type
0     c1      a       1       1
1     c2     NaN     NaN     NaN
2     c3     NaN     NaN     NaN
3     c4      b       3       2

When I merge them with one the following statements, I get additional rows:

cities = pd.merge(cities,stadiums[['Name','Capacity','Type']],left_on='Stadium',right_on='name',how='left')

cities = pd.merge(cities,stadiums[['Name','Capacity','Type']],left_on='Stadium',right_on='name',how='outer')

How do I merge these dataframes?

ASGM · Accepted Answer · 2018-10-31 14:16:00Z

2

Your problem is that you're performing an outer merge (how='outer'), which means all the rows from both dataframes are included in the output. If you just want to match on the rows from the cities dataframe, try how='left' instead.

answered Oct 31, 2018 at 14:16

ASGM

11.5k1 gold badge37 silver badges54 bronze badges

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

Comments

BENY · Accepted Answer · 2018-10-31 14:17:08Z

0

outer to left

cities = pd.merge(cities,stadiums[['Name','Capacity','Type']],left_on='Stadium',right_on='Name',how='left')
cities
Out[157]: 
  Name_x Stadium Name_y  Capacity  Type
0     c1       a      a       1.0   1.0
1     c2     NaN    NaN       NaN   NaN
2     c3     NaN    NaN       NaN   NaN
3     c4       b      b       3.0   2.0

answered Oct 31, 2018 at 14:17

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

BENY Over a year ago

@Shankze you have duplicated in merge keys , try to remove them before merge

Collectives™ on Stack Overflow

Pandas - Merge on columns containing a few null values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related