如何在Pandas DataFrame中去除货币符号并计算每行总和？

原始DataFrame样式：

CCD     CFO     CP3     DHC     ERS     FRO     HDI     IHI     IPI     ODF     PAE  
                       0 EGP           40 USD          210 USD  
inclu                         1500 THB                 70 INR          855 EUR
       inclu                                   60 CNY

解决方案步骤 #

1. 构建可处理的DataFrame

先把原始文本格式的数据转换为pandas DataFrame，明确空值和内容的对应位置：

import pandas as pd
import re

# 原始数据行，空值用None占位
raw_data = [
    [None, None, None, "0 EGP", None, "40 USD", None, "210 USD", None, None, None],
    ["inclu", None, None, None, "1500 THB", None, None, "70 INR", None, "855 EUR", None],
    [None, "inclu", None, None, None, None, "60 CNY", None, None, None, None]
]

# 列名列表
columns = ["CCD", "CFO", "CP3", "DHC", "ERS", "FRO", "HDI", "IHI", "IPI", "ODF", "PAE"]

df = pd.DataFrame(raw_data, columns=columns)

2. 清洗单元格内容

编写函数提取数字或保留inclu，自动去除货币符号：

def clean_cell(value):
    if pd.isna(value):
        return None
    # 匹配'inclu'或纯数字
    match_result = re.search(r'(inclu|\d+)', str(value))
    if match_result:
        content = match_result.group(1)
        # 数字转整数，'inclu'保留字符串
        return int(content) if content.isdigit() else content
    return None

# 应用清洗函数到整个DataFrame
cleaned_df = df.applymap(clean_cell)

3. 计算每行数值总和

遍历每行，只对数值类型的单元格求和，跳过inclu和空值：

def calculate_row_sum(row):
    total = 0
    for val in row:
        if isinstance(val, (int, float)):
            total += val
    return total

# 添加"行总和"列
cleaned_df['行总和'] = cleaned_df.apply(calculate_row_sum, axis=1)

最终输出结果 #

运行上述代码后，得到的最终DataFrame如下：

CCD    CFO  CP3  DHC   ERS  FRO  HDI  IHI  IPI   ODF  PAE  行总和
0   None   None  NaN    0  None   40  None  210  None  None  None   250
1  inclu   None  NaN  NaN  1500  None  None   70  None  855  None  2425
2   None  inclu  NaN  NaN  None  None   60  None  None  None  None    60

内容的提问来源于stack exchange，提问作者huo shankou