Skip to content
Advertisement

Pandas: How to fill nan value for column with part of value in other columns

I Want the value in city column to be filled with first word of venue column

enter image description here

I tried using df.city.fillna(value=df.venue.str.split()[0]) but it taking first row values to fill Thank you in advance

Advertisement

Answer

From your DataFrame :

>>> import pandas as pd
>>> from io import StringIO

>>> df = pd.read_csv(StringIO("""
id,city,venue
2343242,NaN,Sharjah Cricket Stadium
4354534,NaN,Dubai Internationnl Cricket Stadium
4564564,NaN,Dubai Internationnl Cricket Stadium
3454355,NaN,Sharjah Cricket Stadium
5676575,NaN,Sharjah Cricket Stadium"""))
>>> df
    id          city    venue
0   2343242     NaN     Sharjah Cricket Stadium
1   4354534     NaN     Dubai Internationnl Cricket Stadium
2   4564564     NaN     Dubai Internationnl Cricket Stadium
3   3454355     NaN     Sharjah Cricket Stadium
4   5676575     NaN     Sharjah Cricket Stadium

After the split() you used, we can use map to assign the first list element to the NaN values in the City column as expected :

>>> df['city'] = df['city'].fillna(value=df['venue'].str.split().map(lambda x: x[0]))
>>> df
    id          city        venue
0   2343242     Sharjah     Sharjah Cricket Stadium
1   4354534     Dubai       Dubai Internationnl Cricket Stadium
2   4564564     Dubai       Dubai Internationnl Cricket Stadium
3   3454355     Sharjah     Sharjah Cricket Stadium
4   5676575     Sharjah     Sharjah Cricket Stadium

EDIT:

Even shorter, thanks to @HenryEcker :

>>> df['city'] = df['city'].fillna(value=df['venue'].str.split().str[0])
>>> df
    id          city        venue
0   2343242     Sharjah     Sharjah Cricket Stadium
1   4354534     Dubai       Dubai Internationnl Cricket Stadium
2   4564564     Dubai       Dubai Internationnl Cricket Stadium
3   3454355     Sharjah     Sharjah Cricket Stadium
4   5676575     Sharjah     Sharjah Cricket Stadium
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement