如何用Pandas+XlsxWriter在Excel折线图上对齐时间轴线段

核心问题在于：XlsxWriter的日期型X轴是基于Excel日期序列号（1900年1月1日为1，逐日/逐时递增）的，若数据集B的时间未转换为对应序列号或格式不匹配，就会出现位置偏移。以下针对两种常见的数据集B格式给出具体解决方案：

格式一：时段型事件（开始/结束时间+事件值） #

这类数据每行对应一个事件的时间区间和固定值，需要将开始、结束时间转换为Excel日期序列号，再通过散点图系列绘制线段。

示例代码 #

import pandas as pd
from xlsxwriter.utility import datetime_to_excel_datetime

# 数据集A：时间、温度、功率
df_a = pd.DataFrame({
    '时间': pd.date_range('2024-05-01 08:00', periods=24, freq='H'),
    '温度': [20,21,23,25,24,22,21,20]*3,
    '功率': [100,120,150,180,160,130,110,100]*3
})

# 数据集B：事件时段数据
df_b = pd.DataFrame({
    '事件名称': ['设备维护', '峰值负载'],
    '开始时间': pd.to_datetime(['2024-05-01 10:00', '2024-05-01 16:00']),
    '结束时间': pd.to_datetime(['2024-05-01 12:00', '2024-05-01 18:00']),
    '事件基准值': [80, 90]
})

# 初始化Excel写入器
writer = pd.ExcelWriter('data_with_events.xlsx', engine='xlsxwriter')
df_a.to_excel(writer, sheet_name='监测数据', index=False)
df_b.to_excel(writer, sheet_name='事件记录', index=False)
workbook = writer.book
ws_data = writer.sheets['监测数据']

# 创建基础折线图
chart = workbook.add_chart({'type': 'line'})

# 添加温度、功率系列
chart.add_series({
    'name': '温度',
    'categories': '=监测数据!$A$2:$A$25',
    'values': '=监测数据!$B$2:$B$25',
})
chart.add_series({
    'name': '功率',
    'categories': '=监测数据!$A$2:$A$25',
    'values': '=监测数据!$C$2:$C$25',
})

# 设置X轴为日期轴（关键）
chart.set_x_axis({
    'type': 'date',
    'date_axis': True,
    'num_format': 'yyyy-mm-dd HH:mm',
})

# 逐个添加事件线段
for _, row in df_b.iterrows():
    # 将Python datetime转换为Excel日期序列号
    start_serial = datetime_to_excel_datetime(row['开始时间'], use_1904=False)
    end_serial = datetime_to_excel_datetime(row['结束时间'], use_1904=False)
    
    # 用散点系列绘制水平线段（隐藏标记点，设置虚线样式）
    chart.add_series({
        'name': row['事件名称'],
        'categories': [start_serial, end_serial],
        'values': [row['事件基准值'], row['事件基准值']],
        'marker': {'none': True},
        'line': {'width': 2, 'dash_type': 'dash'},
    })

# 插入图表到工作表
ws_data.insert_chart('D2', chart)
writer.close()

格式二：离散时间点事件 #

这类数据是事件发生的具体时间点和对应值，需确保事件时间与数据集A的时间轴完全对齐，可通过合并数据集实现。

示例代码 #

import pandas as pd

# 数据集A
df_a = pd.DataFrame({
    '时间': pd.date_range('2024-05-01 08:00', periods=24, freq='H'),
    '温度': [20,21,23,25,24,22,21,20]*3,
    '功率': [100,120,150,180,160,130,110,100]*3
})

# 数据集B：离散事件时间点
df_b = pd.DataFrame({
    '时间': pd.to_datetime(['2024-05-01 10:00', '2024-05-01 12:00', '2024-05-01 16:00', '2024-05-01 18:00']),
    '事件值': [80, 80, 90, 90]
})

# 合并数据集，确保时间轴完全对齐（缺失事件的时间点填充NaN）
df_combined = pd.merge(df_a, df_b, on='时间', how='left')

# 写入Excel并创建图表
writer = pd.ExcelWriter('combined_data_chart.xlsx', engine='xlsxwriter')
df_combined.to_excel(writer, sheet_name='合并数据', index=False)
workbook = writer.book
ws_combined = writer.sheets['合并数据']

chart = workbook.add_chart({'type': 'line'})

# 添加温度、功率系列
chart.add_series({
    'name': '温度',
    'categories': '=合并数据!$A$2:$A$25',
    'values': '=合并数据!$B$2:$B$25',
})
chart.add_series({
    'name': '功率',
    'categories': '=合并数据!$A$2:$A$25',
    'values': '=合并数据!$C$2:$C$25',
})

# 添加事件线段系列
chart.add_series({
    'name': '事件标记',
    'categories': '=合并数据!$A$2:$A$25',
    'values': '=合并数据!$D$2:$D$25',
    'line': {'width': 2, 'dash_type': 'dash'},
    'marker': {'none': True},
})

# 设置日期型X轴
chart.set_x_axis({
    'type': 'date',
    'date_axis': True,
    'num_format': 'yyyy-mm-dd HH:mm',
})

ws_combined.insert_chart('E2', chart)
writer.close()

关键注意事项 #

1. 必须将所有时间数据转换为Python datetime类型，再通过datetime_to_excel_datetime转换为Excel序列号，或依赖Pandas自动转换（写入Excel时会自动将datetime转为序列号）。
2. 图表X轴必须设置'type': 'date'和'date_axis': True，确保Excel按日期规则渲染X轴。
3. 若线段为垂直方向（标记时间点的竖线），只需交换categories和values的取值逻辑即可。

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