カテゴリ:
hr.icon
Rでピボット
Rで作業をする時も、まずはエクセルでデータの整形をして、それからそのデータをRに読み込むということもあると思います。 特にエクセルのピボットテーブルなどは直感的に使い勝手がいいので、縦型のデータを(集計作業もはさんで)横型に変換するために使うこともあるかもしれません。 正直、最終成果物としてクロス集計表が欲しいのなら、すべてエクセルでやってしまえばいいことも多いと思います。 ただ、前処理としてクロス集計表を作り、そこから分析を進めるような場合は、なるべく手作業をなくして再現性を確保するためにも、Rで完結させたいところです。 そこでエクセルのピボットテーブルで行うような集計(縦横変換)をRでどのように行うのか、単純な方法を紹介します。
R部分では主にデータの前処理を行うtidyrとdplyrパッケージを使った方法を紹介しますので、まずどちらも含まれたtidyverseパッケージを読み込みます。
code:パッケージの読み込み
# まだインストールしていない場合はまずインストールします。
install.packages("tidyverse")
# インストールしてあればパッケージの読み込みをします。
library(tidyverse)
使用するデータ
今回使用するものは、次のような変数を持つ、学生ごとの履修単位数に関する縦型の架空のデータです。
ID
簡単のためA,B,Cと3人分のIDが入っているものを扱います。
(通常IDは何桁かのアルファベットと数値の組み合わせとなっていると思います)
学年
1,2,3,4の学年の値が入っています。
学期
1,2,3の学期の値が入っています。
(前期・後期・夏期集中など大学によってパターンが異なると思います)
開講言語
ここでは日本語で開講されている科目はJ、英語で開講されている科目はEとしています。
単位数
履修単位数が入っています(0以上の整数)。
したがって、3名分*4学年分*3学期分*2開講言語=72行のテーブルになっています。
データを読み込んで、先頭10行を見てみましょう。
code:データの読み込みと確認
unit <- read_csv("unit.csv",locale = locale(encoding = "cp932"))
# read_csvもtidyverseパッケージに入っています。
head (unit,10)
## # A tibble: 10 x 5
## ID 学年 学期 開講言語 単位数
## <chr> <dbl> <dbl> <chr> <dbl>
## 1 A 1 1 J 6
## 2 A 1 2 J 6
## 3 A 1 3 J 3
## 4 A 1 1 E 0
## 5 A 1 2 E 0
## 6 A 1 3 E 13
## 7 A 2 1 J 9
## 8 A 2 2 J 6
## 9 A 2 3 J 3
## 10 A 2 1 E 3
それでは、集計方法を見ていきます。
エクセルでの集計方法
まずはエクセルでの集計方法をみて、それからそれに対応するRでの集計方法を見ていきます。ID・学年ごとに開講言語が英語の単位数の合計、開講言語が日本語の単位数の合計を集計してみましょう。
エクセル2016のピボットテーブルでは次のように操作します。
(1)ピボットテーブルの挿入
https://gyazo.com/299124c34bd52228b7a85e8f10636a27
(2)範囲の確認
https://gyazo.com/0fbfbcb1c3aeb60e8a29fe4d89a900e4
(3)集計
https://gyazo.com/2d01b77bc377caef5681beee0113ea6e
(4)集計結果
https://gyazo.com/c377a6f49f8ed14b314a1f16e59dd294
直感的に集計出来て、とっても簡単ですね。
Rでの集計方法(tidyrとdplyr(*2))
基本
それではいよいよRでの集計方法をみてみましょう。
code:基本的な集計
# unit_summaryに集計したものを入れます。
unit_summary <- unit %>%
# group_byの中に何で集計するかを指定します。エクセルでのピボットテーブルで「行」と「列」に入れたものです。
# エクセルでは「列」に入れた”開講言語”も、ここでは一度行に入れます。
group_by(ID,学年,開講言語) %>%
# 集計方法を指定します(*3)。まず集計した値の変数名を指定(ここでは”total”)して、次に何をどのように集計するか指定します。
# エクセルで”単位数”をΣ値に入れて集計方法を”合計”にしたのと同じ操作です。
summarize(total = sum(単位数))
まだ必要な操作があるのですが、一度ここでどのようなテーブルになっているか確認してみましょう。
code:基本集計データの確認
unit_summary
## # A tibble: 24 x 4
## ID 学年 開講言語 total
## <chr> <dbl> <chr> <dbl>
## 1 A 1 E 13
## 2 A 1 J 15
## 3 A 2 E 12
## 4 A 2 J 18
## 5 A 3 E 15
## 6 A 3 J 21
## 7 A 4 E 15
## 8 A 4 J 21
## 9 B 1 E 19
## 10 B 1 J 9
## # ... with 14 more rows
この状態ではまだエクセルのピボットテーブルと同じような形になっていません。 開講言語を「列」に入れるような操作が必要です。 これは縦型データを横型に変換しているのと同じような操作になります。
code:spread(横型変換)
# spread関数でどの変数を横型にして、値は何を入れるのか指定します。
unit_spread <- unit_summary %>% spread(開講言語,total)
code:spread結果の確認
# どのようなテーブルになったか見てみます。
unit_spread
## # A tibble: 12 x 4
## ID 学年 E J
## <chr> <dbl> <dbl> <dbl>
## 1 A 1 13 15
## 2 A 2 12 18
## 3 A 3 15 21
## 4 A 4 15 21
## 5 B 1 19 9
## 6 B 2 30 6
## 7 B 3 21 18
## 8 B 4 9 21
## 9 C 1 15 16
## 10 C 2 27 9
## 11 C 3 30 9
## 12 C 4 21 9
これでエクセルのピボットテーブルで作成した集計表と同じような集計表ができました。ぱちぱち。
列に総計を入れたい
もし、総計の列も欲しければ次のように変数を作成しましょう。
code:変数の作成
unit_soukei <- unit_spread %>% mutate("総計" = E + J)
unit_soukei
## # A tibble: 12 x 5
## ID 学年 E J 総計
## <chr> <dbl> <dbl> <dbl> <dbl>
## 1 A 1 13 15 28
## 2 A 2 12 18 30
## 3 A 3 15 21 36
## 4 A 4 15 21 36
## 5 B 1 19 9 28
## 6 B 2 30 6 36
## 7 B 3 21 18 39
## 8 B 4 9 21 30
## 9 C 1 15 16 31
## 10 C 2 27 9 36
## 11 C 3 30 9 39
## 12 C 4 21 9 30
行に小計を入れたい
「いや、IDごとの小計も欲しいんだけど」という場合、例えば次のような方法があります。
code:小計の挿入
# まず小計のデータを作ってみます。学年の変数の値を”小計”とするものを作り、そこに小計の値を入れます。
unit_totalID <- unit_soukei %>% group_by(ID) %>%
summarise("E" = sum(E), "J" = sum(J), "総計" =sum(総計)) %>%
# この集計には「学年」の変数がないので「学年」の値がすべて"小計"になるような変数を作ります。
mutate("学年" = "小計") %>%
# 列の順番をunit_soukeiと合わせておきます。
select(ID,学年,E,J,総計)
# どのようなデータか一度みてます。
unit_totalID
## # A tibble: 3 x 5
## ID 学年 E J 総計
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 A 小計 55 75 130
## 2 B 小計 79 54 133
## 3 C 小計 93 43 136
このデータがunit_soukeiに足されればいいことになります。 しかし、unit_soukeiの学年の型はdblで、unit_totalIDの学年の型はchrなので、一度unit_soukeiの学年の型をchrにします。
code:型の指定
unit_soukei2 <- unit_soukei %>% mutate(学年 = as.character(学年))
unit_soukei2
## # A tibble: 12 x 5
## ID 学年 E J 総計
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 A 1 13 15 28
## 2 A 2 12 18 30
## 3 A 3 15 21 36
## 4 A 4 15 21 36
## 5 B 1 19 9 28
## 6 B 2 30 6 36
## 7 B 3 21 18 39
## 8 B 4 9 21 30
## 9 C 1 15 16 31
## 10 C 2 27 9 36
## 11 C 3 30 9 39
## 12 C 4 21 9 30
これで学年の下に表示されている型が<chr>になりました。 では、バインドしましょう。
# 2つのテーブルを列方向にバインドします。
code:バインド
unit_bind <- bind_rows(unit_soukei2,unit_totalID) %>%
# 学年で昇順、IDの順番にで昇順にしましょう。(降順にしたい場合はIDの部分をdesc(ID)などとします。)
arrange(ID,学年)
# できたものを見てみます。
unit_bind
## # A tibble: 15 x 5
## ID 学年 E J 総計
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 A 1 13 15 28
## 2 A 2 12 18 30
## 3 A 3 15 21 36
## 4 A 4 15 21 36
## 5 A 小計 55 75 130
## 6 B 1 19 9 28
## 7 B 2 30 6 36
## 8 B 3 21 18 39
## 9 B 4 9 21 30
## 10 B 小計 79 54 133
## 11 C 1 15 16 31
## 12 C 2 27 9 36
## 13 C 3 30 9 39
## 14 C 4 21 9 30
## 15 C 小計 93 43 136
別に学年の小計なんていらないかもしれませんが、例えばこんな方法で作ることができました。
列に2つ変数を入れるには?
エクセルのピボットテーブルでは列に2つ変数名を入れることもできました。 例えば次の図のようなものです。
https://gyazo.com/95984db3f7ea34c27d73ff92b1d8d589
Rで同じような表を作りたくても、ピボットテーブルの集計結果のように、 列名を2行にして1行目を空欄というようなことはできないので、列の名前を工夫しなければいけません。 例えば次のような方法があります。
code:変数と変数の結合
# 文字列操作を行うのでstirngrパッケージを読み込みます。
# はじめての場合は、install.packages("stringr")が必要です。
library(stringr)
# unit_summary2に集計したものを入れます。
unit_summary2 <- unit %>%
group_by(ID,学年,開講言語) %>%
summarize(total = sum(単位数)) %>%
# ”開講言語”と”学年”をアンダーバーでつなぐ変数を作ります。
mutate(開講言語と学年 = str_c(開講言語,学年,sep="_"))
ここで一度テーブルを見てみます。
code:結合した変数の確認
unit_summary2
## # A tibble: 24 x 5
## ID 学年 開講言語 total 開講言語と学年
## <chr> <dbl> <chr> <dbl> <chr>
## 1 A 1 E 13 E_1
## 2 A 1 J 15 J_1
## 3 A 2 E 12 E_2
## 4 A 2 J 18 J_2
## 5 A 3 E 15 E_3
## 6 A 3 J 21 J_3
## 7 A 4 E 15 E_4
## 8 A 4 J 21 J_4
## 9 B 1 E 19 E_1
## 10 B 1 J 9 J_1
## # ... with 14 more rows
このテーブルで”開講言語_学年を”spreadしてしまえば、 実質エクセルのピボットテーブルテーブルで列に2変数入れたのと同じような結果が得られます。
code:spreadと結果の確認
unit_spread2 <- unit_summary2 %>%
# 一度グループを解除します。
ungroup() %>%
# ”ID”と”開講言語と学年”でグループ化します。
group_by(ID,開講言語と学年) %>%
# 改めて集計します。
summarise("total" = sum(total)) %>%
# 開講言語と学年でspreadします。
spread(開講言語と学年,total)
# 結果を見てみます。
unit_spread2
## # A tibble: 3 x 9
## ID E_1 E_2 E_3 E_4 J_1 J_2 J_3 J_4
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 A 13 12 15 15 15 18 21 21
## 2 B 19 30 21 9 9 6 18 21
## 3 C 15 27 30 21 16 9 9 9
これで、エクセルのピボットテーブルで列に2変数入れた場合と実質同じような集計表ができました。
利点
集計表が欲しいときに、その集計表そのものが最終的に欲しいものなのであれば、 エクセルで作ってしまうのが楽かもしれません。 しかし、集計表はあくまで途中に必要なものであり、そのあとその集計表をいろいろいじりたい場合にはRで済ませてしまった方が楽かもしれません。
たとえば、 次のような何かのアンケート結果があったとします。
code:アンケート結果
result
## ID 回答
## 1 A とても満足
## 2 B まぁ満足
## 3 C やや不満
これにunit_talbleIDをつなぎ合わせたいので、一度この集計表を作りたいというような場合は、 いちいちエクセルで集計したものをRで読み込めるように加工してから読み込む、 なんてことをせずにR上で終わらせられます。
code:左結合
table_needed <- left_join(result,unit_totalID,by = "ID")
## Warning: Column ID joining factor and character vector, coercing into
## character vector
table_needed
## ID 回答 学年 E J 総計
## 1 A とても満足 小計 55 75 130
## 2 B まぁ満足 小計 79 54 133
## 3 C やや不満 小計 93 43 136
ここをスタート地点として、このテーブルをRで分析していくことができます。 アンケート結果などはIDごとに一行になっているけれど、成績の細かいデータはデータベースに入っていて縦型でID*科目数分の行数になっていることも多いと思うので一度必要な集計をしてから結合したいということはよくあるかもしれません。
Rでの集計方法(rpivotTable)
「いやいや、Rでもインタラクティブに集計したいんだ!」という場合はrpivotTable(*4)パッケージを使う方法もあります。
code:読み込み
# 初めての場合はinstall.packages("rpivotTable")でパッケージをインストールします。
# パッケージを読み込みます。
library(rpivotTable)
この画面でエクセルのようにいじるだけです。
rpivotTable(unit)
https://gyazo.com/4d36bbd1b9629b05d2b885c33f6c2d2d
一応コードでも表現できます。
code:rpivotTable関数
figure1 <- rpivotTable(unit,rows = c("ID","学年"),cols = "開講言語", vals = "単位数", aggregatorName ="Sum", renderName = "Table",width = "400px",height ="500px")
figure1
https://gyazo.com/ee6048897bd161a58d56b32b6099ca0e
ただし、これをテーブルとして取り出したり、エクセルとして出力したりするには、Shinyを使わなければいけないようで、 簡単にはできないようです(*5)。 そのため、rpivotTableはちょっとアイデアを確認するのに使う程度にして、最終的には再現性を維持するためにもtidyrとdplyrで集計するのがよいでしょう。
hr.icon
注
(1) この内容は私個人の見解であり、所属組織の見解ではありません。
(2) tidyrとdplyrの使い方の詳細は、Rサポーターズ著『パーフェクトR』(技術評論社)のデータ処理の章がわかりやすく参考にさせていただいています。
(3) 集計方法は以下のようなものがあります。
合計:sum, カウント:n() (変数名を入れない), 平均:mean,
最大値:max, 最小値;min,中央値:median, 不偏標準偏差:sd, 不偏分散:var
(5) “Export the data from rpivotTable in excel ♯62”
※当コラムの文責及び著作権は、すべて投稿者に帰属します。