如何用Python Pandas解析含行列合并单元格的XLS数据并转换为指定JSON结构

嘿，我完全懂你的困扰——当Excel里同时存在行合并和列合并单元格时，单纯靠df.index.to_series().ffill()确实搞不定，因为这个方法只处理了行方向的缺失值，压根没考虑列合并的情况。我来给你一步步拆解解决方案，帮你把表格转成你要的JSON结构：

核心思路 #

首先得把所有合并单元格的内容填充到它覆盖的每一个单元格里（不管是跨行还是跨列），让DataFrame里没有空值，再根据目标JSON结构整理数据。

具体实现步骤 #

1. 读取Excel并获取合并单元格信息 #

我们用openpyxl来读取文件，它能直接拿到所有合并单元格的范围和对应值，比Pandas默认读取更适合这种场景：

import pandas as pd
from openpyxl import load_workbook

# 加载你的Excel文件（xlsx格式直接用，xls格式建议转成xlsx或者用xlrd旧版本）
wb = load_workbook('your_data.xlsx')
ws = wb.active

# 获取所有合并单元格的范围列表
merged_cells = list(ws.merged_cells.ranges)

2. 填充所有合并单元格的缺失值 #

把表格转成DataFrame后，合并区域里只有第一个单元格有值，其他都是NaN，我们需要遍历每个合并区域，把值填充到整个区域：

# 转换为DataFrame
df = pd.DataFrame(ws.values)

# 遍历每个合并单元格区域
for merged_range in merged_cells:
    # 把openpyxl的1-based索引转成Pandas的0-based
    start_row, start_col = merged_range.min_row - 1, merged_range.min_col - 1
    end_row, end_col = merged_range.max_row - 1, merged_range.max_col - 1
    # 获取合并单元格的原始值
    fill_value = df.iloc[start_row, start_col]
    # 填充整个合并区域
    df.loc[start_row:end_row, start_col:end_col] = fill_value

3. 整理数据并转换为目标JSON结构 #

现在DataFrame里所有单元格都有值了，接下来按照你要的JSON格式整理。结合你给出的示例和表格结构，我们可以这样处理：

# 提取左侧的基础属性（对应JSON里的time、category等字段）
attr_columns = df.iloc[:, :6]  # 假设前6列是这些属性
# 因为是合并行，所以取第一行的非重复值即可
attr_values = attr_columns.drop_duplicates().iloc[0].tolist()
attr_keys = ["time", "category", "variety", "specification", "unit", "average"]
base_dict = dict(zip(attr_keys, attr_values))

# 提取右侧的区域、市场和价格数据
price_area = df.iloc[:, 6:]
result_list = []

# 假设每一组区域+市场+价格占3列（根据你的表格结构调整）
for col_idx in range(0, price_area.shape[1], 3):
    # 获取区域、市场、价格的值
    region = price_area.iloc[0, col_idx]
    market = price_area.iloc[0, col_idx+1]
    price = price_area.iloc[1, col_idx+2]
    
    # 组合成目标字典
    item = base_dict.copy()
    item.update({
        "region": region,
        "market": market,
        "price": price
    })
    result_list.append(item)

# 转换成JSON格式
import json
final_json = json.dumps(result_list, indent=2)
print(final_json)

关键提示 #

• 上面的行列索引（比如[:, :6]、range(0, price_area.shape[1], 3)）需要根据你实际表格的结构调整，你可以先打印df.head()看看数据分布再修改。
• 如果你的文件是.xls格式，openpyxl不支持的话，改用xlrd==1.2.0（新版本xlrd不再支持xls）来读取合并单元格信息，逻辑是类似的。

备注：内容来源于stack exchange，提问作者jckling