How to fill Null values in a Pandas DataFrame based on a value from a different column?

So I'm currently working for a portfolio project and I have a large Dataframe with (among many other columns) two columns, one for the "neighbourhood group" and the other for the "neighbourhood". The thing is in the first column there are some NaN values. And I need to find out how to fill those with the respective city area.

Example:

resident   neighbourhood group    neighbourhood
John       Brooklyn               Clinton Hill
Mae        Brooklyn               Clinton Hill
Richard    Manhattan              East Harlem
Clark      Manhattan              Upper West Side
...
Claire          NaN               Clinton Hill
Susan           NaN               East Harlem

This is just a simple example. I have hundreds of these cases impossible to manually change so many of them. Any idea how to assigned the proper area to the right neighbourhood?

I've tried to no avail to build different functions to fixed the issue, but I can't manage to wrap my head around it.

I recommend to build a reference dictionary to match the neighbourhood to the neighbourhood group. Assuming this is the original dataframe:

import pandas as pd

data = {'resident': {0: 'John', 1: 'Mae', 2: 'Richard', 3: 'Clark', 4: 'Claire', 5: 'Susan'}, 'neighbourhoodgroup': {0: 'Brooklyn', 1: 'Brooklyn', 2: 'Manhattan', 3: 'Manhattan', 4: None, 5: None}, 'neighbourhood': {0: 'ClintonHill', 1: 'ClintonHill', 2: 'EastHarlem', 3: 'UpperWestSide', 4: 'ClintonHill', 5: 'EastHarlem'}}
df = pd.DataFrame(data)

'''
  resident neighbourhoodgroup  neighbourhood
0     John           Brooklyn    ClintonHill
1      Mae           Brooklyn    ClintonHill
2  Richard          Manhattan     EastHarlem
3    Clark          Manhattan  UpperWestSide
4   Claire               None    ClintonHill
5    Susan               None     EastHarlem
'''

First create a reference dictionary reference with key from 'neighbourhood' column and value from the 'neighbourhoodgroup' column.

df_ref = df.dropna().drop_duplicates(['neighbourhoodgroup', 'neighbourhood'])
reference = {}
for k, v in list(zip(df_ref.neighbourhood, df_ref.neighbourhoodgroup)):
    reference[k] = v

'''
{'ClintonHill': 'Brooklyn',
 'EastHarlem': 'Manhattan',
 'UpperWestSide': 'Manhattan'}
'''

Next, apply the dictionary reference to the dataframe

df['result'] = df.neighbourhood.apply(lambda x: reference[x])
print(df)

'''
  resident neighbourhoodgroup  neighbourhood     result
0     John           Brooklyn    ClintonHill   Brooklyn
1      Mae           Brooklyn    ClintonHill   Brooklyn
2  Richard          Manhattan     EastHarlem  Manhattan
3    Clark          Manhattan  UpperWestSide  Manhattan
4   Claire               None    ClintonHill   Brooklyn
5    Susan               None     EastHarlem  Manhattan
'''

  resident neighbourhoodgroup  neighbourhood
0     John           Brooklyn    ClintonHill
1      Mae           Brooklyn    ClintonHill
2  Richard          Manhattan     EastHarlem
3    Clark          Manhattan  UpperWestSide
4   Claire               <NA>    ClintonHill
5    Susan               <NA>     EastHarlem

We can make a lookup from neighbourhood to neighbourhoodgroup by dropping duplicates and NaNs, and converting to a dictionary:

lookup_df = df[['neighbourhoodgroup','neighbourhood']].drop_duplicates().dropna()
lookup_dict = {x.neighbourhood: x.neighbourhoodgroup for x in lookup_df.itertuples()}

{'ClintonHill': 'Brooklyn', 'EastHarlem': 'Manhattan', 'UpperWestSide': 'Manhattan'}

Then we match the neighbourhoods to the neighbourhoodgroups using DataFrame.replace():

neighbourhood_fill = df.neighbourhood.replace(lookup_dict)

Then we replace the NaNs in the neighbourhoodgroup column with our new fill values, using fillna:

df.neighbourhoodgroup.fillna(neighbourhood_fill, inplace=True)

  resident neighbourhoodgroup  neighbourhood
0     John           Brooklyn    ClintonHill
1      Mae           Brooklyn    ClintonHill
2  Richard          Manhattan     EastHarlem
3    Clark          Manhattan  UpperWestSide
4   Claire           Brooklyn    ClintonHill
5    Susan          Manhattan     EastHarlem

Another possible solution:

d = df.iloc[:, 1:].dropna().drop_duplicates()

(df.update(
    df['neighbourhood'][df['neighbourhood group'].isna()]
    .map(dict(zip(d.iloc[:,1], d.iloc[:,0])))
    .rename('neighbourhood group')))

Output:

  resident neighbourhood group    neighbourhood
0     John            Brooklyn     Clinton Hill
1      Mae            Brooklyn     Clinton Hill
2  Richard           Manhattan      East Harlem
3    Clark           Manhattan  Upper West Side
4   Claire            Brooklyn     Clinton Hill
5    Susan           Manhattan      East Harlem