【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
tidyxl/unpivotrの詳細解説サイト
英語の解説ですが、下記サイトに具体的な例も用いながら詳細に説明しているので、こちらも適宜参照下さい。
副業やってます
副業としてデータ分析やR言語を用いた業務効率化・自動化対応などをやっていますので、もしご興味ある方は以下のサイトからお問い合わせください。
拙い記事でしたが、ご一読ありがとうございました!
よろしければサポートお願いします。