🔬

Pandasのケーススタディ（DynamoDBのダンプCSVの集計）

2021/12/17に公開

AWS

背景

DynamoDBから統計処理をしてDynamoDBに入れるという状況で、統計テーブルに入っているデータがあっているか確認するためにpandasでごりごりいじる必要がありました。マネジメントコンソールからCSVでデータをダウンロードできるのですが、カラムの中にjsonが入っていたりといろいろ大変…。

データ構造

下記のような、問題が複数入っているアンケートテーブルがあったとします。
ユーザーは複数回アンケートに答えることができ、回答一回で1レコードがまとめて入ります。（正規化はパフォーマンスの都合でしませんでした）
配列の中身が不思議な形をしているのはDynamoのダンプだからです。こんな値が文字列で入っているんです、使いにくい…。

user_id	answer	target_questions	answer_date
1	[{"S":"yes"}, {"S":"no"}]	[{"S":"q1"}, {"S":"q2"}]	2021/11/12
2	[{"S":"yes"}, {"S":"yes"}]	[{"S":"q2"}, {"S":"q3"}]	2021/11/13
1	[{"S":"yes"}, {"S":"yes"}]	[{"S":"q1"}, {"S":"q2"}]	2021/11/15

ここから下記のように、回答データを集計するという要件でした。ユーザーが複数回回答している場合は最新の回答のみを集計対象として、問題idごとに1レコードになるように集計します。

target_question	answer_count	yes_count
q1	1	1
q2	2	2
q3	1	1
この集計したレコードの値があっているのか知りたいので、最初の回答データをpandasでいじってデータと同じ値になるか確認するのが本記事の目的です。

どう集計するか

方針としては、CSVを読み込んでpandasで整形していきます。以下のようなことをすれば実現できると考えました。

target_questionsカラムを分割する
ユーザーごとに最新1レコードになるよう、重複行を省く
集計する

配列の入ったカラムを分割する

まずはtarget_questionsを配列の要素ごとに1行にしたいです（他のカラムはコピー）。中身が文字列なので、まずこれをforループの中で配列にしてからexplodeで複数行に展開していきます。forループの中で渡されるのは参照ではなくコピーなので、.atを使って値を書き換えます。

for idx, row in df.iterrows():
  df.at[idx, "target_questions"] = ast.literal_eval(row["target_questions"])
  df.at[idx, "answer"] = ast.literal_eval(row["answer"])

次にexplodeをするのですが、1つの列しか引数に渡すことができません。今回はtarget_questionsとanswerの2つの列が順番に対応していて、この2つを同時に展開したいです。そういうときはこの記事によると

df = df.apply(pd.Series.explode).reset_index()

でいけるようです。今の段階では下記のようになっていると思いますが、answerやtarget_questionsが辞書なのは嫌なので、中身だけ取り出してしまいましょう。

user_id	answer	target_questions	answer_date
1	{"S":"yes"}	{"S":"q1"}	2021/11/12
1	{"S":"no"}	{"S":"q2"}	2021/11/12
2	{"S":"yes"}	{"S":"q2"}	2021/11/13
2	{"S":"yes"}	{"S":"q3"}	2021/11/13
1	{"S":"yes"}	{"S":"q1"}	2021/11/15
1	{"S":"yes"}	{"S":"q2"}	2021/11/15

for idx, row in df.iterrows():
  df.at[idx, "target_questions"] = row["target_questions"]["S"]
  df.at[idx, "answer"] = row["answer"]["S"]

ユーザーごとに最新1レコードになるよう、重複行を省く

この部分はまずuser_idとanswer_dateでsortをしてから、user_idとtarget_quesionsが同じ行をdrop_duplicatesをします。sortは複数カラムを指定することができて、df = df.sort_values(['answer_date', 'user_id'])とするとまずuser_idでソートされ、次にanswer_dateでソートされます。
こうしてソートした状態でdf = df.drop_duplicates(subset=["user_id", "target_questions"], keep="last")とすると、user_idとtarget_questionsが重複した行がソートした最後の値を残して消えます。今はこうなっているはず。

user_id	answer	target_questions	answer_date
2	"yes"	"q2"	2021/11/13
2	"yes"	"q3"	2021/11/13
1	"yes"	"q1"	2021/11/15
1	"yes"	"q2"	2021/11/15

集計する

target_questionsごとに集計をするので、groupbyをしてから、answer_countはcountをし、yes_countはapplyで個数を数えます。

answer_count = df.groupby('target_questions').count()
yes_count = df.groupby('target_questions').filter(lambda t: t["answer"]=="yes").count()

こんな感じで求めたかった値が表示されたのではないでしょうか。

target_question	answer_count	yes_count
q1	1	1
q2	2	2
q3	1	1

終わりに

pandas初心者には辛い所業だった

追記

こんな記事を見つけました。NoSQLだとどうしてもファイル落として分析でも何か加工してデータを入れるでもCSVファイルベースになることが多いです。そんなときpandasでSQLっぽいことができると業務効率が上がる気がするのでめっちゃ良記事…

背景