python3连接数据库并用matplotlib可视化代码,自己保存的代码,希望大神可以优化!
###############数据库连接并导入相应的表+表头######################################
######mysql数据库
import pymysql
def data_out_sql(DB, sql):
conn = pymysql.connect(host=DB['host'], port=DB['port'], user=DB['user'], passwd=DB['password'], db=DB['dbname'],
charset='utf8')
# 创建游标
cursor = conn.cursor()
# 执行sql语句
cursor.execute(sql)
# 调出数据
data = cursor.fetchall()
# cols为字段信息 例如(('factory_id', 253, None, 6, 6, 0, False), ('szDeviceId', 253, None, 30, 30, 0, False),('update_time', 7, None, 19, 19, 0, False))
cols = cursor.description
# 执行
mit()
conn.close()
# 将数据truple转换为DataFrame
col = []
for i in cols:
col.append(i[0])
data = list(map(list, data))
data = pd.DataFrame(data,columns=col)
print(data)
return data
####postgresql数据库
#导入包
import psycopg2
import pandas as pd
#连接数据库
dbconn = psycopg2.connect(database="****", user="****", password="*****", host="52.82.58.0", port="5432")
print("Opened database successfully")
#读入表
cur = dbconn.cursor()
cur.execute("SELECT * from zzn_all_school_order_user_account_info_test_m")
data = cur.fetchall()
cols = cur.description
cur.close()
dbconn.close()
#加上表头
col=[]
for i in cols:
col.append(i[0])
data=list(map(list,data))
data=pd.DataFrame(data,columns=col)
#把表读为DataFrame格式
df=pd.DataFrame(data,columns=col)
#每个学校和每个月收益条形图
var = df.groupby(['order_time_m','school_name']).total_trevene.sum()
fig = plt.figure(figsize=(15, 15))
ax1 = fig.add_subplot(1,1,1)
ax1.set_xlabel('order_time_m and school_name',fontsize=20)
ax1.set_ylabel('Sum of total_trevene',fontsize=20)
ax1.set_title("order_time_m and school_name wise Sum of total_trevene",fontsize=20)
var.plot(kind='bar')
#每个月收益条形图
var = df.groupby(['order_time_m']).total_trevene.sum()
fig = plt.figure(figsize=(15, 15))
ax1 = fig.add_subplot(1,1,1)
ax1.set_xlabel('order_time_m',fontsize=20)
ax1.set_ylabel('Sum of total_trevene',fontsize=20)
ax1.set_title(" Sum of total_trevene",fontsize=20)
var.plot(kind='bar')
#每个月所有学校收益累加图
var1 = df.groupby(['order_time_m']).total_trevene.sum()
var2=var1.cumsum()
fig = plt.figure(figsize=(15, 15))
ax1 = fig.add_subplot(1,1,1)
ax1.set_xlabel('order_time_m',fontsize=20)
ax1.set_ylabel('Sum of total_trevene',fontsize=20)
ax1.set_title(" Sum of total_trevene",fontsize=20)
var2.plot(kind='line')
#累加图+柱形图
var = df.groupby(['order_time_m']).total_trevene.sum()
fig = plt.figure(figsize=(15, 15))
ax = fig.add_subplot(1,1,1)
var.plot(kind='bar')
ax1 = ax.twinx()
ax1 = ax.twiny()
var1 = df.groupby(['order_time_m']).total_trevene.sum()
var2=var1.cumsum()
var2.plot(kind='line')
ax.legend(loc=0)
ax.grid()
ax1.set_xlabel('order_time_m',fontsize=20)
ax1.set_ylabel('Sum of total_trevene',fontsize=20)
ax1.set_title(" Sum and cumsum of total_trevene",fontsize=20)
ax1.legend(loc=0)
plt.show()
##### 包月净收入及累积
df1=df[df['product_duration_month']==1]
var_1=df1.groupby(['order_time_m']).xinlu_avgevene.sum()
fig = plt.figure(figsize=(15, 15))
ax = fig.add_subplot(1,1,1)
var_1.plot(kind='bar')
ax1 = ax.twinx()
ax1 = ax.twiny()
df2=df[df['product_duration_month']==1]
var1_m=df2.groupby(['order_time_m']).xinlu_avgevene.sum()
var1_cum=var1_m.cumsum()
var1_cum.plot(kind='line')
ax.legend(loc=0)
ax.grid()
ax1.set_xlabel('order_time_m',fontsize=20)
ax1.set_ylabel('month Sum of xinlu_avgevene_cum',fontsize=20)
ax1.set_title("month Sum and cumsum of xinlu_avgevene_cum",fontsize=20)
ax1.legend(loc=0)
#设置刻度
plt.tick_params(labelsize=15)
labels = ax1.get_xticklabels() + ax1.get_yticklabels()
[label.set_fontname('SimHei') for label in labels]
plt.show()
##### 包学期净收入及累积
df3=df[df['product_duration_month']==5]
var5_m=df3.groupby(['order_time_m']).xinlu_avgevene_cum.max()
fig = plt.figure(figsize=(15, 15))
ax = fig.add_subplot(1,1,1)
var5_m.plot(kind='bar')
ax1 = ax.twinx()
ax1 = ax.twiny()
df4=df[df['product_duration_month']==5]
var5_sm=df4.groupby(['order_time_m']).xinlu_avgevene_cum.max()
var5_cum=var5_sm.cumsum()
var5_cum.plot(kind='line')
ax.legend(loc=0)
ax.grid()
ax1.set_xlabel('order_time_m',fontsize=20)
ax1.set_ylabel('semester Sum of xinlu_avgevene_cum',fontsize=20)
ax1.set_title("semester Sum and cumsum of xinlu_avgevene_cum",fontsize=20)
ax1.legend(loc=0)
#设置刻度
plt.tick_params(labelsize=15)
labels = ax1.get_xticklabels() + ax1.get_yticklabels()
[label.set_fontname('SimHei') for label in labels]
plt.show()
###包月+包学期
var_m=var_1+var5_m
fig = plt.figure(figsize=(15, 15))
ax = fig.add_subplot(1,1,1)
var_m.plot(kind='bar')
ax1 = ax.twinx()
ax1 = ax.twiny()
var_cum=var1_cum+var5_cum
var_cum.plot(kind='line')
ax.legend(loc=0)
ax.grid()
ax1.set_xlabel('order_time_m',fontsize=20)
ax1.set_ylabel('semester and month Sum of xinlu_avgevene_cum',fontsize=20)
ax1.set_title("semester and month Sum and cumsum of xinlu_avgevene_cum",fontsize=20)
ax1.legend(loc=0)
#设置刻度
plt.tick_params(labelsize=15)
labels = ax1.get_xticklabels() + ax1.get_yticklabels()
[label.set_fontname('SimHei') for label in labels]
plt.show()
##分学校分月总人数
sum_people=df.groupby(['order_time_m','school_name','product_name','payed_type_case']).people_co.sum()
len(sum_people)
list3=sum_people.tolist()
##分学校分月总净收入
List2=df.groupby(['order_time_m','school_name','product_name','payed_type_case']).order_co.sum()
list2=List2.tolist()
List1=df.groupby(['order_time_m','school_name','product_name','payed_type_case']).avg_reven_mon.sum()
len(List1)
arpu=[]
for i in range(len(list1)):
arpu.append(list1[i]*list2[i]/list3[i])
y=arpu
from pandas.core.frame import DataFrame
df1=df[['order_time_m','school_name','product_name','payed_type_case','arpu']]
df1
##ARPU画图
var_arpu=df1.groupby(['order_time_m','school_name']).arpu.mean()
fig = plt.figure(figsize=(15, 15))
ax = fig.add_subplot(1,1,1)
var_arpu.plot(kind='bar')
#设置刻度
plt.tick_params(labelsize=15)
labels = ax.get_xticklabels() + ax.get_yticklabels()
[label.set_fontname('SimHei') for label in labels]
plt.show()
##每笔订单的散点图
df.plot.scatter(x='order_co',y='xinlu_trevene')
##热力图
df0=df[['order_time_m','school_name','product_name','payed_type_case','reven']]
target = pd.pivot_table(df0, index='order_time_m',columns='school_name',values= 'reven')
fig = plt.figure(figsize=(15, 15))
ax = fig.add_subplot(1,1,1)
plt.imshow(target, cmap=plt.cm.hot)
plt.colorbar(shrink=0.5)
#设置刻度
plt.tick_params(labelsize=15)
labels = ax.get_xticklabels() + ax.get_yticklabels()
[label.set_fontname('SimHei') for label in labels]
plt.show()
##各学校收入占比 饼状图
schoo_sum=df.groupby('school_name').xinlu_trevene.sum()
xinlu_sum=df['xinlu_trevene'].sum()
y1=schoo_sum/xinlu_sum.tolist()
#labels = '西北大学', '西安交通大学', '西安科技大学', '陕西中医药大学','西安邮电大学','西安欧亚学院','咸阳师范学院' # 设置标签
#sizes = [xibei_sum/xinlu_sum, xj_sum/xinlu_sum, xike_sum/xinlu_sum, shanxizy_sum/xinlu_sum,xy_sum/xinlu_sum,ouya_sum/xinlu_sum,xianyang_sum/xinlu_sum] # 占比,和为100
labels='咸阳师范学院','西北大学','西安交通大学','西安欧亚学院','西安科技大学','西安邮电大学','陕西中医药大学'
sizes=y1
colors = ['yellowgreen', 'gold', 'lightskyblue', 'lightcoral'] # 颜色
explode = (0, 0.1, 0, 0,0,0,0) # 展开第二个扇形,即Hogs,间距为0.1
plt.pie(sizes, explode=explode, labels=labels, colors=colors, autopct='%1.1f%%', shadow=True,
startangle=90) # startangle控制饼状图的旋转方向
plt.axis('equal') # 保证饼状图是正圆,否则会有一点角度偏斜
plt.show()
#### 各学校订单数环形图
vals1=df.groupby('school_name').order_co.sum()
vals2=df.order_co.sum()
y2=vals1/vals2
y3=y2.tolist()
val1 = y3
#val2 = y3
val3=[1]
fig, ax = plt.subplots()
labels = '咸阳师范学院', '西北大学', '西安交通大学', '西安欧亚学院','西安科技大学','西安邮电大学','陕西中医药大学'
ax.pie(val1, radius=1.2,autopct='%1.1f%%',pctdistance=0.9)
#ax.pie(val2, radius=1,autopct='%1.1f%%',pctdistance=0.75)
ax.pie(val3, radius=0.6,colors='w')
ax.set(aspect="equal", title='Pie plot with `ax.pie`') #plt.legend()
plt.legend(labels,bbox_to_anchor=(1, 1), loc='best', borderaxespad=0.)
plt.show()