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

「データサイエンス100本ノック（構造化データ加工編）」の R と SQL 実装を丁寧に解説するシリーズです。本編にはない、R によるデータベース操作の方法についても、あわせて紹介しています。

R のデータ加工は dplyr をベースとしており、データフレーム操作とデータベース操作の両面から学べる構成になっています。

RStudio や VSCode など、一般的な R の開発環境でそのまま実行できるようにしているので、実践的なスキルを身につけたい方におすすめです。

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

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

演習問題

難易度

R-056 の抽出結果と性別コード（gender_cd）により、新たに性別×年代の組み合わせを表すカテゴリデータを作成し、10件表示せよ。組み合わせを表すカテゴリの値は任意とする。

出力イメージ:

# A tibble: 10 × 5
   customer_id    birth_day  gender_age gender_cd   age
CS021313000114 1981-04-29 1_30-39    1            37
CS037613000071 1952-04-01 9_60+      9            66
CS031415000172 1976-10-04 1_40-49    1            42
CS028811000001 1933-03-27 1_60+      1            86
CS001215000145 1995-03-29 1_20-29    1            24
CS020401000016 1974-09-15 0_40-49    0            44
CS015414000103 1977-08-09 1_40-49    1            41
CS029403000008 1973-08-17 0_40-49    0            45
CS015804000004 1931-05-02 0_60+      0            87
CS033513000180 1962-07-11 1_50-59    1            56

実行環境の構築について

R コード実行環境の構築に必要な手順はこちらから確認できます。セットアップが完了すると、必要なパッケージとデータを使用できるようになります。

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

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

利用するデータ

以下のデータを利用します。

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

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

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

# A tibble: 21,971 × 4
   customer_id    birth_day  gender_cd   age
   <chr>          <date>     <chr>     <int>
 1 CS021313000114 1981-04-29 1            37
 2 CS037613000071 1952-04-01 9            66
 3 CS031415000172 1976-10-04 1            42
 4 CS028811000001 1933-03-27 1            86
 5 CS001215000145 1995-03-29 1            24
 6 CS020401000016 1974-09-15 0            44
 7 CS015414000103 1977-08-09 1            41
...

解答例と実行結果

df_cust = df_customer %>% 
  select(customer_id, birth_day, gender_cd, age) %>% 
  mutate(
    age_rng = epikit::age_categories(
      age, lower = 0, upper = 60, by = 10
    )
  )

df_cust %>% 
  unite("gender_age", gender_cd, age_rng, sep = "_", remove = FALSE) %>% 
  mutate(
    gender_age = 
      if_else(
        is.na(gender_cd) | is.na(age), 
        NA, 
        gender_age
      )
  ) %>% 
  select(-age_rng) %>% 
  head(10)

# A tibble: 10 × 5
   customer_id    birth_day  gender_age gender_cd   age
   <chr>          <date>     <chr>      <chr>     <int>
CS021313000114 1981-04-29 1_30-39    1            37
CS037613000071 1952-04-01 9_60+      9            66
CS031415000172 1976-10-04 1_40-49    1            42
CS028811000001 1933-03-27 1_60+      1            86
CS001215000145 1995-03-29 1_20-29    1            24
CS020401000016 1974-09-15 0_40-49    0            44
CS015414000103 1977-08-09 1_40-49    1            41
CS029403000008 1973-08-17 0_40-49    0            45
CS015804000004 1931-05-02 0_60+      0            87
CS033513000180 1962-07-11 1_50-59    1            56

解説

select() により、customer_id、birth_day、gender_cd、age の4カラムのみを抽出します。
mutate() と epikit::age_categories() を用いて、age を年代区分 (10歳刻み、60歳以上は「60+」) に変換し、age_rng 列を作成します。
unite() を用いて、gender_cd と age_rng を組み合わせた gender_age 列を新しく作成します。
- 区切り文字は "_" に設定し、元の gender_cd と age_rng のカラムは remove = FALSE により保持しています。
mutate() と if_else() により、gender_cd または age が欠損している場合は gender_age を NA にします。これにより、不完全なデータに対して誤った連結を防いでいます。
select(-age_rng) で age_rng 列を削除します。
head(10) で、結果の先頭10行を表示します。

R (データベース操作)

利用するデータ

以下のデータを利用します。

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

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

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

   customer_id    birth_day  gender_cd   age
   <chr>          <date>     <chr>     <int>
CS021313000114 1981-04-29 1            37
CS037613000071 1952-04-01 9            66
CS031415000172 1976-10-04 1            42
CS028811000001 1933-03-27 1            86
CS001215000145 1995-03-29 1            24
CS020401000016 1974-09-15 0            44
CS015414000103 1977-08-09 1            41
...

解答例と実行結果

db_result = db_customer %>% 
  select(customer_id, birth_day, gender_cd, age) %>% 
  mutate(
    tmp = pmin(FLOOR(age / 10) * 10, 60) %>% as.integer() %>% as.character(), 
    age_rng = sql("LPAD(tmp, 2, '0')"), 
    gender_age = 
      if_else(
        is.na(gender_cd) | is.na(age), 
        NA, 
        stringr::str_c(gender_cd, age_rng, sep = "_")
      )
  ) %>% 
  select(-c(tmp, age_rng)) %>% 
  head(10)

db_result %>% collect()

# A tibble: 10 × 5
   customer_id    birth_day  gender_cd   age gender_age
   <chr>          <date>     <chr>     <int> <chr>     
CS021313000114 1981-04-29 1            37 1_30      
CS037613000071 1952-04-01 9            66 9_60      
CS031415000172 1976-10-04 1            42 1_40      
CS028811000001 1933-03-27 1            86 1_60      
CS001215000145 1995-03-29 1            24 1_20      
CS020401000016 1974-09-15 0            44 0_40      
CS015414000103 1977-08-09 1            41 1_40      
CS029403000008 1973-08-17 0            45 0_40      
CS015804000004 1931-05-02 0            87 0_60      
CS033513000180 1962-07-11 1            56 1_50

解説

select() で customer_id、birth_day、gender_cd、age の4カラムを抽出します。
mutate() を用いて、年代および、性別・年代の組み合わせ列 gender_age を作成します。以下の処理が行われています。
- tmp 列では、年齢を FLOOR(age / 10) * 10 で10歳単位に切り捨てた上で最大値を60に制限し、数値を文字列 (character) 型に変換しています。
- age_rng 列では、sql("LPAD(tmp, 2, '0')") を使い、2桁の文字列にゼロ埋めしています。 (例：20 → "20"、5 → "05")
- gender_age 列では、gender_cd と age_rng を stringr::str_c() で _ 区切りで連結しています。ただし、gender_cd または age が NA の場合は NA を返すように if_else() で制御しています。
select(-c(tmp, age_rng)) により中間列を除外します。
head(10) で、先頭の10行を抽出します。
db_result %>% collect()
データベース操作の結果を R のデータフレーム (tibble) として取得します。

sql() による SQL 構文の埋め込みについてはこちらを参照してください。

SQL

利用するデータ

以下のデータを利用します。

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

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

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

# A tibble: 21,971 × 4
   customer_id    birth_day  gender_cd   age
   <chr>          <date>     <chr>     <int>
 1 CS021313000114 1981-04-29 1            37
 2 CS037613000071 1952-04-01 9            66
 3 CS031415000172 1976-10-04 1            42
 4 CS028811000001 1933-03-27 1            86
 5 CS001215000145 1995-03-29 1            24
 6 CS020401000016 1974-09-15 0            44
 7 CS015414000103 1977-08-09 1            41
...

自動生成された SQL クエリ

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

db_result %>% show_query(cte = FALSE)

SQL

SELECT
  customer_id,
  birth_day,
  gender_cd,
  age,
  CASE 
    WHEN ((gender_cd IS NULL) OR (age IS NULL)) THEN NULL 
    WHEN NOT ((gender_cd IS NULL) OR (age IS NULL)) THEN 
      (CONCAT_WS('_', gender_cd, age_rng)) 
  END AS gender_age
FROM (
  SELECT
    customer_id,
    birth_day,
    gender_cd,
    age,
    CAST(CAST(LEAST(FLOOR(age / 10.0) * 10.0, 60.0) AS INTEGER) AS TEXT) AS tmp,
    LPAD(tmp, 2, '0') AS age_rng
  FROM customer
) q01
LIMIT 10

解答例

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

SQL

SELECT
  customer_id,
  birth_day, 
  gender_cd,
  age, 
  CASE 
    WHEN gender_cd IS NULL OR age_rng IS NULL THEN NULL
    ELSE CONCAT_WS('_', gender_cd, age_rng) 
  END AS gender_age
FROM (
  SELECT
    *, 
    CASE 
      WHEN age IS NOT NULL THEN 
        LPAD(
          CAST(
            LEAST(CAST(FLOOR(age / 10.0) * 10.0 AS INTEGER), 60) AS TEXT
          ), 
          2, '0'
        )
    END AS age_rng
  FROM 
    customer
)
LIMIT 10

解説

サブクエリ内で age_rng を計算しています。具体的には、年齢 (age) を10歳刻みで区切り、最大値を60に制限した上で文字列に変換し、LPAD(..., 2, '0') により2桁のゼロ埋めを行っています。(例：20 → "20"、5 → "05")
- FLOOR(age / 10.0) * 10.0：年齢を10単位で切り捨て
- LEAST(..., 60)：最大60に制限
- CAST(... AS TEXT)：文字列化
- LPAD(..., 2, '0')：2桁でゼロ埋め
外側のクエリでは gender_cd と age_rng を結合し、gender_age を作成しています。
- CONCAT_WS('_', gender_cd, age_rng)：区切り文字 _ で結合
- CASE WHEN gender_cd IS NULL OR age_rng IS NULL THEN NULL により、いずれかが欠損している場合は NULL を返す。
最終的に、customer_id、birth_day、gender_cd、age、gender_age の5列を出力します。
LIMIT 10 により、先頭の10件を抽出します。

実行結果の確認

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

query = sql("
SELECT
  customer_id,
  birth_day, 
  gender_cd,
  age, 
  CASE 
    WHEN gender_cd IS NULL OR age_rng IS NULL THEN NULL
    ELSE CONCAT_WS('_', gender_cd, age_rng) 
  END AS gender_age
FROM (
  SELECT
    *, 
    CASE 
      WHEN age IS NOT NULL THEN 
        LPAD(
          CAST(
            LEAST(CAST(FLOOR(age / 10.0) * 10.0 AS INTEGER), 60) AS TEXT
          ), 
          2, '0'
        )
    END AS age_rng
  FROM 
    customer
)
LIMIT 10
"
)

query %>% db_get_query(con)

# A tibble: 10 × 5
   customer_id    birth_day  gender_cd   age gender_age
   <chr>          <date>     <chr>     <int> <chr>     
CS021313000114 1981-04-29 1            37 1_30      
CS037613000071 1952-04-01 9            66 9_60      
CS031415000172 1976-10-04 1            42 1_40      
CS028811000001 1933-03-27 1            86 1_60      
CS001215000145 1995-03-29 1            24 1_20      
CS020401000016 1974-09-15 0            44 0_40      
CS015414000103 1977-08-09 1            41 1_40      
CS029403000008 1973-08-17 0            45 0_40      
CS015804000004 1931-05-02 0            87 0_60      
CS033513000180 1962-07-11 1            56 1_50      

リンク