归一化时间序列数据集后，如何保留Timestamp索引与缩放值？

问题背景 #

我尝试对时间序列数据集做归一化处理，代码能正常运行，但最后生成的新DataFrame只包含缩放后的值，丢失了原始的Timestamp列。我的代码如下：

import pandas as pd
from sklearn import preprocessing

data_consumption2 = pd.read_excel(r"C:\Users\user\Desktop\Thesis\Tarek\Parent.xlsx", sheet_name="Consumption")
data_consumption2['Timestamp'] = pd.to_datetime(data_consumption2['Timestamp'], unit='s')
data_consumption2.fillna(0,inplace=True)
data_consumption2 = data_consumption2.set_index('Timestamp')
# returns a numpy array
min_max_scaler = preprocessing.MinMaxScaler()
x_scaled = min_max_scaler.fit_transform(data_consumption2.values)
data_consumption2 = pd.DataFrame(x_scaled)

希望得到一个既包含原始Timestamp又有缩放值的DataFrame。

解决方案 #

你遇到的问题根源在于：当你把Timestamp设为DataFrame的索引后，用data_consumption2.values提取数据时，会只返回纯数值的numpy数组，完全丢失了索引（也就是Timestamp）的信息，最后转成新DataFrame自然就没有这部分内容了。这里有两种简单的修复方式：

方法1：缩放后恢复原索引

这种方法保留你原来将Timestamp设为索引的逻辑，在生成缩放后的DataFrame时，手动把原索引和列名加回去：

import pandas as pd
from sklearn import preprocessing

data_consumption2 = pd.read_excel(r"C:\Users\user\Desktop\Thesis\Tarek\Parent.xlsx", sheet_name="Consumption")
data_consumption2['Timestamp'] = pd.to_datetime(data_consumption2['Timestamp'], unit='s')
data_consumption2.fillna(0,inplace=True)

# 先保存原始数值列的列名，后续复用
original_numeric_cols = data_consumption2.columns.drop('Timestamp')
# 将Timestamp设为索引
data_consumption2 = data_consumption2.set_index('Timestamp')

# 对整个DataFrame（此时只有数值列）做缩放
min_max_scaler = preprocessing.MinMaxScaler()
x_scaled = min_max_scaler.fit_transform(data_consumption2)

# 生成新DataFrame时，指定原索引和列名
data_consumption2_scaled = pd.DataFrame(x_scaled, index=data_consumption2.index, columns=original_numeric_cols)

# 如果想把Timestamp变回普通列，执行下面这行
# data_consumption2_scaled = data_consumption2_scaled.reset_index()

这样得到的data_consumption2_scaled会保留Timestamp作为索引，同时包含所有缩放后的数值列。如果需要把Timestamp转为普通列，只需执行注释里的reset_index()即可。

方法2：不将Timestamp设为索引，单独处理数值列

如果你更希望Timestamp保持为普通列，可以先分离出Timestamp和数值列，只对数值列做缩放，最后再合并回去：

import pandas as pd
from sklearn import preprocessing

data_consumption2 = pd.read_excel(r"C:\Users\user\Desktop\Thesis\Tarek\Parent.xlsx", sheet_name="Consumption")
data_consumption2['Timestamp'] = pd.to_datetime(data_consumption2['Timestamp'], unit='s')
data_consumption2.fillna(0,inplace=True)

# 分离Timestamp列和数值列
timestamp_series = data_consumption2['Timestamp']
numeric_df = data_consumption2.drop('Timestamp', axis=1)

# 对数值列做缩放
min_max_scaler = preprocessing.MinMaxScaler()
numeric_scaled = min_max_scaler.fit_transform(numeric_df)

# 合并Timestamp和缩放后的数值列
data_consumption2_scaled = pd.DataFrame(numeric_scaled, columns=numeric_df.columns)
data_consumption2_scaled['Timestamp'] = timestamp_series

# 可选：调整列顺序，把Timestamp放在最前面
data_consumption2_scaled = data_consumption2_scaled[['Timestamp'] + list(numeric_df.columns)]

这种方式下，Timestamp会作为普通列和缩放后的数值列共存，完全符合你的需求。

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