使用pandas pivot_table()方法意外生成大量NaN值的问题

背景与现象 #

处理5个结构一致的PLC采集数据DataFrame，每个包含TIMESTAMP（时间戳）、NAME（变量名）、VALUE（变量值）等5个字段。执行透视操作（以TIMESTAMP为索引、NAME为列、VALUE为值）后，3个列数≤3的DataFrame结果正常，但2个列数超过10的（分别为84列、13列）出现大量NaN值。

数据结构 #

• 原DataFrame形状：((85247, 5), (255737, 5), (255734, 5), (574065, 5), (567587, 5))
• 透视后形状：((85247, 1), (85258, 3), (85258, 3), (85542, 84), (85216, 13))

执行代码 #

import pandas as pd

# 转换时间戳格式
df_WATER['TIMESTAMP'] = pd.to_datetime(df_WATER['TIMESTAMP'], errors='ignore')
df_HSD['TIMESTAMP'] = pd.to_datetime(df_HSD['TIMESTAMP'], errors='ignore')
df_HSCPLC3['TIMESTAMP'] = pd.to_datetime(df_HSCPLC3['TIMESTAMP'], errors='ignore')
df_HSCPLC2ACT['TIMESTAMP'] = pd.to_datetime(df_HSCPLC2ACT['TIMESTAMP'], errors='ignore')
df_FURNACE['TIMESTAMP'] = pd.to_datetime(df_FURNACE['TIMESTAMP'], errors='ignore')

# 透视操作
df_WATER = df_WATER.pivot_table(index='TIMESTAMP', columns='NAME', values='VALUE')
df_HSD = df_HSD.pivot_table(index='TIMESTAMP', columns='NAME', values='VALUE')
df_HSCPLC3 = df_HSCPLC3.pivot_table(index='TIMESTAMP', columns='NAME', values='VALUE')
df_HSCPLC2ACT = df_HSCPLC2ACT.pivot_table(index='TIMESTAMP', columns='NAME', values='VALUE')
df_FURNACE = df_FURNACE.pivot_table(index='TIMESTAMP', columns='NAME', values='VALUE')

异常示例 #

TIMESTAMP                                                  
2022-11-18 20:00:00.224                              NaN   
2022-11-18 20:00:00.731                              NaN   
2022-11-18 20:00:01.240                              NaN   
...                                                  ...   

原因排查与解决方法 #

这不是pivot_table的函数限制，核心问题出在时间戳处理或数据采样逻辑上：

1. 时间戳转换不完整 #

errors='ignore'会跳过转换失败的时间戳，导致部分时间戳仍是字符串类型，透视时被识别为不同索引，进而生成NaN。

• 解决：先排查无效时间戳，处理后再转换：

  # 找出转换失败的时间戳
  invalid_rows = df_HSCPLC2ACT[pd.to_datetime(df_HSCPLC2ACT['TIMESTAMP'], errors='coerce').isna()]
  print(invalid_rows)
  
  # 处理无效数据后，重新转换（去掉errors参数，强制转换报错，确保所有时间戳格式正确）
  df_HSCPLC2ACT['TIMESTAMP'] = pd.to_datetime(df_HSCPLC2ACT['TIMESTAMP'])
  

2. 变量采样时间不匹配 #

PLC采集时，不同变量的采样时间可能不完全同步（比如A变量在某时间点有数据，B变量无采样），透视后对应位置自然出现NaN。

• 解决：按固定频率重采样对齐时间戳，选择合适的填充方式：

  # 先完成透视
  df_pivoted = df_HSCPLC2ACT.pivot_table(index='TIMESTAMP', columns='NAME', values='VALUE')
  # 按1秒频率重采样，用前向填充补全NaN（可根据需求换成bfill/mean等）
  df_aligned = df_pivoted.resample('1S').ffill()
  

3. 重复时间戳与变量组合 #

如果同一时间戳+变量存在多条记录，pivot_table默认用mean聚合，若数据异常可能导致NaN，可指定聚合方式明确处理：

df_HSCPLC2ACT = df_HSCPLC2ACT.pivot_table(
    index='TIMESTAMP', 
    columns='NAME', 
    values='VALUE', 
    aggfunc='first'  # 取第一条记录，也可根据需求用last/sum等
)

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