表格可视化和线性回归模型预测

摘要：

本项目通过对对三个数据文件的可视化分析，每个数据文件做3个可视化图形(不同的图形)，并建立模型对排名进行预测。

选题背景：

对外经济贸易、旅游业和居民收入情况是衡量一个国家经济和居民生活情况的重要依据。本项目对这三类数据进行可视化分析来体现我国近几年的发展情况。

数据说明：

该项目的数据均来自国家统计局。

实施过程及代码：

 

导入需要用到的库

import pandas as pd 
import matplotlib.pyplot as plt
import numpy as np
import math
plt.rcParams['font.sans-serif']=['SimHei']#这两句作用为防止中文乱码
plt.rcParams['axes.unicode_minus']=False

读取csv数据

economy = pd.read_csv(r'D:/program/表格数据可视化/对外经济贸易 货物进出口总额.csv', header=2)
economy.head(8)

 

 

对表格‘对外经济贸易 货物进出口总额’进行可视化

 

画出进出口总额随时间变化趋势图

x = [2020,2019,2018,2017,2016,2015,2014,2013,2012,2011]
y = []
jinchukouzonge = economy.loc[0][1:]
for i in jinchukouzonge:
    y.append(i)
fig, axes = plt.subplots(1, 1, figsize=(10, 5))
# 折线图
axes.plot(x, y, linestyle='-', color='#DE6B58', marker='x', linewidth=1.5)
# 画网格线
axes.grid(which='minor', c='lightgrey')
# 设置x、y轴标签
axes.set_ylabel("亿元(人民币)")
axes.set_xlabel("年份")
plt.title('进出口总额变化趋势')
plt.savefig(r'D:/program/表格数据可视化/进出口总额.png', dpi=100)
# 展示图片
plt.show()

 

 画出出口总额随时间变化的柱状图

chukouzonge = economy.loc[1][1:]
jinkouzonge = economy.loc[2][1:]
jinchukoucha = economy.loc[3][1:]
y = []
y1 = []
y2 = []
x = [2020,2019,2018,2017,2016,2015,2014,2013,2012,2011]
for i in chukouzonge:
    y.append(i)
for i in jinkouzonge:
    y1.append(i)
for i in jinchukoucha:
    y2.append(i)
size = 10
x = np.array(x)
a = np.random.random(size)
b = np.random.random(size)
c = np.random.random(size)

total_width, n = 0.8, 3
width = total_width / n
x = x - (total_width - width) / 2
plt.figure(figsize=(15,10))
plt.bar(x, y,  width=width, label='出口总额')
plt.bar(x + width, y1, width=width, label='进口总额')
plt.bar(x + 2 * width, y2, width=width, label='进出口差额')
plt.title('进出口情况',fontdict={'weight':'normal','size': 20})
plt.xlabel('年份',fontdict={'weight':'normal','size': 15})
plt.ylabel('亿元(人民币)',fontdict={'weight':'normal','size': 15})
plt.legend()
plt.savefig(r'D:/program/表格数据可视化/进出口情况.png', dpi=100)
plt.show()

 

 进出口总额(美元)占比饼状图

x = ['2020','2019','2018','2017','2016','2015','2014','2013','2012','2011']
y = []
jinchukouzonge = economy.loc[4][1:]
for i in jinchukouzonge:
    y.append(i)
labels = 'Frogs', 'Hogs', 'Dogs', 'Logs'
sizes = [15, 30, 45, 10]
explode = (0.1, 0, 0, 0,0,0,0,0,0,0)  # only "explode" the 2nd slice (i.e. 'Hogs')

fig1, ax1 = plt.subplots()
ax1.pie(y, explode=explode, labels=x, autopct='%1.1f%%',
        shadow=True, startangle=90)
ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.
plt.title('进出口总额(美元)占比饼状图')
plt.savefig(r'D:/program/表格数据可视化/进出口总额(美元)占比饼状图.png', dpi=100)
plt.show()

 

 

对表格‘旅游业—国内旅游情况’进行可视化

travel = pd.read_csv(r'D:/program/表格数据可视化/旅游业—国内旅游情况.csv', header=2)
travel.head(9)

 

 人均消费柱状图

guonei = travel.loc[6][1:]
chengzhen = travel.loc[7][1:]
nongcun = travel.loc[8][1:]
x = [2020,2019,2018,2017,2016,2015,2014,2013,2012,2011]
y = []
y1 = []
y2 = []
for i in guonei:
    y.append(i)
for i in chengzhen:
    y1.append(i)
for i in nongcun:
    y2.append(i)
size = 10
x = np.array(x)
a = np.random.random(size)
b = np.random.random(size)
c = np.random.random(size)

total_width, n = 0.8, 3
width = total_width / n
x = x - (total_width - width) / 2
plt.figure(figsize=(15,10))
plt.bar(x + width, y1, width=width, label='城镇居民国内旅游人均花费',color='g')
plt.bar(x, y,  width=width, label='国内旅游人均花费')
plt.bar(x + 2 * width, y2, width=width, label='农村居民国内旅游人均花费')
plt.title('旅游人均花费情况',fontdict={'weight':'normal','size': 20})
plt.xlabel('年份',fontdict={'weight':'normal','size': 15})
plt.ylabel('元(人民币)',fontdict={'weight':'normal','size': 15})
plt.legend()
plt.savefig(r'D:/program/表格数据可视化/旅游人均花费情况.png', dpi=100)
plt.show()

 

 国内旅游总花费趋势图

x = [2020,2019,2018,2017,2016,2015,2014,2013,2012,2011]
y = []
zonghuafei = travel.loc[3][1:]
for i in jinchukouzonge:
    y.append(i)
fig, axes = plt.subplots(1, 1, figsize=(10, 5))
# 折线图
axes.plot(x, y, linestyle='-', color='r', marker='+', linewidth=1.5)
# 画网格线
axes.grid(which='minor', c='lightgrey')
# 设置x、y轴标签
axes.set_ylabel("亿元(人民币)")
axes.set_xlabel("年份")
plt.title('国内旅游总花费趋势')
plt.savefig(r'D:/program/表格数据可视化/国内旅游总花费趋势图.png', dpi=100)
# 展示图片
plt.show()

 

 国内游客人次饼状图

 

 国内游客人次饼状图

对表格‘人民生活—全国及分城镇居民收支基本情况’进行可视化

life = pd.read_csv(r'D:/program/表格数据可视化/人民生活—全国及分城镇居民收支基本情况.csv', header=2)
life.head(12)

 

 画居民人均可支配收入变化趋势图

x = [2020,2019,2018,2017,2016,2015,2014,2013]
y = []
jumin = life.loc[3][1:]
for i in jumin:
    y.append(i)
fig, axes = plt.subplots(1, 1, figsize=(10, 5))
# 折线图
axes.plot(x, y, linestyle='-', color='b', marker='+', linewidth=1.5)
# 画网格线
axes.grid(which='minor', c='lightgrey')
# 设置x、y轴标签
axes.set_ylabel("元(人民币)")
axes.set_xlabel("年份")
plt.title('居民人均可支配收入变化趋势图')
plt.savefig(r'D:/program/表格数据可视化/居民人均可支配收入变化趋势图.png', dpi=100)
# 展示图片
plt.show()

 

 人均消费支出对比柱状图

jm = life.loc[6][1:]
chengzhen = life.loc[8][1:]
nongcun = life.loc[10][1:]
y = []
y1 = []
y2 = []
for i in jm:
    y.append(i)
for i in chengzhen:
    y1.append(i)
for i in nongcun:
    y2.append(i)
size = 10
x = [2020,2019,2018,2017,2016,2015,2014,2013]
x = np.array(x)
a = np.random.random(size)
b = np.random.random(size)
c = np.random.random(size)

total_width, n = 0.8, 3
width = total_width / n
x = x - (total_width - width) / 2
plt.figure(figsize=(15,10))
plt.bar(x + width, y1, width=width, label='城镇居民人均消费支出',color='g')
plt.bar(x, y,  width=width, label='居民人均消费支出')
plt.bar(x + 2 * width, y2, width=width, label='农村居民人均消费支出')
plt.title('人均消费支出对比柱状图',fontdict={'weight':'normal','size': 20})
plt.xlabel('年份',fontdict={'weight':'normal','size': 15})
plt.ylabel('元(人民币)',fontdict={'weight':'normal','size': 15})
plt.legend()
plt.savefig(r'D:/program/表格数据可视化/人均消费支出对比柱状图.png', dpi=100)
plt.show()

 

 居民人均可支配收入同比增长饼图

x = ['2020','2019','2018','2017','2016','2015','2014']
y = []
guonei = life.loc[1][1:-1]
print(guonei)
for i in guonei:
    y.append(i)
explode = (0, 0, 0, 0.2,0,0,0)  # only "explode" the 2nd slice (i.e. 'Hogs')

fig1, ax1 = plt.subplots()
ax1.pie(y, explode=explode, labels=x, autopct='%1.1f%%',
        shadow=True, startangle=90)
ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.
plt.title('居民人均可支配收入同比增长饼状图')
plt.savefig(r'D:/program/表格数据可视化/居民人均可支配收入同比增长饼状图.png', dpi=100)
plt.show()

2020年     4.7
2019年     8.9
2018年     8.7
2017年     9.0
2016年     8.4
2015年     8.9
2014年    10.1
Name: 1, dtype: object

 

通过线性回归模型预测居民人均可支配收入(元)和城镇居民人均可支配收入(元)之间的关系

X = []
y = []
for i in life.loc[0][1:]:
    X.append(i)
for i in life.loc[2][1:]:
    y.append(i)

print(X)
print(y)

 

X_train = X[:9]
y_train = y[:9]
n_train = len(X_train)

X_test = X[5:]
y_test = y[5:]
n_test = len(X_test)

训练模型 y = w*x+b

 

实际数据的分布接近一条直线所以可以直接用一次函数线性回归模型即可预测，后面也使用了二次函数线性回归模型，两者预测效果基本一致

from sklearn.linear_model import LinearRegression
from sklearn.linear_model import Lasso
from sklearn.linear_model import Ridge
x_data = np.array(X_train)
y_data = np.array(y_train ) 
x_data = x_data.reshape(-1,1)
y_data = y_data.reshape(-1,1)
X2 = np.hstack([x_data])
print(X2.shape)
model = LinearRegression()  # 线性回归
model.fit(X2,y_data)
y_pre = model.predict(X2)
t = np.arange(len(x_data))
plt.plot(x_data,y_pre,color='g')
plt.scatter(x_data, y_data)
plt.title('y = w*x+w2*(x^2)+b')
plt.legend(('预测值','实际值'),loc='upper left')
plt.show()

 

 

训练模型 y = wx+w2(x^2)+b

x_data = np.array(X_train)
y_data = np.array(y_train ) 
x_data = x_data.reshape(-1,1)
y_data = y_data.reshape(-1,1)
X2 = np.hstack([x_data ** 2,x_data])
print(X2.shape)
model = LinearRegression()  # 线性回归
model.fit(X2,y_data)
y_pre = model.predict(X2)
t = np.arange(len(x_data))
plt.plot(x_data,y_pre,color='g')
plt.scatter(x_data, y_data)
plt.title('y = w*x+w2*(x^2)+b')
plt.legend(('预测值','实际值'),loc='upper left')
plt.show()

完整代码

import pandas as pd 
import matplotlib.pyplot as plt
import numpy as np
import math
plt.rcParams['font.sans-serif']=['SimHei']#这两句作用为防止中文乱码
plt.rcParams['axes.unicode_minus']=False

economy = pd.read_csv(r'D:/program/表格数据可视化/对外经济贸易 货物进出口总额.csv', header=2)
economy.head(8)

x = [2020,2019,2018,2017,2016,2015,2014,2013,2012,2011]
y = []
jinchukouzonge = economy.loc[0][1:]
for i in jinchukouzonge:
    y.append(i)
fig, axes = plt.subplots(1, 1, figsize=(10, 5))
# 折线图
axes.plot(x, y, linestyle='-', color='#DE6B58', marker='x', linewidth=1.5)
# 画网格线
axes.grid(which='minor', c='lightgrey')
# 设置x、y轴标签
axes.set_ylabel("亿元(人民币)")
axes.set_xlabel("年份")
plt.title('进出口总额变化趋势')
plt.savefig(r'D:/program/表格数据可视化/进出口总额.png', dpi=100)
# 展示图片
plt.show()

chukouzonge = economy.loc[1][1:]
jinkouzonge = economy.loc[2][1:]
jinchukoucha = economy.loc[3][1:]
y = []
y1 = []
y2 = []
x = [2020,2019,2018,2017,2016,2015,2014,2013,2012,2011]
for i in chukouzonge:
    y.append(i)
for i in jinkouzonge:
    y1.append(i)
for i in jinchukoucha:
    y2.append(i)
size = 10
x = np.array(x)
a = np.random.random(size)
b = np.random.random(size)
c = np.random.random(size)

total_width, n = 0.8, 3
width = total_width / n
x = x - (total_width - width) / 2
plt.figure(figsize=(15,10))
plt.bar(x, y,  width=width, label='出口总额')
plt.bar(x + width, y1, width=width, label='进口总额')
plt.bar(x + 2 * width, y2, width=width, label='进出口差额')
plt.title('进出口情况',fontdict={'weight':'normal','size': 20})
plt.xlabel('年份',fontdict={'weight':'normal','size': 15})
plt.ylabel('亿元(人民币)',fontdict={'weight':'normal','size': 15})
plt.legend()
plt.savefig(r'D:/program/表格数据可视化/进出口情况.png', dpi=100)
plt.show()

x = ['2020','2019','2018','2017','2016','2015','2014','2013','2012','2011']
y = []
jinchukouzonge = economy.loc[4][1:]
for i in jinchukouzonge:
    y.append(i)
labels = 'Frogs', 'Hogs', 'Dogs', 'Logs'
sizes = [15, 30, 45, 10]
explode = (0.1, 0, 0, 0,0,0,0,0,0,0)  # only "explode" the 2nd slice (i.e. 'Hogs')

fig1, ax1 = plt.subplots()
ax1.pie(y, explode=explode, labels=x, autopct='%1.1f%%',
        shadow=True, startangle=90)
ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.
plt.title('进出口总额(美元)占比饼状图')
plt.savefig(r'D:/program/表格数据可视化/进出口总额(美元)占比饼状图.png', dpi=100)
plt.show()

x = ['2020','2019','2018','2017','2016','2015','2014','2013','2012','2011']
y = []
jinchukouzonge = economy.loc[4][1:]
for i in jinchukouzonge:
    y.append(i)
labels = 'Frogs', 'Hogs', 'Dogs', 'Logs'
sizes = [15, 30, 45, 10]
explode = (0.1, 0, 0, 0,0,0,0,0,0,0)  # only "explode" the 2nd slice (i.e. 'Hogs')

fig1, ax1 = plt.subplots()
ax1.pie(y, explode=explode, labels=x, autopct='%1.1f%%',
        shadow=True, startangle=90)
ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.
plt.title('进出口总额(美元)占比饼状图')
plt.savefig(r'D:/program/表格数据可视化/进出口总额(美元)占比饼状图.png', dpi=100)
plt.show()

travel = pd.read_csv(r'D:/program/表格数据可视化/旅游业—国内旅游情况.csv', header=2)
travel.head(9)

guonei = travel.loc[6][1:]
chengzhen = travel.loc[7][1:]
nongcun = travel.loc[8][1:]
x = [2020,2019,2018,2017,2016,2015,2014,2013,2012,2011]
y = []
y1 = []
y2 = []
for i in guonei:
    y.append(i)
for i in chengzhen:
    y1.append(i)
for i in nongcun:
    y2.append(i)
size = 10
x = np.array(x)
a = np.random.random(size)
b = np.random.random(size)
c = np.random.random(size)

total_width, n = 0.8, 3
width = total_width / n
x = x - (total_width - width) / 2
plt.figure(figsize=(15,10))
plt.bar(x + width, y1, width=width, label='城镇居民国内旅游人均花费',color='g')
plt.bar(x, y,  width=width, label='国内旅游人均花费')
plt.bar(x + 2 * width, y2, width=width, label='农村居民国内旅游人均花费')
plt.title('旅游人均花费情况',fontdict={'weight':'normal','size': 20})
plt.xlabel('年份',fontdict={'weight':'normal','size': 15})
plt.ylabel('元(人民币)',fontdict={'weight':'normal','size': 15})
plt.legend()
plt.savefig(r'D:/program/表格数据可视化/旅游人均花费情况.png', dpi=100)
plt.show()

x = [2020,2019,2018,2017,2016,2015,2014,2013,2012,2011]
y = []
zonghuafei = travel.loc[3][1:]
for i in jinchukouzonge:
    y.append(i)
fig, axes = plt.subplots(1, 1, figsize=(10, 5))
# 折线图
axes.plot(x, y, linestyle='-', color='r', marker='+', linewidth=1.5)
# 画网格线
axes.grid(which='minor', c='lightgrey')
# 设置x、y轴标签
axes.set_ylabel("亿元(人民币)")
axes.set_xlabel("年份")
plt.title('国内旅游总花费趋势')
plt.savefig(r'D:/program/表格数据可视化/国内旅游总花费趋势图.png', dpi=100)
# 展示图片
plt.show()

life = pd.read_csv(r'D:/program/表格数据可视化/人民生活—全国及分城镇居民收支基本情况.csv', header=2)
life.head(12)

x = [2020,2019,2018,2017,2016,2015,2014,2013]
y = []
jumin = life.loc[3][1:]
for i in jumin:
    y.append(i)
fig, axes = plt.subplots(1, 1, figsize=(10, 5))
# 折线图
axes.plot(x, y, linestyle='-', color='b', marker='+', linewidth=1.5)
# 画网格线
axes.grid(which='minor', c='lightgrey')
# 设置x、y轴标签
axes.set_ylabel("元(人民币)")
axes.set_xlabel("年份")
plt.title('居民人均可支配收入变化趋势图')
plt.savefig(r'D:/program/表格数据可视化/居民人均可支配收入变化趋势图.png', dpi=100)
# 展示图片
plt.show()

jm = life.loc[6][1:]
chengzhen = life.loc[8][1:]
nongcun = life.loc[10][1:]
y = []
y1 = []
y2 = []
for i in jm:
    y.append(i)
for i in chengzhen:
    y1.append(i)
for i in nongcun:
    y2.append(i)
size = 10
x = [2020,2019,2018,2017,2016,2015,2014,2013]
x = np.array(x)
a = np.random.random(size)
b = np.random.random(size)
c = np.random.random(size)

total_width, n = 0.8, 3
width = total_width / n
x = x - (total_width - width) / 2
plt.figure(figsize=(15,10))
plt.bar(x + width, y1, width=width, label='城镇居民人均消费支出',color='g')
plt.bar(x, y,  width=width, label='居民人均消费支出')
plt.bar(x + 2 * width, y2, width=width, label='农村居民人均消费支出')
plt.title('人均消费支出对比柱状图',fontdict={'weight':'normal','size': 20})
plt.xlabel('年份',fontdict={'weight':'normal','size': 15})
plt.ylabel('元(人民币)',fontdict={'weight':'normal','size': 15})
plt.legend()
plt.savefig(r'D:/program/表格数据可视化/人均消费支出对比柱状图.png', dpi=100)
plt.show()

x = ['2020','2019','2018','2017','2016','2015','2014']
y = []
guonei = life.loc[1][1:-1]
print(guonei)
for i in guonei:
    y.append(i)
explode = (0, 0, 0, 0.2,0,0,0)  # only "explode" the 2nd slice (i.e. 'Hogs')

fig1, ax1 = plt.subplots()
ax1.pie(y, explode=explode, labels=x, autopct='%1.1f%%',
        shadow=True, startangle=90)
ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.
plt.title('居民人均可支配收入同比增长饼状图')
plt.savefig(r'D:/program/表格数据可视化/居民人均可支配收入同比增长饼状图.png', dpi=100)
plt.show()

X = []
y = []
for i in life.loc[0][1:]:
    X.append(i)
for i in life.loc[2][1:]:
    y.append(i)

print(X)
print(y)

X_train = X[:9]
y_train = y[:9]
n_train = len(X_train)

X_test = X[5:]
y_test = y[5:]
n_test = len(X_test)

from sklearn.linear_model import LinearRegression
from sklearn.linear_model import Lasso
from sklearn.linear_model import Ridge
x_data = np.array(X_train)
y_data = np.array(y_train ) 
x_data = x_data.reshape(-1,1)
y_data = y_data.reshape(-1,1)
X2 = np.hstack([x_data])
print(X2.shape)
model = LinearRegression()  # 线性回归
model.fit(X2,y_data)
y_pre = model.predict(X2)
t = np.arange(len(x_data))
plt.plot(x_data,y_pre,color='g')
plt.scatter(x_data, y_data)
plt.title('y = w*x+w2*(x^2)+b')
plt.legend(('预测值','实际值'),loc='upper left')
plt.show()

x_data = np.array(X_train)
y_data = np.array(y_train ) 
x_data = x_data.reshape(-1,1)
y_data = y_data.reshape(-1,1)
X2 = np.hstack([x_data ** 2,x_data])
print(X2.shape)
model = LinearRegression()  # 线性回归
model.fit(X2,y_data)
y_pre = model.predict(X2)
t = np.arange(len(x_data))
plt.plot(x_data,y_pre,color='g')
plt.scatter(x_data, y_data)
plt.title('y = w*x+w2*(x^2)+b')
plt.legend(('预测值','实际值'),loc='upper left')
plt.show()

 

总结：

可以看出各个指标都随时间增长，足以说明我国近几年的经济和居民生活水平趋于上涨趋势，并且可以看出居民人均可支配收入(元)和城镇居民人均可支配收入(元)之间的关系呈线性关系。