如何用Pandas读取含合并单元格的Excel并统一空列聚合结果

我有一个Excel文件，读取后得到的DataFrame如下：

Name  Selector  Range  Policy
0  Name 1       NaN    NaN     NaN
1     NaN       NaN    NaN     NaN
2     NaN       NaN    NaN     NaN
3     NaN       NaN    NaN     NaN
4  Name 2       NaN    NaN     NaN

注：Name 2是合并了4行的单元格，但在DataFrame中仅占1行。

我使用以下代码处理数据：

from pprint import pprint
import pandas as pd

excel_data = pd.read_excel(io="pandas.xlsx", usecols="A:D")
df = pd.DataFrame(data=excel_data)
print(df)

data = (
    df.ffill()
    .fillna(value="")
    .groupby(by=df.columns[:1].to_list(), as_index=False)
    .aggregate(func=list)
    .rename(
        columns={
            df.columns[0]: "name",
            df.columns[1]: "selector",
            df.columns[2]: "range",
            df.columns[3]: "policy",
        }
    )
    .to_dict(orient="records")
)

pprint(data)

终端输出结果为：

[{'name': 'Name 1',
  'policy': ['', '', '', ''],
  'range': ['', '', '', ''],
  'selector': ['', '', '', '']},
 {'name': 'Name 2', 'policy': [''], 'range': [''], 'selector': ['']}]

我希望当A列以外的所有列均为空时，输出格式改为：

[{'name': 'Name 1', 'policy': [''], 'range': [''], 'selector': ['']},
 {'name': 'Name 2', 'policy': [''], 'range': [''], 'selector': ['']}]

有两种方式可以实现需求：

方式一：生成字典后遍历处理 #

在得到最终字典列表后，遍历每个条目，对selector、range、policy字段的列表进行判断：如果列表内所有元素都是空字符串，就替换为仅包含一个空字符串的列表。修改后的代码如下：

from pprint import pprint
import pandas as pd

excel_data = pd.read_excel(io="pandas.xlsx", usecols="A:D")
df = pd.DataFrame(data=excel_data)
print(df)

data = (
    df.ffill()
    .fillna(value="")
    .groupby(by=df.columns[:1].to_list(), as_index=False)
    .aggregate(func=list)
    .rename(
        columns={
            df.columns[0]: "name",
            df.columns[1]: "selector",
            df.columns[2]: "range",
            df.columns[3]: "policy",
        }
    )
    .to_dict(orient="records")
)

# 新增处理逻辑：将全空列表替换为单元素空列表
for item in data:
    for col in ['selector', 'range', 'policy']:
        if all(val == "" for val in item[col]):
            item[col] = [""]

pprint(data)

方式二：聚合阶段自定义函数处理 #

在分组聚合时直接使用自定义函数，判断列内元素是否全为空，按需返回结果，避免后续遍历操作：

from pprint import pprint
import pandas as pd

def aggregate_empty_list(x):
    # 所有元素为空则返回单元素空列表，否则返回原列表
    return [""] if all(v == "" for v in x) else x.tolist()

excel_data = pd.read_excel(io="pandas.xlsx", usecols="A:D")
df = pd.DataFrame(data=excel_data)
print(df)

data = (
    df.ffill()
    .fillna(value="")
    .groupby(by=df.columns[:1].to_list(), as_index=False)
    .aggregate({
        df.columns[0]: "first",  # Name列组内值相同，取第一个即可
        df.columns[1]: aggregate_empty_list,
        df.columns[2]: aggregate_empty_list,
        df.columns[3]: aggregate_empty_list
    })
    .rename(
        columns={
            df.columns[0]: "name",
            df.columns[1]: "selector",
            df.columns[2]: "range",
            df.columns[3]: "policy",
        }
    )
    .to_dict(orient="records")
)

pprint(data)

两种方式都能得到目标输出格式，可根据实际场景选择。

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