Table of Contents
In [61]:
# DB接続
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
font_size =24
fig_size = (14,8)
# 8tops上でのファイル (ファイル名は環境に応じて変更すること)
conn = sqlite3.connect("/home/raspimngr/db/kofu_traveler.sqlite3")
#conn = sqlite3.connect("kofu_traveler.sqlite3")
cur = conn.cursor()
# テーブル名 (flow, flow_trunc10, flow_all_trunc, flow_all, flow_all_trunc10のどれか)
table_name = "flow_all_trunc10"
# 時間帯別流動数
def plot_hourly_flow(path, sub_title="", filename_body="temp", image_type="", direction=False,
sdate="2019-09-01", edate="2019-12-31", legend_outside=False):
count_data = {"00": {}, "01":{},"02": {},"03": {},"04": {},"05": {},"06": {},"07": {},"08": {},"09": {},
"10": {},"11": {},"12": {},"13": {},"14": {},"15": {},"16": {},"17": {},"18": {},"19": {},
"20": {},"21": {},"22": {},"23": {}}
opt_direction = ""
for l, pos in path.items():
if direction:
opt_direction = ''
else:
opt_direction = ' or (origin="' + pos[1] + '" and destination="' + pos[0] + '")'
sql = ("select hour, sum(number) from " + table_name
+ ' where ((origin="' + pos[0] + '" and destination="' + pos[1] + '")'
+ opt_direction + ")"
+ ' and yearday>="' + sdate + '" and yearday<="' + edate+'" '
+ ' group by hour order by hour')
# print(sql)
result = cur.execute(sql).fetchall()
for v in result:
count_data[v[0]][l] = v[1]
df = pd.DataFrame.from_dict(count_data)
df = df.T
df = df.reset_index()
fig = plt.figure( figsize=fig_size)
ax = fig.add_subplot(1, 1, 1)
ax = df.plot(ax=ax, fontsize=font_size, xticks=df.index, lw=4)
if legend_outside:
ax.legend(fontsize=font_size,loc='center left', bbox_to_anchor=(1.0, 0.5))
else:
ax.legend(fontsize=font_size)
ax.set_title("時間別移動アドレス数"+sub_title + " "+ sdate + " ～ " + edate, fontsize=font_size)
ax.set_xlabel("時間", fontsize=font_size)
ax.set_ylabel("アドレス数", fontsize=font_size)
if image_type !="":
if image_type=="emf": # 出力形式がemf指定のときは、svgにセーブしてからinkscapeで変換
plt.savefig(filename_body + ".svg" , bbox_inches="tight")
import subprocess
subprocess.run("inkscape --file " + filename_body + ".svg"
+ " --export-emf " + filename_body + ".emf", shell=True)
else:
plt.savefig(filename_body + "." + image_type, bbox_inches="tight")
plt.show()
def get_name_pairs(point_pairs, area="kofu"):
"""
[["12","22"], [ ], ... のような地点ペアを与えて、地点名をキーに入れて返す
"""
filename = {"kofu": "/var/www/html/kofu/sensor_points.csv",
"fuefuki": "/var/www/html/ff/sensor_points.csv",
"hakushu": "/var/www/html/hakushu/sensor_points.csv",
"ttri": "/home/toyotamngr/csv/toyota/sensor_points.csv"}
df = pd.read_csv(filename[area])
path = {}
for p in point_pairs:
if area == "kofu":
p0 = "kofu" + p[0]
p1 = "kofu" + p[1]
else:
p0 = p[0]
p1 = p[1]
key = (df[df.センサ名 == p0]['短縮名'].values[0] + " - "
+ df[df.センサ名 == p1]['短縮名'].values[0])
path[key] = [p[0], p[1]]
return path
if __name__ == "__main__":
# path = {}
# path["奥藤 - 防災新館"] = ["22", "9"]
# path["防災新館 - ダン"] = ["9", "17"]
# path["ダン - 風月堂"] = ["3", "17"]
# path["ブラザー - ルパン"] = ["13", "24"]
# path["キュイエール - クラフト"] = ["16", "18"] # クラフトは2019年用なのでない
pairs = [["22", "9"], ["9", "17"], ["13", "24"]]
path = get_name_pairs(pairs)
# print(path)
plot_hourly_flow(path, "(一方向)","hourlyFlow", "emf", direction=True, legend_outside=True)
In [ ]:
甲府市中心街¶
In [62]:
# オリンピック通り(30)と周辺のつながり
pairs = [["30", "24"], ["30", "13"], ["30", "31"]]
path = get_name_pairs(pairs)
plot_hourly_flow(path, "(双方向)","olympicFlow", "emf", sdate="2019-10-01", edate="2019-12-31",
direction=False, legend_outside=True)
In [85]:
# 城東・舞鶴
pairs = [["9", "17"], ["17", "9"], ["17", "3"], ["3","17"]]
path = get_name_pairs(pairs)
plot_hourly_flow(path, "(城東通り、舞鶴通り)","JotoMaiduruFlow", "emf", sdate="2018-10-01", edate="2019-12-31",
direction=True, legend_outside=True)
In [89]:
# 駅南北
pairs = [["26", "27"], ["27", "26"], ["27", "28"], ["28","27"],["25","27"],["27", '25']]
path = get_name_pairs(pairs)
plot_hourly_flow(path, "(甲府駅周辺)","KofuStationFlow", "emf", sdate="2019-08-01", edate="2019-12-31",
direction=True, legend_outside=True)
In [100]:
# 春日モール・銀座通り
pairs = [["24", "13"], ["24", "19"], ["24", "11"],["24","20"]]
path = get_name_pairs(pairs)
plot_hourly_flow(path, "(春日・銀座)","GinzaKasugaFlow", "emf", sdate="2018-08-01", edate="2019-12-31",
direction=False, legend_outside=True)
In [97]:
# DB接続
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
import copy
# 8tops上でのファイル (ファイル名は環境に応じて変更すること)
conn = sqlite3.connect("/home/raspimngr/db/kofu_traveler.sqlite3")
#conn = sqlite3.connect("kofu_traveler.sqlite3")
cur = conn.cursor()
# テーブル名 (flow, flow_trunc10, flow_all_trunc, flow_all, flow_all_trunc10のどれか)
table_name = "flow_all_trunc10"
# 時間帯別流動数
def plot_hourly_flow_1seg(path, path_name="", sub_title="", filename_body="temp", image_type="", direction=False,
smonth="2019-09", emonth="2020-02", legend_outside=False):
count_data = {"00": 0, "01":0,"02": 0,"03": 0,"04": 0,"05": 0,"06": 0,"07": 0,"08": 0,"09": 0,
"10": 0,"11": 0,"12": 0,"13": 0,"14": 0,"15": 0,"16": 0,"17": 0,"18": 0,"19": 0,
"20": 0 ,"21": 0,"22": 0,"23": 0}
count_data_both = copy.deepcopy(count_data)
sday = "01"
eday = {"01": "31", "02": "28", "03": "31", "04": "30", "05": "31", "06": "30",
"07": "31", "08": "31", "09": "30", "10": "31", "11": "30", "12": "31"}
font_size = 16
if direction:
#opt_direction = ''
fig_size = (17,6)
else:
#opt_direction = ' or (origin="' + path[1] + '" and destination="' + path[0] + '")'
fig_size = (8,6)
flow_number = {} # 順方向データの入れ物
flow_number2 = {} # 逆方向
flow_number_both = {} # 双方向の和
month = smonth
dt_month = datetime.strptime(smonth+"-01", "%Y-%m-%d")
while month <= emonth:
sdate = month + "-" + sday
edate = month + "-" + eday[month[-2:]]
sql = ("select hour, sum(number) from " + table_name
+ ' where (origin="' + path[0] + '" and destination="' + path[1] + '")'
+ ' and yearday>="' + sdate + '" and yearday<="' + edate+'" '
+ ' group by hour order by hour')
result = cur.execute(sql).fetchall()
sql2 = ("select hour, sum(number) from " + table_name
+ ' where (origin="' + path[1] + '" and destination="' + path[0] + '")'
+ ' and yearday>="' + sdate + '" and yearday<="' + edate+'" '
+ ' group by hour order by hour')
result2 = cur.execute(sql2).fetchall()
for v in result:
count_data[v[0]]= v[1] # 時ごとの流動数
count_data_both[v[0]] = v[1]
# print(count_data)
flow_number[month] = copy.deepcopy(count_data)
for v in result2:
count_data[v[0]]= v[1] # 時ごとの流動数
count_data_both[v[0]] += v[1]
flow_number2[month] = copy.deepcopy(count_data)
flow_number_both[month] = copy.deepcopy(count_data_both)
dt_month = dt_month + relativedelta(months=1)
month = datetime.strftime(dt_month, "%Y-%m") # 月をひとつ進める
df = pd.DataFrame.from_dict(flow_number)
df = df.reset_index()
df2 = pd.DataFrame.from_dict(flow_number2)
df2 = df2.reset_index()
df_both = pd.DataFrame.from_dict(flow_number_both)
df_both = df_both.reset_index()
# 描画
fig = plt.figure( figsize=fig_size)
if direction:
ax = fig.add_subplot(1,2,1)
df.plot(ax=ax, fontsize=font_size, xticks=df.index, lw=4)
ax.set_title(path_name[0] + " - " + path_name[1], fontsize=font_size)
ax.legend(fontsize=font_size)
ax2 = fig.add_subplot(1,2,2)
df2.plot(ax=ax2, fontsize=font_size, xticks=df.index, lw=4)
ax2.set_title(path_name[1] + " - " + path_name[0], fontsize=font_size)
ax2.legend(fontsize=font_size)
else:
ax = fig.add_subplot(1, 1, 1)
df_both.plot(ax=ax, fontsize=font_size, xticks=df.index, lw=4)
ax.set_title(path_name[0] + " - " + path_name[1] + " (双方向)", fontsize=font_size)
ax.legend(fontsize=font_size)
# return(df,df2)
甲府¶
In [98]:
path = get_name_pairs([["9", "17"]], area="kofu")
# path辞書で、キーが地点名をハイフンでつないだ文字列、値が地点IDペアのリスト
# ここでは1つしかいらないのだが、forの繰り返しとする
for p in path:
path_name = p.split("-") # 名称分解
plot_hourly_flow_1seg(path[p], path_name, smonth="2019-08", emonth="2019-12", direction=True)
参考¶
In [35]:
import pandas as pd
filename = {"kofu": "/var/www/html/kofu/sensor_points.csv",
"fuefuki": "/var/www/html/ff/sensor_points.csv",
"hakushu": "/var/www/html/hakushu/sensor_points.csv",
"ttri": "/home/toyotamngr/csv/toyota/sensor_points.csv"}
pd.read_csv(filename["kofu"])
#df = pd.read_csv(filename['kofu'])
#df[df.センサ名=="kofu7"]['短縮名'].values[0]
Out[35]:
In [ ]: