How to fill null values in pandas dataframe, with values from another column in python?

Question

I need to fill the null values in City column based on Country column. If the country is same then it should fill the NaN value with the frequent city name corresponding to the country.

Input:

ID      City            Country
0       New York            USA
1            NaN            USA
2         London            UK
3         Mumbai            IND
4         Sydney            AUS
5            NaN            AUS
6         Sydney            AUS
7         Brisbane          AUS

Output:

ID      City            Country
0       New York            USA
1       New York            USA
2         London            UK
3         Mumbai            IND
4         Sydney            AUS
5         Sydney            AUS
6         Sydney            AUS
7         Brisbane          AUS

jezrael · Accepted Answer · 2021-11-25 17:20:55Z

2

Idea is replace possible empty strings to NaNs and then replace values of group by first non NaNs values:

df['City'] = (df.groupby('Country')['City']
                .transform('first'))

Or forward and back filling missing values:

df['City'] = (df.groupby('Country')['City']
                .transform(lambda x: x.ffill().bfill()))

edited Nov 25, 2021 at 17:20

answered Nov 25, 2021 at 15:04

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

How to fill null values in pandas dataframe, with values from another column in python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related