【ETF分析 ② 前処理編】R Nested Data Frameを活用してCSV Fileの前処理を行う

今回は

• 【ETF分析 ①データ収集編】Python Selenium+Beautifulsoup4を使ってETF構成銘柄を取得する

の続きデータ前処理編になります。
k-bind.hatenablog.com

Rのtidyverseが提供するNested Data Frameという構造を活用して階層的な処理でCSVファイルのクレンジングを行います。

CSVファイルを眺めて問題点を確認する

まず、こちらは前回スクレイピングしたETFデータ一覧です。

ダウンロードしたETF一覧

全て月次断面のファイルなのでそれぞれ上場している期間に応じて×月数分のファイルが存在します。
それぞれのCSVファイルを開いて見てみると問題点がハッキリとします。
新興国株式ETFと日本株最小分散ETFのCSVファイルを開いてみます。
不要なデータは灰色線で囲み、必要なデータは青線で囲んでいます。
（今回は月次断面のポートフォリオを必要としています）

上の画像の通り、ダウンロードしたETFの銘柄によってファイルフォーマットがまちまちです。取得または提供されたファイルフォーマットがファイル毎にバラバラなのはデータ分析においてはよくある話ですね。
一つ一つファイルフォーマットを確認してデータを加工してもよいのですが銘柄は12種類もあるうえこれ以上増えたりフォーマットが変わったりしたら目で追っていくのは大変です。
そこである程度フォーマットが変わったところで自動的に必要なデータが抽出できるような仕組みを考えたいと思います。

必要なデータのみ取り出すには

ファイルのフォーマットはそれぞれ違えど、共通点があります。
それは記載されている内容がデータサマリーと詳細に分類出来るという事です。
ここでいうデータサマリーは基準日や純資産総額のデータ、詳細は保有銘柄リストに該当します。
データ個数は詳細＞＞＞サマリーと概ねなるので、データの塊毎に個数（面積）を取得し面積が最も大きいデータ集合体を取得することが出来ればつまり保有銘柄一覧を取得で生きると概ね考えられます。

従ってこれからの作業の方向性は下記の通りになります。
１：読み込んだCSVファイルから必要な構成銘柄一覧の情報がどこに存在するか特定する関数を定義
２：月次CSVデータを１で定義した関数で加工処理
３：加工済みデータをマージしてETF銘柄ごとのCSVファイルとして保存する

ざっくりとしたNested Data Frameの説明

今回はRのNested Data Frameを活用して作業していきますのでざっくりとした説明をしたいと思います。Nested Data Frameは今回も含め実に多様な場面で重宝する手段になります。

こちらの図は簡略化したイメージ図になります（厳密的に正確ではないのはご了承ください）。Nested Data Frameは一つのデータフレームの中に階層構造を持たすことが出来ます。
今回は読み込んだCSVのデータ（データフレーム）を階層構造としてデータフレーム内に持たせます。なおネストさせる構造体はデータフレームだけではなく、行列やモデルオブジェクトなども階層的に持たすことが出来ます。
階層構造のデータフレームが持てることの恩恵は色々あるとは思いますが一つはやはりdplyrとして一貫した操作性が保てることだと個人的に思います。
今回は階層化された読み込み済みのCSVデータに対して、必要なデータのみ抽出するオリジナル関数を適応しますが、それ以外にも自身で設計したモデルを適応したりかなり自由かつ柔軟に一つのデータフレーム内で取り扱うことが出来ます。素晴らしいですね！
これから記述するコードのプロセス図は以下の通りになります。

Rコード

ではこれらのプロセスをRで記述していきます。
ライブラリの読み込み（必要に応じてインストールしてください）

library(tidyverse)

前回ダウンロードしたCSVファイル一覧からファイル名をベクトルとして取得する。

csv_path <- "C:/(CSVファイルが保存されているパス)/"
# ファイル一覧
csv_files <- list.files(csv_path, pattern = "*csv")

ファイル一覧のベクトルからETF種類を文字列操作で取り出す

# ETF種類一覧
cate_etf <- csv_files %>% 
  str_sub(., 10, 1000) %>%  # 左文字10文字目から最後まで取得
  str_replace_all(".csv", "") # .csvを取り除く

ここで今回の肝の一つである、読み込んだデータから必要な部分を抽出し抽出後のデータフレームを返す関数の定義です。
やっていることは最初の方に書いた通り、データの塊に分類してより面積（データ個数）が大きな塊の位置情報からデータを抽出する関数です。

######################################################################
# csvを加工して切り出す関数
# 引数:読み込んだCSVのデータ
# 戻り値：加工後のデータ
######################################################################
func_progcsv <-
  function(df){
    # vectorのうちNAではない要素の数を返す
    func_vnum <- function(vec){sum(!is.na(vec))}
    
    # 行ごとに値の含まれている要素を返す
    max_collength <-
      df %>% 
      apply(., 1, func_vnum)
    
    # データのブロックを作成する
    for(i in 1:length(max_collength)){
      if(i == 1){
        bulk_df <- 
          data.frame(matrix(rep(NA, 3),
                            nrow=1))[0,]
        pre_f <- 0
        k <- 0
      }
      f <- max_collength[i]
      if(f != pre_f){
        k = k + 1
      }
      bulk_df <- rbind(bulk_df, c(pre_f, f, k))
      pre_f <- f
    }
    
    # データのブロックからデータ個数が最大となるブロックの情報を確認する
    bulkdf_summary <-
      bulk_df %>% 
      as.tibble() %>% 
      setNames(c("pre", "post", "no")) %>% 
      mutate_at(vars(no), funs(as.factor)) %>% 
      group_by(no) %>% 
      summarise(colnum = max(post),
                datacount = sum(post)) %>% 
      ungroup() %>% 
      filter(datacount == max(datacount))
    
    # 必要な列数と行のインデックスを取り出す
    ret_colnum <- bulkdf_summary$colnum
    ret_no <- bulkdf_summary$no
    ret_index <- which(bulk_df[,3] == ret_no)  
    
    # 元のデータから切り出す
    prog_df <-
      df %>% 
      slice(ret_index) %>% 
      select(rep(1:ret_colnum)) %>% 
      setNames(.[1,]) %>% 
      slice(-1)
    
    return(prog_df)
  }
######################################################################

そしてここが今回のメインテーマであるNested Data Frameを使ったデータ処理です。
1：csvデータをすべて読み込みNested Data Frameを作成
2：Nested Data Frameに先ほど定義した加工用の関数を与えてCSVデータを処理する
3：ETF銘柄別にCSVファイルを出力するためにNested Data FrameをETF銘柄別にsplitする
ここで一点だけご注意いただきたいのは、read_csvは引数をデフォルト設定した場合、最初に読み込まれる5行目で列名が決定されるそうです。
今回のCSVファイルの場合も、デフォルトで読み込むと1～2列分しか読み込まれないことが殆どであるため、データの漏れがないように予め26列程余分なデータまで読み込んでいます。

df_fileinfo <-
  # ①：csvデータをすべて読み込みNested Data Frameを作成
  data.frame(yymm = str_sub(csv_files, 1, 8),
             files = str_c(csv_path, csv_files),
             category = cate_etf) %>% 
  as.tibble() %>% 
  mutate_all(., funs(as.character)) %>% 
  mutate(csvdf = map(.$files,
                     ~read_csv(.,
                               col_names = letters[1:26]))) %>%
  # ②：Nested Data Frameにmap関数を使い先ほど定義した加工用の関数を与えてCSVデータを処理する
  mutate(csvdf = map(.$csvdf, ~func_progcsv(.))) %>% 
  # ③：マージ用にNested Data FrameをETF銘柄別にsplitする
  select(-files) %>% split(.$category)

この時点で既に加工済みのデータフレームが得られたので最後にNested Data Frameをunnest()関数を使うことでETF銘柄別にデータをマージ。
最後にそれぞれCSV毎に出力して終了となります。

for(i in 1:length(df_fileinfo)){
  print(i)
  output_df <- unnest(df_fileinfo[[i]])
  output_filename <- names(df_fileinfo[i])
  write.table(output_df,
              str_c("./csv/", output_filename, ".csv"),
              row.names = F, sep = ",")
}

無事CSVファイルが出力され中身も整ったデータが得られました。

参考にさせていただいたサイトです。
この場で感謝申し上げます。ありがとうございました。
qiita.com
suryu.me

今回は以上になります。ここまで読んでくださりありがとうございます。

ようやく分析の段階に近づいてきましたね。
次回は集計、可視化、分析と考察をしてETF分析編は一旦完結させる予定です。
内容は簡単なものになると思います。