python使用回归算法预测当年每月投产次数

数据治理

发布日期: 2021-04-07

任务：根据前几年的投产数据，预测当年每月可能的投产次数，绘制曲线图

环境：python3

工具：jupter

首先引入容器中已有的库：

from sklearn.linear_model import LinearRegression
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import math
import sqlalchemy
from sqlalchemy.engine import create_engine

pymysql 没有，需要安装。

如能访问外网安装方式：

!pip install pymysql -i https://pypi.mirrors.ustc.edu.cn/simple/

使用内部源在线安装pymysql：

!mkdir ~/.pip/
!touch ~/.pip/pip.conf
!echo [global] > ~/.pip/pip.conf
!echo timeout = 60 >> ~/.pip/pip.conf
!echo index = http://nexusproxy.paas.x/repository/pypi-group/pypi >> ~/.pip/pip.conf
!echo index-url = http://nexusproxy.paas.x/repository/pypi-group/simple >> ~/.pip/pip.conf
!echo trusted-host = nexusproxy.paas.x >> ~/.pip/pip.conf
!pip install pymysql 
import pymysql

提取训练数据：

PREYEAR = 2021

db_connect_string = 'mysql+pymysql://dmaxpro:**********@ip:3307/db'
sql = "select * from tb_online_fact_sum where year >= '2016' and year < '"+ str(PREYEAR) +"' "

sql = sqlalchemy.text(sql)
print(sql)

engine = create_engine(db_connect_string)
df_train = pd.read_sql(sql, engine)
df_train.tail(10)

1 2	#变更数据类型 df_train = df_train.astype({'count':'int','month':'int','year':'int'})

# 开始线性回归
# Y=ax+b
x_train = df_train['month'] 
y_train = df_train['count']

plt.scatter(x_train,y_train)

x_train = x_train.values  # Seial转NArray类型
x_train = x_train.reshape(-1,1)
y_train = y_train.values 

LR = LinearRegression()
LR.fit(x_train,y_train)
predicted = LR.predict(x_train)
print(predicted)

plt.plot(x_train,predicted,color='red')
plt.title('Online Times')
plt.xlabel('Months')
plt.ylabel('Times')
plt.show()

Out:

### 分组处理开始
df_train_group = df_train.groupby('month')
df_result = pd.DataFrame(columns=['id','year','month','count','sumCount'])

# https://www.cnblogs.com/guxh/p/9420610.html
#df_result2.drop(df_result2.index[0:], inplace=True)     # 删除所有行

index = 0
sumTimes = int(0)
for month,df_train_month in df_train_group:
    print('往期数据：')
    print(df_train_month)
    # 建立线性回归模型
    regr = LinearRegression()
    x_train = df_train_month['year']    
    y_train = df_train_month['count']
    x_train = x_train.values.reshape(-1,1)
    y_train = y_train.values
    
    # 拟合
    regr.fit(x_train, y_train) # 注意此处.reshape(-1, 1)，因为X是一维的！
    # 待预测年
    year = PREYEAR
    # 不难得到直线的斜率、截距
    #a, b = regr.coef_, regr.intercept_
    # 方式1：根据直线方程计算的价格
    # print(a * year + b)

    # 方式2：根据predict方法预测的价格
    expect = regr.predict(np.array(year).reshape(1, -1))
    # 取整
    expect1 = int(round(expect[0]))
    print(str(year) + '.' + str(month) + ' 预测数据:' + str(expect1))
    sumTimes = sumTimes + expect1
    s = pd.Series([index, year, month, expect1, sumTimes], index=df_result.columns)
    index = index + 1
    df_result = df_result.append(s,ignore_index=True)
    
    
    # 画图
    # 1.真实的点

    plt.title(str(month) +' Month Online Times')
    plt.xlabel('Year')
    plt.ylabel('Times')

    plt.scatter(x_train, y_train, color='blue')

    # 2.拟合的直线
    plt.plot(x_train, regr.predict(x_train), color='red', linewidth=4)

    plt.show()

Out:

1	df_result.head(50)

Out:

	id	year	month	count	sumCount
0	0	2021	1	183	183
1	1	2021	2	69	252
2	2	2021	3	209	461
3	3	2021	4	257	718
4	4	2021	5	199	917
5	5	2021	6	380	1297
6	6	2021	7	346	1643
7	7	2021	8	274	1917
8	8	2021	9	176	2093
9	9	2021	10	234	2327
10	10	2021	11	272	2599
11	11	2021	12	358	2957

绘制图形：

# 设置画布大小
plt.figure(figsize=(20, 6))

plt.title( str(PREYEAR) + ' Months Online Times')
plt.xlabel('Months')
plt.ylabel('Times')

px = df_result['month']    
py = df_result['count']
pyo = df_result['sumCount']

# plt.scatter(px, py, color='blue')
plt.plot(px, py, label='Online Times Sum',linewidth=2, color='red',  marker='^', markerfacecolor='blue', markersize=12)
plt.plot(px, pyo, label='Online Times PER',linewidth=2, color='blue',  marker='o', markerfacecolor='red', markersize=12)
#plt.plot(px, pyo, color='b', linewidth=1)
for a, b, c in zip(px, py, pyo):
    plt.text(a, b, b, ha='center', va='bottom', fontsize=15)
    plt.text(a, c, c, ha='center', va='bottom', fontsize=15)

# 2.拟合的直线
#plt.plot(x_train, regr.predict(x_train), color='red', linewidth=4)
plt.legend()
plt.show()

Out:

## 存储新数据
print('开始存储结果数据---')
store_db_connect_string = 'mysql+pymysql://dmaxpro:*********@ip:3307/db'
store_engine = create_engine(store_db_connect_string)
df_result.to_sql(name='prod_epg_onlinepredict_temp',con=store_engine,if_exists='append' ,index=False)

#print('存储结果数据结束---')