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

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

日付型を文字列に変換

目次

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

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

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

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

演習問題

難易度
顧客データ（customer）の生年月日（birth_day）は日付型でデータを保有している。これをYYYYMMDD形式の文字列に変換し、顧客ID（customer_id）とともに10件表示せよ。

出力イメージ:

   customer_id    birth_day
 1 CS021313000114 19810429 
 2 CS037613000071 19520401 
 3 CS031415000172 19761004 
 4 CS028811000001 19330327 
 5 CS001215000145 19950329 
 6 CS020401000016 19740915 
 7 CS015414000103 19770809 
 8 CS029403000008 19730817 
 9 CS015804000004 19310502 
10 CS033513000180 19620711

実行環境の構築について

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

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

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

利用するデータ

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

  • df_customer の顧客ID (customer_id) と⽣年⽉⽇ (birth_day)

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

R
df_customer %>% select(customer_id, birth_day)
# A tibble: 21,971 × 2
   customer_id    birth_day 
   <chr>          <date>    
 1 CS021313000114 1981-04-29
 2 CS037613000071 1952-04-01
 3 CS031415000172 1976-10-04
 4 CS028811000001 1933-03-27
 5 CS001215000145 1995-03-29
 6 CS020401000016 1974-09-15
 7 CS015414000103 1977-08-09
 ...

解答例と実行結果

R
df_customer %>% 
  mutate(birth_day = strftime(birth_day, "%Y%m%d")) %>% 
  select(customer_id, birth_day) %>% 
  head(10)
# A tibble: 10 × 2
   customer_id    birth_day
   <chr>          <chr>    
 1 CS021313000114 19810429 
 2 CS037613000071 19520401 
 3 CS031415000172 19761004 
 4 CS028811000001 19330327 
 5 CS001215000145 19950329 
 6 CS020401000016 19740915 
 7 CS015414000103 19770809 
 8 CS029403000008 19730817 
 9 CS015804000004 19310502 
10 CS033513000180 19620711

解説

strftime() は、日付データを指定したフォーマットの文字列に変換します。

  • birth_day = strftime(birth_day, "%Y%m%d")
    birth_day (Date型) を YYYYMMDD の文字列形式に変換します。

  • select(customer_id, birth_day)
    customer_id と変換後の birth_day のみを抽出します。

  • head(10)
    先頭の 10 行を取得します。

R (データベース操作)

解答例と実行結果

データベースでの操作も同様の流れです。
データフレーム df_customer をテーブル参照 db_customer に置き換えることで、同様の操作を実行できます。

R
db_result = db_customer %>% 
  mutate(birth_day = STRFTIME(birth_day, "%Y%m%d")) %>% 
  select(customer_id, birth_day) %>% 
  head(10)

db_result %>% collect()
# A tibble: 10 × 2
   customer_id    birth_day
   <chr>          <chr>    
 1 CS021313000114 19810429 
 2 CS037613000071 19520401 
 3 CS031415000172 19761004 
 4 CS028811000001 19330327 
 5 CS001215000145 19950329 
 6 CS020401000016 19740915 
 7 CS015414000103 19770809 
 8 CS029403000008 19730817 
 9 CS015804000004 19310502 
10 CS033513000180 19620711

解説

  • strftime() は dbplyr ではサポートされていないため、代わりに SQL の strftime 関数を使用します。

  • strftime は慣例に従い大文字で記述します。
    (詳しくは dbplyr が認識できない式 を参照してください。)

  • db_result %>% collect()
    データベース操作の結果を R のデータフレーム (tibble) として取得します。

SQL

利用するデータ

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

  • customer テーブルの顧客ID (customer_id) と⽣年⽉⽇ (birth_day)

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

R
"SELECT customer_id, birth_day FROM customer" %>% db_get_query(con)
# A tibble: 21,971 × 2
  customer_id    birth_day 
  <chr>          <date>    
1 CS021313000114 1981-04-29
2 CS037613000071 1952-04-01
3 CS031415000172 1976-10-04
4 CS028811000001 1933-03-27
5 CS001215000145 1995-03-29
6 CS020401000016 1974-09-15
7 CS015414000103 1977-08-09
...

自動生成された SQL クエリ

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

R
db_result %>% show_query()
SQL
SELECT customer_id, STRFTIME(birth_day, '%Y%m%d') AS birth_day
FROM customer
LIMIT 10

解答例

このクエリをフォーマットすると、次のようになります。

SQL
SELECT 
  customer_id, 
  STRFTIME(birth_day, '%Y%m%d') AS birth_day
FROM 
  customer
LIMIT 10

解説

STRFTIME() は、日付データを指定したフォーマットの文字列に変換します。

  • STRFTIME(birth_day, '%Y%m%d')
    birth_day (DATE型) を YYYYMMDD の文字列形式に変換します。

  • SELECT customer_id, birth_day
    customer_id と変換後の birth_day のみを取得します。

  • LIMIT 10
    結果から 10 件 を取得します。

実行結果の確認

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

R
query = sql("
SELECT 
  customer_id, 
  STRFTIME(birth_day, '%Y%m%d') AS birth_day
FROM 
  customer
LIMIT 10
"
)

query %>% db_get_query(con)
# A tibble: 10 × 2
   customer_id    birth_day
   <chr>          <chr>    
 1 CS021313000114 19810429 
 2 CS037613000071 19520401 
 3 CS031415000172 19761004 
 4 CS028811000001 19330327 
 5 CS001215000145 19950329 
 6 CS020401000016 19740915 
 7 CS015414000103 19770809 
 8 CS029403000008 19730817 
 9 CS015804000004 19310502 
10 CS033513000180 19620711