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

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

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

演習問題

難易度

043で作成した売上サマリデータ（sales_summary）は性別の売上を横持ちさせたものであった。このデータから性別を縦持ちさせ、年代、性別コード、売上金額の3項目に変換せよ。ただし、性別コードは男性を “00”、女性を “01”、不明を “99” とする。

出力イメージ:

   age_range gender_cd sum_amount
0-9       00                 0
10-19     00              1591
20-29     00             72940
30-39     00            177322
40-49     00             19355
50-59     00             54320
60-69     00            272469
70-79     00             13435
80-89     00             46360
90-99     00                 0
100+      00                 0
0-9       01                 0
10-19     01            149836
20-29     01           1363724
...
100+      99                 0

実行環境の構築について

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

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

ここでは、元のデータから縦持ちさせ、年代、性別コード、売上金額の 3 項目に変換する解答例を紹介します。

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("00", "01", "99"))
    )
  )

df_sales %>% 
  tidyr::complete(
    age_range, gender_cd, 
    fill = list(sum_amount = 0.0)
  ) %>% 
  arrange(gender_cd, age_range)

# A tibble: 33 × 3
   age_range gender_cd sum_amount
   <fct>     <fct>          <dbl>
0-9       00                 0
10-19     00              1591
20-29     00             72940
30-39     00            177322
40-49     00             19355
50-59     00             54320
60-69     00            272469
70-79     00             13435
80-89     00             46360
90-99     00                 0
100+      00                 0
0-9       01                 0
10-19     01            149836
20-29     01           1363724
30-39     01            693047
40-49     01           9320791
50-59     01           6685192
60-69     01            987741
70-79     01             29764
80-89     01            262923
90-99     01              6260
100+      01                 0
0-9       99                 0
10-19     99              4317
20-29     99             44328
30-39     99             50441
40-49     99            483512
50-59     99            342923
60-69     99             71418
70-79     99              2427
80-89     99              5111
90-99     99                 0
100+      99                 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("00", "01", "99"))
    )
  )

gender_cd 列に対して forcats::lvls_revalue() を適用し、0 を 00、1 を 01、9 を 99 に変換します。

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

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

性別・年齢範囲の全パターンの作成と並び替え

df_sales %>% 
  tidyr::complete(
    age_range, gender_cd, 
    fill = list(sum_amount = 0.0)
  ) %>% 
  arrange(gender_cd, age_range)

tidyr::complete() により、全ての age_range × gender_cd の組み合わせを作成します。
元のデータに存在しない組み合わせには、sum_amount を 0.0 で埋めます。
arrange() で、gender_cd と age_range の昇順に並び替えます。

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
 ...

解答例と実行結果

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

db_sales = 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")
  ) %>% 
  mutate(
    gender_cd = case_match(
      gender_cd, 
      "0" ~ "00", 
      "1" ~ "01", 
      .default = "99"
    )
  )

db_result = db_sales %>% 
  tidyr::complete(
    age_range, gender_cd, 
    fill = list(sum_amount = 0.0)
  ) %>% 
  arrange(gender_cd, age_range)

db_result %>% collect()

# A tibble: 27 × 3
   age_range gender_cd sum_amount
       <int> <chr>          <dbl>
      10 00              1591
      20 00             72940
      30 00            177322
      40 00             19355
      50 00             54320
      60 00            272469
      70 00             13435
      80 00             46360
      90 00                 0
      10 01            149836
      20 01           1363724
      30 01            693047
      40 01           9320791
      50 01           6685192
      60 01            987741
      70 01             29764
      80 01            262923
      90 01              6260
      10 99              4317
      20 99             44328
      30 99             50441
      40 99            483512
      50 99            342923
      60 99             71418
      70 99              2427
      80 99              5111
      90 99                 0

解説

inner_join()
- db_customer と db_receipt を customer_id をキーとして内部結合します。
- db_receipt から customer_id と amount のみを選択しています。
mutate(age_range ...)
- 年齢 (age) を 10 歳単位に切り捨てて、age_range 列を作成します。
- floor(age / 10) * 10 で年齢を 10 歳ごとに分類し、それを整数型に変換しています。
summarise()
- gender_cd と age_range ごとに amount の合計 (sum(amount)) を求めます。

mutate(gender_cd ...)

gender_cd 列に対して case_match() を使用し、0 を 00、1 を 01、9 を 99 に変換します。

ここまでの処理により、db_sales は次のようなデータになります。

   gender_cd age_range sum_amount
   <chr>         <int>      <dbl>
01               20    1363724
99               20      44328
00               80      46360
01               10     149836
99               60      71418
01               60     987741
99               10       4317
 ...

tidyr::complete()
- 全ての age_range × gender_cd の組み合わせを作成します。
- 元のデータに存在しない組み合わせには、sum_amount を 0.0 で埋めます。
arrange()
- gender_cd と 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 クエリ

データベース操作による結果 (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
),
q03 AS (
  SELECT DISTINCT age_range
  FROM q02 q01
  GROUP BY gender_cd, age_range
),
q04 AS (
  SELECT customer.*, amount
  FROM customer
  INNER JOIN receipt
    ON (customer.customer_id = receipt.customer_id)
),
q05 AS (
  SELECT q01.*, CAST((FLOOR(age / 10.0) * 10.0) AS INTEGER) AS age_range
  FROM q04 q01
),
q06 AS (
  SELECT DISTINCT gender_cd
  FROM q05 q01
  GROUP BY gender_cd, age_range
),
q07 AS (
  SELECT age_range, gender_cd
  FROM q03 LHS
  CROSS JOIN q06 RHS
),
q08 AS (
  SELECT customer.*, amount
  FROM customer
  INNER JOIN receipt
    ON (customer.customer_id = receipt.customer_id)
),
q09 AS (
  SELECT q01.*, CAST((FLOOR(age / 10.0) * 10.0) AS INTEGER) AS age_range
  FROM q08 q01
),
q10 AS (
  SELECT gender_cd, age_range, SUM(amount) AS sum_amount
  FROM q09 q01
  GROUP BY gender_cd, age_range
),
q11 AS (
  SELECT
    COALESCE(LHS.age_range, RHS.age_range) AS age_range,
    COALESCE(LHS.gender_cd, RHS.gender_cd) AS gender_cd,
    sum_amount
  FROM q07 LHS
  FULL JOIN q10 RHS
    ON (LHS.age_range = RHS.age_range AND LHS.gender_cd = RHS.gender_cd)
),
q12 AS (
  SELECT age_range, gender_cd, COALESCE(sum_amount, 0.0) AS sum_amount
  FROM q11 q01
)
SELECT
  age_range,
  CASE
WHEN (gender_cd IN ('0')) THEN '00'
WHEN (gender_cd IN ('1')) THEN '01'
ELSE '99'
END AS gender_cd,
  sum_amount
FROM q12 q01
ORDER BY gender_cd, age_range

解答例

このクエリを参考にして、次の解答例が得られます。

SQL

WITH joined_data AS (
  SELECT
    c.customer_id,
    CAST((FLOOR(age / 10.0) * 10.0) AS INTEGER) AS age_range, 
    CASE c.gender_cd 
      WHEN '0' THEN '00'
      WHEN '1' THEN '01'
      ELSE '99' 
    END AS gender_cd,
    r.amount
  FROM 
    customer c
  INNER JOIN 
    receipt r 
  USING(customer_id)
),
all_combinations AS (
  SELECT 
    jd.age_range, g.gender_cd 
  FROM 
    (SELECT DISTINCT age_range FROM joined_data) AS jd
  CROSS JOIN 
    (VALUES ('00'), ('01'), ('99')) AS g(gender_cd)
),
sales_summary AS (
  SELECT 
    gender_cd, 
    age_range, 
    SUM(amount) AS sum_amount
  FROM 
    joined_data
  GROUP BY 
    gender_cd, age_range
)
SELECT 
  ac.age_range,
  ac.gender_cd,
  COALESCE(ss.sum_amount, 0.0) AS sum_amount
FROM 
  all_combinations ac
LEFT JOIN 
  sales_summary ss
USING   
  (age_range, gender_cd)
ORDER BY 
  ac.gender_cd, ac.age_range

解説

1. `joined_data` の作成

SQL

WITH joined_data AS (
  SELECT
    c.customer_id,
    CAST((FLOOR(age / 10.0) * 10.0) AS INTEGER) AS age_range, 
    CASE c.gender_cd 
      WHEN '0' THEN '00'
      WHEN '1' THEN '01'
      ELSE '99' 
    END AS gender_cd,
    r.amount
  FROM 
    customer c
  INNER JOIN 
    receipt r 
  USING(customer_id)
)

customer テーブルと receipt テーブルを結合し、age と gender_cd を変換します。

INNER JOIN による結合
- customer_id をキーに customer テーブルと receipt テーブルを結合します。
年齢範囲 (age_range) の計算
- age を 10 歳刻みの年齢範囲に変換します。(例: age が 23 の場合、age_range は 20。)
- FLOOR(age / 10.0) * 10.0 により、年齢を 10 の倍数に切り下げます。
性別 (gender_cd) の変換
- gender_cd を '0' → '00'、'1' → '01'、それ以外 → '99' に変換します。

2. `all_combinations` の作成

SQL

all_combinations AS (
  SELECT 
    jd.age_range, g.gender_cd 
  FROM 
    (SELECT DISTINCT age_range FROM joined_data) AS jd
  CROSS JOIN 
    (VALUES ('00'), ('01'), ('99')) AS g(gender_cd)
)

CROSS JOIN による全組み合わせの作成
- age_range のユニークな値と gender_cd の 3 種類 ('00', '01', '99') の全組み合わせを作成します。
- age_range は joined_data から取得し、gender_cd は VALUES 句で指定しています。

3. `sales_summary` の作成

SQL

sales_summary AS (
  SELECT 
    gender_cd, 
    age_range, 
    SUM(amount) AS sum_amount
  FROM 
    joined_data
  GROUP BY 
    gender_cd, age_range
)

売上金額 (sum_amount) の集計
- SUM(amount) と GROUP BY により、(gender_cd, age_range) の各組み合わせごとに売上金額を集計します。

4. メインの `SELECT` 文と結合

SQL

SELECT 
  ac.age_range,
  ac.gender_cd,
  COALESCE(ss.sum_amount, 0.0) AS sum_amount
FROM 
  all_combinations ac
LEFT JOIN 
  sales_summary ss
USING   
  (age_range, gender_cd)
ORDER BY 
  ac.gender_cd, ac.age_range

LEFT JOIN と USING
- all_combinations を基準に、(age_range, gender_cd) をキーとして sales_summary を LEFT JOIN します。
COALESCE()
- COALESCE(ss.sum_amount, 0.0) により、売上金額が NULL の場合は 0.0 に置き換えます。
ORDER BY
- 結果を gender_cd と age_range の昇順に並び替えます。

実行結果の確認

この SQLクエリの実行結果は、次のようにして確認できます。

query = sql("
WITH joined_data AS (
  SELECT
    c.customer_id,
    CAST((FLOOR(age / 10.0) * 10.0) AS INTEGER) AS age_range, 
    CASE c.gender_cd 
      WHEN '0' THEN '00'
      WHEN '1' THEN '01'
      ELSE '99' 
    END AS gender_cd,
    r.amount
  FROM 
    customer c
  INNER JOIN 
    receipt r 
  USING(customer_id)
),
all_combinations AS (
  SELECT 
    jd.age_range, g.gender_cd 
  FROM 
    (SELECT DISTINCT age_range FROM joined_data) AS jd
  CROSS JOIN 
    (VALUES ('00'), ('01'), ('99')) AS g(gender_cd)
),
sales_summary AS (
  SELECT 
    gender_cd, 
    age_range, 
    SUM(amount) AS sum_amount
  FROM 
    joined_data
  GROUP BY 
    gender_cd, age_range
)
SELECT 
  ac.age_range,
  ac.gender_cd,
  COALESCE(ss.sum_amount, 0.0) AS sum_amount
FROM 
  all_combinations ac
LEFT JOIN 
  sales_summary ss
USING   
  (age_range, gender_cd)
ORDER BY 
  ac.gender_cd, ac.age_range
"
)

query %>% db_get_query(con)

# A tibble: 27 × 3
   age_range gender_cd sum_amount
       <int> <chr>          <dbl>
      10 00              1591
      20 00             72940
      30 00            177322
      40 00             19355
      50 00             54320
      60 00            272469
      70 00             13435
      80 00             46360
      90 00                 0
      10 01            149836
      20 01           1363724
      30 01            693047
      40 01           9320791
      50 01           6685192
      60 01            987741
      70 01             29764
      80 01            262923
      90 01              6260
      10 99              4317
      20 99             44328
      30 99             50441
      40 99            483512
      50 99            342923
      60 99             71418
      70 99              2427
      80 99              5111
      90 99                 0

リンク