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

2021.01.06
Python備忘録
DataFrame, Django, ECシステム, MySQL, pandas, python, スクリプト備忘録, 自作システム

1. Django：MySQLに格納された日計売上をPandasで月集計する方法
2. 大まかな流れ
3. 具体的な例
4. チョット解説
5. まとめ

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

月間の売上集計、集客分析etc、月でまとめて表示したいことは沢山あります。
で、集計したデータをグラフ化しようとすると欠損月があったりする場合に面倒になったりします。
ここら辺の処理がpandas使ったら楽だったので備忘録です。

大まかな流れ

Forループ用の店舗リストを用意する ※SQLがあればその抽出でOK
SQL：日次売上集計(例の場合Detail)にフィルターを掛けデータ抽出 ※集計期間/店舗ID
抽出した値をDataFrameに変換
日付にindexを貼る
月ごとに集計
集計結果を受渡用のDataFrameに格納。
2店舗目以降の場合、前回のDataFrameに横結合

ポイントは日付をインデックスとした横結合をしていく事です。
具体的にはこんな感じのDataFrameを作成します。

date　　　shopA　 shopB
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

この月間売上抽出スクリプトはこんな感じです。

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

(後略)

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日スタートである必要があります。

fd = yd.replace(day=1)

1	fd = yd.replace(day=1)

この6行目の指示でdatetime.dateフォーマットの日付部分を1に変更し

bw24 = fd - relativedelta(years=2)

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行目の工程を挟む必要がある点です。

list_base["date"] = pd.to_datetime(list_base["date"])

1	list_base["date"] = pd.to_datetime(list_base["date"])

25行目～30行目

この段階で生成されたDataFrameは下のような姿になっています。

date　　　shopA
2020/09　　5800
2020/10　 15351
2020/11　101870
2020/12　 17502

date　　　shopB
2020/09　　4300
2020/10　　6786
2020/11　　5668
2020/12　　6784

shopBをshopAの右側に追加してあげれば欲しいデータの形になるのですが、Forループ1回目と2回目以降で操作が変わります。その為、nカウンタを用意してループごとに加算し処理を変えます。

list_base_02 = pd.concat([list_base_02, ma.rename(columns={'date':'date','sales': sb['shop_code']})],axis=1)

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を省くと縦追加になり、下のような姿になります。

date
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ですが、数こなしてくと慣れてきますね。
今は数字ですが、最終的にはコレを文字で行っていきたい訳だし…まだハードル高いなぁ。

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

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

大まかな流れ

具体的な例

チョット解説

5行目～7行目

15行目～18行目

20行目～23行目

25行目～30行目

まとめ

関連

VBScriptからEXEファイル(実行ファイル)を作成する便利ツール 2021.01.05

SEOワードの有効性と関係性を視覚化してみる 2021.01.06

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

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

大まかな流れ

具体的な例

チョット解説

5行目～7行目

15行目～18行目

20行目～23行目

25行目～30行目

まとめ

共有:

関連

VBScriptからEXEファイル(実行ファイル)を作成する便利ツール 2021.01.05

SEOワードの有効性と関係性を視覚化してみる 2021.01.06