[R & SQL] データサイエンス100本ノック＋α - R-043 (解説)

参考記事:
[R & SQL] データサイエンス100本ノック＋α - 概要・導入

設問文はデータサイエンス100本ノック（構造化データ加工編）より引用しています。
すべてのコードはコピー＆ペーストでそのまま実行できます。

演習問題

難易度

レシート明細データ（receipt）と顧客データ（customer）を結合し、性別コード（gender_cd）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリデータを作成せよ。性別コードは0が男性、1が女性、9が不明を表すものとする。

ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。

出力イメージ:

   age_range   male  female unknown
0-9            0       0       0
10-19       1591  149836    4317
20-29      72940 1363724   44328
30-39     177322  693047   50441
40-49      19355 9320791  483512
50-59      54320 6685192  342923
60-69     272469  987741   71418
70-79      13435   29764    2427
80-89      46360  262923    5111
90-99          0    6260       0
100+           0       0       0

実行環境の構築について

R コード実行環境の構築に必要な手順はこちらから確認できます。

セットアップが完了すると、必要なパッケージとデータを使用できるようになります。

解答例を以下に示します。

R (データフレーム操作)

利用するデータ

以下のデータを利用します。
主要なカラムを抜き出してデータの一部を確認します。

df_customer の顧客ID (customer_id)、性別コード (gender_cd)、年齢 (age)

df_customer %>% select(customer_id, gender_cd, age)

# A tibble: 21,971 × 3
  customer_id    gender_cd   age
  <chr>          <chr>     <int>
1 CS021313000114 1            37
2 CS037613000071 9            66
3 CS031415000172 1            42
4 CS028811000001 1            86
5 CS001215000145 1            24
6 CS020401000016 0            44
7 CS015414000103 1            41
...

df_receipt の顧客ID (customer_id) と売上金額 (amount)

df_receipt %>% select(customer_id, amount)

# A tibble: 104,681 × 2
  customer_id    amount
  <chr>           <dbl>
1 CS006214000001    158
2 CS008415000097     81
3 CS028414000014    170
4 ZZ000000000000     25
5 CS025415000050     90
6 CS003515000195    138
7 CS024514000042     30
...

解答例と実行結果

max_age = df_customer$age %>% max(na.rm = TRUE)

df_sales = df_customer %>% 
  inner_join(
    df_receipt, by = "customer_id"
  ) %>% 
  mutate(
    age_range = 
      epikit::age_categories(
        age, 
        lower = 0, 
        upper = floor(!!max_age / 10) * 10 + 10, 
        by = 10
      )
  ) %>% 
  summarise(
    sum_amount = sum(amount), 
    .by = c("gender_cd", "age_range")
  ) %>% 
  mutate(
    across(
      gender_cd, 
      ~ forcats::lvls_revalue(.x, c("male", "female", "unknown"))
    )
  )

df_sales %>% 
  pivot_wider(
    id_cols = age_range, 
    id_expand = TRUE, 
    names_from = gender_cd, 
    values_from = sum_amount, 
    names_expand = TRUE, 
    values_fill = 0.0
  )

# A tibble: 11 × 4
   age_range   male  female unknown
   <fct>      <dbl>   <dbl>   <dbl>
0-9            0       0       0
10-19       1591  149836    4317
20-29      72940 1363724   44328
30-39     177322  693047   50441
40-49      19355 9320791  483512
50-59      54320 6685192  342923
60-69     272469  987741   71418
70-79      13435   29764    2427
80-89      46360  262923    5111
90-99          0    6260       0
100+           0       0       0

解説

年齢の最大値を取得

max_age = df_customer$age %>% max(na.rm = TRUE)

df_customer データフレームの age 列の最大値を取得します。

`df_customer` と `df_receipt` を結合

df_sales = df_customer %>%
  inner_join(
    df_receipt, by = "customer_id"
  )

customer_id をキーに df_customer と df_receipt を内部結合 (inner_join()) し、顧客ごとの購買データを作成します。

年齢を 10 歳ごとのカテゴリに変換

mutate(
  age_range =
    epikit::age_categories(
      age,
      lower = 0,
      upper = floor(!!max_age / 10) * 10 + 10,
      by = 10
    )
)

年齢を 10 歳ごとの範囲に分類し、age_range 列としてデータフレームに追加します。

age_categories() は、指定された年齢 (age) を範囲ごとに分類します。
lower = 0 で年齢範囲の下限を 0 歳に設定します。
upper = floor(!!max_age / 10) * 10 + 10 で、max_age に基づいた上限年齢を計算し、年齢範囲の上限に設定します。
by = 10 で、10 歳ごとに年齢を分類します。

性別と年齢範囲別に売上金額を集計

summarise(
  sum_amount = sum(amount),
  .by = c("gender_cd", "age_range")
)

gender_cd と age_range ごとに売上金額 amount を合計します。

性別コードをラベルに変換

mutate(
  across(
    gender_cd,
    ~ forcats::lvls_revalue(.x, c("male", "female", "unknown"))
  )
)

gender_cd 列に対して forcats::lvls_revalue() を適用し、0 を male、1 を female、9 を unknown に変換します。

ここまでの処理により、df_sales は次のようなデータフレームになります。

# A tibble: 25 × 3
   gender_cd age_range sum_amount
   <fct>     <fct>          <dbl>
 1 female    40-49        9320791
 2 female    20-29        1363724
 3 female    50-59        6685192
 4 female    30-39         693047
 5 unknown   40-49         483512
 6 unknown   30-39          50441
 7 unknown   20-29          44328
 8 male      60-69         272469
 ...
25 unknown   10-19           4317

データを横持ちに整形

df_sales %>%
  pivot_wider(
    id_cols = age_range,
    id_expand = TRUE,
    names_from = gender_cd,
    values_from = sum_amount,
    names_expand = TRUE,
    values_fill = 0.0
  )

pivot_wider() を使用して、age_range を行に、gender_cd を列に変換し、売上金額 sum_amount を各セルに対応する値として横持ち形式で整理します。

id_cols = age_range： age_range を行の識別子として設定します。
id_expand = TRUE： age_range の値が欠けている場合でも、全ての年齢層を展開します。
names_from = gender_cd： gender_cd を列名に使用します。性別 (male、female、unknown) に基づいて列が作成されます。
values_from = sum_amount： sum_amount (売上金額) を対応するセルの値として使用します。
names_expand = TRUE： gender_cd 列に性別コードの一部が存在しない場合でも、すべてのカテゴリ (male、female、unknown) を列に展開します。
values_fill = 0.0： sum_amount の値が欠けている場合、欠損したセルを 0.0 で埋めます。

R (データベース操作)

利用するデータ

以下のデータを利用します。
主要なカラムを抜き出してデータの一部を確認します。

db_customer の顧客ID (customer_id)、性別コード (gender_cd)、年齢 (age)

db_customer %>% select(customer_id, gender_cd, age) %>% collect()

  customer_id    gender_cd   age
  <chr>          <chr>     <int>
CS021313000114 1            37
CS037613000071 9            66
CS031415000172 1            42
CS028811000001 1            86
CS001215000145 1            24
CS020401000016 0            44
CS015414000103 1            41
...

db_receipt の顧客ID (customer_id) と売上金額 (amount)

db_receipt %>% select(customer_id, amount) %>% collect()

   customer_id    amount
   <chr>           <dbl>
CS006214000001    158
CS008415000097     81
CS028414000014    170
ZZ000000000000     25
CS025415000050     90
CS003515000195    138
CS024514000042     30
 ...

解答例 (1)

dbplyr は epikit::age_categories() に対応していないため、年齢を用いた計算により 10 歳単位に分類します。

以下は、pivot_wider() を使用して横持ち形式に変換する解答例です。

解答コードと実行結果

db_result = db_customer %>% 
  inner_join(
    db_receipt %>% select(customer_id, amount), 
    by = "customer_id"
  ) %>% 
  mutate(
    age_range = 
      (floor(age / 10) * 10) %>% as.integer()
  ) %>% 
  summarise(
    sum_amount = sum(amount), 
    .by = c("gender_cd", "age_range")
  ) %>% 
  pivot_wider(
    id_cols = age_range, 
    names_from = gender_cd, 
    values_from = sum_amount, 
    names_sort = TRUE, 
    values_fill = 0.0
  ) %>% 
  rename(
    male = "0", female = "1", unknown = "9"
  ) %>% 
  arrange(age_range)

db_result %>% collect()

# A tibble: 9 × 4
  age_range   male  female unknown
      <int>  <dbl>   <dbl>   <dbl>
      10   1591  149836    4317
      20  72940 1363724   44328
      30 177322  693047   50441
      40  19355 9320791  483512
      50  54320 6685192  342923
      60 272469  987741   71418
      70  13435   29764    2427
      80  46360  262923    5111
      90      0    6260       0

解説

inner_join()
- db_customer と db_receipt を customer_id をキーとして内部結合します。
- db_receipt から customer_id と amount のみを選択しています。
mutate()
- 年齢 (age) を 10 歳単位に切り捨てて、age_range 列を作成します。
- floor(age / 10) * 10 で年齢を 10 歳ごとに分類し、それを整数型に変換しています。
summarise()
- gender_cd と age_range ごとに amount の合計 (sum(amount)) を求めます。
pivot_wider()
- age_range を行に、gender_cd を列に展開し、sum_amount を対応するセルの値として横持ち形式に整理します。
- 欠損箇所を 0.0 で埋め、性別の列名 (0、1、9) をソートします。
rename()
- 列名 0、1、9 をそれぞれ male、female、unknown にリネームします。
arrange()
- age_range の昇順に並べ替えます。
db_result %>% collect()
- データベース操作の結果を R のデータフレーム (tibble) として取得します。

解答例 (2)

pivot_wider() を使用しない場合の解答例です。

解答コードと実行結果

db_result = db_customer %>% 
  inner_join(
    db_receipt %>% select(customer_id, amount), 
    by = "customer_id"
  ) %>%
  mutate(
    age_range = (floor(age / 10) * 10) %>% as.integer()
  ) %>%
  summarise(
    male = sum(if_else(gender_cd == "0", amount, 0.0)),
    female = sum(if_else(gender_cd == "1", amount, 0.0)),
    unknown = sum(if_else(gender_cd == "9", amount, 0.0)), 
    .by = age_range
  ) %>%
  arrange(age_range)

db_result %>% collect()

# A tibble: 9 × 4
  age_range   male  female unknown
      <int>  <dbl>   <dbl>   <dbl>
      10   1591  149836    4317
      20  72940 1363724   44328
      30 177322  693047   50441
      40  19355 9320791  483512
      50  54320 6685192  342923
      60 272469  987741   71418
      70  13435   29764    2427
      80  46360  262923    5111
      90      0    6260       0

解説

inner_join()
- db_customer と db_receipt を customer_id をキーとして内部結合します。
- db_receipt から customer_id と amount のみを選択しています。
mutate()
- 年齢 (age) を 10 歳単位に切り捨てて、age_range 列を作成します。
- floor(age / 10) * 10 で年齢を 10 歳ごとに分類し、それを整数型に変換しています。
summarise()
- gender_cd に基づいて、各性別 (male, female, unknown) ごとの売上金額を集計します。
- if_else(gender_cd == "0", amount, 0.0) の式で、性別コードが 0 (男性) であれば amount、そうでない場合は 0.0 に変換します。性別コード 1 と 9 についても同様に処理します。
arrange()
- 年齢範囲 (age_range) の昇順に並べ替えます。
db_result %>% collect()
データベース操作の結果を R のデータフレーム (tibble) として取得します。

SQL

利用するデータ

以下のデータを利用します。
主要なカラムを抜き出してデータの一部を確認します。

customer テーブルの顧客ID (customer_id)、性別コード (gender_cd)、年齢 (age)

"SELECT customer_id, gender_cd, age FROM customer" %>% db_get_query(con)

# A tibble: 21,971 × 3
  customer_id    gender_cd   age
  <chr>          <chr>     <int>
1 CS021313000114 1            37
2 CS037613000071 9            66
3 CS031415000172 1            42
4 CS028811000001 1            86
5 CS001215000145 1            24
6 CS020401000016 0            44
7 CS015414000103 1            41
...

receipt テーブルの顧客ID (customer_id) と売上金額 (amount)

"SELECT customer_id, amount FROM receipt" %>% db_get_query(con)

# A tibble: 104,681 × 2
  customer_id    amount
  <chr>           <dbl>
1 CS006214000001    158
2 CS008415000097     81
3 CS028414000014    170
4 ZZ000000000000     25
5 CS025415000050     90
6 CS003515000195    138
7 CS024514000042     30
...

自動生成された SQL クエリ

データベース操作の解答例 (2) による操作結果 (db_result) に基づき、自動生成された SQLクエリを show_query() で確認できます。

db_result %>% show_query(cte = TRUE)

SQL

WITH q01 AS (
  SELECT customer.*, amount
  FROM customer
  INNER JOIN receipt
    ON (customer.customer_id = receipt.customer_id)
),
q02 AS (
  SELECT q01.*, CAST((FLOOR(age / 10.0) * 10.0) AS INTEGER) AS age_range
  FROM q01
)
SELECT
  age_range,
  SUM(CASE WHEN (gender_cd = '0') THEN amount WHEN NOT (gender_cd = '0') THEN 0.0 END) AS male,
  SUM(CASE WHEN (gender_cd = '1') THEN amount WHEN NOT (gender_cd = '1') THEN 0.0 END) AS female,
  SUM(CASE WHEN (gender_cd = '9') THEN amount WHEN NOT (gender_cd = '9') THEN 0.0 END) AS "unknown"
FROM q02 q01
GROUP BY age_range
ORDER BY age_range

解答例

このクエリをより簡潔な形に書き直すと、次のようになります。

SQL

SELECT
  CAST(FLOOR(c.age / 10.0) * 10.0 AS INTEGER) AS age_range,
  SUM(CASE WHEN c.gender_cd = '0' THEN r.amount ELSE 0 END) AS male,
  SUM(CASE WHEN c.gender_cd = '1' THEN r.amount ELSE 0 END) AS female,
  SUM(CASE WHEN c.gender_cd = '9' THEN r.amount ELSE 0 END) AS "unknown"
FROM 
  customer c
INNER JOIN 
  receipt r 
USING (customer_id)
GROUP BY 
  age_range
ORDER BY 
  age_range

解説

年齢範囲 (age_range) の計算
- FLOOR(c.age / 10.0) * 10.0) で、age を 10 で割り、小数部分を切り捨て、10 倍して年齢範囲を作成します。
- 例えば、age が 23 の場合、FLOOR(23 / 10.0) * 10 は 20 となります。
性別ごとの売上金額の集計
- SUM(CASE WHEN c.gender_cd = '0' THEN r.amount ELSE 0 END) で、性別コードが 0 (男性) の場合の売上金額 amount を集計します。同様に、性別コードが 1 (女性)、9 (不明) の場合も処理します。
INNER JOIN と USING
- customer テーブルと receipt テーブルを customer_id をキーとして内部結合します。
GROUP BY
- age_range でグループ化し、各年齢範囲ごとに集計します。
ORDER BY
- 結果を age_range の昇順に並べ替えます。

実行結果の確認

この SQLクエリの実行結果は、次のようにして確認できます。
シングルクオート (’) とダブルクオート (") を含むため、エスケープを避ける目的で raw string リテラル r"( )" を使用しています。

query = r"(
SELECT
  CAST(FLOOR(c.age / 10.0) * 10.0 AS INTEGER) AS age_range,
  SUM(CASE WHEN c.gender_cd = '0' THEN r.amount ELSE 0 END) AS male,
  SUM(CASE WHEN c.gender_cd = '1' THEN r.amount ELSE 0 END) AS female,
  SUM(CASE WHEN c.gender_cd = '9' THEN r.amount ELSE 0 END) AS "unknown"
FROM 
  customer c
INNER JOIN 
  receipt r 
USING (customer_id)
GROUP BY 
  age_range
ORDER BY 
  age_range
)"

query %>% db_get_query(con)

# A tibble: 9 × 4
  age_range   male  female unknown
      <int>  <dbl>   <dbl>   <dbl>
      10   1591  149836    4317
      20  72940 1363724   44328
      30 177322  693047   50441
      40  19355 9320791  483512
      50  54320 6685192  342923
      60 272469  987741   71418
      70  13435   29764    2427
      80  46360  262923    5111
      90      0    6260       0

リンク