南京地铁客运量数据可视化分析 - Pyecharts实现

/--coding:utf8-*-\nimport sqlite3\nimport os\nimport time, datetime\nimport pandas as pd\nfrom pyecharts import Bar, Pie\n\nyear1=2018\n\n#是否打印sql\nSHOW_SQL = True\nDB_FILE_PATH = 'NajingMetro.db'\n\ndef get_conn(path):\n '''获取到数据库的连接对象，参数为数据库文件的绝对路径\n 如果传递的参数是存在，并且是文件，那么就返回硬盘上面改\n 路径下的数据库文件的连接对象；否则，返回内存中的数据接\n 连接对象'''\n conn = sqlite3.connect(path)\n if os.path.exists(path) and os.path.isfile(path):\n print('硬盘上面:[{}]'.format(path))\n return conn\n else:\n conn = None\n print('内存上面:[:memory:]')\n return sqlite3.connect(':memory:')\n\ndef get_cursor(conn):\n '''该方法是获取数据库的游标对象，参数为数据库的连接对象\n 如果数据库的连接对象不为None，则返回数据库连接对象所创\n 建的游标对象；否则返回一个游标对象，该对象是内存中数据\n 库连接对象所创建的游标对象'''\n if conn is not None:\n return conn.cursor()\n else:\n return get_conn('').cursor()\n\ndef create_table(conn, sql):\n '''创建数据库表'''\n if sql is not None and sql != '':\n cu = get_cursor(conn)\n if SHOW_SQL:\n print('执行sql:[{}]'.format(sql))\n cu.execute(sql)\n conn.commit()\n print('创建数据库表[student]成功!')\n close_all(conn, cu)\n else:\n print('the [{}] is empty or equal None!'.format(sql))\n\ndef close_all(conn, cu):\n '''关闭数据库游标对象和数据库连接对象'''\n try:\n if cu is not None:\n cu.close()\n finally:\n if cu is not None:\n cu.close()\n\ndef save(conn, sql, data):\n '''插入数据'''\n if sql is not None and sql != '':\n if data is not None:\n cu = get_cursor(conn)\n for d in data:\n if SHOW_SQL:\n print('执行sql:[{}],参数:[{}]'.format(sql, d))\n try:\n cu.execute(sql, d)\n try:\n conn.commit()\n except:\n print("Unexpected error:")\n except:\n print("Unexpected error:")\n close_all(conn, cu)\n else:\n print('the [{}] is empty or equal None!'.format(sql))\n\ndef fetchall(conn, sql):\n '''查询所有数据'''\n if sql is not None and sql != '':\n cu = get_cursor(conn)\n if SHOW_SQL:\n print('执行sql:[{}]'.format(sql))\n cu.execute(sql)\n r = cu.fetchall()\n if len(r) > 0:\n for e in range(len(r)):\n print(r[e])\n else:\n print('the [{}] is empty or equal None!'.format(sql)) \n\ndef fetchone(conn, sql, data):\n '''查询一条数据'''\n if sql is not None and sql != '':\n if data is not None:\n #Do this instead\n d = (data,) \n cu = get_cursor(conn)\n if SHOW_SQL:\n print('执行sql:[{}],参数:[{}]'.format(sql, data))\n cu.execute(sql, d)\n r = cu.fetchall()\n if len(r) > 0:\n for e in range(len(r)):\n print(r[e])\n else:\n print('the [{}] equal None!'.format(data))\n else:\n print('the [{}] is empty or equal None!'.format(sql))\n\ndef update(conn, sql, data):\n '''更新数据'''\n if sql is not None and sql != '':\n if data is not None:\n cu = get_cursor(conn)\n for d in data:\n if SHOW_SQL:\n print('执行sql:[{}],参数:[{}]'.format(sql, d))\n cu.execute(sql, d)\n conn.commit()\n close_all(conn, cu)\n else:\n print('the [{}] is empty or equal None!'.format(sql))\n\ndef delete(conn, sql, data):\n '''删除数据'''\n if sql is not None and sql != '':\n if data is not None:\n cu = get_cursor(conn)\n for d in data:\n if SHOW_SQL:\n print('执行sql:[{}],参数:[{}]'.format(sql, d))\n cu.execute(sql, d)\n conn.commit()\n close_all(conn, cu)\n else:\n print('the [{}] is empty or equal None!'.format(sql))\n\ndef savedata(sql,data):\n '''保存数据测试...'''\n conn = get_conn(DB_FILE_PATH)\n save(conn, sql, data)\n\ndef getdate(date1):\n global year1\n if date1.find('月') != -1:\n date1=date1.replace('月',';')\n date1=date1.replace('日',';')\n list2=date1.split(';')\n if '1' == list2[0] and '2' == list2[1]:\n year1=year1-1\n date2=str(year1)+'-'+list2[0]+'-'+list2[1]\n else:\n date2=str(year1)+'-'+list2[0]+'-'+list2[1]\n date3 = datetime.datetime.strptime(date2,'%Y-%m-%d').date()\n return date3\n\ndef averagenum(num):\n nsum = 0\n for i in range(len(num)):\n nsum += num[i]\n return nsum / len(num)\n\nif name == "main":\n\n i=0\n bar = Bar("南京地铁", "总图",title_pos='left',width=1400, height=700)\n conn = get_conn(DB_FILE_PATH)\n cu = get_cursor(conn)\n fetchall_sql = '''SELECT LineALL FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnt0 = cu.fetchall()\n cntl0=[]\n for l0 in cnt0:\n cntl0.append(l0[0])\n\n \n\n fetchall_sql = '''SELECT DATE FROM NajingMetro'''\n cu.execute(fetchall_sql)\n date1 = cu.fetchall()\n datel1=[]\n for d1 in date1:\n datel1.append(d1[0])\n\n fetchall_sql = '''SELECT Line1 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnt1 = cu.fetchall()\n cntl1=[]\n for l1 in cnt1:\n cntl1.append(l1[0])\n\n fetchall_sql = '''SELECT Line2 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnt2 = cu.fetchall()\n cntl2=[]\n for l2 in cnt2:\n cntl2.append(l2[0])\n\n fetchall_sql = '''SELECT Line3 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnt3 = cu.fetchall()\n cntl3=[]\n for l3 in cnt3:\n cntl3.append(l3[0])\n\n fetchall_sql = '''SELECT Line4 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnt4 = cu.fetchall()\n cntl4=[]\n for l4 in cnt4:\n cntl4.append(l4[0])\n\n fetchall_sql = '''SELECT Line10 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnt10 = cu.fetchall()\n cntl10=[]\n for l10 in cnt10:\n cntl10.append(l10[0])\n\n fetchall_sql = '''SELECT LineS1 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnts1 = cu.fetchall()\n cntls1=[]\n for ls1 in cnts1:\n cntls1.append(ls1[0])\n\n fetchall_sql = '''SELECT LineS3 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnts3 = cu.fetchall()\n cntls3=[]\n for ls3 in cnts3:\n cntls3.append(ls3[0])\n\n fetchall_sql = '''SELECT LineS7 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnts7 = cu.fetchall()\n cntls7=[]\n for ls7 in cnts7:\n cntls7.append(ls7[0])\n\n fetchall_sql = '''SELECT LineS8 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnts8 = cu.fetchall()\n cntls8=[]\n for ls8 in cnts8:\n cntls8.append(ls8[0])\n\n fetchall_sql = '''SELECT LineS9 FROM NajingMetro'''\n cu.execute(fetchall_sql)\n cnts9 = cu.fetchall()\n cntls9=[]\n for ls9 in cnts9:\n cntls9.append(ls9[0])\n\n\n cntl0.reverse()\n cntl1.reverse()\n cntl2.reverse()\n cntl3.reverse()\n cntl4.reverse()\n cntl10.reverse()\n cntls1.reverse()\n cntls3.reverse()\n cntls7.reverse()\n cntls8.reverse()\n cntls9.reverse()\n\n\n bar.add('全线网客运量',date1,cntl0,mark_point=["max","min"],mark_line=["average"],is_more_utils=True)\n bar.add('1号线', date1, cntl1,is_datazoom_show=True)\n bar.add('2号线', date1, cntl2,is_datazoom_show=True)\n bar.add('3号线', date1, cntl3,is_datazoom_show=True)\n bar.add('4号线', date1, cntl4,is_datazoom_show=True)\n bar.add('10号线', date1, cntl10,is_datazoom_show=True)\n bar.add('S1机场线', date1, cntls1,is_datazoom_show=True)\n bar.add('S3宁和线', date1, cntls3,is_datazoom_show=True)\n bar.add('S7宁溧线', date1, cntls7,is_datazoom_show=True)\n bar.add('S8宁天线', date1, cntls8,is_datazoom_show=True)\n bar.add('S9宁高线', date1, cntls9,is_datazoom_show=True)\n\n #bar.show_config()\n bar.render('南京地铁.html')\n\n cntl0.reverse()\n cntl1.reverse()\n cntl2.reverse()\n cntl3.reverse()\n cntl4.reverse()\n cntl10.reverse()\n cntls1.reverse()\n cntls3.reverse()\n cntls7.reverse()\n cntls8.reverse()\n cntls9.reverse()\n\n print(cntls9[0:30])\n print(averagenum(cntls9[0:30]))\n attr = ["1号线", "2号线", "3号线", "4号线", "10号线", "S1机场线", "S3宁和线", "S7宁溧线", "S8宁天线", "S9宁高线"]\n v1 = [averagenum(cntl1[0:30]), averagenum(cntl2[0:30]), averagenum(cntl3[0:30]), averagenum(cntl4[0:30]), averagenum(cntl10[0:30]), averagenum(cntls1[0:30]),averagenum(cntls3[0:30]),averagenum(cntls7[0:30]),\n averagenum(cntls8[0:30]),averagenum(cntls9[0:30])]\n pie = Pie("南京地铁","各线路占比",title_pos='left',width=1400, height=700)#新建饼图示例pie\n\n pie.add("", attr, v1, is_label_show=True)\n #pie.show_config()#是否在命令行中显示config，此行可省略\n pie.render("南京地铁饼图.html")\n上面代码报错：from pyecharts import Bar\nModuleNotFoundError: No module named 'pyecharts.chart'内容：您需要安装pyecharts包才能运行该代码。您可以使用以下命令安装pyecharts包：\n\npip install pyecharts\n\n安装完成后，您可以重新运行代码。