職場ではExcelでデータ集計しているという職場も多いと思います。

その中でタブに1回目,2回目,,,や1月,2月,,,というように同じデータ形式を複数シートで管理する場合もあるかもしれません。

同じ形式で複数シートにまたがるデータをRで一度に読み込む方法を紹介します。

今回はfim.xlsx という架空のファイルを準備していますが、記事を見ながらご自身でExcelファイルを作ってみても大丈夫です。


(注意)
今回の記事はr-wakalang」でのアドバイスを参考にさせていただきました。
自分も別の方法でしていたのですが、あまりにもスマートな方法なので今回の方法に切り替えました。





1.前提

まずこの方法ができるためにはいくつか前提が必要です。

・全てのシートが同じ形式

シート毎に列名やいろいろなものがバラバラでは読み込めません。
フォーマットはちゃんと決めましょう。もしくはシートを複数選択しまとめて処理をしましょう。
cntr(macではcommand)を押しながらクリックするとシートを複数選択→一括処理ができます。

・セル結合が無い

Excelでセル結合をすると見栄えは良くなったように感じますが、集計を行う時はExcelでもRでも非常に困る場合が多いです!!!!!

・列のタイトルが複数行にまたいでいる

例えば病院で下位項目があるテストを記録すると列名が複数行にまたぐ場合があります。

そしてこのような場合、上の行はだいたいセル結合されています・・・


スクリーンショット 2019-08-20 22.47.19



2.Excelでの対策

ただ上司にセル結合+列名を2行にしないと減給だ!!!と脅される場合もあるかもしれません。

自分ならもう一列Rで列名読み込むための行を作るという回避策を用意します。
そのときに役に立つのがExcelの&です。

ポイントは以下の3つです。
①集計に使うシートを全て選択し一括処理の状態にする
② 実際のデータ入力する列の1つ上の行に挿入する(ここでは3行目)

スクリーンショット 2019-08-20 22.49.52



③ A,B,C列は実質1行なので を使う。
④ D列以降は複数行にまたがるため でつなげる
⑤ D1は絶対参照($)をつけるとコピーで失敗しない

(下の図ではわかりやすいように数式を表示しています)

スクリーンショット 2019-08-21 0.18.00


実際の見え方はこうなります。
スクリーンショット 2019-08-20 23.05.24


⑥D3をコピー→右の空いた所全てを選択し貼り付け

⑤誰かに邪魔されないように3行目を非表示にすれば完成!


スクリーンショット 2019-08-20 23.06.38

スクリーンショット 2019-08-20 23.07.25




3.Excelの&について

D1&D2とするとD1の値とD2の値を文字列としてくっつけてくれます

D1はFIMD2は食事なので、=D1&D2FIM食事となります。

今回はFIMと食事の間に_をはさみます。
(- * , . )などの記号や空白はRで読み込んだ際に不都合が出ることがあります。
そのため間に挟む記号は _ (アンダーバー)をおすすめします。

_のような文字列を&で繋げる場合は"(ダブルクォーテーション)ではさみます。

よって=D1&"_"&D2 は FIM_食事となります。


4.Excelの相対参照と絶対参照について

D3にある=D1&"_"&D2をコピーしてE3に貼り付けるとE1&"_"&E2となります。
他にもD3を選択→セル右下のちっちゃい■をクリックし右にビーーーーーーっと伸ばすと一気にコピーできます。
Excelはコピーした位置関係を覚えてくれるのでこういった処理は非常に便利です。これを相対参照と言います。

しかしこの方法を使うと不都合が出ます。本来E3セルにはD1&"_"&E2と1行目は動かずに2行目を動かしたいところです。
この動かさないを指定するのが$(絶対参照)です。

$D1:左右にコピペしてもDの値は変わらない。上下にコピペすると1の値は変わる
D$1:左右にコピペするとDの値が変わる。上下にコピペしても1の値は変わらない
$D$1:上下左右にコピペしてもD1の値は変わらない。

今回は横にコピペしてもずれてほしくないので、D3の計算式ではD1ではなく$D1もしくは$D$1とすれば上手くいきます。するとE3〜は一気にコピペ可能です。


5.Rへの読み込み

前置きが長くなりましたがいよいよRに取り込みます。

・プロジェクトを作成し、Excelデータも同じフォルダに置く

実際に分析を行う時は仕事1つ1つでプロジェクトを作ると管理が便利です。
プロジェクトについては【1-6】Rstudioのプロジェクトについて解説しますで紹介しています。


・パッケージを読み込む

パッケージに関しては【1-7】Rで使うパッケージのインストールについて紹介します。をご参照ください。

Excelを読み込むにはいくつかあるのですが今回はreadxlパッケージとpurrrパッケージを使います。
purrrパッケージは2章で使ってきたtidyverseパッケージに含まれています。


#tidyverseパッケージをインストールしていなければインストール。
install.packages("
readxl
")
install.packages("tidyverse")

#パッケージをインストールしている方は以下でもOK
library(
readxl)
library(tidyverse)


・実際に読み込む

file <- "fim.xlsx"
sheets <- excel_sheets(file)
dat <- 
  sheets %>% 
  map_dfr( ~ read_excel(file, skip = 2, sheet = .x) %>% 
             mutate(sheet = .x)) 
これで全てのタブを読み込むことができます。



(以下補足)
6.何をしているのか?

1つずつ説明していきます。

ファイル名を指定する

まずfileという変数(別に名前はfileでなくても良い)にExcelのファイルを指定します。
そうすれば後のread_excel関数で楽ができます。

シートの名前リストを作る

Excelから複数シートを読み込むにはシートのリストが必要です。

readxlパッケージのexcel_sheet関数がまさにそれです。

sheets <- excel_sheets(file)
スクリーンショット 2019-08-21 19.47.36



これでsheetsという変数にタブの名前が入りました。

今回はこのままで問題ありませんが、Excelの中には「基本情報」や「選択項目」など必要ないタブもあるかもしれません。
その時はsheetsから間引きます。間引く時は【1-12】Rで特定の条件にあう要素を抜き出す方法で紹介した [ ] が使えます。

シートは名前だけでなく、左から1,2,,,,と自動的に番号が振り分けられています。
いらないシート番号がわかっている時はいらない番号だけ指定すればいいです。
(もちろんいる番号だけ指定してもいいです)

sheets <- excel_sheets(file)

sheets <- sheets[-2]
スクリーンショット 2019-08-21 19.52.58


シートの数が多すぎてシート番号がもはや分からない時は  %in% でシート名を指定することができます。
sheets <- excel_sheets(file) 
sheets <- sheets[!sheets %in% "2回目"]
スクリーンショット 2019-08-21 19.53.16


1つだけExcelを読み込む時のread_excel

1つだけExcelを読み込む関数はいくつかあるのですが、今回はread_excel関数を使います。
read_excel関数はxls,xlsxどちらも読み込むことができます。

read_excel(file, skip = 2, sheet = "1回目") 
基本の形は read_excel("Excelファイル", sheet = "シート名") となります。
そしてskip=を使うと最初の数行を飛ばして読み込むことができ、今までの対策が活きてきます。
skip=2で1,2行目をskipし3行目を列名、4行目以降をデータとして読み込むことができます。

スクリーンショット 2019-08-22 0.27.55



繰り返し処理でdata.frameにデータを追加するmap_dfr関数

map_dfr関数はExcelの複数のシートやフォルダの中にあるcsvファイルをまとめて読み込むのに便利です。
ちなみにmap(繰り返し処理)、df(データフレーム)、r(縦につなげる)でmap_dfrです。
横につなげる時はmap_dfc関数ですがここでは目的に合致しません。


最初に.xにあたる繰り返すためのリストを用意して%>%でつなぎます。
ここでは"1回目"→"2回目"→"3回目"としたいわけです。そこで先程のsheets <- excel_sheets(file)でそれを作りました。


そして先程紹介したread_excel関数を使います。最初に ~(チルダ)を入れることがポイントです。


加えて足した後にこのデータがどのシートかわかる(集計する)ためにmutate関数をつかってsheet(別の名前でもいい)という列名を追加します。ここでも.xを使うことで"1回目", "2回目", "3回目"を追加してくれます。

スクリーンショット 2019-08-22 0.22.50



7.まとめ

今回はExcelで複数のシートを読み込む方法を紹介しました。
今までExcel使っていたけどRも使ってみたいなという方がいましたら練習と実用を兼ねている課題だと思いますのでぜひ挑戦してみてください。


8.参考にさせていただいたサイト

purrrを使ったExcelの読み込みはr-wakalangさんの質問を参考にさせていただきました。
それまではforを使っていたのですが、map_dfrはとても便利でした!
r-wakalangはRを使っている方は絶対におすすめです!