<jupyter_start><jupyter_text>数据预处理  从数据库获取数据<jupyter_code>def getdata():
    data=[]
    db=pymysql.connect(host='192.168.127.12',port=30002,user='root',password='<PASSWORD>',db='data_portal')
    cursor=db.cursor()
    sql="select * from ads_all_d_zc_tsg_zjc_fssj where fssj>='2020-01-01' order by fssj asc"
    try:
        cursor.execute(sql)
        #data=db.commit()
        #print(data)
        ## 将单条数据存入list中
        for line in cursor.fetchall():
            data.append(line)
    except Exception as e:
        #print(e)
        db.rollback()  
    db.close()
    
    return data<jupyter_output><empty_output><jupyter_text>需要将‘时间’单维度扩展为多维度：   --1、年  --2、月  --3、日  4、季节：春(3-5)：0，夏(6-8)：1 ，秋(9-11)：2，冬(12-2)：3  5、上学期(9月-12月)：0，寒假（1月-2月）:1,下学期（3月-6月）：2，暑假（7月-8月）：3  6、每周七天：周一：1....周天：7     获取季节、学期信息<jupyter_code>def large_date(yf):
        #季节
    if  yf>=3 and yf<=5:
        jj=0
    elif yf>=6 and yf<=8:
        jj=1
    elif yf>=9 and yf<=11:
        jj=2
    else:
        jj=3
    
        #学期
    if  yf>=9 and yf<=12:
        xq=0
    elif yf>=1 and yf<=2:
        xq=1
    elif yf>=3 and yf<=6:
        xq=2
    else:
        xq=3
        
    return jj,xq<jupyter_output><empty_output><jupyter_text>将读取的数据转化为Dateframe格式<jupyter_code>#data_one=[]
#tomm=datetime.datetime.strptime('2019-12-31', "%Y-%m-%d")
#tom=datetime.datetime.strptime('2019-12-31', "%Y-%m-%d")
data_all=[]
data=getdata()
data
for dt in data:
    tomm=dt[0]
#     if dt[0]==(tomm+datetime.timedelta(days=1)):
#         tomm=dt[0]
#         count=int(dt[1])
        
#     else:
#         tomm=(tomm+datetime.timedelta(days=1))
#         count=0
    
    
    data_one=[]
    data_one.append(tomm)
    data_one.append(int(tomm.strftime('%Y')))
    data_one.append(int(tomm.strftime('%m')))
    data_one.append(int(tomm.strftime('%d')))
    
    yf=int(tomm.strftime('%m'))
    
    jj,xq=large_date(yf)
    data_one.append(jj)
    

    data_one.append(xq)
    # 星期
    data_one.append(tomm.weekday()+1)
    
    data_one.append(dt[1])
    data_all.append(data_one)
df = pd.DataFrame(data_all, columns=['Date', 'Year', 'Month','Day','Season','Term','Weekday','Count']).set_index('Date', drop=True) 
        
        
df<jupyter_output><empty_output><jupyter_text>由于中间有时间缺失，需要补充时间及进入图书馆的人数<jupyter_code>i = datetime.date(2020, 1, 1)  # 默认将i设为数据的起始时间
# 判断起始日（2020-01-01）是否有数据，若不存在则将起始日的数据进行添加
if i not in df.index.values.tolist():
    # ps：需要设置索引
    new = pd.DataFrame({'Year': 2020, 'Month': 1, 'Day': 1, 'Season': 3, 'Term': 1, 'Weekday': 3, 'Count': 0},
                       index=[i])   # 自定义索引为：i ，这里也可以不设置index
    # df[df.index==i]=[2020,1,1,3,1,3,0]
    df = df.append(new, ignore_index=False)
## 进行数据缺失的检测：缺失增添数据规则：使用前一周的同一星期进行填充（eg：本周三数据缺失，使用上一周的周三数据(若存在数据)填充）
while i <= datetime.date(2021, 6, 30):
    if i not in df.index.values.tolist():
        ## 季节和学期信息
        yf = int(i.strftime('%m'))
        jj, xq = large_date(yf)
        # 获取此日期前一周的同一天时间
        i_7last = i-datetime.timedelta(days=7)
        ## 星期信息
        week = i.weekday()+1
        # 判断前七天的数据是否在读取的数据内
        if i_7last >= datetime.date(2020, 1, 1):  # 如果前一周的数据在所获取的数据的范围内：
            # df[i]=[int(i.strftime('%Y')),int(i.strftime('%m')),int(i.strftime('%d')),jj,xq,week,df[i_7last]['Count']]
            count=df.iloc[df.index==i_7last]['Count']
        else:  # 若所获取的数据不在范围内，则取前一天的数据：
            i_1last = i-datetime.timedelta(days=1)
            # df[i]=[int(i.strftime('%Y')),int(i.strftime('%m')),int(i.strftime('%d')),jj,xq,week,df[i_1last]['Count']]
            count=df.iloc[df.index==i_1last]['Count']
        new=pd.DataFrame({'Year': int(i.strftime('%Y')), 'Month': int(i.strftime('%m')), 'Day': int(i.strftime('%d')), 'Season': jj, 'Term': xq, 'Weekday': week, 'Count': 0},index=[i])
        df = df.append(new, ignore_index=False)
    i = i+datetime.timedelta(days=1)
df=df.sort_index()<jupyter_output><empty_output><jupyter_text>数据集划分 将2020-01-01～2020-12-31的数据划分为训练集  2021-01-01～2021-06-30的数据划分为测试集<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

# 设置中文显示
plt.rcParams['font.sans-serif'] = ['Microsoft JhengHei']
plt.rcParams['axes.unicode_minus'] = False

import math
import sklearn.metrics as skm
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import Dense, Flatten
from tensorflow.keras.layers import Conv1D,MaxPooling1D
df.shape
df.iloc[df.index==datetime.date(2020,1,6)]
df.iloc[df.index==datetime.date(2021,1,4)]
df.iloc[df.index==datetime.date(2021,6,27)]<jupyter_output><empty_output><jupyter_text>将2020-01-06～2021.1.3划分为训练集   2021.1.4～2021.6.27 划分为测试集<jupyter_code>df[5:369]
df[369:-3]
## 划分数据集
def split_dataset(data):
    '''
    该函数实现以周为单位切分训练数据和测试数据
    '''
    # data为统计数据，shape为(547, 7)
    train, test = data[5:369], data[369:-3]
    train = np.array(np.split(train, len(train)/7)) # 将数据划分为按周为单位的数据
    test = np.array(np.split(test, len(test)/7))
    return train, test<jupyter_output><empty_output><jupyter_text>模型 CNN<jupyter_code># def evaluate_forecasts(actual, predicted):
#     '''
#     该函数实现根据预期值评估一个或多个周预测损失
#     思路：统计所有单日预测的 RMSE
#     '''
#     scores = list()
#     for i in range(actual.shape[1]):
#         mse = skm.mean_squared_error(actual[:, i], predicted[:, i])
#         rmse = math.sqrt(mse)
#         scores.append(rmse)
    
#     s = 0 # 计算总的 RMSE
#     for row in range(actual.shape[0]):
#         for col in range(actual.shape[1]):
#             s += (actual[row, col] - predicted[row, col]) ** 2
#     score = math.sqrt(s / (actual.shape[0] * actual.shape[1]))
#     print('actual.shape[0]:{}, actual.shape[1]:{}'.format(actual.shape[0], actual.shape[1]))
#     return score, scores

# def summarize_scores(name, score, scores):
#     s_scores = ', '.join(['%.1f' % s for s in scores])
#     print('%s: [%.3f] %s\n' % (name, score, s_scores))
    
# def sliding_window(train, sw_width=7, n_out=7, in_start=0):
#     '''
#     该函数实现窗口宽度为7、滑动步长为1的滑动窗口截取序列数据
#     '''
#     data = train.reshape((train.shape[0] * train.shape[1], train.shape[2])) # 将以周为单位的样本展平为以天为单位的序列
#     X, y = [], []
    
#     for _ in range(len(data)):
#         in_end = in_start + sw_width
#         out_end = in_end + n_out
        
#         # 保证截取样本完整，最大元素索引不超过原序列索引，则截取数据；否则丢弃该样本
#         if out_end < len(data):
#             # 训练数据以滑动步长1截取
#             train_seq = data[in_start:in_end, 0]
#             train_seq = train_seq.reshape((len(train_seq), 1))
#             X.append(train_seq)
#             y.append(data[in_end:out_end, 0])
#         in_start += 1
        
#     return np.array(X), np.array(y)

# def cnn_model(train, sw_width, in_start=0, verbose_set=0, epochs_num=20, batch_size_set=4):
#     '''
#     该函数定义 1D CNN 模型
#     '''
#     train_x, train_y = sliding_window(train, sw_width, in_start=0)
    
#     n_timesteps, n_features, n_outputs = train_x.shape[1], train_x.shape[2], train_y.shape[1]
    
#     model = Sequential()
#     model.add(Conv1D(filters=16, kernel_size=3, activation='relu', 
#                      input_shape=(n_timesteps, n_features)))
    
#     model.add(MaxPooling1D(pool_size=2))
#     model.add(Flatten())
#     model.add(Dense(10, activation='relu'))
#     model.add(Dense(units=n_outputs))
    
#     model.compile(loss='mse', optimizer='adam', metrics=['accuracy'])
#     print(model.summary())
    
#     model.fit(train_x, train_y,
#               epochs=epochs_num, batch_size=batch_size_set, verbose=verbose_set)
#     return model

# def forecast(model, pred_seq, sw_width):
#     '''
#     该函数实现对输入数据的预测
#     '''
#     data = np.array(pred_seq)
#     data = data.reshape((data.shape[0]*data.shape[1], data.shape[2]))
    
#     input_x = data[-sw_width:, 0] # 获取输入数据的最后一周的数据
#     input_x = input_x.reshape((1, len(input_x), 1)) # 重塑形状[1, sw_width, 1]
    
#     yhat = model.predict(input_x, verbose=0) # 预测下周数据
#     yhat = yhat[0] # 获取预测向量
#     return yhat

# def evaluate_model(model, train, test, sd_width):
#     '''
#     该函数实现模型评估
#     '''
#     history_fore = [x for x in train]
#     predictions = list() # 用于保存每周的前向验证结果；
#     for i in range(len(test)):
#         yhat_sequence = forecast(model, history_fore, sd_width) # 预测下周的数据
#         predictions.append(yhat_sequence) # 保存预测结果
#         history_fore.append(test[i, :]) # 得到真实的观察结果并添加到历史中以预测下周
    
#     predictions = np.array(predictions) # 评估一周中每天的预测结果
#     score, scores = evaluate_forecasts(test[:, :, 0], predictions)
#     return score, scores

# def model_plot(score, scores, days, name):
#     '''
#     该函数实现绘制RMSE曲线图
#     '''
#     plt.figure(figsize=(8,6), dpi=150)
#     plt.plot(days, scores, marker='o', label=name)
#     plt.grid(linestyle='--', alpha=0.5)
#     plt.ylabel(r'$RMSE$', size=15)
#     plt.title('CNN 模型预测结果',  size=18)
#     plt.legend()
#     plt.show()
    
# def main_run(dataset, sw_width, days, name, in_start, verbose, epochs, batch_size):
#     '''
#     主函数：数据处理、模型训练流程
#     '''
#     # 划分训练集和测试集
#     train, test = split_dataset(dataset.values)
#     # 训练模型
#     model = cnn_model(train, sw_width, in_start, verbose_set=0, epochs_num=20, batch_size_set=4)
#     # 计算RMSE
#     score, scores = evaluate_model(model, train, test, sw_width)
#     # 打印分数
#     summarize_scores(name, score, scores)
#     # 绘图
#     model_plot(score, scores, days, name)
        
# if __name__ == '__main__':
    
# #     dataset = pd.read_csv('household_power_consumption_days.csv', header=0, 
# #                    infer_datetime_format=True, engine='c',
# #                    parse_dates=['datetime'], index_col=['datetime'])
#     dateset=df
#     days = ['sun', 'mon', 'tue', 'wed', 'thr', 'fri', 'sat']
#     name = 'cnn'
    
#     sliding_window_width=7
#     input_sequence_start=0
    
#     epochs_num=20
#     batch_size_set=4
#     verbose_set=0
    
#     main_run(dateset, sliding_window_width, days, name, input_sequence_start,
#              verbose_set, epochs_num, batch_size_set)<jupyter_output><empty_output><jupyter_text>训练模型LSTM<jupyter_code>import tensorflow as tf
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

# 设置中文显示
plt.rcParams['font.sans-serif'] = ['Microsoft JhengHei']
plt.rcParams['axes.unicode_minus'] = False

import math
import sklearn.metrics as skm
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import LSTM, Dense


# def split_dataset(data):
#     '''
#     该函数实现以周为单位切分训练数据和测试数据
#     '''
#     # data为按天的耗电量统计数据，shape为(1442, 8)
#     # 测试集取最后一年的46周（322天）数据，剩下的159周（1113天）数据为训练集，以下的切片实现此功能。
#     train, test = data[1:-328], data[-328:-6]
#     train = np.array(np.split(train, len(train)/7)) # 将数据划分为按周为单位的数据
#     test = np.array(np.split(test, len(test)/7))
#     return train, test

def evaluate_forecasts(actual, predicted):
    '''
    该函数实现根据预期值评估一个或多个周预测损失
    思路：统计所有单日预测的 RMSE
    '''
    scores = list()
    for i in range(actual.shape[1]):
        mse = skm.mean_squared_error(actual[:, i], predicted[:, i])
        rmse = math.sqrt(mse)
        scores.append(rmse)
    
    s = 0 # 计算总的 RMSE
    for row in range(actual.shape[0]):
        for col in range(actual.shape[1]):
            s += (actual[row, col] - predicted[row, col]) ** 2
    score = math.sqrt(s / (actual.shape[0] * actual.shape[1]))
    print('actual.shape[0]:{}, actual.shape[1]:{}'.format(actual.shape[0], actual.shape[1]))
    return score, scores

def summarize_scores(name, score, scores):
    s_scores = ', '.join(['%.1f' % s for s in scores])
    print('%s: [%.3f] %s\n' % (name, score, s_scores))
    
def sliding_window(train, sw_width=7, n_out=7, in_start=0):
    '''
    该函数实现窗口宽度为7、滑动步长为1的滑动窗口截取序列数据
    '''
    data = train.reshape((train.shape[0] * train.shape[1], train.shape[2])) # 将以周为单位的样本展平为以天为单位的序列
    X, y = [], []
    
    for _ in range(len(data)):
        in_end = in_start + sw_width
        out_end = in_end + n_out
        
        # 保证截取样本完整，最大元素索引不超过原序列索引，则截取数据；否则丢弃该样本
        if out_end < len(data):
            # 训练数据以滑动步长1截取
            train_seq = data[in_start:in_end, 0]
            train_seq = train_seq.reshape((len(train_seq), 1))
            X.append(train_seq)
            y.append(data[in_end:out_end, 0])
        in_start += 1
        
    return np.array(X), np.array(y)

def lstm_model(train, sw_width, in_start=0, verbose_set=0, epochs_num=20, batch_size_set=4):
    '''
    该函数定义 LSTM 模型
    单变量
    '''
    train_x, train_y = sliding_window(train, sw_width, in_start=0)
    
    n_timesteps, n_features, n_outputs = train_x.shape[1], train_x.shape[2], train_y.shape[1]
    
    model = Sequential()
    model.add(LSTM(200, activation='relu', 
                   input_shape=(n_timesteps, n_features)))
    model.add(Dense(100, activation='relu'))
    model.add(Dense(n_outputs))
    
    model.compile(loss='mse', optimizer='adam', metrics=['accuracy'])
    print(model.summary())
    
    model.fit(train_x, train_y,
              epochs=epochs_num, batch_size=batch_size_set, verbose=verbose_set)
    return model

def forecast(model, pred_seq, sw_width):
    '''
    该函数实现对输入数据的预测
    '''
    data = np.array(pred_seq)
    data = data.reshape((data.shape[0]*data.shape[1], data.shape[2]))
    
    input_x = data[-sw_width:, 0] # 获取输入数据的最后一周的数据
    input_x = input_x.reshape((1, len(input_x), 1)) # 重塑形状[1, sw_width, 1]
    
    yhat = model.predict(input_x, verbose=0) # 预测下周数据
    yhat = yhat[0] # 获取预测向量
    return yhat

def evaluate_model(model, train, test, sd_width):
    '''
    该函数实现模型评估
    '''
    history_fore = [x for x in train]
    predictions = list() # 用于保存每周的前向验证结果；
    for i in range(len(test)):
        yhat_sequence = forecast(model, history_fore, sd_width) # 预测下周的数据
        predictions.append(yhat_sequence) # 保存预测结果
        history_fore.append(test[i, :]) # 得到真实的观察结果并添加到历史中以预测下周
    
    predictions = np.array(predictions) # 评估一周中每天的预测结果
    score, scores = evaluate_forecasts(test[:, :, 0], predictions)
    return score, scores

def model_plot(score, scores, days, name):
    '''
    该函数实现绘制RMSE曲线图
    '''
    plt.figure(figsize=(8,6), dpi=150)
    plt.plot(days, scores, marker='o', label=name)
    plt.grid(linestyle='--', alpha=0.5)
    plt.ylabel(r'$RMSE$', size=15)
    plt.title('LSTM 模型预测结果',  size=18)
    plt.legend()
    plt.show()
    
def main_run(dataset, sw_width, days, name, in_start, verbose, epochs, batch_size):
    '''
    主函数：数据处理、模型训练流程
    '''
    save_path='predict_tsg_num.h5'
    # 划分训练集和测试集
    train, test = split_dataset(dataset.values)
    # 训练模型
    model = lstm_model(train, sw_width, in_start, verbose_set=0, epochs_num=20, batch_size_set=4)
    #  保存模型
    model.save(save_path)
    # 计算RMSE
    score, scores = evaluate_model(model, train, test, sw_width)
    # 打印分数
    summarize_scores(name, score, scores)
    # 绘图
    model_plot(score, scores, days, name)

    
if __name__ == '__main__':
    
#     dataset = pd.read_csv('household_power_consumption_days.csv', header=0, 
#                    infer_datetime_format=True, engine='c',
#                    parse_dates=['datetime'], index_col=['datetime'])
    dataset=df
    days = ['sun', 'mon', 'tue', 'wed', 'thr', 'fri', 'sat']
    name = 'LSTM'
    
    sliding_window_width= 7
    input_sequence_start=0
    
    epochs_num=70
    batch_size_set=16
    verbose_set=0
    
    
    main_run(dataset, sliding_window_width, days, name, input_sequence_start,
             verbose_set, epochs_num, batch_size_set)


# forecast(model, pred_seq, 7)
# def main_run1(dataset, sw_width, days, name, in_start, verbose, epochs, batch_size):
#     '''
#     主函数：数据处理、模型训练流程
#     '''
#     # 划分训练集和测试集
#     train, test = split_dataset(dataset.values)
#     # 训练模型
#     model = lstm_model(train, sw_width, in_start, verbose_set=0, epochs_num=20, batch_size_set=4)
#     history_fore = [x for x in train]
#     forcast=forecast(model, history_fore, sw_width)
#     print(forcast)
# if __name__ == '__main__':
    
# #     dataset = pd.read_csv('household_power_consumption_days.csv', header=0, 
# #                    infer_datetime_format=True, engine='c',
# #                    parse_dates=['datetime'], index_col=['datetime'])
#     dataset=df
#     days = ['sun', 'mon', 'tue', 'wed', 'thr', 'fri', 'sat']
#     name = 'LSTM'
    
#     sliding_window_width= 7
#     input_sequence_start=0
    
#     epochs_num=70
#     batch_size_set=16
#     verbose_set=0
    
    
#     main_run1(dataset, sliding_window_width, days, name, input_sequence_start,
#              verbose_set, epochs_num, batch_size_set)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15960024227740763, 'fraction_numerical': 0.02798304058146578, 'mean_word_length': 3.113353263577479, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 42, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '244231', 'license': 'MIT', 'n_tokens_mistral': 7288, 'n_tokens_neox': 7418, 'n_words': 1030, 'path': 'tensorflow-GPU/tsg/model_train.ipynb', 'repo_name': 'ZZh2333/ubuntuProjectFiles', 'size': '16508'}