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





このサイトはRどころかプログラミング自体わからない状態からRの基本的な使い方がわかり、集計やグラフ作成、基本的な医療統計(もしかしたら機械学習の基礎)が使えることを目指しています。

自分はプログラミングの専門家でもありませんし、データサイエンティストとして生計を立てているわけでもありません。

病院に勤務している一理学療法士です。

ただ、日々業務をしながらデータ分析とかしてみたいんだけど、そもそも何からしたらいいのかわからない学会で統計が必要なんだけど全然わからないと感じている方は自分も含めいると思います。

そういった分析の専門家でない方に少しでも参考になる部分があり、もし興味が出てきたらこのサイトを卒業して、然るべき書籍や講義などステップアップしていただければ幸いです。



自分は2018年夏にRや統計を独学しようと思い、Edxで「HarvardX  Professional Certificate inData Science」を受講し修了しました。


スクリーンショット 2019-07-13 22.34.33


このサイトはそこで学んだことの自分への備忘録にもなります。

MITTI1210 on Twitter

Edxで「ハーバードX データサイエンスプロフェッショナルコース」受講中 ①Rの基礎 ②グラフの作り方 ③確率 ④推論とモデリング ⑤生産性ツール ⑥ラングリング(データ整理) ⑦線形回帰 ⑧機械学習 ⑨応用 全9回の講義。全て合格目指します。 https://t.co/SkfjzAH64w






サイトの内容はプログラム経験者や独学が得意な方にはくどく、必要な情報が全て載っていないとお叱りを受けると思います。ただ、プログラム未経験者だった自分が「このぐらい噛み砕いて説明してほしかった!」「まずはこんな順で教えてほしかった!」と感じたことを記事にしています


まずはイメージがしやすいこと、とりあえず動くことを念頭において記事を作成しています。情報を網羅するというより必要最低限の情報だけ伝え、必要になった場面で追加の情報が出てくるようにしたいと考えています。もしかしたら辞書的な使い方には向いていないかもしれません。多くの素晴らしいサイトや書籍がありますので、そちらもご参照ください。


そのため第1章から順に読むと徐々に知識が追加され、途中で復習できるよう構成しています。

途中で読んでわからないところは基本的に以前の記事に情報があるようにしているので、困った時は前の記事を確認してみてください!



第1章:Rの基本の「き」


Rって何?

【1-1】統計ソフトRで何ができるか説明してみる


Rのソフトについて

【1-2】RコマンダーやRStudioなどRのソフトにも色々あるので解説してみる

【1-3】Rのソフトはどれを使えばいいか?目的別チャートを作りました

【1-4】はじめてのRStudio。基本的な画面の説明をします

【1-5】Rコマンダーの基本的な画面の説明を行います

【1-6】Rstudioのプロジェクトについて解説します

【1-7】Rで使うパッケージのインストールについて紹介します。



Rの基本的な使い方について

【1-8】R の「変数」について説明します。

【1-9】医療統計をRで使うために必要な「データフレーム」にの考え方ついて

【1-11】Rで医療統計で必要なtable1を作るtableoneパッケージについて紹介します

【1-12】Rで特定の条件にあう要素を抜き出す方法

【演習1】R初心者が統計をかけるための前準備の流れを復習します


第2章:データを扱う

【2-1】Rのfor関数、apply関数を使ってまとめて標準偏差などの統計量を求める方法

【2-2】Rのmutate関数を使って列の追加や修正を行う

【演習2】データハンドリングの基礎を復習します



第3章:グラフを作る

【3-1】ExcelにはないRでグラフを作るメリットと特徴

【3-2】ggplot2でグラフを作る流れを説明します

【3-3】Rのggplot2で散布図を作るgeom_point関数

【3-4】Rのggplot2でヒストグラムを作るgeom_histogram関数

【3-5】Rのggplot2で密度曲線を作るgeom_density関数

【3-6】Rのggplot2で箱ひげ図を作るgeom_boxplot関数

【3-7】棒グラフの基本とRのggplot2で棒グラフを作るgeom_bar関数

【3-8】ggplot2で折れ線グラフを作るgeom_line関数


その他


自己紹介

昨年の振り返りと今年の目標


Qiita

ROC曲線とPR曲線の違いを混合行列と有病率から考えてみる

ggplot2で100%積み重ね棒グラフの真ん中に値を表示させたい

箱ひげ図の「ひげ」の上端と下端の値を知りたいので関数を自作した








第3章ではggplot2を使ったグラフの作り方を紹介しています。


【3-1】ExcelにはないRでグラフを作るメリットと特徴

【3-2】ggplot2でグラフを作る流れを説明します

【3-3】Rのggplot2で散布図を作るgeom_point関数

【3-4】Rのggplot2でヒストグラムを作るgeom_histogram関数

【3-5】Rのggplot2で密度曲線を作るgeom_density関数

【3-6】Rのggplot2で箱ひげ図を作るgeom_boxplot関数

【3-7】棒グラフの基本とRのggplot2で棒グラフを作るgeom_bar関数

今回は折れ線グラフを紹介します。

折れ線グラフは時系列の変化を追うのに適しています。

今回は実際のサイトから表を読み込んでグラフを作成します。


1.データの読み込み


今回は気象庁のサイトから東京の平均気温を読み込みます。

スクリーンショット 2019-08-19 0.19.12



webサイトからデータを抜き出すにはrvestパッケージを使います。
以下コードですが、まだ説明していないものが多いのでそのままコピー→実行すると表を読み込めます。

# まだrvestパッケージをインストールしたことなければインストール
install.packages("rvest")
# rvestを呼び出す library(rvest) # urlを指定する
url <- "https://www.data.jma.go.jp/obd/stats/etrn/view/monthly_s3.php?prec_no=44&block_no=47662&year=&month=&day=&view=" #url を読み込む。
h <- read_html(url)
# 読み込んだhtmlの中で"テーブル形式" のものを読み込む tab <- h %>% html_nodes("table")
# 読み込むと5つのtableがあることがわかる。1つずつ確認すると4つ目が目的のtableだった。tableをdata.frame型に変換する。 dat <- tab[[4]] %>% html_table
head(dat)
スクリーンショット 2019-08-19 0.34.09


2.データハンドリング

このデータをグラフにしたいのですが以下の数点が気になります。

スクリーンショット 2019-08-19 16.09.58


「年の値」の列がいらない

一番右にある「年の値」はグラフを作るには必要なさそうです。
いらない列を削除するにはselect関数を使います。





wideデータになっている

【2-5】Rでデータを集計するのに便利なlongデータとgather関数でも紹介しましたが、今回のデータはwideデータになっています。Excelではwideデータでグラフを作成しますが、Rでデータ集計、グラフを作るにはlongデータに変える必要があります。

wideデータをlongデータに変える関数はgather関数です。



今回もggplotパッケージが含まれているtidyverseパッケージを読み込みます。


気温の値に数値ではない値がある

データをよく見ると )] のように数値ではないものが含まれています。

他にもよくみられるのが1,000以上のような , も計算には邪魔です。

試しにstr関数でデータの概要を見てみます。

str(dat)

スクリーンショット 2019-08-19 16.13.21



int : 整数
num : 数値(整数以外も含む)
chr : 文字列

2月と6〜11月は文字列になっているので計算してくれません。
そのため不要な記号を消す必要があります。
まだ紹介していませんが、数値以外の全てを消すparse_number関数というものがあります。


行が多すぎる

str関数に書いてますが145行あります。このままでもグラフは作れますが、今回はデータを間引きます。
行を削除するのはslice関数を使います。

今回は1880,1885,,,,,,2015,2019年を抜き出します。

スクリーンショット 2019-08-19 0.34.09

よく見ると最初の1880年は6行目のようです。

6,11,16,,,,,と抜き出すにはseq関数を使います。

seq(6, 145, 5)
スクリーンショット 2019-08-19 16.40.38

これで6から145までを5間隔で抜き出すことができます。ちなみに2019年は145行目です。

実際のコード


select関数、gather関数、parse_number関数、slice関数、この後グラフを作るggplot2関数は全てtidyverseパッケージに含まれています。

#tidyverseパッケージをインストールしていなければインストール。

install.packages("tidyverse")

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

library(tidyverse)

もしRの基礎を勉強中であれば上のヒントでデータハンドリングを試してみてください。
いくつか方法はありますが、以下1例です。

dat_long <- dat %>% select(-年の値) %>% slice(seq(6, 145, 5), 145) %>% gather(key = 月, value = 気温, -年, factor_key = FALSE) %>% mutate(気温 = parse_number(気温)) %>%
ポイントは以下のとおりです。
・gather関数でlongデータにする前にselect関数,slice関数を使いました。
・gather関数でfactor_key = TRUEをすれば1月,2月,,,となり、入れないと10月,11月,12月,1月,,,となります。
・parse_number関数はgather関数の後で使いました(1列で済むため)。
・parse_number関数はmutate関数とセットで使う

スクリーンショット 2019-08-19 22.10.57

スクリーンショット 2019-08-19 22.15.21

1880年の1月,2月,,,と並んでいませんがグラフ作成に支障はありません。

これでデータの完成です。

3.折れ線グラフの基本的な作り方

折れ線グラフを作る時はgeom_line関数を使います。
aes関数の中でgroupを設定します。

ggplot() + theme_gray(base_family = "HiraKakuPro-W3") + geom_line(data = dat_long, aes(x = 月, y = 気温, group = 年))

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


もしgroupを指定しないと目的のグラフになりません。

ggplot() + theme_gray(base_family = "HiraKakuPro-W3") + geom_line(data = dat_long, aes(x = 月, y = 気温))
スクリーンショット 2019-08-20 11.48.11




4.group毎に色を変える

group毎に色を変えるにはcolor(もしくはcol)を指定します。

ggplot() + theme_gray(base_family = "HiraKakuPro-W3") + geom_line(data = dat_long, aes(x = 月, y = 気温, group = 年, color = 年))
スクリーンショット 2019-08-20 11.47.49

色の薄い最近の方が暑いのがわかります。

今回色がグラデーションになっています。これは年の型がint型(数値)になっているためです。
数値でなくfactorに変換するとグラデーションではなくなります。as.factor関数(もしくはfactor関数)を使います。

ggplot() + theme_gray(base_family = "HiraKakuPro-W3") + geom_line(data = dat_long, aes(x = 月, y = 気温, group = 年, color = as.factor(年)))
スクリーンショット 2019-08-20 11.49.29

数が多すぎてどの色が何年かわからなくなりました・・・
ただgroupの数が少ない時は便利です。


5.グラデーションの色を指定する
グラデーションの色を指定するにはscale_colour_gradientn関数を使います。
scale_color_gradientn(c("最小値の色", "最大値の色"))と指定します。

ggplot() + theme_gray(base_family = "HiraKakuPro-W3") + geom_line(data = dat_long, aes(x = 月, y = 気温, group = 年, color = 年)) + scale_colour_gradientn(colours = c("black", "red"))
スクリーンショット 2019-08-20 11.47.37



最初と最後だけでなく、途中の色も指定できます。
ggplot() + theme_gray(base_family = "HiraKakuPro-W3") + geom_line(data = dat_long, aes(x = 月, y = 気温, group = 年, color = 年)) + scale_colour_gradientn(colours = c("blue", "yellow", "red"))
スクリーンショット 2019-08-20 11.47.21


6.線の色を透過させる

線が重なっているところは新しい線が上書きしています。
第3章では何度も紹介していますが、色の透過はalphaで指定します。groupに関わらず一括で指定するのでaes関数の外で指定します。

ggplot() + theme_gray(base_family = "HiraKakuPro-W3") + geom_line(data = dat_long, aes(x = 月, y = 気温, group = 年, color = 年), alpha = 0.5) + scale_colour_gradientn(colours = c("blue", "yellow", "red"))
スクリーンショット 2019-08-20 11.47.09


ちなみに年を間引かなければこのようになります。

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


こうみても気温が少しずつ上昇している傾向が伺えます。


7.折れ線グラフに点をつける

もう少しデータを間引き、1880年と2018年のデータだけにします。
dat_long <- 
  dat %>% 
    select(-年の値) %>% 
    slice(6, 144) %>% 
    gather(key = 月, value = 気温, -年, factor_key = TRUE) %>% 
    mutate(気温 = parse_number(気温))


折れ線につける点は折れ線グラフに散布図を重ねます。
散布図は【3-3】Rのggplot2で散布図を作るgeom_point関数で紹介しました。
ggplot() + 
  theme_gray(base_family = "HiraKakuPro-W3") +
  geom_line(data = dat_long, aes(x = 月, y = 気温, group = 年, col = as.factor(年)), alpha = 0.5) +
  geom_point(data = dat_long, aes(x = 月, y = 気温, col = as.factor(年)))
スクリーンショット 2019-08-20 15.29.42



上のコードではdata=とaes()の一部が同じです。このように同じデータ、aesを使う場合はggplot()に入れる事ができます。加えてラベルのas.factor(年)の見栄えをlabs関数で変更します。
ggplot(data = dat_long, aes(x = 月, y = 気温, col = as.factor(年))) + 
  theme_gray(base_family = "HiraKakuPro-W3") +
  geom_line(aes(group = 年), alpha = 0.5) +
  geom_point() +
  labs(color = "")
スクリーンショット 2019-08-20 19.24.13


8.線のタイプを変更する


線のタイプはlinetypeで指定します。
注意点としてはgeom_point関数のshape時に紹介しましたがfactor型にする必要があります。



ggplot(data = dat_long, aes(x = 月, y = 気温)) + 
  theme_gray(base_family = "HiraKakuPro-W3") +
  geom_line(aes(group = as.factor(年), linetype= as.factor(年)), alpha = 0.5) +
  geom_point()
スクリーンショット 2019-08-20 19.26.21

9.数値を入れる場合

数値を追加するにはgeom_text関数が使えますが、数値の位置が点の位置と重なってしまいます。
加えて右のラベルの点とaが重なって見栄えが悪いです。

ggplot(data = dat_long, aes(x = 月, y = 気温, col = as.factor(年))) + 
  theme_gray(base_family = "HiraKakuPro-W3") +
  geom_line(aes(group = 年), alpha = 0.5) +
  geom_text(aes(label = 気温)) +
  geom_point()
スクリーンショット 2019-08-20 19.29.02



vjustで上下にずらすことができます。ただそもそも点の位置が近いとどうしても重なります。
ラベルに表示させないためにはshow.legend=FALSEを指定します。

ggplot(data = dat_long, aes(x = 月, y = 気温, col = as.factor(年))) + 
  theme_gray(base_family = "HiraKakuPro-W3") +
  geom_line(aes(group = 年), alpha = 0.5) +
  geom_text(aes(label = 気温), vjust = 1.5, show.legend = FALSE) +
  geom_point()
スクリーンショット 2019-08-20 19.28.12




文字が重なって困る時は自動的に場所を調整してくれるgeom_text-repel関数が使えます。

ただgeom_text_repel関数はtidyverseパッケージに入ってません。ggrepelパッケージのインストールが必要です。

#一度も使ったことがなければインストール
install.packages("ggrepel") #インストール後はlibraryで呼び出す
library(ggrepel)
geom_text → geom_text_repelに変更するだけで自動調整してくれます。
ggplot(data = dat_long, aes(x = 月, y = 気温, col = as.factor(年))) + 
  theme_gray(base_family = "HiraKakuPro-W3") +
  geom_line(aes(group = 年), alpha = 0.5) +
  geom_text_repel(aes(label = 気温), show.legend = FALSE) +
  geom_point()
スクリーンショット 2019-08-20 19.26.34




10.まとめ

今回は折れ線グラフの作成について紹介しました。

折れ線グラフではgroupの指定が重要だと感じています。

またRを使ったグラフ作成はデータハンドリングが大半を締めますので、なれない方は第2章を参考にしてください。


↑このページのトップヘ