使用Pandas补全CSV中region_cd与model_cd字段缺失值的方法咨询

实现步骤与代码 #

首先补全代码中缺失的io库导入，再按照你描述的逻辑构建两个字段的匹配映射，分别补全缺失值即可：

import pandas as pd
import requests as r
# 原代码遗漏io库导入，此处补上
import io

#variables needed for ease of file access
url = 'http://drd.ba.ttu.edu/isqs3358/hw/hw2/'
file_1 = 'powergeneration.csv'

res = r.get(url + file_1)
# 校验文件请求状态
if res.status_code == 200:
    df = pd.read_csv(io.StringIO(res.text), delimiter=',')
else:
    raise Exception(f"文件请求失败，状态码：{res.status_code}")

# 1、补全model_cd缺失值：通过(region_cd, RevenueProduced)映射唯一model_cd
# 提取三个字段都非空的行去重，生成映射字典
model_map = df.dropna(subset=['region_cd', 'model_cd', 'RevenueProduced'])\
              .drop_duplicates(subset=['region_cd', 'RevenueProduced'])\
              .set_index(['region_cd', 'RevenueProduced'])['model_cd']\
              .to_dict()
# 仅填充model_cd为空的行，不覆盖已有有效值
df['model_cd'] = df.apply(
    lambda row: model_map.get((row['region_cd'], row['RevenueProduced']), row['model_cd']) 
    if pd.isna(row['model_cd']) else row['model_cd'],
    axis=1
)

# 2、补全region_cd缺失值：通过(model_cd, RevenueProduced)映射唯一region_cd
region_map = df.dropna(subset=['region_cd', 'model_cd', 'RevenueProduced'])\
               .drop_duplicates(subset=['model_cd', 'RevenueProduced'])\
               .set_index(['model_cd', 'RevenueProduced'])['region_cd']\
               .to_dict()
# 仅填充region_cd为空的行
df['region_cd'] = df.apply(
    lambda row: region_map.get((row['model_cd'], row['RevenueProduced']), row['region_cd']) 
    if pd.isna(row['region_cd']) else row['region_cd'],
    axis=1
)

# 执行后可打印查看缺失值补全效果
print("补全后各字段缺失数量统计：")
print(df.isna().sum())

逻辑说明 #

• 生成映射时仅保留三个字段都非空的行，避免无效值干扰映射规则
• 去重操作保证每组键对应唯一的目标值，符合你提到的RevenueProduced可关联确定唯一取值的前提
• 填充逻辑仅修改原本为空的字段，不会覆盖已有的有效值

内容的提问来源于stack exchange，提问作者Andrew Kestler