数据分析案例（5）利用MYSQL+Python对某网站的用户数进行分析

数据与代码链接：
https://pan.baidu.com/s/1vF3hzzI83tDDVl-kM03shQ
提取码：0202

user.sql文件大家自行导入mysql数据库中，我是放在了test01数据库中

数据由于是存储在MYsql数据库的，因此我们需要利用Python连接mysql数据库，把数据取出来

import pymysql
import pandas as pd
import matplotlib.pyplot as plt
#连接MySQL数据库，指定密码（passwd）和数据库（db）
conn = pymysql.connect(host = "localhost",user = 'root',passwd ='123456',db = 'test01',charset="utf8")
#SQL查询语句
sql_query = 'SELECT * FROM test01.user'
#读取MySQL数据
data = pd.read_sql(sql_query, con=conn)
# 关闭数据库连接
conn.close()
# 提取指定列数据
data=data[['username','addtime']]
#列重命名
data.rename(columns = {'addtime':'注册日期','username':'用户数量'},inplace=True)
#将数据类型转换为日期类型
data['注册日期'] = pd.to_datetime(data['注册日期'])
# 将日期设置为索引
data = data.set_index('注册日期')
#按月统计每一年的注册用户
index=['1月','2月','3月','4月','5月','6月','7月','8月','9月','10月','11月','12月']
'''
print(data)
                        用户数量
注册日期                         
2017-01-01 01:57:00  mr000001
2017-01-01 07:33:00  mr000002
2017-01-01 07:50:00  mr000003
2017-01-01 12:28:00  mr000004
2017-01-01 12:44:00  mr000005
...                       ...
2019-12-31 23:01:00  mr192304
2019-12-31 23:16:00  mr192305
2019-12-31 23:16:00  mr192306
2019-12-31 23:17:00  mr192307
2019-12-31 23:35:00  mr192308
'''

#提取2017年(还可以这样取！，datatime类型的就是好用)
df_2017=data['2017']

'''
print(df_2017)
                        用户数量
注册日期                         
2017-01-01 01:57:00  mr000001
2017-01-01 07:33:00  mr000002
2017-01-01 07:50:00  mr000003
2017-01-01 12:28:00  mr000004
2017-01-01 12:44:00  mr000005
...                       ...
2017-12-31 23:24:00  mr018459
2017-12-31 23:25:00  mr018460
2017-12-31 23:44:00  mr018461
2017-12-31 23:55:00  mr018462
2017-12-31 23:57:00  mr018463

data_2017_01=data['2017-01']#提取2017-01月份的数据
print(data_2017_01)
                        用户数量
注册日期                         
2017-01-01 01:57:00  mr000001
2017-01-01 07:33:00  mr000002
2017-01-01 07:50:00  mr000003
2017-01-01 12:28:00  mr000004
2017-01-01 12:44:00  mr000005
...                       ...
2017-01-31 11:51:00  mr000552
2017-01-31 12:21:00  mr000553
2017-01-31 12:49:00  mr000554
2017-01-31 20:22:00  mr000555
2017-01-31 23:31:00  mr000556

'''

df_2017=df_2017.resample('M').size().to_period('M')
df_2017.index=index
'''
print(df_2017)
1月      556
2月      659
3月      814
4月      814
5月      848
6月     1068
7月     1544
8月     1378
9月     1537
10月    2305
11月    3675
12月    3265
dtype: int64
'''
df_2018=data['2018']
df_2018=df_2018.resample('M').size().to_period('M')
df_2018.index=index

df_2019=data['2019']
df_2019=df_2019.resample('M').size().to_period('M')
df_2019.index=index
#合并
dfs=pd.concat([df_2017,df_2018,df_2019],axis=1)
'''
print(dfs)
        0      1     2
1月    556   3983  5863
2月    659   3676  5160
3月    814   6877  7865
4月    814  11394  6391
5月    848   6284  6355
6月   1068   5574  7344
7月   1544  10387  9216
8月   1378   7940  7612
9月   1537   7672  7609
10月  2305   7484  8438
11月  3675   7895  8929
12月  3265   6414  7483
'''
#设置列索引
dfs.columns=['2017年','2018年','2019年']
# 导出数据为Excel文件
dfs.to_excel('result2.xlsx',index=False)
#绘制折线图
#解决中文乱码
plt.rcParams['font.sans-serif']=['SimHei']
plt.title('年度注册用户分析图')
x=index
y1=dfs['2017年']
y2=dfs['2018年']
y3=dfs['2019年']
#一表画多图
plt.plot(x,y1,label='2017年',color='b',marker='o')
plt.plot(x,y2,label='2018年',color='g',marker='o')
plt.plot(x,y3,label='2019年',color='r',marker='o')
#添加文本标签(这样在每个点上都有数据)
for a,b1,b2,b3 in zip(x,y1,y2,y3):
    plt.text(a,b1+200,b1,ha = 'center',va = 'bottom',fontsize=8)
    plt.text(a,b2+100,b2,ha='center', va='bottom', fontsize=8)
    plt.text(a,b3+200,b3,ha='center', va='bottom', fontsize=8)
x = range(0, 12, 1)
plt.xlabel('注册日期')
plt.ylabel('用户数量')
plt.legend()
plt.show()

其中下面是连接数据库并取数的的代码
大家需要更改的是password,db
user一般都是默认的root，如果不是的话，那么也需要改掉

conn = pymysql.connect(host = "localhost",user = 'root',passwd ='123456',db = 'test01',charset="utf8")
#SQL查询语句
sql_query = 'SELECT * FROM test01.user'
#读取MySQL数据
data = pd.read_sql(sql_query, con=conn)
# 关闭数据库连接
conn.close()

下面这个是2018-04月份的新增用户数量
大家还可以更改分析的时间

毕竟数据是有2017-2019三年的数据

import pymysql
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
from pandas.plotting import register_matplotlib_converters
#解决图表显示日期出现警告信息
register_matplotlib_converters()

#连接MySQL数据库，指定密码（passwd）和数据库（db）
conn = pymysql.connect(host = "localhost",user = 'root',passwd ='123456',db = 'test01',charset="utf8")
#SQL查询语句
sql_query = 'SELECT * FROM test01.user'
#读取MySQL数据
data = pd.read_sql(sql_query, con=conn)
# 关闭数据库连接
conn.close()
# print(data.head())
# print(data.info())

data=data[['username','addtime']]
data.rename(columns={'addtime':'注册时间','usertime':'用户数量'},inplace=True)
data['注册时间']=pd.to_datetime(data['注册时间'])
data=data.set_index('注册时间',drop=True)
data=data['2018-04-01':'2018-04-30']
df=data.resample("D").size().to_period("D")
'''
注意这时不能用sum（），sum（）只能机械的把上面的加到下面
此时我们需要的是计数，那就是size()，row_number（）那样的
而如果此时值是数值型的？
就不能再用size（），而必须使用sum()
'''
df.to_excel('result1.xlsx')
x=pd.date_range(start='2018-04-01',periods=30)
y=df

#---------------可视化——————————————————————————————————————
sns.set_style('darkgrid')
plt.rcParams['font.sans-serif']=['SimHei'] #解决中文乱码
plt.title('网站4月份每天新增用户趋势图')
plt.plot(x,y)
plt.xticks(fontproperties = 'Times New Roman', size = 8,rotation=20)#X轴字体大小
plt.xlabel('时间')
plt.ylabel('新增用户数')
plt.show()