Excel交叉表转一维表及数据处理

摘要：将具有多级表头的Excel交叉表转换为一维表，并对列名进行优化和日期格式化，最终可导出为JSON格式。

1. 背景与目标

      原始数据是一个带有两行表头的Excel文件，第一行为年份，第二行为月份，数据部分包含采购交货计划主表ID、工厂、物料以及不同日期的数据。

2. 数据结构说明

      原始数据结构如下：

| 采购交货计划主表ID | 工厂 | 物料 | 2025 | 2025 | 2025 | 2025 | 2025 |
|--------------------|------|------|------|------|------|------|------|
|                    |      |      | 01-02| 01-03| 01-04| 01-05| 01-06|
| 1                  | fz   | H002 | 6    | 8    | 9    | 8    | 9    |
| 2                  | fz   | H003 | 16   | 82   | 9    | 8    | 9    |

      目标是将其转换为一维表，每一行包含：采购交货计划主表ID、工厂、物料、年_月日、数量。

3. 实现步骤

3.1 读取Excel文件

      使用`pandas.read_excel()`函数读取Excel文件，并指定双行表头。

import pandas as pd

df = pd.read_excel("a.xlsx", header=[0, 1])

3.2 处理列名

      将多级表头合并为一个字符串，并对重复列名进行处理（如果两行相同则只保留一行）。

df.columns = [
    col[0] if (isinstance(col, tuple) and col[0] == col[1]) 
    else ''.join(col).strip() if isinstance(col, tuple) 
    else col 
    for col in df.columns
]

3.3 转换为一维表

      使用`melt()`函数将宽表转换为长表，并提取年_月日字段。

id_vars = df.columns[:3].tolist()
value_vars = df.columns[3:].tolist()

df_melted = df.melt(
    id_vars=id_vars,
    value_vars=value_vars,
    var_name='年_月日',
    value_name='数量'
)

3.4 格式化年_月日字段

      如果`年_月日`字段中包含类似`28W20250705`的格式，可以提取出日期部分并格式化为`YYYY-MM-DD`。

df_melted['年_月日'] = df_melted['年_月日'].str[4:8] + '-' + df_melted['年_月日'].str[8:10] + '-' + df_melted['年_月日'].str[10:12]

4. 导出为JSON

      将处理后的数据导出为JSON格式，便于后续使用。

json_data = df_melted.to_json(orient='records', force_ascii=False)

df_melted.to_json("output.json", orient='records', ensure_ascii=False, indent=4)

5. 注意事项

• 确保Excel文件路径正确，避免因路径错误导致读取失败。
• 处理过程中需要注意数据类型是否一致，例如数值型数据可能需要转换为字符串。
• 如果遇到警告信息如`Workbook contains no default style, apply openpyxl's default`，可以忽略或通过升级库来解决。

6. 总结

      通过上述步骤，可以有效地将Excel中的交叉表数据转换为一维表，并进行必要的格式化和导出操作。这不仅提高了数据的可读性和可用性，还为后续的数据分析和处理提供了便利。