4 Case 4: window関数問題
4.1 Question 4
以下のようなログデータがあります:
library(tidyverse)
library(lubridate)
set.seed(57)
n = 1000
df_5 <- data.frame(
user_id = 100000 + sample(1:50, n, replace = TRUE),
shop_id = sample(paste("shop", str_pad(1:10, width = 2, pad = "0"), sep = "_"), n, replace = TRUE),
dt = ymd_hms("2019-02-14 00:00:00") +
days(sample(0:27, n, replace = TRUE)) +
hours(sample(17:23, n, replace = TRUE)) +
minutes(sample(0:59, n, replace = TRUE)) +
seconds(sample(0:59, n, replace = TRUE))
)
knitr::kable(head(df_5))| user_id | shop_id | dt |
|---|---|---|
| 100013 | shop_09 | 2019-03-12 18:05:34 |
| 100026 | shop_01 | 2019-03-01 18:09:00 |
| 100002 | shop_08 | 2019-03-07 17:19:27 |
| 100009 | shop_06 | 2019-02-23 18:57:06 |
| 100037 | shop_10 | 2019-03-06 22:28:48 |
| 100034 | shop_06 | 2019-02-14 17:38:08 |
ここで、shop_id == "shop_05"にはじめて訪問する前と後で、ユーザーが各店舗に何回訪問したかを集計したいです:
| user_id | shop_id | post | pre |
|---|---|---|---|
| 100001 | shop_01 | 5 | 0 |
| 100001 | shop_02 | 2 | 0 |
| 100001 | shop_03 | 2 | 0 |
| 100001 | shop_04 | 3 | 0 |
| 100001 | shop_05 | 5 | 0 |
| 100001 | shop_06 | 2 | 1 |
4.2 Answer
いろいろやり方がありますが、こんな感じでもできます:
df_5_result <- df_5 %>%
arrange(user_id, dt) %>%
group_by(user_id) %>%
mutate(target_flag = if_else(shop_id == "shop_05", 1, 0)) %>%
mutate(cum_target = cumsum(target_flag)) %>%
mutate(pre_post = if_else(cum_target == 0, "pre", "post")) %>%
group_by(user_id, shop_id, pre_post) %>%
summarise(count = n()) %>%
spread(pre_post, count, fill = 0)
knitr::kable(head(df_5_result))| user_id | shop_id | post | pre |
|---|---|---|---|
| 100001 | shop_01 | 5 | 0 |
| 100001 | shop_02 | 2 | 0 |
| 100001 | shop_03 | 2 | 0 |
| 100001 | shop_04 | 3 | 0 |
| 100001 | shop_05 | 5 | 0 |
| 100001 | shop_06 | 2 | 1 |
4.3 解説
4.3.1 考え方
ログデータを前処理するときに頻出するパターンですが、その状況によってバリエーションが出てきます。このケースでは、フラグ立てとwindow関数の応用でいけます。
流れは以下のとおりです:
- user_id, dtで並べ替え
- user単位でgroup by
- ターゲットに訪問したレコード(行)にフラグ(
1)を立てる - ユーザー単位でフラグの累積和を算出
- フラグ累積和が0(つまりまだターゲットに訪問していない)のレコードを
pre、それ以外をpostとする - あとはgroup byし直して普通に集計
ポイントは3-5です。SQLのノウハウでもよく共有されてるやり方をdplyrでトレースしてます。
4.3.2 手順
まずは並べ替えてgroup by:
res <- df_5 %>%
arrange(user_id, dt) %>%
group_by(user_id)
knitr::kable(head(res))| user_id | shop_id | dt |
|---|---|---|
| 100001 | shop_06 | 2019-02-14 21:04:51 |
| 100001 | shop_05 | 2019-02-14 22:52:52 |
| 100001 | shop_05 | 2019-02-16 22:08:02 |
| 100001 | shop_03 | 2019-02-17 19:45:45 |
| 100001 | shop_07 | 2019-02-19 18:02:35 |
| 100001 | shop_04 | 2019-02-20 20:50:28 |
今回の処理では、常にユーザー単位で処理をします。また、時系列で並べ替えておく必要もあるのでこの操作となります。
次に、ターゲットに訪問しているログを特定します:
res <- res %>%
mutate(target_flag = if_else(shop_id == "shop_05", 1, 0))
knitr::kable(head(res, 20))| user_id | shop_id | dt | target_flag |
|---|---|---|---|
| 100001 | shop_06 | 2019-02-14 21:04:51 | 0 |
| 100001 | shop_05 | 2019-02-14 22:52:52 | 1 |
| 100001 | shop_05 | 2019-02-16 22:08:02 | 1 |
| 100001 | shop_03 | 2019-02-17 19:45:45 | 0 |
| 100001 | shop_07 | 2019-02-19 18:02:35 | 0 |
| 100001 | shop_04 | 2019-02-20 20:50:28 | 0 |
| 100001 | shop_10 | 2019-02-22 19:41:16 | 0 |
| 100001 | shop_03 | 2019-02-22 23:51:35 | 0 |
| 100001 | shop_01 | 2019-02-24 20:27:48 | 0 |
| 100001 | shop_09 | 2019-02-25 19:16:26 | 0 |
| 100001 | shop_06 | 2019-02-26 21:50:13 | 0 |
| 100001 | shop_01 | 2019-02-26 22:43:49 | 0 |
| 100001 | shop_06 | 2019-02-27 21:23:47 | 0 |
| 100001 | shop_09 | 2019-02-28 19:05:35 | 0 |
| 100001 | shop_10 | 2019-03-04 19:23:41 | 0 |
| 100001 | shop_05 | 2019-03-04 23:53:19 | 1 |
| 100001 | shop_07 | 2019-03-04 23:53:47 | 0 |
| 100001 | shop_05 | 2019-03-05 17:13:47 | 1 |
| 100001 | shop_04 | 2019-03-06 20:44:25 | 0 |
| 100001 | shop_04 | 2019-03-06 23:01:55 | 0 |
この後に、累積和を算出するdplyr::cumsumを利用します:
res <- res %>%
mutate(cum_target = cumsum(target_flag))
knitr::kable(head(res, 20))| user_id | shop_id | dt | target_flag | cum_target |
|---|---|---|---|---|
| 100001 | shop_06 | 2019-02-14 21:04:51 | 0 | 0 |
| 100001 | shop_05 | 2019-02-14 22:52:52 | 1 | 1 |
| 100001 | shop_05 | 2019-02-16 22:08:02 | 1 | 2 |
| 100001 | shop_03 | 2019-02-17 19:45:45 | 0 | 2 |
| 100001 | shop_07 | 2019-02-19 18:02:35 | 0 | 2 |
| 100001 | shop_04 | 2019-02-20 20:50:28 | 0 | 2 |
| 100001 | shop_10 | 2019-02-22 19:41:16 | 0 | 2 |
| 100001 | shop_03 | 2019-02-22 23:51:35 | 0 | 2 |
| 100001 | shop_01 | 2019-02-24 20:27:48 | 0 | 2 |
| 100001 | shop_09 | 2019-02-25 19:16:26 | 0 | 2 |
| 100001 | shop_06 | 2019-02-26 21:50:13 | 0 | 2 |
| 100001 | shop_01 | 2019-02-26 22:43:49 | 0 | 2 |
| 100001 | shop_06 | 2019-02-27 21:23:47 | 0 | 2 |
| 100001 | shop_09 | 2019-02-28 19:05:35 | 0 | 2 |
| 100001 | shop_10 | 2019-03-04 19:23:41 | 0 | 2 |
| 100001 | shop_05 | 2019-03-04 23:53:19 | 1 | 3 |
| 100001 | shop_07 | 2019-03-04 23:53:47 | 0 | 3 |
| 100001 | shop_05 | 2019-03-05 17:13:47 | 1 | 4 |
| 100001 | shop_04 | 2019-03-06 20:44:25 | 0 | 4 |
| 100001 | shop_04 | 2019-03-06 23:01:55 | 0 | 4 |
この結果を見ればすぐにわかるかと思います。cumsumとかはwindow関数と呼ばれ、mutateの中で活用します。SQLだとover句をイメージしてもらえるとスムーズです。
このとき、0となっているのは｢まだフラグが立ってない時期(つまり訪問前)のログ｣となりますので、あとはこれを利用してpre-postラベルを準備します:
res <- res %>%
mutate(pre_post = if_else(cum_target == 0, "pre", "post"))
knitr::kable(head(res, 20))| user_id | shop_id | dt | target_flag | cum_target | pre_post |
|---|---|---|---|---|---|
| 100001 | shop_06 | 2019-02-14 21:04:51 | 0 | 0 | pre |
| 100001 | shop_05 | 2019-02-14 22:52:52 | 1 | 1 | post |
| 100001 | shop_05 | 2019-02-16 22:08:02 | 1 | 2 | post |
| 100001 | shop_03 | 2019-02-17 19:45:45 | 0 | 2 | post |
| 100001 | shop_07 | 2019-02-19 18:02:35 | 0 | 2 | post |
| 100001 | shop_04 | 2019-02-20 20:50:28 | 0 | 2 | post |
| 100001 | shop_10 | 2019-02-22 19:41:16 | 0 | 2 | post |
| 100001 | shop_03 | 2019-02-22 23:51:35 | 0 | 2 | post |
| 100001 | shop_01 | 2019-02-24 20:27:48 | 0 | 2 | post |
| 100001 | shop_09 | 2019-02-25 19:16:26 | 0 | 2 | post |
| 100001 | shop_06 | 2019-02-26 21:50:13 | 0 | 2 | post |
| 100001 | shop_01 | 2019-02-26 22:43:49 | 0 | 2 | post |
| 100001 | shop_06 | 2019-02-27 21:23:47 | 0 | 2 | post |
| 100001 | shop_09 | 2019-02-28 19:05:35 | 0 | 2 | post |
| 100001 | shop_10 | 2019-03-04 19:23:41 | 0 | 2 | post |
| 100001 | shop_05 | 2019-03-04 23:53:19 | 1 | 3 | post |
| 100001 | shop_07 | 2019-03-04 23:53:47 | 0 | 3 | post |
| 100001 | shop_05 | 2019-03-05 17:13:47 | 1 | 4 | post |
| 100001 | shop_04 | 2019-03-06 20:44:25 | 0 | 4 | post |
| 100001 | shop_04 | 2019-03-06 23:01:55 | 0 | 4 | post |
ここまでくれば、あとは集計です:
res <- res %>%
group_by(user_id, shop_id, pre_post) %>%
summarise(count = n())
knitr::kable(head(res, 10))| user_id | shop_id | pre_post | count |
|---|---|---|---|
| 100001 | shop_01 | post | 5 |
| 100001 | shop_02 | post | 2 |
| 100001 | shop_03 | post | 2 |
| 100001 | shop_04 | post | 3 |
| 100001 | shop_05 | post | 5 |
| 100001 | shop_06 | post | 2 |
| 100001 | shop_06 | pre | 1 |
| 100001 | shop_07 | post | 2 |
| 100001 | shop_08 | post | 1 |
| 100001 | shop_09 | post | 2 |
さて、ここでログデータお約束の｢ログがないデータは集計できない｣問題が発生します。例えば、｢1回目からshop_05に来たならば、その人のpreログデータは生成されていないので出てこない｣といった状況です。
この対処法はいくつかあるのですが、今回は面倒だったのでspreadするときにfill = 0として埋めることにしました:
res <- res %>%
spread(pre_post, count, fill = 0)
knitr::kable(head(res, 10))| user_id | shop_id | post | pre |
|---|---|---|---|
| 100001 | shop_01 | 5 | 0 |
| 100001 | shop_02 | 2 | 0 |
| 100001 | shop_03 | 2 | 0 |
| 100001 | shop_04 | 3 | 0 |
| 100001 | shop_05 | 5 | 0 |
| 100001 | shop_06 | 2 | 1 |
| 100001 | shop_07 | 2 | 0 |
| 100001 | shop_08 | 1 | 0 |
| 100001 | shop_09 | 2 | 0 |
| 100001 | shop_10 | 2 | 0 |
これで完了です。
4.4 応用
今回のケースはズバリそのものというシチュエーションは少ないですが、これをベースにいろんな応用ができます。たとえば、｢shop_05への初回訪問以降、ある店を訪問した後に、次に訪問した店を把握できるようなデータがほしい｣という場合には、以下のようになります:
df_5_fromto <- df_5 %>%
# 並べ替えとユーザー単位でグループ化
arrange(user_id, dt) %>%
group_by(user_id) %>%
# フラグ立てとpre-postラベル付与
mutate(target_flag = if_else(shop_id == "shop_05", 1, 0)) %>%
mutate(cum_target = cumsum(target_flag)) %>%
mutate(pre_post = if_else(cum_target == 0, "pre", "post")) %>%
# lead関数でひとつ下へずらす
mutate(from_shop_id = shop_id,
to_shop_id = lead(shop_id)) %>%
# preはいらんので取り除く
filter(pre_post != "pre")
knitr::kable(head(df_5_fromto, 20))| user_id | shop_id | dt | target_flag | cum_target | pre_post | from_shop_id | to_shop_id |
|---|---|---|---|---|---|---|---|
| 100001 | shop_05 | 2019-02-14 22:52:52 | 1 | 1 | post | shop_05 | shop_05 |
| 100001 | shop_05 | 2019-02-16 22:08:02 | 1 | 2 | post | shop_05 | shop_03 |
| 100001 | shop_03 | 2019-02-17 19:45:45 | 0 | 2 | post | shop_03 | shop_07 |
| 100001 | shop_07 | 2019-02-19 18:02:35 | 0 | 2 | post | shop_07 | shop_04 |
| 100001 | shop_04 | 2019-02-20 20:50:28 | 0 | 2 | post | shop_04 | shop_10 |
| 100001 | shop_10 | 2019-02-22 19:41:16 | 0 | 2 | post | shop_10 | shop_03 |
| 100001 | shop_03 | 2019-02-22 23:51:35 | 0 | 2 | post | shop_03 | shop_01 |
| 100001 | shop_01 | 2019-02-24 20:27:48 | 0 | 2 | post | shop_01 | shop_09 |
| 100001 | shop_09 | 2019-02-25 19:16:26 | 0 | 2 | post | shop_09 | shop_06 |
| 100001 | shop_06 | 2019-02-26 21:50:13 | 0 | 2 | post | shop_06 | shop_01 |
| 100001 | shop_01 | 2019-02-26 22:43:49 | 0 | 2 | post | shop_01 | shop_06 |
| 100001 | shop_06 | 2019-02-27 21:23:47 | 0 | 2 | post | shop_06 | shop_09 |
| 100001 | shop_09 | 2019-02-28 19:05:35 | 0 | 2 | post | shop_09 | shop_10 |
| 100001 | shop_10 | 2019-03-04 19:23:41 | 0 | 2 | post | shop_10 | shop_05 |
| 100001 | shop_05 | 2019-03-04 23:53:19 | 1 | 3 | post | shop_05 | shop_07 |
| 100001 | shop_07 | 2019-03-04 23:53:47 | 0 | 3 | post | shop_07 | shop_05 |
| 100001 | shop_05 | 2019-03-05 17:13:47 | 1 | 4 | post | shop_05 | shop_04 |
| 100001 | shop_04 | 2019-03-06 20:44:25 | 0 | 4 | post | shop_04 | shop_04 |
| 100001 | shop_04 | 2019-03-06 23:01:55 | 0 | 4 | post | shop_04 | shop_01 |
| 100001 | shop_01 | 2019-03-06 23:30:15 | 0 | 4 | post | shop_01 | shop_02 |
あとは集計したりネットワーク分析をしたりと利活用できるでしょう。