从DataFrame拆分账户名与卡号列：groupby方法失效求助

需要将包含Account / Card、Balance Start、Balance End、Difference列的DataFrame，把Account / Card列拆分为账户名和卡号两列。原DataFrame数据如下：

Account / Card  Balance Start  Balance End  Difference
0            MASTER         701.00       701.00        0.00
0               NaN            NaN          NaN         NaN
1   Algemeen/*!%@!#        2240.25      2181.45      -58.80
9         120989894           0.00         0.00        0.00
5         159142752           0.00         0.00        0.00
4         286695535         300.00       300.00        0.00
12        578951057          30.00        30.00        0.00
13        706602827           0.00         0.00        0.00
2         735592139           0.00         0.00        0.00
11        810226753         250.00       229.16      -20.84
3         811695012           0.00         0.00        0.00
6         818329167          22.85        22.85        0.00
0               NaN            NaN          NaN         NaN
2           General        3913.56      3813.56     -100.00
8         138738399          42.79        27.79      -15.00
0         646746507           0.00         0.00        0.00
1         767402418           2.90         2.90        0.00
10        948214665         545.07       391.57     -153.50
7         A98744534         461.32        75.36     -385.96
0               NaN            NaN          NaN         NaN
0            Totals        8509.74      7775.64     -734.10

尝试了以下groupby代码但未达到预期效果：

for date, new_df in balances.groupby(level=0):
    print(new_df)

解决方案1：基于空行分组（贴合原数据结构） #

原数据用全空行分隔不同账户组，每个组的第一行是账户名，下方为该账户的卡号。可以按此规则分组处理：

import pandas as pd
import numpy as np

# 标记全空行
balances['is_empty_row'] = balances.isna().all(axis=1)
# 生成分组ID：每遇到一个空行，分组ID递增
balances['group_id'] = balances['is_empty_row'].cumsum()
# 过滤空行和总计行
filtered_df = balances[~balances['is_empty_row'] & (balances['Account / Card'] != 'Totals')].copy()

# 为每个分组拆分账户名和卡号
def process_group(group):
    # 每组第一行是账户名
    account_name = group.iloc[0]['Account / Card']
    group['Account Name'] = account_name
    # 卡号列：仅保留非账户名的行
    group['Card Number'] = np.where(group.index == group.index[0], np.nan, group['Account / Card'])
    return group

# 分组处理并整理结果
result_df = filtered_df.groupby('group_id').apply(process_group)
result_df = result_df.drop(columns=['is_empty_row', 'group_id']).reset_index(drop=True)
# 移除账户名行（卡号为NaN的行）
result_df = result_df.dropna(subset=['Card Number']).reset_index(drop=True)

# 输出最终结构
print(result_df[['Account Name', 'Card Number', 'Balance Start', 'Balance End', 'Difference']])

解决方案2：基于内容规则识别 #

如果空行分隔不固定，可通过内容特征区分账户名和卡号：账户名包含字母/特殊字符，卡号为纯数字或带前缀的类卡号格式：

import pandas as pd
import numpy as np

# 识别账户名行：非纯数字字符串，且不是总计行
def is_account_name(x):
    if not isinstance(x, str) or x == 'Totals':
        return False
    # 移除特殊字符后判断是否为纯数字，非纯数字则为账户名
    cleaned = x.replace('/*!%@!#', '')
    return not cleaned.isdigit()

balances['is_account'] = balances['Account / Card'].apply(is_account_name)

# 向前填充账户名，为每行匹配所属账户
balances['Account Name'] = balances.loc[balances['is_account'], 'Account / Card'].ffill()

# 提取卡号列：非账户名行的Account/Card值
balances['Card Number'] = np.where(balances['is_account'], np.nan, balances['Account / Card'])

# 过滤空行、账户名行和总计行
result_df = balances[
    ~balances['is_account'] 
    & ~balances['Account / Card'].isna() 
    & (balances['Account / Card'] != 'Totals')
].copy()

# 整理最终列顺序
result_df = result_df[['Account Name', 'Card Number', 'Balance Start', 'Balance End', 'Difference']].reset_index(drop=True)

print(result_df)

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