こんにちは。SENSYプロダクト開発チームPdMの岩間です。
今回はGoogle Colaboratoryとスプレッドシートを使ってAI予測値の精度集計を定型化した話について紹介していきます。
精度集計における属人化の排除や工数削減に繋がるかと思うのでぜひご覧ください。
アウトプットイメージ
今回のお話では最終的に下図のような精度集計がスプレッドシートに出力されます。
これは日付単位で精度を集計した例になっています。
実績値と予測値の平均値のような統計値であったり誤差率のような機械学習的な評価指標、また、予測値の上振れ下振れなどを集計させています。
処理の流れ
自動化にあたって以下の3つを準備します。
- 予測値と実績値のテーブル
- 精度出力先のスプレッドシート
- 予実テーブルを読み込み精度を集計しスプシに出力するColaboratory
Colaboratoryで予実テーブルを読み込みクエリで精度を集計します。
スプレッドシートにはヘッダーや条件付き書式、セルの書式などを設定したマスタシートを作成しておきColaboratoryでそのシートをコピーし、そこに精度集計結果を書き込むようにしています。
準備と実行
予測値と実績値のテーブル準備
まず、予測値と実績値を持ったテーブルを作成します。
下図は需要予測を問題設定とした時のテーブルです。
sales_date(日付), store_name(店舗), sku_name(商品)の組み合わせごとに予測値と実績値を持っています。これが精度集計の最小単位になります。
この際にweek_name(曜日)やgroup_name(カテゴリ情報)など精度集計の軸となるカラムも保有させておくことで、曜日別やカテゴリ別で精度を集計させることができます。
スプレッドシートの準備
次に精度集計結果の出力先となるスプレッドシートへマスタシートを作成します。
以下の項目を設定しています。
- 数値出力先カラムの設定
- カンマ区切り
- 桁数
- カラムごとに条件付書式でカラースケールを設定
- ヘッダーの入力と固定
Colaboratoryの準備と実行
最後に予実テーブルから精度を集計し、マスタシートをコピーしそこへ精度集計結果を出力するColaboratoryを作成していきます。
スプシのURLやGCPPJTの定義、import、認証を実行します。
# スプシのURL
ss_url = 'xxx'
# GCP PJT
gcp_project_id = 'xxx'
from google.colab import auth
auth.authenticate_user()
from google.colab import drive
drive.mount('/content/drive/')
import gspread
from google.auth import default
creds, _ = default()
gc = gspread.authorize(creds)
workbook = gc.open_by_url(ss_url)
import pandas as pd
from google.cloud import bigquery
bq_client = bigquery.Client(project_id)
次に精度集計結果が格納されたDataframeからマスタシートをコピーした先へ結果を出力する関数を定義します。
def write_metric(df, sheet_title):
# マスタシートのシート名
master_worksheet = workbook.worksheet("xxx")
sheet = workbook.duplicate_sheet(source_sheet_id=master_worksheet.id, new_sheet_name=sheet_title, insert_sheet_index=4)
sheet.add_rows(df.shape[0])
for row_index, row in df.iterrows():
row_index = row_index + 4
cell_list = sheet.range(row_index, 1, row_index, sheet.col_count)
target_row = []
target_row.append(row['group_by_col_name'])
target_row.append(row['count'])
target_row.append(row['cnt_sku'])
target_row.append(row['avg_actual'])
target_row.append(row['avg_prod_pred'])
target_row.append(row['mae_prod'])
target_row.append(row['mape_prod'])
target_row.append(row['wape_prod'])
target_row.append(row['ratio_prod_pred_up'])
target_row.append(row['ratio_prod_pred_down'])
target_row.append(row['ratio_prod_pred_fit'])
for idx, data in enumerate(target_row):
cell_list[idx].value = data
res = sheet.update_cells(cell_list, value_input_option='USER_ENTERED')
予実テーブルを読み込み精度集計するクエリを書きます。
結果はデータフレームに格納しています。
query = f'''
with
base as (
select
sales_date, week_name, concat(sales_date, " : ", week_name) as group_by_col_name,
count(distinct sku_id) as cnt_sku,
avg(actual) as avg_actual,
avg(prod_pred) as avg_prod_pred,
COUNT(*) AS count,
avg(abs(prod_pred - actual)) as mae_prod,
avg(SAFE_DIVIDE((abs(prod_pred - actual)), actual)) as mape_prod,
SAFE_DIVIDE(SUM(ABS(prod_pred - actual)),SUM(actual)) as wape_prod,
SAFE_DIVIDE(COUNTIF(SAFE_DIVIDE((prod_pred - actual), actual) > 0.05), COUNT(*)) AS ratio_prod_pred_up, -- 上振れ率
SAFE_DIVIDE(COUNTIF(SAFE_DIVIDE((prod_pred - actual),actual) < -0.05), COUNT(*)) AS ratio_prod_pred_down, -- 下振れ率
from xxx -- 予実テーブル名
group by
sales_date, week_name
)
select
*,
1 - ratio_prod_pred_up - ratio_prod_pred_down as ratio_prod_pred_fit, -- 的中率
from base
order by
sales_date, week_name
'''
metric_df = bq_client.query(query).to_dataframe()
print(metric_df.shape)
metric_df.head()
上記のクエリを実行して以下のような集計単位ごとに精度を集計したデータフレームが得られます。この時、クエリ側でgroup byを追加することで日別や店舗別、カテゴリ別など粒度を変えることができます。
最後にスプシへ書き込む関数を実行します。
sheet_title = 'xxx' # 出力先シート名
write_metric(metric_df, sheet_title)
応用
以上までで予測値と実績値の精度集計をスプシに出力することができました。
クエリやマスタシートを工夫することでさらに以下のようなことも可能になります。
- 精度集計粒度の追加
- 評価指標の追加
- 実験中のモデルの精度と今までのモデルの精度比較
- 折れ線グラフや棒グラフなど図の追加
最後に
Colaboratoryとスプレッドシートを組み合わせることで属人化しがちな精度集計を定型化することができました。集計自体はクエリでやっているので状況に合わせて集計指標を変えるなど自由度が高く非常に便利です。
ぜひ参考になればと思います。
