pythonによるDBから取得したデータの整形について

Question

pythonでDBからデータを取得し、GoogleChartsを用いてグラフを描画するために、取得した値をうまく整形する方法を探しています。
取得したデータは、

id, point, genre_id, date
1, 10, 1, 2016/06/11
2, 1, 2, 2016/06/11
3, 8, 1, 2016/06/12
4, 9, 1, 2016/06/13
5, 3, 2, 2016/06/13
︙
となっており、毎日データを集めているのですが、ジャンルによっては日によって値が存在しない場合があります。
その場合は欠損値やnullではなくレコードそのものが存在しません。

このようなデータをGoogleCharts用に

date, genre_1, genre_2
2016/06/11, 10, 1
2016/06/12, 8, 0
2016/06/13, 9, 3
︙
としたいと考えています。

力技でなんとかできるのですが、スマートな方法はないでしょうか？

Answer 1

タグにMySQLがありますが、pythonに持ってくる前にDB側で処理させる案はどうでしょうか。
(当方のテスト環境はPostgreSQLですが、MySQLでもほとんど同じだと思います)

testテーブルに以下のようなデータが入っているとして、

id | point | genre_id | date
----+-------+----------+------------
1 | 10 | 1 | 2016-06-11
2 | 1 | 2 | 2016-06-11
3 | 8 | 1 | 2016-06-12
4 | 9 | 1 | 2016-06-13
5 | 3 | 2 | 2016-06-13

↓のSQL文を実行すると、

SELECT
	date,
	SUM(CASE WHEN genre_id=1 THEN point ELSE 0 END) AS "genre_1",
	SUM(CASE WHEN genre_id=2 THEN point ELSE 0 END) AS "genre_2"
FROM
	test
GROUP BY
	date
ORDER BY date;

以下のような結果になります。

date    | genre_1 | genre_2

------------+---------+---------
2016-06-11 | 10 | 1
2016-06-12 | 8 | 0
2016-06-13 | 9 | 3

Comments

[sh1bainu]

SQLでもなんとかする方法はあると思っていたのですが思いつきませんでした。こういう書き方は知らなかったので参考にさせていただきます！実のところ2つのテーブルをJOINして値を集計しているので少し面倒な感じになってしまうと思いますが取り組んでみたいと思います。ありがとうございます。

Answer 2

日付のリストを作って、
それを使って欠けている組み合わせだけゼロレコードを作るか、
全ての日のゼロレコードを作っておいて、合算するようにしてはどうでしょうか。
（ゼロレコード：ポイントがゼロのレコード）

日付のリストは、pandasモジュールのdate_range関数を使うと簡単に作れます。

• 例：ジャンル1のゼロ件レコードを生成

import pandas as pd

zero_records = pd.date_range('2016-06-01', periods=12).map(lambda x: 'id, 0, 1, ' + x.strftime('%Y/%m/%d'))

for x in zero_records:
    print(x)

• 実行結果

id, 0, 1, 2016/06/01
id, 0, 1, 2016/06/02
id, 0, 1, 2016/06/03
id, 0, 1, 2016/06/04
id, 0, 1, 2016/06/05
id, 0, 1, 2016/06/06
id, 0, 1, 2016/06/07
id, 0, 1, 2016/06/08
id, 0, 1, 2016/06/09
id, 0, 1, 2016/06/10
id, 0, 1, 2016/06/11
id, 0, 1, 2016/06/12

---

参考リンク：

Python pandas で日時関連のデータ操作をカンタンに - StatsFragments
http://sinhrks.hatenablog.com/entry/2014/11/09/183603

Comments

[sh1bainu]

私自身も考えていましたが、とりあえず「すべてゼロ埋め→指定の部分だけ変更」にいたしました。pandasの情報ありがとうございます。