使用python和plotly分析可视化mysql数据库

数据分析
文章目录
  1. 1. 分析结果
  2. 2. 分析步骤
    1. 2.1. 在Mysql中创建world数据库
    2. 2.2. 分析可视化world数据
      1. 2.2.1. 导入外部包
        1. 2.2.1.1. 使用pymysql连接查询数据库
        2. 2.2.1.2. 将查询的结果加载到DataFrame中
        3. 2.2.1.3. 计算汇总统计量
      2. 2.2.2. 先只分析关心的几个国家的数据
        1. 2.2.2.1. 使用plotly 下拉列表显示关心国家的信息
      3. 2.2.3. 计算GNP与平均寿命和人口的相关性
      4. 2.2.4. 根据洲建立数据透视表分析
        1. 2.2.4.1. 使用饼状图分析各大洲人口占比
        2. 2.2.4.2. seaborn绘制各洲GNP平均值对比
        3. 2.2.4.3. 对全球平均寿命分组绘图
      5. 2.2.5. 利用散点图绘制各国的平均寿命和GNP
      6. 2.2.6. 绘制气泡图

分析结果

选择Mysql官网上的world数据库，包含所有国家的所属大洲、人口、平均寿命、国民生产总值GNP，得出的结论如下：

  1. 全球平均寿命37岁，平均国民生产总值1.2万亿美元
  2. 在美国、中国、韩国、匈牙利、澳大利亚五个国家中，国民生产总值最高的是澳大利亚，平均寿命最高的是美国79.4，人口最多的中国13亿
  3. 看国民产生总值与人口、平均寿命有没有相关性，都是弱相关，人口相关性高一点
    相关系数<0.4显著弱相关; 0.4-0.75中等相关; 大于0.75强相关
  4. 北美的国民生产总值最高，人口亚洲最多占51%
  5. 平均寿命65-75最多，84个，占38%
  6. 国民生产总值和平均寿命都位于前前列的国家是美国、中国、日本

分析步骤

在Mysql中创建world数据库

将MySQL的“world”样例数据库导入到本地中。

1
2
3
CREATE DATABASE world;
USE world;
SOURCE /Desktop/world.sql;

分析可视化world数据

导入外部包

sns.set_style 为微软雅黑，才能在图表中正常显示中文

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# plotly库
import pymysql.cursors
import pandas as pd
import plotly.plotly as py
import plotly
from plotly.graph_objs import *
plotly.tools.set_credentials_file(username='walsky', api_key='...')
py.sign_in("walsky", "...")

# pandas、nump、seaborn库
from pandas import DataFrame, Series
import numpy as np
from numpy import random
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns
sns.set_style('whitegrid',{'font.sans-serif':['simhei','Arial']})
import plotly.graph_objs as go
plotly.offline.init_notebook_mode()
使用pymysql连接查询数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
connection = pymysql.connect(host = 'localhost',
                             user = 'walsky',
                             password = 'wal139491007',
                             db = 'world',
                             charset='utf8mb4',
                             cursorclass = pymysql.cursors.DictCursor
                             )
try:
    with connection.cursor() as cursor:
        sql = 'select Name, Continent, Population, LifeExpectancy, GNP from Country'
        cursor.execute(sql)
        result = cursor.fetchall()
finally:
    connection.close()
将查询的结果加载到DataFrame中

df1 按照原始查询到的数据生成的DataFrame

1
2
df1 = pd.DataFrame(result)
df1.columns

df 是按照平均寿命升序排列的DataFrame

1
2
3
df = pd.DataFrame( result )
df = df[['Name','Continent','Population','LifeExpectancy','GNP']]
df = df.sort_values(['LifeExpectancy'],ascending=True)

计算汇总统计量

汇总统计量是 数字列的计算结果，包括非空值count数量，最大最小，中位数、均值等。

1
df.describe()

观测前五行数据和中国的数据：

先只分析关心的几个国家的数据

1
2
3
4
5
6
7
# 只分析几个国家的数据
countries = ['United States', 'China', 'South Korea', 'Hungary', 'Austraila', 'Canada']
for country in countries:
    print(country in df.Name.unique())

df_several_country = df1[df1.Name.isin(countries)]
df_several_country



结果为：

使用plotly 下拉列表显示关心国家的信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
strace1 = Scatter(
    x=countries, y=df_several_country.GNP,
    line=Line(
        color='#FAEE1C',
        width=3
    ),
    name='GNP'
)
strace2 = Scatter(
      x =countries,y=df_several_country.LifeExpectancy,
   line=Line(
        color='#F3558E',
        width=3
    ),
    name='LifeExpectancy'
)
strace3 = Scatter(
    x=countries,y=df_several_country.Population,
    line = Line(
        color='#9C1DE7',
        width=3
    ),
    name='Population'
)
data = Data([strace1,strace2,strace3])
layout = Layout(
    title='Using dropdown menus show several countries Info',
    updatemenus=list([
        dict(
            x=-0.05,
            y=1,
            yanchor='top',
            buttons=list([
                dict(
                    args=['visible', [True, True, True, True]],
                    label='All',
                    method='restyle'
                ),
                dict(
                    args=['visible', [True, False, False, False]],
                    label='GDP',
                    method='restyle'
                ),
                dict(
                    args=['visible', [False, True, False, False]],
                    label='LifeExpectancy',
                    method='restyle'
                ),
                dict(
                    args=['visible', [False, False, True, False]],
                    label='Population',
                    method='restyle'
                )
            ]),
        )
    ]),
)
fig = Figure(data=data, layout=layout)
#url_1 = py.plot(data, filename='dropdmenu-for-dashboard', auto_open=False)
plotly.offline.iplot(fig,filename='dropdmenu-for-dashboard')

绘图结果如下图：

计算GNP与平均寿命和人口的相关性

使用corr函数计算相关性，越接近 0，表示越没有相关性。越接近 1，则正相关性越强，越接近 -1，则负相关性越强：GNP与人口的相关性比平均寿命相关性强。

在多变量因素影响时比较有用，例如成绩，房价受多方面因素影响。

1
df1.corr()['GNP'][['LifeExpectancy','Population']].plot.bar()

根据洲建立数据透视表分析

pivot_table分析各大洲平均国民生产总值和人口

1
2
mean_GNP_Po = df1.pivot_table(['GNP','Population'],index='Continent',aggfunc='mean')
mean_GNP_Po

OutPut:

使用饼状图分析各大洲人口占比
1
2
3
4
5
6
7
8
labels=mean_GNP_Po.index
values=mean_GNP_Po.Population
colors = ['#FAEE1C', '#F3558E', '#9C1DE7', '#98FB98','#CD1076','#C1FFC1','#4F94CD']

trace=go.Pie(labels=labels,values=values,marker={'colors': colors},text='Different')
# dashborad需要版本2.0.5以上
#url_2 = py.plot([trace], filename='pie-for-dashboard', auto_open=False)
plotly.offline.iplot([trace], filename='pie-for-dashboard')

绘图结果如下：


seaborn绘制各洲GNP平均值对比

seabron是基于python matplotlib的另一个流行绘图库，图形更加丰富，但没有交互功能。

1
2
3
4
# 每个州GNP平均值对比
gnp_mean = mean_GNP_Po.GNP.sort_values()
gnp_bar = sns.barplot(x=mean_GNP_Po.index,y=gnp_mean,data=mean_GNP_Po)
gnp_bar.set(title='各大洲GNP平均值升序')

对全球平均寿命分组绘图

查看平均寿命分组，使用cut离散化到多个面元

使用plotly bar charts进行绘制

添加鼠标移动到bar chart上面的文字

1
2
3
4
5
6
percent_list = []
sum_size = lf_size.sum()
for i in lf_size:
    p = i/sum_size
    percent_list.append('Count about:' + format(p,'0.1%')) #将小数转换为百分比
percent_list

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
trace_bar = go.Bar(
    x = lf_size.index,
    y = lf_size.values,
    text = percent_list,
    marker=dict(
        color='rgb(158,202,225)',
        line=dict(
            color='rgb(8,48,107)',
            width=1.5,# 外层的边线
        )
    ),
    opacity=0.8
)
data =Data([trace_bar])

layout = go.Layout(
    title = 'The size between different LifeExpectancy in World',
    xaxis=XAxis(title= '不同LifeExpectancy分组'),
    yaxis=YAxis(title='LifeExpectancy')
)

fig = go.Figure(data=data, layout=layout)
plotly.offline.iplot(fig, filename='sizeOfLife-bar')

利用散点图绘制各国的平均寿命和GNP

散点图上每个点对应一个国家，鼠标移动到点上回显示国家名称，平均寿命和GNP。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 绘制散点图
trace1 = Scatter(
    x=df['GNP'],
    y=df['LifeExpectancy'],
    text=country_names,
    mode='markers'
)
layout = Layout(
    title='Life expectancy vs GNP from MySQL world database',
    xaxis=XAxis( type='log', title='GNP' ),
    yaxis=YAxis( title='Life expectancy' ),
)
data = Data([trace1])
fig = Figure(data=data, layout=layout)
plotly.offline.iplot(fig, filename='world GNP vs life expectancy')

绘制气泡图

绘制一幅气泡图，气泡大小表示人口的多少，气泡的颜色代表不同的大洲。
定义一个make_text函数 表示鼠标悬停时显示的字符。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
sizemode = 'area'
# 设置size为area代替diameter（直径），这样显得气泡图更大

sizeref=df['Population'].max()/1e2**2
# 最大气泡区域将在100像素的量级


colors = {
    'Asia':"rgb(255,65,54)",
    'Europe':"rgb(133,20,75)",
    'Africa':"rgb(0,116,217)",
    'North America':"rgb(255,133,27)",
    'South America':"rgb(23,190,207)",
    'Antarctica':"rgb(61,153,112)",
    'Oceania':"rgb(255,220,0)",
}


# 定义一个鼠标悬停产生字体函数
def make_text(X):
    return 'Country: %s\
    <br>Life Expectancy: %s years\
    <br>Population: %s million'\
    % (X['Name'], X['LifeExpectancy'], X['Population']/1e6)

# 定义一个trace_generating函数，返回Scatter对象re

def make_trace(X, continent, sizes, color):
    return Scatter(
        x=X['GNP'],
        y=X['LifeExpectancy'],
        name=continent,    # label continent names on hover
        mode='markers',    # 点标记
        text=X.apply(make_text, axis=1).tolist(),
        marker= Marker(
            color=color,
            size=sizes,            # size大小表示人口
            sizeref=sizeref,
            sizemode=sizemode,
            opacity=0.6,
            line= Line(width=3,color="white")  # 网格颜色
        )
    )
# 初始化Data对象
data = Data()
#一般写作data = Data([trace1,trace2]), Data函数里面是list

# for 循环得到groupby后各个大陆的数据
# 每个大陆dataframe分组 命名为X

for continent, X in df.groupby('Continent'):
    sizes = X['Population']
    color = colors[continent]
    data.append(make_trace(X, continent, sizes, color))

# 设置x,y，图表标题
title = "Life expectancy vs GNP from MySQL world database (bubble chart)"
x_title = "Gross National Product"
y_title = "Life Expectancy [in years]"

# 设置坐标风格，dict
axis_style = dict(
    type='log',
    zeroline=False,       # 不从零开始
    gridcolor='#FFFFFF',
    ticks='outside',
    ticklen=8,            # tick 长度
    tickwidth=1.5
)

# layout
layout = Layout(
    title=title,
    plot_bgcolor='#EFECEA',  # 背景颜色
    hovermode="closest",
    xaxis=XAxis(
        axis_style,
        title=x_title,
        range=[2.5,7.2], # 取log的限制
    ),
    yaxis=YAxis(
        axis_style,
        title=y_title,
    )
)
fig = Figure(data=data, layout=layout)
plotly.offline.iplot(fig, filename='LIFE_GNP_bubble')

绘制结果如下：

分享到 评论