【R言語】闇Excel対処法

闇Excelって知ってますか。

例えば以下のように表頭が複数行ある、セル結合がある場合など、人間にとっては見やすいが、機械にとっては扱いづらいExcelのことを指します。

よく会社でも

Aさん「〇〇分析したいんですけど・・」
私「じゃあデータ送ってもらってもいいですかね？」
Aさん「このExcelにデータ入っているので送ります～」
私「（闇Excel参上）・・。XX日頃にお戻ししますね😊（くそがっ・・）」

となります。もう松田優作状態です。
こうしたことが日常茶飯事です。

闇ExcelにR言語で対応しよう

こうした闇Excelに対応可能なパッケージがtidyxlとunpivotrになります。
まずはパッケージをインストールして読み込みます。

＃ パッケージのインストール
remotes::install_github("nacnudus/tidyxl")
remotes::install_github("nacnudus/unpivotr")

# パッケージの読み込み
library(pacman)
p_load(tidyxl, unpivotr, tidyverse)

# 他にも必要なパッケージを読み込む
devtools::install_github("nacnudus/smungs")
p_load(smungs)

メインで使うのはtidyxl::xlsx_cellsとunpivotr::beheadです。前者は「すべてのセルの情報（空白、結合、色付きかどうかなど、、、etc）を1つデータフレームとして読み込む」関数になり、後者は「ヘッダーの位置を指定すると、そのヘッダーにで構成されるセルを自動で紐付けてくれる」関数です。

具体的にどのように動くか試していきましょう。まずはxlsx_cellsです。

# 上に画像で貼ったデータのセル情報をxlsx_cellsで読み取る
cells = xlsx_cells(smungs::ozmarriage)

少し小さくて見づらくて恐縮ですが、以下のようにセルの情報をデータフレームにして読み込んでくれます。

続いて、beheadです。3行に分かれている表頭や表側をbeheadで加工しながらtidyなデータフレームにまとめていきたいと思います。

table1 = cells |> 
  dplyr::filter(sheet == "Table 1", row >= 5L, !is_blank) |> 
  mutate(character = str_trim(character)) |> 
  behead(direction = "up-left", name = "population") |> 
  behead(direction = "up-left", name = "response") |> 
  behead(direction = "up", name = "unit") |> 
  behead(direction = "left", name = "state") |> 
  arrange(row, col) |> 
  select(row, data_type, numeric, state, population, response, unit) |> 
  spatter(unit) |> 
  select(-row)

すると以下のようなデータフレームになります。ここまでいったら加工は簡単ですね。

beheadの中のdirectionの設定は、元のExcelファイルを見ながらでないと分からないので、元のExcelを見ながら適切なdirectionを指定してください。
なおdirectionに設定できる値については、以下のコードを打ち込めばHelpに表示されるので適宜使ってみてください。

# directionのhelp
?unpivotr::direction

?unpivotr::directionで表示されるもの（抜粋）

tidyxl/unpivotrの詳細解説サイト

英語の解説ですが、下記サイトに具体的な例も用いながら詳細に説明しているので、こちらも適宜参照下さい。

Spreadsheet Munging Strategies

副業やってます

副業としてデータ分析やR言語を用いた業務効率化・自動化対応などをやっていますので、もしご興味ある方は以下のサイトからお問い合わせください。

リサーチ、データ分析、統計 | sankac studio

拙い記事でしたが、ご一読ありがとうございました！