Python 如何根据不同列中的值填充 Pandas DataFrame 中的空值

发布于07月20日

因此，我目前正在为一个项目组合项目工作，我有一个大型的Dataframe(在许多其他列中)，它有两个列，一个用于"邻居组"，另一个用于"邻居".问题是，在第一列中有大约NaN个值.我需要找出如何用各自的城市区域填充这些区域.

示例:

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

这只是一个简单的例子.我有成百上千个这样的 case ，不可能手动更换这么多.你知道怎么把合适的区域分配给合适的社区吗？

我试图构建不同的函数来解决这个问题，但都无济于事，但我无法理解它.

推荐答案

我建议建立一本参考词典，将邻居与邻居组相匹配.假设这是原始数据帧:

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
'''

首先创建参考词典reference，其具有来自‘Neighbhood’列的关键字和来自‘Neighborhood HoodGroup’列的值.

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'}
'''

接下来，将字典引用应用于数据帧

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
'''