python之根据另一列填写空单元格

我想根据另一列匹配/映射数据框中的缺失值。例如，

         City         State              Country 
      Chicago            IL        United States 
       Boston            MA        United States 
    San Diego             
  Los Angeles            CA        United States 
San Francisco 
   Sacramento      
    Vancouver            BC               Canada 

所以，如果我想填写与洛杉矶相同的那三个城市的省份和国家的空单元格。我该怎么办？

下面是我的代码，但我完全陷入其中。

CA_cities = ['San Diego', 'Los Angeles', 'San Francisco', 'Sacramento'] 
df.loc[df['City'] == CA_cities, 'State' = 'CA' and 'Country' = 'United States'] 

任何帮助将不胜感激。

请您参考如下方法:

您可以将 groupby 与 isin 创建的掩码一起使用，然后通过前后填充替换 NaN:

CA_cities = ['San Diego', 'Los Angeles', 'San Francisco', 'Sacramento'] 
 
df = df.groupby(df['City'].isin(CA_cities)).apply(lambda x: x.ffill().bfill()) 
print (df) 
            City State        Country 
0        Chicago    IL  United States 
1         Boston    MA  United States 
2      San Diego    CA  United States 
3    Los Angeles    CA  United States 
4  San Francisco    CA  United States 
5     Sacramento    CA  United States 
6      Vancouver    BC         Canada 

更通用的解决方案是创建城市组，例如在字典中，将 keys 与值和 map 列交换:

print (df) 
            City State        Country 
0        Chicago    IL  United States 
1       Chicago1   NaN            NaN 
2         Boston    MA  United States 
3      San Diego   NaN            NaN 
4    Los Angeles    CA  United States 
5  San Francisco   NaN            NaN 
6     Sacramento   NaN            NaN 
7      Vancouver    BC         Canada 
 
cities = {'CA': ['San Diego', 'Los Angeles', 'San Francisco', 'Sacramento'],  
          'IL':['Chicago','Chicago1']} 
d = {k: oldk for oldk, oldv in cities.items() for k in oldv} 
 
df = df.groupby(df['City'].map(d).fillna(df['City'])).apply(lambda x: x.ffill().bfill()) 
#slowier alternative 
#df = df.groupby(df['City'].replace(d)).apply(lambda x: x.ffill().bfill()) 
print (df) 
            City State        Country 
0        Chicago    IL  United States 
1       Chicago1    IL  United States 
2         Boston    MA  United States 
3      San Diego    CA  United States 
4    Los Angeles    CA  United States 
5  San Francisco    CA  United States 
6     Sacramento    CA  United States 
7      Vancouver    BC         Canada 

详细信息:

print (df['City'].map(d).fillna(df['City'])) 
0           IL 
1           IL 
2       Boston 
3           CA 
4           CA 
5           CA 
6           CA 
7    Vancouver 
Name: City, dtype: object 
 
print (d) 
{'San Diego': 'CA', 'Los Angeles': 'CA', 'San Francisco': 'CA',  
 'Sacramento': 'CA', 'Chicago': 'IL', 'Chicago1': 'IL'}