def switch_sales(switch, pred_value):
#switch変数は入力する値の名前
if switch == "取扱額":
title = "取扱金額予測(取扱額)"
X_col = '取扱額'
y_col = '取扱人数（人）'
axis = ["取扱額", "取扱人数（人）"]
png_path = file.png_export_path("sales_amount")
excel_path = file.excel_export_path("sales_amount")
elif switch == "取扱人数（人）":
title = "取扱金額予測(取扱人数)"
X_col = '取扱人数（人）'
y_col = '取扱額'
axis = ["取扱人数（人）", "取扱額"]
png_path = file.png_export_path("sales_count")
excel_path = file.excel_export_path("sales_count")
#共通設定
sheet_name = "予測金額"
legend_list = "実測値"
add_color = None
add_legend = "予測値"
is_circle = None
col_del = []
index_flg = False
flg = sales(pred_value, title, X_col, y_col, axis, png_path, excel_path,
sheet_name, legend_list, add_color, add_legend, is_circle,
col_del, index_flg)
return flg
def monthly():
#データフレーム作成
col_name1 = '年'
num = 12
df = truncate_df(col_name1, num).reset_index(drop=True)
col_name2 = '月'
month_np = df['月'].unique()
df_list = com_pd.create_df_list(df, month_np, col_name2)
#「年」の最小値と最大値取得
year_min = df['年'].min()
year_max = df['年'].max()
#グラフ作成
title = str(year_min) + "~" + str(year_max) + ":取扱金額と人数の年月別"
x = '取扱人数（人）'
y = '取扱額'
legend_list = month_np.astype('str')
axis = ["取扱人数（人）", "取扱額", None]
fig = common.graph.create_scatter(df_list, title, x, y, legend_list, axis)
#エクセルファイル出力
col_list = ['年', '月', '取扱額', '取扱人数（人）']
pivot_list = ['年', '月', ['取扱額', '取扱人数（人）']]
df_pivot = pivot_sum(df, col_list, pivot_list)
png_path = file.png_export_path("monthly")
excel_path = file.excel_export_path("monthly")
col_del = None
sheet_name = '取扱金額と人数の年月別'
position = "B" + str(len(df) + 5)
index_flg = True
flg = common.excel.export_excel(fig, png_path, excel_path, col_del,
sheet_name, index_flg, df_pivot)
return flg
def yearly_count():
#データフレーム作成
col_name = '年'
num = 12
df = truncate_df(col_name, num).reset_index(drop=True)
year_np = df['年'].unique()
df_list = com_pd.create_df_list(df, year_np, col_name)
#「年」の最小値と最大値取得
year_min = df['年'].min()
year_max = df['年'].max()
#グラフ作成
title = str(year_min) + "~" + str(year_max) + ":年別取扱人数"
x = '月'
y1 = '取扱人数（人）'
y2 = None
legend_list1 = year_np.astype('str')
legend_list2 = None
times_min = None
times_max = None
axis = ["月", "取扱人数（人）", None]
fig = common.graph.create_line(df_list, title, x, y1, y2, legend_list1,
legend_list2, times_min, times_max, axis)
#エクセルファイル出力
col_list = ['年', '月', '取扱人数（人）']
pivot_list = ['年', '月', '取扱人数（人）']
df_pivot = pivot_sum(df, col_list, pivot_list)
png_path = file.png_export_path("yearly_count")
excel_path = file.excel_export_path("yearly_count")
col_del = None
sheet_name = '年別取扱人数'
position = "B" + str(len(df) + 5)
index_flg = True
flg = common.excel.export_excel(fig, png_path, excel_path, col_del,
sheet_name, index_flg, df_pivot)
return flg
def staying_trip_price_times():
#データフレーム作成
sql_list = [sql1,sql3]
category = "宿泊旅行"
df = sort_category(sql_list, category)
#「年」の最小値と最大値取得
year_min = df['年'].min()
year_max = df['年'].max()
#グラフ作成
title = str(year_min) + "~" + str(year_max) + ":宿泊旅行の単価と回数"
x = '年'
y1 = '単価'
y2 = '回数'
df_list = [df]
legend_list1 = ["単価"]
legend_list2 = ["回数"]
times_min =df['回数'].min()
times_max =df['回数'].max()
axis = [None,"単価","回数"]
fig = common.graph.create_line(df_list,title,x,y1,y2,legend_list1,legend_list2,times_min,times_max,axis)
#エクセルファイル出力
png_path = file.png_export_path("staying_trip_price_times")
excel_path = file.excel_export_path("staying_trip_price_times")
col_del = []
sheet_name='宿泊旅行の単価と回数'
position = "B" + str(len(df) + 5)
index_flg = False
flg =common.excel.export_excel(fig, png_path, excel_path, col_del, sheet_name,index_flg, df)
return flg
def handling_total():
#データフレーム作成
col_list = ['年', '取扱額', '取扱人数（人）']
group = '年'
df = group_sum(col_list, group)
#「年」の最小値と最大値取得
year_min = df['年'].min()
year_max = df['年'].max()
#グラフ作成
title = str(year_min) + "~" + str(year_max) + ":取扱金額と人数の総数"
x = '年'
y1 = '取扱額'
y2 = '取扱人数（人）'
legend_list1 = ["取扱額"]
legend_list2 = ["取扱人数（人）"]
times_min = df['取扱人数（人）'].min()
times_max = df['取扱人数（人）'].max()
axis = ["年", "取扱額", "取扱人数（人）"]
df_list = [df]
fig = common.graph.create_line(df_list, title, x, y1, y2, legend_list1,
legend_list2, times_min, times_max, axis)
#エクセルファイル出力
png_path = file.png_export_path("handling_total")
excel_path = file.excel_export_path("handling_total")
col_del = None
sheet_name = '取扱金額と人数の総数'
position = "B" + str(len(df) + 5)
index_flg = False
flg = common.excel.export_excel(fig, png_path, excel_path, col_del,
sheet_name, index_flg, df)
return flg
def times():
#データフレーム作成
sql_list1 = [sql1,sql2]
sql_list2 = [sql1,sql3]
category1 = '日帰り'
category2 = '宿泊旅行'
df1 = sort_category(sql_list1,category1)
df2 = sort_category(sql_list2,category2)
df_list = [df1,df2]
df = com_pd.concat_df(df_list)
#「年」の最小値と最大値取得
year_min = df['年'].min()
year_max = df['年'].max()
#グラフ作成
title = str(year_min) + "~" + str(year_max) + ":1人当たりの旅行回数"
x = '年'
y1 = '回数'
y2 = None
legend_list1 = ["日帰り","宿泊旅行"]
legend_list2 = None
times_min =None
times_max =None
axis = [None,"回数",None]
fig = common.graph.create_line(df_list,title,x,y1,y2,legend_list1,legend_list2,times_min,times_max,axis)
#エクセルファイル出力
png_path = file.png_export_path("times")
excel_path = file.excel_export_path("times")
col_del = ['単価']
sheet_name='1人当たりの旅行回数'
position = "B" + str(len(df) + 5)
index_flg = False
flg =common.excel.export_excel(fig, png_path, excel_path, col_del, sheet_name,index_flg, df)
return flg
def unit_price():
#データフレーム作成
df = com_pd.get_table((sql1))
df1 = df.loc[df['種類']=="日帰り"]
df2 = df.loc[df['種類']=="宿泊旅行"]
#「年」の最小値と最大値取得
year_min = df['年'].min()
year_max = df['年'].max()
#グラフ作成
title = str(year_min) + "~" + str(year_max) + ":1回あたりの旅行単価"
x = '年'
y1 = '単価'
y2 = None
df_list = [df1,df2]
legend_list1 = ["日帰り","宿泊旅行"]
legend_list2 = None
times_min =None
times_max =None
axis = [None,"単価",None]
fig = common.graph.create_line(df_list,title,x,y1,y2,legend_list1,legend_list2,times_min,times_max,axis)
#エクセルファイル出力
png_path = file.png_export_path("unit_price")
excel_path = file.excel_export_path("unit_price")
col_del = []
sheet_name='1回あたりの旅行単価'
position = "B" + str(len(df) + 5)
index_flg = False
flg =common.excel.export_excel(fig, png_path, excel_path, col_del, sheet_name,index_flg, df)
return flg
def stying_trip_times_number():
#データフレーム作成
sql_list = [sql3,sql4]
left_list = ['年']
right_list = ['年']
df = create_join_df(sql_list, left_list,right_list)
#「年」の最小値と最大値取得
year_min = df['年'].min()
year_max = df['年'].max()
#グラフ作成
title = str(year_min) + "~" + str(year_max) + ":宿泊旅行の回数と宿泊数"
x = '年'
y1 = '回数'
y2 = '宿泊数'
df_list = [df]
legend_list1 = ["回数"]
legend_list2 = ["宿泊数"]
times_min =df['宿泊数'].min()
times_max =df['宿泊数'].max()
axis = [None,"回数","宿泊数"]
fig = common.graph.create_line(df_list,title,x,y1,y2,legend_list1,legend_list2,times_min,times_max,axis)
#エクセルファイル出力
png_path = file.png_export_path("stying_trip_times_number")
excel_path = file.excel_export_path("stying_trip_times_number")
col_del = []
sheet_name='宿泊旅行の回数と宿泊数'
position = "B" + str(len(df) + 5)
index_flg = False
flg =common.excel.export_excel(fig, png_path, excel_path, col_del, sheet_name,index_flg, df)
return flg
def export_excel(df): file_name = "temperature_analysis" png_path = file.png_export_path(file_name) excel_path = file.excel_export_path(file_name) sheet_name = "data" index_flg = False #データ整理 df['年月'] = df['year'].astype(str) + "年" +df['month'].astype(str) + "月" df.sort_values(['地域','year','month'],inplace=True) # df.sort_values(['地域','年月'],inplace=True) col_del = ["year","month","時期"] df.drop(columns=col_del,inplace=True) df = df.reindex(columns=['地域','年月','平均気温','最高気温','最低気温','平均湿度','最大震度']) #エクセル出力 df.to_excel(excel_path, sheet_name=sheet_name,index=index_flg) position = "B" + str(len(df) + 5) flg = common.excel.paste_image(excel_path,png_path,position) return flg
def trip_target_total():
df = com_pd.get_table(sql)
#「年」の最小値と最大値取得
year_min = df['年'].min()
year_max = df['年'].max()
#集計して、順位の振り直し
col_list = ['旅行先', 'point']
group = '旅行先'
df_sort = add_rank(df, col_list, group)
#グラフ作成
title = str(year_min) + "~" + str(year_max) + ":人気の旅行先(累計)"
x = '旅行先'
y = 'point'
fig = common.graph.create_bar(df_sort, title, x, y)
#エクセルファイル出力
png_path = file.png_export_path("trip_target_total")
excel_path = file.excel_export_path("trip_target_total")
col_del = ['point']
sheet_name = '人気の旅行先(累計)'
position = "B" + str(len(df_sort) + 5)
index_flg = False
flg = common.excel.export_excel(fig, png_path, excel_path, col_del,
sheet_name, index_flg, df_sort)
return flg
def monthly_sales():
#データフレーム作成
df1 = com_pd.get_table(sql1)
df2 = com_pd.get_table(sql2)
df2 = add_holiday(df2)
df2 = add_flg(df2)
df_weather = get_weather()
#結合
df_list = [df1, df2]
left_list = [['年', '月']]
right_list = left_list
df_join = com_pd.left_join_df(df_list, left_list, right_list)
df_join.fillna(0, inplace=True)
#説明変数と目的変数を用意
x = df_join[['年', '月', '祝日数', 'ct_flg']]
y = df_join['取扱額']
# X = sm.add_constant(x)
X = x
#学習
model = sm.OLS(y, X)
result = model.fit()
# print(result.summary())
pred_year = 2020
pred_df_list = [set_month_df(pred_year)]
X_pred = com_pd.left_join_df(pred_df_list, left_list, right_list)
#predictの引数はmodelのXの要素数に合わせる
y_pred = result.predict(X_pred)
#グラフ作成
df_list = [df_join]
X_col = '月'
y_col = '取扱額'
title = "test"
legend_list = "実測値"
axis = ["月", "取扱額"]
fig = graph.create_scatter(df_list, title, X_col, y_col, legend_list, axis)
#去年の実績グラフ
year_max = df_join['年'].max()
if year_max == pred_year:
last_year = pred_year - 1
else:
last_year = year_max
x_axis = df_join.loc[df_join['年'] == last_year]['月']
y_axis = df_join.loc[df_join['年'] == last_year]['取扱額']
add_color = COLOR(11)
add_legend = str(last_year) + "年の" + "実測値"
is_circle = None
fig = graph.add_line(fig, x_axis, y_axis, add_color, add_legend, is_circle)
#予測グラフ作成
X_axis = X_pred['月']
add_color = "red"
add_legend = str(pred_year) + "年の" + "予測値"
is_circle = None
fig = graph.add_line(fig, X_axis, y_pred, add_color, add_legend, is_circle)
#エクセル出力用のデータ作成
idx_amt = str(pred_year) + "年の取扱金額"
idx_last_amt = str(last_year) + "年の取扱金額"
index = [idx_amt, idx_last_amt]
columns = np.sort(df1['月'].unique())
data = [y_pred.values, y_axis.values]
df_excel = pd.DataFrame(data=data, index=index, columns=columns)
#エクセルファイル出力
png_path = file.png_export_path("monthly_sales")
excel_path = file.excel_export_path("monthly_sales")
col_del = []
sheet_name = "月毎の取扱金額予測"
index_flg = True
flg = common.excel.export_excel(fig, png_path, excel_path, col_del,
sheet_name, index_flg, df_excel)
return flg
