如何将Pandas Series转换为DataFrame并拆分数据至列

我有一个类似Pandas Series的数据集：

0 DATE DECRIPTION DEBIT CREDIT BALANCE
1 28/04/2022 Rent Due 1,150.00 £ -£1,150.00
2 17/05/2022 Payment Received from Berns & Co 1,...
3 27/05/2022 Payment 1,150.00 £ £1,150.00
4 28/05/2022 Rent Due 1,150.00 £ £0.00

尝试用以下代码处理，但不知道如何通过apply结合split将数据拆分为DataFrame的列：

def clean_data(s):
    if "Rent Due" in s:
        print("Rent Due")
        s = s.split("Rent Due")
    elif "Payment Received from Berns & Co" in s:
        print("Berns")
        s = s.split("Payment Received from Berns & Co", )
    elif "Payment" in s:
        s = s.split("Payment")
        print("Payment")
    elif "Electricity Charges" in s:
        s = s.split("Electricity Charges")
    print(s)
    return s
bv = data.apply(clean_data)

已知第一行是列名，计划用bv.columns = ['DATE', 'DESCRIPTION', 'DEBIT', 'CREDIT']设置，但不知道如何完成拆分转换。

步骤1：分离列名与数据行 #

首先提取Series的第一行作为列名（修正原列名的拼写错误DECRIPTION为DESCRIPTION），并取出后续的实际数据行：

import pandas as pd

# 定义列名，修正拼写错误
columns = ['DATE', 'DESCRIPTION', 'DEBIT', 'CREDIT', 'BALANCE']
# 跳过第一行（列名行），取出数据行
data_rows = data.iloc[1:]

步骤2：重构清洗函数，返回结构化列表 #

原函数仅做了字符串分割，未整理成对应列的格式。修改clean_data函数，让它返回包含各字段的列表，确保每个字段对应正确的列：

def clean_data(row):
    # 提取日期：取字符串前10位（格式DD/MM/YYYY）
    date = row[:10].strip()
    # 剩余部分用于提取描述和金额
    remaining_content = row[10:].strip()
    
    # 根据描述关键词拆分，提取描述内容
    if "Rent Due" in remaining_content:
        desc = "Rent Due"
        amount_part = remaining_content.split("Rent Due")[1].strip()
    elif "Payment Received from Berns & Co" in remaining_content:
        desc = "Payment Received from Berns & Co"
        amount_part = remaining_content.split("Payment Received from Berns & Co")[1].strip()
    elif "Payment" in remaining_content:
        desc = "Payment"
        amount_part = remaining_content.split("Payment")[1].strip()
    elif "Electricity Charges" in remaining_content:
        desc = "Electricity Charges"
        amount_part = remaining_content.split("Electricity Charges")[1].strip()
    else:
        desc = ""
        amount_part = remaining_content
    
    # 拆分金额部分，处理带£的格式
    amount_components = amount_part.split()
    debit = f"{amount_components[0]} {amount_components[1]}" if len(amount_components)>=2 else ""
    credit = amount_components[2] if len(amount_components)>=3 else ""
    balance = amount_components[3] if len(amount_components)>=4 else ""
    
    # 返回对应列的结构化列表
    return [date, desc, debit, credit, balance]

步骤3：应用函数并转换为DataFrame #

使用apply处理每一行数据，将返回的列表转换为DataFrame并设置列名：

# 应用清洗函数，得到包含结构化列表的Series
processed_series = data_rows.apply(clean_data)
# 将列表Series转换为DataFrame
result_df = pd.DataFrame(processed_series.tolist(), columns=columns)
# 若不需要BALANCE列，可直接删除
result_df = result_df.drop(columns=['BALANCE'])

步骤4：金额格式转换（可选） #

如果需要将金额转换为数值类型用于计算，可进一步处理：

def convert_to_numeric(currency_str):
    if not currency_str:
        return 0.0
    # 移除£符号和千位分隔符，转换为浮点数
    return float(currency_str.replace('£', '').replace(',', ''))

result_df['DEBIT'] = result_df['DEBIT'].apply(convert_to_numeric)
result_df['CREDIT'] = result_df['CREDIT'].apply(convert_to_numeric)

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