タグ:Excel

RStudioではまずExcelやcsvファイル等を読み込む必要がありますが、そもそもExcelではRに読み込みやすい形とそうでない形があります。Excelの見栄え的には良くても実際にRで読み込もうとすると大変な手間になることもあります。

今回はRで読み込みやすいExcelのポイントを紹介します。



1.上や左に空白はないか?

スクリーンショット 2019-10-20 20.07.06

RでなにもせずにExcelファイルを読み込むとA1を始めとしてデータを読み込みます。よくExcelでかけ線を設定している場合B2から表をつくることがありますが、Rで読み込むときには一手間必要です。



2.一番下に合計の行がないか?

加えてもし一番下に合計や平均の列があったらそれも1データとして読み込んでしまうので注意が必要です。
Excelは集計のために使い分析をRで行う場合はそもそも無いほうが安全です。
もしExcelでも集計を行いたい場合は集計を別のタブで行うかピボットテーブルを使う方法があります。



3.タイトル行が2行に渡っていないか?

タイトル行が2行に渡っているとRではうまく読み込んでくれません。
そしてそういう場合はセル結合されていることも多いです。
こういった場合も対処が必要です。

タイトルが2行に渡った時の対処法に関しては以下の記事で説明しています。




4.無駄な空間を作らない
スクリーンショット 2019-11-24 6.59.39
上記の図のように空欄だとパソコンは読み込んでくれません。
列名をちゃんと書き、データは空欄にせず入力しましょう。

5.桁数に使う , を入れていないか

10,000などカンマ含む場合、Rで読み込むと数字ではなく文字として認識されます。
「どうしても , はつけないとダメ」と上司に言われてもRで , を取る方法はあります(parse_number関数)


6.マイナスの値を▲にしていないか?

-100を▲100にしていたらマイナスに直す必要があります。
文字の置換(ここでは▲ → − )にするにはstr_replaceなどの関数がありますが、手間を考えると−の方が手間が省けます。


7.和暦か西暦か?

和暦だと古いExcelではいつまでたっても令和になりません。
計算や列の順番を考えると西暦のほうが後々対処しやすいです。
加えて2001年を01年としないよう注意しましょう。


8.tidyなデータか?

データ分析を行う上で必要な考え方にtidy(タイディー:整然データ)なデータかどうかという考え方があります。

tidyに関しては以下の記事が具体例も交えながらわかりやすく説明されています。




これからデータ収集を考えている方は参考になると思います。


9.被験者間要因は左側に、被験者内要因は右側に並べる
先程tidyなデータの話がありましたが、実際にデータを取りExcelに打ち込む時に覚えておくと後で便利になる考え方があります。それは被験者間要因被験者内要因です。
スクリーンショット 2019-11-24 6.33.40
被験者間要因はいわゆる対応のないデータのことで被験者全体をA法、B法の2つに割り振るといった方法です。なので同じ人がA法とB法を行うことはありません。

被験者内要因はいわゆる対応のあるデータのことで同じ人が繰り返し測定します

加えて被験者間要因は列の左側に、被験者内要因は列の右側に並べると後々分析しやすくなります。
また被験者間要因は縦に、被験者内要因は横につなげるとEZRで分析を行うには都合がいいです。
もし横に列が長くなりすぎて困る場合は全部縦にしても大丈夫です。あとで横に変換できます。
スクリーンショット 2019-11-24 6.35.12
この方法は特に分散分析を行う場合に便利になります。



10.まとめ

予めExcelのデータをRで読み込みやすい形にするとデータの前処理で心が折れにくくなりますので参考にしてみてください!


第4章は統計を扱います。

今回「シロート統計学」のハルさんとコラボレーションすることとなりました。

ハルさん、ありがとうございます!

シロート統計学はEZRを使った統計分析をわかりやすく解説されています。



第4章はシロート統計学で使われていたEZRをRで行うとどうなるのか?といった視点で進めていきます。

今回使うデータもハルさんのサイトと同じものを使わせでいただく事になりました。それぞれ見比べることで参考にしてみてください!



今回は4章を進めるにあたって抑えておきたいRの基本的な操作について復習します。

1.プロジェクトを作る

第4章では色々なデータを扱うため、フォルダやRのファイルが煩雑になりやすいです。そこで今回はプロジェクトを使って管理します。

RStudioでプロジェクトを作成する方法は第1章で紹介しています。




今回は「R練習」というプロジェクトを作りました。スクリーンショット 2019-10-20 7.54.14


更にデータを入れるフォルダを作ります。分析に使うExcelファイルはデスクトップやマイドキュメントに置かずにここに置くようにします!
スクリーンショット 2019-10-20 23.07.53

そしてRのコードを書くスクリプトファイルを作ります。
今回は【4-1】で使うためのスクリプトファイルを作成しました。
スクリーンショット 2019-10-20 23.12.27

これで完成です!
スクリーンショット 2019-10-20 7.57.37




2.Excelのデータを読み込む

Excelを読み込む方法はいくつかありますが、ここではコードを書かない方法を紹介します。


まずデータが入ってるdataフォルダに進みRStudioのfilesタブからImport DatasetでExcelファイルをRで読み込みます。


スクリーンショット 2019-10-23 8.08.00

View Fileではエクセルファイルを直接開くことができます
(エクスプローラやFinderを使わなくていいので便利!)

ここではImport Datasetを選びます。

スクリーンショット 2019-10-23 8.11.09

Name:Rで使うときのデータの名前(自由に決められる)
sheet:Excelに複数タブあればどれを使うか指定できる
Range:データの読み込む範囲を指定できる

主には上記3つで読み込む範囲を指定します。
もし1枚目のタブでデータの読み込む範囲の左上がA1だったらNameの指定だけでも大丈夫です。

そしてこの方法の良いところは右にコードを自動で書いてくれることです。これを忘れずにスクリプトにコピーします。

そうすることで2回目以降は1秒もかからずに行えるのでかなりの時短になります!




まとめ

今後色々な検定やデモデータを使うことになります。EZRを使うとあまり意識されない部分ですが、プロジェクトファイルを作ることでデータの管理やスクリプトファイルの管理が進めやすくなります。

次回はt検定を紹介します。





職場では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を使っている方は絶対におすすめです!





↑このページのトップヘ