mariadb可视化工具 Windows

数据可是的话

• 简单入手

• timeline轮播年份
• 柱状轮播图
• 饼状图：
• 线图 + zoom滑块

• GDP考核csv处理，考试片

• timeline_bar
• 饼图
• 线图DataZoomOpts
• 改变线图滑块位置
• streamlit 控件年份 + 柱状图
• streamlit 控件年份 + 饼状图
• metabase可视化
• Metabase制作一个面板两个图表，一个用问题（线图或需要的饼图），一个用SQL简单groupby（饼图或柱状图）

参考文档

简单入手

时间,地点1,地点2,地点3,地点4
1982,5323,1777,2383,1163
1983,5963,1978,2646,1338
1984,7208,2316,3106,1786
1985,9016,2564,3867,2585
1986,10275,2789,4493,2994
1987,12059,3233,5252,3574
1988,15043,3865,6587,4590
1989,16992,4266,7278,5448
1990,18668,5062,7717,5888
1991,21781,5342,9102,7337
1992,26923,5867,11700,9357

!pip install --index-url https://pypi.douban.com/simple pandas
 !pip install --index-url https://pypi.douban.com/simple pyecharts

timeline轮播年份

柱状轮播图

import pandas as pd
df1 = pd.read_csv('gdp版本1.csv')
#读取表头地点
x = []
for i in df1.iloc[0:0]:
    x.append(i)
del x[0]
#读取时间
t1=[]
for i in df1['时间']:
    t1.append(i)
#读取地点GDP
v1 = []
for i in df1.iterrows():
    v = (i[1][1],i[1][2],i[1][3],i[1][4])
    c = []
    for k in v:
        c.append(int(k))
    v1.append(c)

可视化

from pyecharts import options as opts
from pyecharts.charts import Bar, Timeline
from pyecharts.globals import ThemeType
tl = Timeline()
for i in range(11):
    bar = (
        Bar({"theme": ThemeType.MACARONS})
        .add_xaxis(x)
        .add_yaxis("",v1[i])
        .set_global_opts(title_opts=opts.TitleOpts("某地区{}年GDP".format(t1[i])))
    )
    tl.add(bar, "{}年".format(t1[i]))
tl.render_notebook()

饼状图：

from pyecharts import options as opts
from pyecharts.charts import Pie, Timeline
from pyecharts.faker import Faker

tl = Timeline()
for i in range(11):
    pie = (
        Pie()
        .add(
            "商家A",
            [list(z) for z in zip(x,v1[i])],
            rosetype="radius",
            radius=["30%", "55%"],
        )
        .set_global_opts(title_opts=opts.TitleOpts("某商店{}年营业额".format(t1[i])))
    )
    print(v1[i])
    tl.add(pie, "{}年".format(t1[i]))
tl.render_notebook()

线图 + zoom滑块

"""
线图 + zoom滑块
"""
import pandas as pd
df1 = pd.read_csv('gdp版本1.csv')
#读取表头地点
x1 = []
for i in df1.iloc[0:0]:
    x1.append(i)
del x1[0]
#读取时间
t1=[]
for i in df1['时间']:
    t1.append(str(i))
#读取地点GDP
v1 = []
for i in range(1,5):
    g = []
    for k in df1['地点{}'.format(i)]:
        g.append(int(k))
    
    v1.append(g)
import pandas as pd
import numpy as np
from pyecharts.charts import Line
from pyecharts import options as opts


#线图
def show_line():
    line = Line().add_xaxis(t1)

    for i in range(len(x1)):
        line.add_yaxis(x1[i],list(v1[i]),is_smooth=True)

    line.set_global_opts(title_opts=opts.TitleOpts(title="线性滑块"),
                         datazoom_opts=opts.DataZoomOpts(pos_left = True),
                         yaxis_opts=opts.AxisOpts(type_="log",is_scale=True))
    return line

show_line().render_notebook()

GDP考核csv处理，考试片

province,2016y,2015y,2014y,2013y,2012y,2011y,2010y,2009y,2008y,2007y,2006y,2005y,2004y,2003y,2002y,2001y,2000y,1999y,1998y,1997y
北京市,25669.13,23014.59,21330.83,19800.81,17879.4,16251.93,14113.58,12153.03,11115,9846.81,8117.78,6969.52,6033.21,5007.21,4315,3707.96,3161.66,2678.82,2377.18,2077.09
天津市,17885.39,16538.19,15726.93,14442.01,12893.88,11307.28,9224.46,7521.85,6719.01,5252.76,4462.74,3905.64,3110.97,2578.03,2150.76,1919.09,1701.88,1500.95,1374.6,1264.63
河北省,32070.45,29806.11,29421.15,28442.95,26575.01,24515.76,20394.26,17235.48,16011.97,13607.32,11467.6,10012.11,8477.63,6921.29,6018.28,5516.76,5043.96,4514.19,4256.01,3953.78
山西省,13050.41,12766.49,12761.49,12665.25,12112.83,11237.55,9200.86,7358.31,7315.4,6024.45,4878.61,4230.53,3571.37,2855.23,2324.8,2029.53,1845.72,1667.1,1611.08,1476
内蒙古,18128.1,17831.51,17770.19,16916.5,15880.58,14359.88,11672,9740.25,8496.2,6423.18,4944.25,3905.03,3041.07,2388.38,1940.94,1713.81,1539.12,1379.31,1262.54,1153.51
辽宁省,22246.9,28669.02,28626.58,27213.22,24846.43,22226.7,18457.27,15212.49,13668.58,11164.3,9304.52,8047.26,6672,6002.54,5458.22,5033.08,4669.06,4171.69,3881.73,3582.46
吉林省,14776.8,14063.13,13803.14,13046.4,11939.24,10568.83,8667.58,7278.75,6426.1,5284.69,4275.12,3620.27,3122.01,2662.08,2348.54,2120.35,1951.51,1672.96,1577.05,1464.34
黑龙江省,15386.09,15083.67,15039.38,14454.91,13691.58,12582,10368.6,8587,8314.37,7104,6211.8,5513.7,4750.6,4057.4,3637.2,3390.1,3151.4,2866.3,2774.4,2667.5
上海市,28178.65,25123.45,23567.7,21818.15,20181.72,19195.69,17165.98,15046.45,14069.86,12494.01,10572.24,9247.66,8072.83,6694.23,5741.03,5210.12,4771.17,4188.73,3801.09,3438.79
江苏省,77388.28,70116.38,65088.32,59753.37,54058.22,49110.27,41425.48,34457.3,30981.98,26018.48,21742.05,18598.69,15003.6,12442.87,10606.85,9456.84,8553.69,7697.82,7199.95,6680.34
浙江省,47251.36,42886.49,40173.03,37756.59,34665.33,32318.85,27722.31,22990.35,21462.69,18753.73,15718.47,13417.68,11648.7,9705.02,8003.67,6898.34,6141.03,5443.92,5052.62,4686.11
安徽省,24407.62,22005.63,20848.75,19229.34,17212.05,15300.65,12359.33,10062.82,8851.66,7360.92,6112.5,5350.17,4759.3,3923.11,3519.72,3246.71,2902.09,2712.34,2542.96,2347.32
福建省,28810.58,25979.82,24055.76,21868.49,19701.78,17560.18,14737.12,12236.53,10823.01,9248.53,7583.85,6554.69,5763.35,4983.67,4467.55,4072.85,3764.54,3414.19,3159.91,2870.9
江西省,18499,16723.78,15714.63,14410.19,12948.88,11702.82,9451.26,7655.18,6971.05,5800.25,4820.53,4056.76,3456.7,2807.41,2450.48,2175.68,2003.07,1853.65,1719.87,1605.77
山东省,68024.49,63002.33,59426.59,55230.32,50013.24,45361.85,39169.92,33896.65,30933.28,25776.91,21900.19,18366.87,15021.84,12078.15,10275.5,9195.04,8337.47,7493.84,7021.35,6537.07
河南省,40471.79,37002.16,34938.24,32191.3,29599.31,26931.03,23092.36,19480.46,18018.53,15012.46,12362.79,10587.42,8553.79,6867.7,6035.48,5533.01,5052.99,4517.94,4308.24,4041.09
湖北省,32665.38,29550.19,27379.22,24791.83,22250.45,19632.26,15967.61,12961.1,11328.92,9333.4,7617.47,6590.19,5633.24,4757.45,4212.82,3880.53,3545.39,3229.29,3114.02,2856.47
湖南省,31551.37,28902.21,27037.32,24621.67,22154.23,19669.56,16037.96,13059.69,11555,9439.6,7688.67,6596.1,5641.94,4659.99,4151.54,3831.9,3551.49,3214.54,3025.53,2849.27
广东省,80854.91,72812.55,67809.85,62474.79,57067.92,53210.28,46013.06,39482.56,36796.71,31777.01,26587.76,22557.37,18864.62,15844.64,13502.42,12039.25,10741.25,9250.68,8530.88,7774.53
广西,18317.64,16803.12,15672.89,14449.9,13035.1,11720.87,9569.85,7759.16,7021,5823.41,4746.16,3984.1,3433.5,2821.11,2523.73,2279.34,2080.04,1971.41,1911.3,1817.25
海南省,4053.2,3702.76,3500.72,3177.56,2855.54,2522.66,2064.5,1654.21,1503.06,1254.17,1065.67,918.75,819.66,713.96,642.73,579.17,526.82,476.67,442.13,411.16
重庆市,17740.59,15717.27,14262.6,12783.26,11409.6,10011.37,7925.58,6530.01,5793.66,4676.13,3907.23,3467.72,3034.58,2555.72,2232.86,1976.86,1791,1663.2,1602.38,1509.75
四川省,32934.54,30053.1,28536.66,26392.07,23872.8,21026.68,17185.48,14151.28,12601.23,10562.39,8690.24,7385.1,6379.63,5333.09,4725.01,4293.49,3928.2,3649.12,3474.09,3241.47
贵州省,11776.73,10502.56,9266.39,8086.86,6852.2,5701.84,4602.16,3912.68,3561.56,2884.11,2338.98,2005.42,1677.8,1426.34,1243.43,1133.27,1029.92,937.5,858.39,805.79
云南省,14788.42,13619.17,12814.59,11832.31,10309.47,8893.12,7224.18,6169.75,5692.12,4772.52,3988.14,3462.73,3081.91,2556.02,2312.82,2138.31,2011.19,1899.82,1831.33,1676.17
西藏,1151.41,1026.39,920.83,815.67,701.03,605.83,507.46,441.36,394.85,341.43,290.76,248.8,220.34,185.09,162.04,139.16,117.8,105.98,91.5,77.24
陕西省,19399.59,18021.86,17689.94,16205.45,14453.68,12512.3,10123.48,8169.8,7314.58,5757.29,4743.61,3933.72,3175.58,2587.72,2253.39,2010.62,1804,1592.64,1458.4,1363.6
甘肃省,7200.37,6790.32,6836.82,6330.69,5650.2,5020.37,4120.75,3387.56,3166.82,2703.98,2277.35,1933.98,1688.49,1399.83,1232.03,1125.37,1052.88,956.32,887.67,793.57
青海省,2572.49,2417.05,2303.32,2122.06,1893.54,1670.44,1350.43,1081.27,1018.62,797.35,648.5,543.32,466.1,390.2,340.65,300.13,263.68,239.38,220.92,202.79
宁夏,3168.59,2911.77,2752.1,2577.57,2341.29,2102.21,1689.65,1353.31,1203.92,919.11,725.9,612.61,537.11,445.36,377.16,337.44,295.02,264.58,245.44,224.59
新疆,9649.7,9324.8,9273.46,8443.84,7505.31,6610.05,5437.47,4277.05,4183.21,3523.16,3045.26,2604.19,2209.09,1886.35,1612.65,1491.6,1363.56,1163.17,1106.95,1039.85

timeline_bar

df = pd.read_csv('gdp.csv')
df = df.iloc[[0,8,9,18,12],0:].set_index("province")
df = df.T.sort_index()
t =  df.index.tolist() #时间
v1 = np.array(df) #值

x = df.columns.values.tolist() #城市
tl = Timeline()
v = []
for i in v1:
    g = []
    for k in i:
        g.append(k)
    v.append(g)
def timeline_bar() -> Timeline:
    tl = Timeline()
    for i in range(len(t)):
        bar = (
            Bar()
            .add_xaxis(x)
            .add_yaxis("GDP",v[i])
            .set_global_opts(title_opts=opts.TitleOpts("四省{}年GDP".format(t[i])))
        )
        tl.add(bar, "{}年".format(t[i]))
    return tl
timeline_bar().render_notebook()

饼图

from pyecharts import options as opts
from pyecharts.charts import Pie, Timeline
from pyecharts.faker import Faker
df = pd.read_csv('gdp.csv')
df = df.iloc[[0,8,9,18,12],0:].set_index("province")
df = df.T.sort_index()
t =  df.index.tolist() #时间
v1 = np.array(df) #值
x = df.columns.values.tolist() #城市
tl = Timeline()
for i in range(11):
    pie = (
        Pie()
        .add(
            "商家A",
            [list(z) for z in zip(x,v1[i])],
            rosetype="radius",
            radius=["30%", "55%"],
        )
        .set_global_opts(title_opts=opts.TitleOpts("某商店{}年营业额".format(t1[i])))
    )
    tl.add(pie, "{}年".format(t1[i]))
tl.render_notebook()

线图DataZoomOpts

import pandas as pd
import numpy as np
from pyecharts.charts import Line
from pyecharts import options as opts
df = pd.read_csv('gdp.csv')
df = df.iloc[[0,8,9,18,12],0:].set_index("province")
df = df.T.sort_index()
print(df)
t =  df.index.tolist() #时间
x = df.columns.values.tolist() #城市
v1 = []
for i in x:
    g = []
    for k in df['{}'.format(i)]:
        g.append(k)
    v1.append(g)
    
print(v1)
#线图
def show_line():
    line = Line().add_xaxis(t)

    for i in range(len(x)):
        line.add_yaxis(x[i],list(v1[i]),is_smooth=True)

    line.set_global_opts(title_opts=opts.TitleOpts(title="线性滑块"),
                         datazoom_opts=opts.DataZoomOpts(pos_left = True),
                         yaxis_opts=opts.AxisOpts(type_="log",is_scale=True))
    return line

show_line().render_notebook()

改变线图滑块位置

def show_line():
    line = Line().add_xaxis(t)
    for i in range(len(x)):
        line.add_yaxis(x1[i],list(v1[i]),is_smooth=True)

    line.set_global_opts(title_opts=opts.TitleOpts(title="线性滑块"),
                         datazoom_opts=opts.DataZoomOpts(pos_left = True,orient='vertical'),
                         yaxis_opts=opts.AxisOpts(type_="log",is_scale=True))
    return line

show_line().render_notebook()

streamlit 控件年份 + 柱状图

"""
streamlit 控件年份 + 柱状图
"""
import pandas as pd
import numpy as np
from pyecharts import options as opts
import streamlit as st
years  = st.slider('   ',2016,1997)
year = years -1997
from pyecharts.charts import Bar
def bar2():
    df = pd.read_csv('gdp.csv')
    df = df.iloc[[0,8,9,18,12],0:].set_index("province")
    df = df.T.sort_index()
    t =  df.index.tolist() #时间
    v1 = np.array(df) #值
    x = df.columns.values.tolist() #城市
    bar = (
        Bar().add_xaxis(x)
            .add_yaxis(t[year],list(v1[year]))
            .set_global_opts(title_opts=opts.TitleOpts("某地区{}年GDP".format(t[year])))
    )
    return bar
st.components.v1.html( bar2().render_embed( ) , width = 950 , height = 500 , scrolling = False )

streamlit 控件年份 + 饼状图

import pandas as pd
import numpy as np
from pyecharts import options as opts
import streamlit as st
years  = st.slider('   ',2016,1997)
year = years -1997
from pyecharts.charts import Pie
def pie2():
    df = pd.read_csv('gdp.csv')
    df = df.iloc[[0,8,9,18,12],0:].set_index("province")
    df = df.T.sort_index()
    t =  df.index.tolist() #时间
    v1 = np.array(df) #值
    x = df.columns.values.tolist() #城市
    pie = (
        Pie()
        .add(
            "",
            [list(z) for z in zip(x,v1[year])],
            rosetype="radius",
            radius=["30%", "55%"],
        )
        .set_global_opts(title_opts=opts.TitleOpts("某商店{}年营业额".format(t[year])))
    )
    return pie
st.components.v1.html( pie2().render_embed( ) , width = 950 , height = 500 , scrolling = False )

metabase可视化

可视化工具阿里云盘下载 下载如下

cmd运行

输入命令：java -jar metabase.jar

随便写点

这里我们稍后添加

进来之后的界面

这里需要添加一个数据库，或者直接使用第一个sampleDatabase（第二个是我的，别管！）

在这里操作数据库，这里的数据库没有数据目前

Metabase制作一个面板两个图表，一个用问题（线图或需要的饼图），一个用SQL简单groupby（饼图或柱状图）

看题目先

咱们先用问题（线图或需要的饼图）
用sql语句导入数据
1、打开数据库mysql新建查询

-- 查询地址
show variables like '%secure%';
-- 这里我的地址是C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\

把文件放在这

注意我的目录

create database if not exists name_hive;
show databases;
use name_hive;
create table GDP(
    province varchar(10),
    2016y float(9,3),
    2015y float(9,3),
    2014y float(9,3),
    2013y float(9,3),
    2012y float(9,3),
    2011y float(9,3),
    2010y float(9,3),
    2009y float(9,3),
    2008y float(9,3),
    2007y float(9,3),
    2006y float(9,3),
    2005y float(9,3),
    2004y float(9,3),
    2003y float(9,3),
    2002y float(9,3),
    2001y float(9,3),
    2000y float(9,3),
    1999y float(9,3),
    1998y float(9,3),
    1997y float(9,3)
);
set @@sql_mode = ansi;
load data infile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/gdp.csv'  
into table name_hive.gdp    
fields terminated by ','  
lines terminated by '\n'
ignore 1 rows;

查询结果

2、创建新的问题

例如

保存后在仪表盘布局

3、sql创建

丢，其实这个数据导入不太好，真的，因为怎么说呢你参考别的sql数据就知道这个有多差了，所以用sql查询group by 分组个啥这个csv都分好啦，时间归时间的，地点归地点的，好一点的嘛，例如下面这个

地点	时间	GDP
地点1	1999	61651
地点1	1997	6151
地点1	1996	6161
地点1	1995	1651
地点1	1994	6151

昂所以这个顶多最后给你看看这个