### 使用Python Pandas读取Excel指定列并构建LSTM模型进行时间序列预测 #### 准备工作 为了完成此任务，需要安装必要的库。可以通过pip命令来安装这些依赖项。 ```bash pip install pandas numpy tensorflow scikit-learn openpyxl ``` #### 数据预处理阶段 首先定义函数`read_excel_column`用于从Excel文件中提取特定的一列数据，并将其转换成适合于训练LSTM网络的时间序列格式。 ```python import pandas as pd def read_excel_column(file_path, sheet_name='Sheet1', column_index_or_name=0): """Read a specific column from an Excel file.""" data = pd.read_excel(file_path, usecols=[column_index_or_name], sheet_name=sheet_name) values = data.values.flatten() return values.tolist() time_series_data = read_excel_column('path/to/excel/file.xlsx') print(f'Time series length: {len(time_series_data)}') ``` 接着按照给定的方法将原始时间序列转化为监督学习问题的形式[^2]： ```python from pandas import DataFrame def timeseries_to_supervised(data, lag=1): df = DataFrame(data) columns = [df.shift(i) for i in range(1, lag + 1)] columns.append(df) df = pd.concat(columns, axis=1) df.fillna(0, inplace=True) return df supervised_df = timeseries_to_supervised(time_series_data) supervised_values = supervised_df.values ``` #### 构建和训练LSTM模型 创建简单的LSTM架构来进行时间序列预测。这里假设已经完成了上述的数据准备过程。 ```python import numpy as np from sklearn.preprocessing import MinMaxScaler from keras.models import Sequential from keras.layers import LSTM, Dense # 归一化特征值到 (0, 1) 范围内 scaler = MinMaxScaler(feature_range=(0, 1)) scaled_values = scaler.fit_transform(supervised_values) train_size = int(len(scaled_values) * 0.8) test_size = len(scaled_values) - train_size train, test = scaled_values[:train_size,:], scaled_values[train_size:len(scaled_values),:] def create_dataset(dataset, look_back=1): X, Y = [], [] for i in range(len(dataset)-look_back-1): a = dataset[i:(i+look_back), :] X.append(a) Y.append(dataset[i + look_back, -1]) return np.array(X), np.array(Y) X_train, y_train = create_dataset(train) X_test, y_test = create_dataset(test) # Reshape input to be [samples, time steps, features] X_train = np.reshape(X_train, (X_train.shape[0], 1, X_train.shape[1])) X_test = np.reshape(X_test, (X_test.shape[0], 1, X_test.shape[1])) model = Sequential() model.add(LSTM(units=50, return_sequences=False, input_shape=(X_train.shape[1], X_train.shape[2]))) model.add(Dense(1)) model.compile(loss='mean_squared_error', optimizer='adam') history = model.fit(X_train, y_train, epochs=100, batch_size=1, verbose=2) predictions = model.predict(X_test) predicted_inversed = scaler.inverse_transform(np.concatenate((np.zeros_like(predictions).reshape(-1, predictions.shape[-1]), predictions), axis=-1))[:, -1] actuals_inversed = scaler.inverse_transform(np.concatenate((np.zeros_like(y_test.reshape(-1, 1)).reshape(-1, y_test.shape[-1]), y_test.reshape(-1, 1)), axis=-1))[:, -1] for actual, predicted in zip(actuals_inversed, predicted_inversed): print(f'Actual={actual:.2f}, Predicted={predicted:.2f}') ```