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

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

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

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

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

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

演習問題

難易度

顧客データ（customer）の年齢（age）をもとに10歳刻みで年代を算出し、顧客ID（customer_id）、生年月日（birth_day）とともに10件表示せよ。ただし、60歳以上は全て60歳代とすること。年代を表すカテゴリ名は任意とする。

出力イメージ:

   customer_id    birth_day    age age_rng
CS021313000114 1981-04-29    37 30-39  
CS037613000071 1952-04-01    66 60+    
CS031415000172 1976-10-04    42 40-49  
CS028811000001 1933-03-27    86 60+    
CS001215000145 1995-03-29    24 20-29  
CS020401000016 1974-09-15    44 40-49  
CS015414000103 1977-08-09    41 40-49  
CS029403000008 1973-08-17    45 40-49  
CS015804000004 1931-05-02    87 60+    
CS033513000180 1962-07-11    56 50-59

実行環境の構築について

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

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

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

利用するデータ

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

df_customer の顧客ID (customer_id)、生年月日 (birth_day)、年齢 (age)

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

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

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

解答例と実行結果

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

# A tibble: 10 × 4
   customer_id    birth_day    age age_rng
   <chr>          <date>     <int> <fct>  
CS021313000114 1981-04-29    37 30-39  
CS037613000071 1952-04-01    66 60+    
CS031415000172 1976-10-04    42 40-49  
CS028811000001 1933-03-27    86 60+    
CS001215000145 1995-03-29    24 20-29  
CS020401000016 1974-09-15    44 40-49  
CS015414000103 1977-08-09    41 40-49  
CS029403000008 1973-08-17    45 40-49  
CS015804000004 1931-05-02    87 60+    
CS033513000180 1962-07-11    56 50-59  

解説

select() で、customer_id、birth_day、age の3つのカラムだけを抽出します。
mutate() を用いて、新たな列 age_rng を作成します。この列は年齢 (age) をもとに年齢区分 (年代別) に変換したものです。
ここでは、年齢区分の生成には epikit::age_categories() を使用しています。この関数は、指定した範囲で年齢をカテゴリ化することができ、今回の例では 0～60歳までは10歳刻み、60歳以上は「60+」というカテゴリでまとめています。例えば、年齢37歳の顧客は 30-39 に、66歳の顧客は 60+ に分類されます。
head(10) で、結果の先頭10行を表示します。

R (データベース操作)

利用するデータ

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

db_customer の顧客ID (customer_id)、生年月日 (birth_day)、年齢 (age)

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

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

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

解答例と実行結果

db_result = db_customer %>% 
  select(customer_id, birth_day, age) %>% 
  mutate(
    age_rng = 
      case_when(
        !is.na(age) ~ 
        pmin((floor(age / 10) * 10), 60) %>% as.integer()
      )
  ) %>% 
  head(10)

db_result %>% collect()

# A tibble: 10 × 4
   customer_id    birth_day    age age_rng
   <chr>          <date>     <int>   <int>
CS021313000114 1981-04-29    37      30
CS037613000071 1952-04-01    66      60
CS031415000172 1976-10-04    42      40
CS028811000001 1933-03-27    86      60
CS001215000145 1995-03-29    24      20
CS020401000016 1974-09-15    44      40
CS015414000103 1977-08-09    41      40
CS029403000008 1973-08-17    45      40
CS015804000004 1931-05-02    87      60
CS033513000180 1962-07-11    56      50

解説

select() で、customer_id、birth_day、age の3カラムのみを抽出します。
mutate() を使い、age_rng という新しい列を作成します。この列は年齢 (age) を10歳ごとの階級に変換したものです。
case_when() で age が欠損値 (NA) でない場合にのみ処理を行います。具体的な処理は以下の通りです。
- floor(age / 10) * 10 により、年齢を10の位で切り捨てます。(例：37歳→30)
- pmin(..., 60) により上限を60に設定します。これにより、60歳以上はすべて60にまとめられます。
head(10) で、結果の先頭10行を表示します。
db_result %>% collect()
データベース操作の結果を R のデータフレーム (tibble) として取得します。

SQL

利用するデータ

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

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

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

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

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

自動生成された SQL クエリ

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

db_result %>% show_query()

SQL

SELECT
  customer_id,
  birth_day,
  age,
  CASE
    WHEN (NOT((age IS NULL))) THEN (CAST(LEAST((FLOOR(age / 10.0) * 10.0), 60.0) AS INTEGER))
  END AS age_rng
FROM customer
LIMIT 10

解答例

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

SQL

SELECT
  customer_id,
  birth_day,
  age,
  CASE 
    WHEN age IS NOT NULL THEN 
      CAST(LEAST((FLOOR(age / 10.0) * 10.0), 60.0) AS INTEGER)
  END AS age_rng
FROM 
  customer
LIMIT 10

解説

SELECT 文 で customer_id、birth_day、age に加え、新たに計算した age_rng を取得します。
CASE WHEN 句を使用して、age が NULL でない場合のみ年齢区分を計算します。これにより NULL 値がそのまま残るようにしています。
年齢区分の計算には以下のロジックを使用します。
- FLOOR(age / 10.0) * 10.0 により、年齢を10歳単位に切り捨てます。(例：37 → 30)
- LEAST(..., 60.0) により、最大値を60に制限します。これにより、年齢が60歳以上はすべて 60 にまとめられます。
- CAST(... AS INTEGER) を使い、小数点以下を除いた整数値に変換します。
LIMIT 10 により、結果の上位10件のみを表示します。

実行結果の確認

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

query = sql("
SELECT
  customer_id,
  birth_day,
  age,
  CASE 
    WHEN age IS NOT NULL THEN 
      CAST(LEAST((FLOOR(age / 10.0) * 10.0), 60.0) AS INTEGER)
  END AS age_rng
FROM 
  customer
LIMIT 10
"
)
query %>% db_get_query(con)

# A tibble: 10 × 4
   customer_id    birth_day    age age_rng
   <chr>          <date>     <int>   <int>
CS021313000114 1981-04-29    37      30
CS037613000071 1952-04-01    66      60
CS031415000172 1976-10-04    42      40
CS028811000001 1933-03-27    86      60
CS001215000145 1995-03-29    24      20
CS020401000016 1974-09-15    44      40
CS015414000103 1977-08-09    41      40
CS029403000008 1973-08-17    45      40
CS015804000004 1931-05-02    87      60
CS033513000180 1962-07-11    56      50

リンク