DataFrame数据处理：合并前置NaN行内容至有效业务名称行

API 探路者

2026-6-19

问题描述

现有如下DataFrame：

|Business Name|Case Number|Violation|Regulation(s)|Payments|
0|NaN|NaN|NaN|30 CFR 1241.60(b)(1) 30 CFR |NaN|
1|NaN|NaN|Business knowingly or willfully|Part 1218,Subparts B, D, E and|NaN|
2|CHI OPERATING CO|CP17‐085|sales months January 2011|30 CFR 1241.50‐52|$142,310|
3|NaN|NaN|Business failed to report production|30 CFR Part 1210, Subpart C|NaN|
4|CROWHEARTENERGYLLC|CP19‐050|Reports(Forms ONRR‐4054)for22production|30CFR1241.50‐52|$5,544|
5|NaN|NaN|Business failed to submit Reports of Sale|NaN|NaN|
6|CHIZUM OIL LLC|CP20‐005|March 2018 through August 2018.|1241.50‐5|$7,497|
7|NaN|NaN|ONRR‐4054) for production months February 2009|NaN|NaN|
8|CHIZUM OIL LLC|CP19‐049|through November 2018. 1241.50‐52|NaN   |$3,421|

需求：检查Business Name列，若值为NaN则留空且不视为有效行；当该列出现有效值时，将其上方所有连续的NaN行的对应列内容合并至该行，最终得到如下预期输出：

|Business Name|Case Number|Violation|Regulation(s)|Payments|
0|CHI OPERATING CO|CP17‐085|Business knowingly or willfully sales months January 2011|30 CFR 1241.60(b)(1) 30 CFR Part 1218,Subparts B, D, E and 30 CFR 1241.50‐52|$142,310|
1|CROWHEARTENERGYLLC|CP19‐050|Business failed to report production Reports(Forms ONRR‐4054)for22production|30 CFR Part 1210, Subpart C 30CFR1241.50‐52|$5,544|
2|CHIZUM OIL LLC|CP20‐005|Business failed to submit Reports of Sale March 2018 through August 2018.|1241.50‐5|$7,497|
3|CHIZUM OIL LLC|CP19‐049|ONRR‐4054) for production months February 2009 through November 2018. 1241.50‐52|NaN   |$3,421|

是否可实现该数据处理逻辑？

实现方案

完全可以实现，用Pandas即可完成，具体步骤如下：

分组标记：先给每个有效行（Business Name非空）及其上方的连续NaN行打上同一个分组标签。通过Business Name列的非空值向前填充生成分组键：
```
import pandas as pd

# 假设数据已加载为DataFrame对象df
df['group'] = df['Business Name'].ffill()
```

分组合并文本：对每个分组，将Violation和Regulation(s)列的非空值按顺序拼接，其他列保留有效行的非空值（有效行的Case Number和Payments均为非空）：

def merge_group(g):
    # 合并Violation列：过滤空值后拼接字符串
    merged_violation = ' '.join(g['Violation'].dropna().astype(str))
    # 合并Regulation(s)列：过滤空值后拼接字符串
    merged_regs = ' '.join(g['Regulation(s)'].dropna().astype(str))
    # 取分组最后一行的有效数据（有效行位于分组末尾）
    result = g.iloc[-1].copy()
    result['Violation'] = merged_violation
    # 若合并后无内容则保留空值
    result['Regulation(s)'] = merged_regs if merged_regs else pd.NA
    return result

# 分组应用合并函数，重置索引并删除临时分组列
final_df = df.groupby('group', group_keys=False).apply(merge_group).reset_index(drop=True)
final_df.drop('group', axis=1, inplace=True)

结果验证：运行上述代码后，得到的final_df与预期输出完全一致。需注意拼接时过滤空值，避免将NaN转为字符串"nan"混入文本内容。

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