如何清洗列类型与值多变的Pandas DataFrame并重构数据？

针对你提出的DataFrame清洗与重构需求，以下是高效实现的步骤：

1. 加载样例数据 #

首先将你提供的字典数据转换为Pandas DataFrame：

import pandas as pd
import re

# 样例数据
sample_data = {0: {20: '11', 21: '11', 22: '14', 23: '16', 24: '18', 25: '19', 26: '19'}, 1: {20: 'Apr', 21: 'Apr', 22: 'Apr', 23: 'Apr', 24: 'Apr', 25: 'Apr', 26: 'Apr'}, 2: {20: 'ACTNOWQUICK', 21: 'Cash', 22: 'ACTNOWQUICK', 23: 'ACTNOWQUICK', 24: 'Inward', 25: 'Cash', 26: 'Inward'}, 3: {20: '1234.56', 21: 'WithdrawalATM', 22: '76.53', 23: '1236.00', 24: 'DR', 25: 'WithdrawalATM', 26: 'CR'}, 4: {20: '1234.98', 21: '50.00', 22: '653.24', 23: '1234.78', 24: 'FUTHN', 25: '70.00', 26: 'YJHK'}, 5: {20: 'HYE912630964589376', 21: '1111.22', 22: 'HYE91234234589376', 23: 'HYE91263234234234376', 24: '60.00', 25: '222.22', 26: '33333.33'}, 6: {20: 'PLUTO', 21: '23523455', 22: 'WiN', 23: 'YOU', 24: '11.11', 25: '123123123', 26: '18.18'}, 7: {20: 'THEATRE', 21: None, 22: 'OTHR', 23: 'TECHY', 24: 'WOL', 25: None, 26: 'OTHER'}, 8: {20: 'OTHER', 21: None, 22: 'JOHNKLING', 23: 'BRO', 24: 'E54E236A58', 25: None, 26: 'Other'}, 9: {20: 'WUN', 21: None, 22: None, 23: 'OTHER', 24: 'FFF', 25: None, 26: 'PFFS'}, 10: {20: 'Cool', 21: None, 22: None, 23: '123123123523452', 24: 'UEJH', 25: None, 26: '(JUPITER)'}, 11: {20: 'Beans', 21: None, 22: None, 23: None, 24: None, 25: None, 26: 'EVEREST'}, 12: {20: 'KIng', 21: None, 22: None, 23: None, 24: None, 25: None, 26: '236272345235'}, 13: {20: None, 21: None, 22: None, 23: None, 24: None, 25: None, 26: None}, 14: {20: None, 21: None, 22: None, 23: None, 24: None, 25: None, 26: None}, 15: {20: None, 21: None, 22: None, 23: None, 24: None, 25: None, 26: None}, 16: {20: None, 21: None, 22: None, 23: None, 24: None, 25: None, 26: None}}

df = pd.DataFrame(sample_data)

2. 定义行处理函数 #

核心逻辑是通过正则表达式识别每行中的数值位置，分割出描述和金额字段：

def process_row(row):
    # 创建Date列
    date = f"{row[0]} {row[1]}"
    
    # 提取第2列及之后的非空值，转为字符串
    cols_after_date = [str(val) for val in row[2:] if val is not None]
    
    # 正则匹配整数或小数（可根据实际需求调整）
    num_pattern = re.compile(r'^-?\d+\.?\d*$')
    
    # 定位第一个数值的索引
    first_num_idx = None
    for i, val in enumerate(cols_after_date):
        if num_pattern.match(val):
            first_num_idx = i
            break
    
    # 定位第二个数值的索引（从第一个数值下一位开始）
    second_num_idx = None
    if first_num_idx is not None:
        for i, val in enumerate(cols_after_date[first_num_idx+1:]):
            if num_pattern.match(val):
                second_num_idx = first_num_idx + 1 + i
                break
    
    # 构建各目标字段
    desc1 = ' '.join(cols_after_date[:first_num_idx]) if first_num_idx is not None else ''
    amount1 = cols_after_date[first_num_idx] if first_num_idx is not None else None
    amount2 = cols_after_date[second_num_idx] if second_num_idx is not None else None
    # 若没有第二个数值，将第一个数值后的所有内容作为Description 2
    desc2 = ' '.join(cols_after_date[second_num_idx+1:]) if second_num_idx is not None else ' '.join(cols_after_date[first_num_idx+1:]) if first_num_idx is not None else ''
    
    return pd.Series(
        [date, desc1, amount1, amount2, desc2],
        index=['Date', 'Description 1', 'Amount 1', 'Amount 2', 'Description 2']
    )

3. 生成最终结果 #

使用apply函数将处理逻辑应用到每一行：

result_df = df.apply(process_row, axis=1)
print(result_df)

关键说明 #

• 正则表达式r'^-?\d+\.?\d*$'支持匹配整数、带小数的数值，以及负数，可根据你的实际数值格式调整（比如添加千分位支持r'^-?\d{1,3}(,\d{3})*\.?\d*$'）
• 函数会自动处理无数值的行，对应字段设为空字符串或None，可根据需求修改默认值
• 对于只有一个数值的行，该数值后的所有内容会被合并为Description 2

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