【2-7】Excelの複数シートをRで一気に読み込む職場ではExcelでデータ集計しているという職場も多いと思います。その中でタブに1回目,2回目,,,や1月,2月,,,というように同じデータ形式を複数シートで管理する場合もあるかもしれません。同じ形式で複数シートにまたがるデータをRで一度に読み込む方法を紹介します。いう架空のファイルを準備していますが、記事を見ながらご自身でExcelファイルを作ってみても大丈夫です。(注意)今回の記事は「r-wakalang」でのアドバイスを参考にさせていただきました。自分も別の方法でしていたのですが、あまりにもスマートな方法なので今回の方法に切り替えました。1.前提まずこの方法ができるためにはいくつか前提が必要です。・全てのシートが同じ形式シート毎に列名やいろいろなものがバラバラでは読み込めません。フォーマットはちゃんと決めましょう。シートを複数選択しまとめて処理をしましょう。・セル結合が無いExcelでセル結合をすると見栄えは良くなったように感じますが、集計を行う時はExcelでもRでも非常に困る場合が多いです!!!!!・列のタイトルが複数行にまたいでいる例えば病院で下位項目があるテストを記録すると列名が複数行にまたぐ場合があります。このような場合、上の行はだいたいセル結合されています・・・2.Excelでの対策ただ上司にセル結合+列名を2行にしないと減給だ!!!と脅される場合もあるかもしれません。自分ならもう一列Rで列名読み込むための行を作るという回避策を用意します。そのときに役に立つのがExcelの&です。ポイントは以下の3つです。D1は絶対参照($)をつけるとコピーで失敗しない(下の図ではわかりやすいように数式を表示しています)実際の見え方はこうなります。6D3をコピー→右の空いた所全てを選択し貼り付け5誰かに邪魔されないように3行目を非表示にすれば完成!3.Excelの&についてD1&D2とするとD1の値とD2の値を文字列としてくっつけてくれます。D1はFIM、D2は食事なので、=D1&D2はFIM食事となります。今回はFIMと食事の間に_をはさみます。)などの記号や空白はRで読み込んだ際に不都合が出ることがあります。(アンダーバー)をおすすめします。_のような文字列を&で繋げる場合は"(ダブルクォーテーション)ではさみます。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パッケージをインストールしていなければインストール。これで全てのタブを読み込むことができます。(以下補足)6.何をしているのか?1つずつ説明していきます。ファイル名を指定するまずfileという変数(別に名前はfileでなくても良い)にExcelのファイルを指定します。すれば後のread_excel関数で楽ができます。シートの名前リストを作るExcelから複数シートを読み込むにはシートのリストが必要です。readxlパッケージのexcel_sheet関数がまさにそれです。excel_sheets(file)これでsheetsという変数にタブの名前が入りました。今回はこのままで問題ありませんが、Excelの中には「基本情報」や「選択項目」など必要ないタブもあるかもしれません。その時はsheetsから間引きます。シートは名前だけでなく、左から1,2,,,,と自動的に番号が振り分けられています。いらないシート番号がわかっている時はいらない番号だけ指定すればいいです。シート名を指定することができます。"2回目"]1つだけExcelを読み込む時のread_excel1つだけExcelを読み込む関数はいくつかあるのですが、今回はread_excel関数を使います。read_excel関数はxls,xlsxどちらも読み込むことができます。skip=を使うと最初の数行を飛ばして読み込むことができ、今までの対策が活きてきます。skip=2で1,2行目をskipし3行目を列名、4行目以降をデータとして読み込むことができます。繰り返し処理でdata.frameにデータを追加するmap_dfr関数map_dfr関数はExcelの複数のシートやフォルダの中にあるcsvファイルをまとめて読み込むのに便利です。ちなみにmap(繰り返し処理)、df(データフレーム)、r(縦につなげる)でmap_dfrです。横につなげる時はmap_dfc関数ですがここでは目的に合致しません。最初に.xにあたる繰り返すためのリストを用意して%>%でつなぎます。ここでは"1回目"→"2回目"→"3回目"としたいわけです。excel_sheets(file)でそれを作りました。先程紹介したread_excel関数を使います。~(チルダ)を入れることがポイントです。加えて足した後にこのデータがどのシートかわかる(集計する)ためにmutate関数をつかってsheet(別の名前でもいい)という列名を追加します。"3回目"を追加してくれます。7.まとめ今回はExcelで複数のシートを読み込む方法を紹介しました。今までExcel使っていたけどRも使ってみたいなという方がいましたら練習と実用を兼ねている課題だと思いますのでぜひ挑戦してみてください。8.参考にさせていただいたサイトpurrrを使ったExcelの読み込みはr-wakalangさんの質問を参考にさせていただきました。第3章ではggplot2を使ったグラフの作り方を紹介しています。今回は折れ線グラフを紹介します。折れ線グラフは時系列の変化を追うのに適しています。今回は実際のサイトから表を読み込んでグラフを作成します。1.データの読み込み今回は気象庁のサイトから東京の平均気温を読み込みます。webサイトからデータを抜き出すにはrvestパッケージを使います。以下コードですが、まだ説明していないものが多いのでそのままコピー→実行すると表を読み込めます。読み込むと5つのtableがあることがわかる。1つずつ確認すると4つ目が目的のtableだった。tableをdata.frame型に変換する。html_tablehead(dat)2.データハンドリングこのデータをグラフにしたいのですが以下の数点が気になります。「年の値」の列がいらない一番右にある「年の値」はグラフを作るには必要なさそうです。いらない列を削除するにはselect関数を使います。wideデータになっている【2-5】Rでデータを集計するのに便利なlongデータとgather関数でも紹介しましたが、今回のデータはwideデータになっています。Excelではwideデータでグラフを作成しますが、Rでデータ集計、グラフを作るにはlongデータに変える必要があります。wideデータをlongデータに変える関数はgather関数です。今回もggplotパッケージが含まれているtidyverseパッケージを読み込みます。ように数値ではないものが含まれています。試しにstr関数でデータの概要を見てみます。文字列2月と6〜11月は文字列になっているので計算してくれません。そのため不要な記号を消す必要があります。紹介していませんが、数値以外の全てを消すparse_number関数というものがあります。行が多すぎるstr関数に書いてますが145行あります。このままでもグラフは作れますが、今回はデータを間引きます。行を削除するのはslice関数を使います。今回は1880,1885,,,,,,2015,2019年を抜き出します。見ると最初の1880年は6行目のようです。6,11,16,,,,,と抜き出すにはseq関数を使います。5)これで6から145までを5間隔で抜き出すことができます。ちなみに2019年は145行目です。実際のコードselect関数、gather関数、parse_number関数、slice関数、この後グラフを作るggplot2関数は全てtidyverseパッケージに含まれています。#tidyverseパッケージをインストールしていなければインストール。install.packages("tidyverse")#既にtidyverseパッケージをインストールしている方は以下でもOKlibrary(tidyverse)もしRの基礎を勉強中であれば上のヒントでデータハンドリングを試してみてください。いくつか方法はありますが、以下1例です。%>%ポイントは以下のとおりです。・gather関数でlongデータにする前にselect関数,slice関数を使いました。TRUEをすれば1月,2月,,,となり、入れないと10月,11月,12月,1月,,,となります。・parse_number関数はgather関数の後で使いました(1列で済むため)。・parse_number関数はmutate関数とセットで使う1880年の1月,2月,,,と並んでいませんがグラフ作成に支障はありません。これでデータの完成です。折れ線グラフを作る時はgeom_line関数を使います。aes関数の中でgroupを設定します。年))もしgroupを指定しないと目的のグラフになりません。気温))4.group毎に色を変えるgroup毎に色を変えるにはcolor(もしくはcol)を指定します。年))色の薄い最近の方が暑いのがわかります。今回色がグラデーションになっています。これは年の型がint型(数値)になっているためです。数値でなくfactorに変換するとグラデーションではなくなります。as.factor関数(もしくはfactor関数)を使います。as.factor(年)))数が多すぎてどの色が何年かわからなくなりました・・・ただgroupの数が少ない時は便利です。5.グラデーションの色を指定するグラデーションの色を指定するにはscale_colour_gradientn関数を使います。"最大値の色"))と指定します。"red"))最初と最後だけでなく、途中の色も指定できます。"red"))6.線の色を透過させる線が重なっているところは新しい線が上書きしています。第3章では何度も紹介していますが、色の透過はalphaで指定します。groupに関わらず一括で指定するのでaes関数の外で指定します。"red"))ちなみに年を間引かなければこのようになります。みても気温が少しずつ上昇している傾向が伺えます。7.折れ線グラフに点をつけるもう少しデータを間引き、1880年と2018年のデータだけにします。parse_number(気温))折れ線につける点は折れ線グラフに散布図を重ねます。散布図は【3-3】Rのggplot2で散布図を作るgeom_point関数で紹介しました。as.factor(年)))上のコードではdata=とaes()の一部が同じです。このように同じデータ、aesを使う場合はggplot()に入れる事ができます。加えてラベルのas.factor(年)の見栄えをlabs関数で変更します。"")8.線のタイプを変更する線のタイプはlinetypeで指定します。注意点としてはgeom_point関数のshape時に紹介しましたがfactor型にする必要があります。数値を追加するにはgeom_text関数が使えますが、数値の位置が点の位置と重なってしまいます。加えて右のラベルの点とaが重なって見栄えが悪いです。geom_point()vjustで上下にずらすことができます。そもそも点の位置が近いとどうしても重なります。ラベルに表示させないためにはshow.legend=FALSEを指定します。geom_point()文字が重なって困る時は自動的に場所を調整してくれるgeom_text-repel関数が使えます。geom_text_repel関数はtidyverseパッケージに入ってません。ggrepelパッケージのインストールが必要です。geom_text_repelに変更するだけで自動調整してくれます。geom_point()10.まとめ今回は折れ線グラフの作成について紹介しました。折れ線グラフではgroupの指定が重要だと感じています。Rを使ったグラフ作成はデータハンドリングが大半を締めますので、なれない方は第2章を参考にしてください。第3章ではggplot2を使ったグラフの作り方を紹介しています。今回は棒グラフを紹介します。棒グラフは馴染みのあるグラフではありますが、自分の体験上「統計に慣れていない方やExcelで棒グラフと折れ線グラフ以外のグラフを作成したことがない方は気づいていない落とし穴もあるかな」とも感じています。棒グラフの作り方は元のデータの集計方法によって作り方が違います。今回は棒グラフの基本や原則、Rのggplot2を使った棒グラフの作成方法を紹介します。エラーバーや散布図との組み合わせも紹介します。1.データの読み込み今回もggplotパッケージが含まれているtidyverseパッケージを読み込みます。#tidyverseパッケージをインストールしていなければインストール。していれば次へinstall.packages("tidyverse")#既にtidyverseパッケージをインストールしている方は以下でもOKlibrary(tidyverse)#今回のデータは以下のコードでdatというデータフレームを作成します。棒の「長さ自体」に意味があるこれを書いていて当たり前のように感じるのですが、実は当たり前ではありません。Excelで棒グラフを作るときにやってしまいがちなのですが、まず棒グラフで一番やってはいけない図を紹介します。棒グラフはグループ毎の長さの比を見て比べるグラフですが、右図のように縦軸の最小の数字を変えてしまうと長さの比が変わってしまいます。3.38倍そのため本来1.09倍しか違わないデータをあたかも3.38倍違うデータと恣意的に見せたと解釈される可能性もあります。そのため棒グラフを使うのであれば必ず最小値は0にする必要があります。棒グラフ以外(箱ひげ図やヒストグラム)などを考えるのも手かもしれません。棒グラフが使われるのは主に「集計してカウント数を表示する」場合か「平均を表示する」ときに使います。棒グラフを作るためには集計されたデータが必要になります。集計せずにグラフを作る方法もありますが、今回は先に集計をしてグラフを作ります。棒グラフの3つのパターンを決めるposition棒グラフといってもいくつかのタイプがあります。"○○"を指定するだけで変更することができます。"dodge"(横に並べる)用途に応じて使い分けます。3.集計したデータを作成するまずデータの確認をします。head(dat)str(dat)性別と身長とgradeという変数があります。求め、それらをパイプ演算子でつないでいます。パイプ演算子(%>%)、group_by、summarize関数については第2章で紹介しています。4.棒グラフの基本的な作り方ggplot2で棒グラフを作る時にはgeom_bar関数を使います。gradeごとの人数をカウントします。"identity"が必要になります。これはデータの集計方法で「この数字のままを使ってね」という意味になります。5.棒の幅を変える棒の幅を変えるにはwidthで指定します。"identity")6.色を変えるグラフの中の色を変えるにはfillを使います。一括で色を指定する時はaes関数の外にfillを指定します。"identity")グループ毎に色を変えるにはaes関数内に色分けしたい変数を入れます。○○で別の変数を指定する。次はdat_stat_grade_sexを使ってグラフを作成します。x軸はgrade、y軸は人数、色を性別にします。positionを指定しないと自動的に"stack"になります。"stack"のときのいろんな指定方法グラフに数値を追加するグラフに数値を追加するにはgeom_text関数を追加します。"stack")ポイントは以下の通りです。"stack"を行うと数値の値と同じ位置に数値がきます。0.5)に変更します。積み上げ棒グラフ(割合)を作る時はpositionに"fill"を指定します。"fill")縦軸は割合(0〜1)になります。縦軸のラベルを%に変えるy軸を指定するscale_y_continuous関数を使うと縦軸のラベルを%に指定することができます。"stack"でも紹介したgeom_textを使います。0.5)に変更します。今回は身長をつかってグラフを作ります。横に並べるにはpositionに"dodge"を指定します。"fill"のときにはy軸を指定するscale_y_continuous関数を使い桁を区切るカンマを付けることができます。scales::comma)の1行を付け加えます。"stack"でも紹介したgeom_textを使います。"dodge"とするとエラーが出ます。position_dodge(○○)で数値を指定します。グループやwidthの指定で幅が変わるので数字を変えながら丁度いい数字を探してください。今回の場合は0.9が丁度いいようです。このままでは線と数字がかぶってしまいます。高さを微調整する時はvjust=数値で指定します。数値がプラスだと下に、マイナスだと上に移動します。"white")11.グラフにエラーバーを付ける10で作成したデータで話を続けます。今回はエラーバーの幅を標準偏差にしています。グラフにエラーバーをつけるにはgeom_errorbarを使います。geom_errorbarではエラーバーの最小値(ymin)と最大値が(ymax)がいるのでaes関数に入れます。position_dodge(○○)で調整してください。geom_textがエラーバーに重ならないようyの値を10に変更しています。自由に変えてみてください。このように自由にグラフを重ねられるのもggplot2の魅力です。"white")12.棒グラフと散布図を組み合わせる統計になれてないと分布を見ずにいきなり棒グラフを作ることがあります(自分がそうでした)。なれないと棒グラフでは分布はわかりにくいです。エラーバーも実は正規分布のデータでないと意味をなしません。実際に発表する場面では使わないかもしれませんが、グラフになれるまではぜひ棒グラフに散布図を組み合わせてみてください。="jitter"を追加する)かgeom_jitterです。0.2)ここでのポイントはgeom_jitterのdataがdatになっている点です。これは棒グラフやエラーバーの数値は集計したデータを使いますが、散布図は200名の生データが必要になるからです。ちなみにggplot2は下に書くほど前面に追加されます。今回はgeom_errorbarがgeom_jitterよりも下にあるので棒が散布図の点で隠れていません。geom_errorbarを下に書くとエラーバーが散布図の影に隠れますので確認してみてください。"identity")このグラフを横にするのはcoord_flip()を足すだけです。coord_flip()14.x軸を並べ替える軸をyの値で並べ替えるにはx軸にreorder関数を使います。並べ替えたい変数名)とします。"identity")ただx軸の名前が変になりました。そのためlabs関数でx軸のタイトルを変更します。labs関数はx,y軸の名前やタイトル・サブタイトルを指定します。"grade")15.凡例を消す棒グラフの場合x軸に名前がついていて凡例がいらない場面もあります。"none")を付け加えます。"none")15.まとめ今回は長くなりましたが、棒グラフについて紹介しました。棒グラフには色々なpositionがあるので最初は思い通りのグラフにならないかもしれませんが、見直しながら少しずつ慣れてみてください。第3章ではggplot2を使ったグラフの作り方を紹介しています。今回は箱ひげ図(boxplot)を紹介します。箱ひげ図は正規分布でないデータやマンホイットニーのU検定などノンパラメトリックの検定で使われます。RコマンダーやEZRでも検定と一緒に箱ひげ図が出てきますが、変数名が日本語だと□□と文字化けしてしまいます。Rstudioでggplot関数を使えば文字化け対策やグループ毎に色を付けるなどきれいなグラフが作れます。1.データの読み込み今回もggplotパッケージが含まれているtidyverseパッケージを読み込みます。#tidyverseパッケージをインストールしていなければインストール。していれば次へinstall.packages("tidyverse")#既にtidyverseパッケージをインストールしている方は以下でもOKlibrary(tidyverse)#データ取り込みます。read.csv(url)このデータはヒストグラムを作る時に作ったデータと同じものを使用しています。2.箱ひげ図(boxplot)とは?箱ひげ図は以下のようなグラフです。「ひげ」の外にあるデータは「外れ値」として点で表示され、大まかな分布を知ることができます。3.四分位とは四分位範囲も含め箱ひげ図で表示されるデータは全て「順位」で決められています。これらはqantile関数で直接求めることができます。4.箱ひげ図の特徴(平均ではなく)中央値を比較するのに向いている箱ひげ図は平均ではなく中央値を表示します。そのため正規分布でないデータやノンパラメトリックの検定でよく使われます。簡易的な分布がわかる棒グラフ±標準偏差ではある程度の分布は示してくれますが、実際今回のデータの最大値・最小値がどうだったのか等は教えてくれません。それに比べ箱ひげ図はある程度の分布を表してくれます。注意したいのが、データに二峰性の分布があると箱ひげ図でも分布がうまく反映できません。グラフを使って何を示したいのか?が大切になってきます。5.箱ひげ図の基本的な作り方ggplot2でヒストグラムを作る時にはgeom_boxplot関数を使います。