Django:MySQLに格納された日計売上をPandasで月集計する方法

Django:MySQLに格納された日計売上をPandasで月集計する方法
月間の売上集計、集客分析etc、月でまとめて表示したいことは沢山あります。
で、集計したデータをグラフ化しようとすると欠損月があったりする場合に面倒になったりします。
ここら辺の処理がpandas使ったら楽だったので備忘録です。
大まかな流れ
- Forループ用の店舗リストを用意する ※SQLがあればその抽出でOK
- SQL:日次売上集計(例の場合Detail)にフィルターを掛けデータ抽出 ※集計期間/店舗ID
- 抽出した値をDataFrameに変換
- 日付にindexを貼る
- 月ごとに集計
- 集計結果を受渡用のDataFrameに格納。
- 2店舗目以降の場合、前回のDataFrameに横結合
ポイントは日付をインデックスとした横結合をしていく事です。
具体的にはこんな感じのDataFrameを作成します。
2020/09 5800 4300
2020/10 15351 6786
2020/11 101870 5668
2020/12 17502 6784
具体的な例
例えばこんなデータがあったとします。【売上データ※Detail】
id | date | sales | access | contact | shop_id |
1 | 2020/9/1 | 5000 | 448 | 4 | 1 |
2 | 2020/9/4 | 600 | 454 | 3 | 1 |
3 | 2020/9/12 | 4300 | 1354 | 5 | 2 |
4 | 2020/9/24 | 200 | 545 | 6 | 1 |
5 | 2020/10/3 | 6400 | 215 | 5 | 1 |
6 | 2020/10/9 | 2200 | 1365 | 8 | 2 |
7 | 2020/10/18 | 8951 | 15385 | 6 | 1 |
8 | 2020/10/27 | 4586 | 155 | 2 | 2 |
9 | 2020/11/7 | 5668 | 1536 | 5 | 2 |
10 | 2020/11/9 | 5464 | 456 | 6 | 1 |
11 | 2020/11/10 | 8761 | 786 | 2 | 1 |
12 | 2020/11/25 | 87645 | 243 | 1 | 1 |
13 | 2020/12/4 | 6785 | 4156 | 6 | 2 |
14 | 2020/12/6 | 6584 | 165 | 8 | 1 |
15 | 2020/12/11 | 1465 | 878 | 4 | 1 |
16 | 2020/12/24 | 9453 | 486 | 6 | 1 |
この月間売上抽出スクリプトはこんな感じです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
import pandas as pd (中略) """データ抽出日時調整""" yd = (datetime.date.today() - datetime.timedelta(days=1))# 昨日 fd = yd.replace(day=1)#昨日と同月1日 bw24 = fd - relativedelta(years=2)#2年前の同月 #クライアント別店舗情報取出し※shop_codeを取出し shop_base = Shops.objects.values('id','mall_id','shop_code') shopdata = [] n = 1 for sb in shop_base: """SQLからデータ抽出※salesのみ取得""" l_box = Dailies.objects.values('date','sales')\ .filter(date__range=[bw24, yd])\ .filter(shop_id=sb['id'])\ .order_by('date') """pandasに入れて集計""" list_base = pd.DataFrame(l_box) list_base["date"] = pd.to_datetime(list_base["date"]) mm = list_base.set_index(["date"]) ma = mm.resample("M").sum() if n == 1: """最初のレコードの時は素直に""" list_base_02 = ma.rename(columns={'date':'date','sales': sb['shop_code']}) else: """2レコード以降は前回までのリストに横側追加""" list_base_02 = pd.concat([list_base_02, ma.rename(columns={'date':'date','sales': sb['shop_code']})],axis=1) n = n+1 (後略) |
チョット解説
見直し用に解説を付けてみます。
5行目~7行目
抽出する日付の制御部分です。
欲しいデータは『月間集計』なので、必要な処理が2つ出てきます。
- 最新データは前日(※当日のデータは未収集/未集計なので)
- 最遠のデータは2年前の1日からの集計の必要がある(※12日スタートなどになってはいけない)
today()で取得した日の2年前としてしまうと。開始日が1日になりません。
欲しいのは月間集計なので、1日スタートである必要があります。
1 |
fd = yd.replace(day=1) |
この6行目の指示でdatetime.dateフォーマットの日付部分を1に変更し
1 |
bw24 = fd - relativedelta(years=2) |
そこから2年前を取得する事で調整しています。
15行目~18行目
このパーツは単純なSQLからの抽出です。
上記例では余計なデータを省くために、15行目で抽出するカラムを指定し、16行目で期間をrange指定しています。
20行目~23行目
Pandasを使い月毎にSUMするパートがここです。
- 20行目:オブジェクトをDataFrame型に変換
- 21行目:日付の場所をdatetime型として認識させる
- 22行目:日付の場所をインデックス化
- 23行目:インデックスを基に月間集計
全体的にこんな流れになっています。
注意しなくてはいけないのが、SQLでDateカラムにしていてもDataFrameに入れた時には日付データと認識されていないので、21行目の工程を挟む必要がある点です。
1 |
list_base["date"] = pd.to_datetime(list_base["date"]) |
25行目~30行目
この段階で生成されたDataFrameは下のような姿になっています。
2020/09 5800
2020/10 15351
2020/11 101870
2020/12 17502
2020/09 4300
2020/10 6786
2020/11 5668
2020/12 6784
shopBをshopAの右側に追加してあげれば欲しいデータの形になるのですが、Forループ1回目と2回目以降で操作が変わります。その為、nカウンタを用意してループごとに加算し処理を変えます。
1 |
list_base_02 = pd.concat([list_base_02, ma.rename(columns={'date':'date','sales': sb['shop_code']})],axis=1) |
この30行目が横追加の指示です。
pd.concat(追加先,追加元,axis=1)
axisを省くと縦追加になり、下のような姿になります。
2020/09 5800
2020/10 15351
2020/11 101870
2020/12 17502
2020/09 4300
2020/10 6786
2020/11 5668
2020/12 6784
まとめ
この作業はグラフ化したいと思って行っていた作業になります。
グラフ化については其々の格納する形が違うのでそこに合わせて形成する項目が以降に入ってきます。
※例えばjson.dumpsしたりとか
取っ付き難かったPandasですが、数こなしてくと慣れてきますね。
今は数字ですが、最終的にはコレを文字で行っていきたい訳だし…まだハードル高いなぁ。
-
前の記事
VBScriptからEXEファイル(実行ファイル)を作成する便利ツール 2021.01.05
-
次の記事
SEOワードの有効性と関係性を視覚化してみる 2021.01.06
コメントを残す