如何在Python DataFrame中替换文本字符并填充不同缺失值

核心思路 #

先区分home_city_#和home_number_#两类列，分别处理字符串替换和缺失值填充，利用Pandas的字符串方法和缺失值填充方法完成需求。

完整代码 #

# 先复制原数据，避免修改原始数据集
customer_home_city_json_2 = customer_home_city_json_1.copy()

# 1. 处理home_city列：替换空格、逗号为下划线
# 筛选所有home_city开头的列
city_cols = [col for col in customer_home_city_json_2.columns if 'home_city' in col]
# 用正则匹配空格或逗号，统一替换为下划线
customer_home_city_json_2[city_cols] = customer_home_city_json_2[city_cols].apply(
    lambda x: x.str.replace(r'[ ,]', '_', regex=True)
)

# 2. 填充缺失值
# 填充home_city列的缺失值为'm'
customer_home_city_json_2[city_cols] = customer_home_city_json_2[city_cols].fillna('m')

# 处理home_number列：填充缺失值为-1
number_cols = [col for col in customer_home_city_json_2.columns if 'home_number' in col]
customer_home_city_json_2[number_cols] = customer_home_city_json_2[number_cols].fillna(-1)

# 可选：将home_number列转为整数类型（原数据是浮点型，按需选择）
customer_home_city_json_2[number_cols] = customer_home_city_json_2[number_cols].astype(int)

原代码问题分析 #

• 仅替换逗号未处理空格：原代码replace(',', '_')只替换了逗号，没处理字符串中的空格，无法得到Sioux_Falls__SD这种格式。
• 错误处理缺失值：Pandas中的缺失值是NaN（数值型）或pd.NA（字符串型），不是字符串'null'，用replace('null', '-1')完全无效，必须用fillna()方法针对性填充。

效果验证 #

处理后你的示例数据会变为：

home_city_1          home_number_1  home_city_2            home_number_2  home_city_3        home_number_3  home_city_4          home_number_4
Coeur_D_Alene__ID    13            Hayden__ID             8               Renton__WA         2               m                    -1
Spokane__WA          3             Amber__WA              2               m                  -1              m                    -1
Sioux_Falls__SD      9             Stone_Mountain__GA     2               Watertown__SD      2               Dell_Rapids__SD      2
Ludowici__GA         11            m                     -1              m                  -1              m                    -1

内容的提问来源于stack exchange，提问作者Peter Park