Rによる帳簿（Excel）への出力
はじめに
Rの解析結果を帳簿（Excel）に出力する方法をまとめた。指定外の範囲の値は変更させずに事前に用意したExcelに解析結果を出力する方法になる。事前に作成した帳簿に出力する状況を想定している。
今回は、palmerpenguinsパッケージに含まれるpenguinsというデータセットを用いて以下の表の作成を目的とした。penguinsには様々な変数が含まれるが、種別、性別にくちばしの長さ、くちばしの厚み、翼の長さ、体重について要約統計量を算出した。具体的には、データ数、平均、標準偏差、最小値、25%点、中央値、75%点、最大値を記述した。
モックアップとして以下のExcelを用意した。今回は敢えて各変数ごとに別のシートに記載するようにした。モックアップでの数値は"99"とした。つまり、"99"としている箇所が解析結果に変更されれば目的を達成したことになる。
【参考】
- Horst AM, Hill AP, Gorman KB (2020). palmerpenguins: Palmer Archipelago (Antarctica) penguin data. R package version 0.1.0.
- package xlsx
目次
- Package
- penguins
- Variables
- データの記述
- データの要約
- データ整形
- データ整形
- 帳簿の形式に調整
- Excelに出力
- まとめ
- （メモ）今後、追加する可能性のある内容
Package
利用するPackageは以下の通り。
xlsxで出力は行っている。
#package
library(tidyverse)
library(dplyr)
library(sqldf)
library(palmerpenguins)
library(xlsx)
penguins
palmerpenguinsに含まれているpenguinsというペンギンの種類、生息している島、くちばしの長さ（mm）、くちばしの厚み（mm）、翼の長さ（mm）、体重（g）および性別の測定値が含まれているデータセットを利用した。
Variables
| 変数名 | 概要 |
|---|---|
| species | ペンギンの種類 |
| island | 生息島 |
| bill_length_mm | くちばしの長さ |
| bill_depth_mm | くちばしの厚み |
| flipper_length_mm | 翼の長さ |
| body_mass_g | 体重 |
| sex | 性別 |
th1 <- penguins
head(th1)
## # A tibble: 6 x 8
## species island bill_length_mm bill_depth_mm flipper_length_~ body_mass_g sex
## <fct> <fct> <dbl> <dbl> <int> <int> <fct>
## 1 Adelie Torge~ 39.1 18.7 181 3750 male
## 2 Adelie Torge~ 39.5 17.4 186 3800 fema~
## 3 Adelie Torge~ 40.3 18 195 3250 fema~
## 4 Adelie Torge~ NA NA NA NA <NA>
## 5 Adelie Torge~ 36.7 19.3 193 3450 fema~
## 6 Adelie Torge~ 39.3 20.6 190 3650 male
## # ... with 1 more variable: year <int>
データの記述
データの要約
はじめに種別、性別ごとにくちばしの長さ、くちばしの厚み、翼の長さ、体重について要約統計量（データ数、平均、標準偏差、最小値、25%点、中央値、75%点、最大値）を算出した。
speciesもしくはsexが欠測のデータは今回は除外している。
以下のプログラムを行うことで、種別、性別ごとの各変数の要約統計量が得られる。
summary_statistic_function <-
list(
n = ~n()-sum(is.na(.x)),
na = ~sum(is.na(.x)),
mean = ~mean(.x, na.rm = TRUE),
median = ~median(.x, na.rm = TRUE),
p50 = ~median(.x, na.rm = TRUE),
var = ~var(.x, na.rm = TRUE),
std = ~sd(.x, na.rm = TRUE),
min = ~min(.x, na.rm = TRUE),
max = ~max(.x, na.rm = TRUE),
p25 = ~quantile(.x,0.25, na.rm = TRUE),
p75 = ~quantile(.x,0.75, na.rm = TRUE)
)
summary_means <- function(df,var_list,statistic,strata=c()){
result <-
df %>% group_by_at(strata) %>%
summarize(dplyr::across(var_list,summary_statistic_function[statistic]))
return(result)
}
th2 <- th1[!(is.na(th1$species)|is.na(th1$sex)),]
th3 <- summary_means(th2,
c("bill_length_mm","bill_depth_mm",
"flipper_length_mm","body_mass_g"),
c("n","mean","std","min","p25","p50","p75","max"),
strata = c("species","sex"))
print(th3)
## # A tibble: 6 x 34
## # Groups: species [3]
## species sex bill_length_mm_n bill_length_mm_mean bill_length_mm_std
## <fct> <fct> <int> <dbl> <dbl>
## 1 Adelie female 73 37.3 2.03
## 2 Adelie male 73 40.4 2.28
## 3 Chinstrap female 34 46.6 3.11
## 4 Chinstrap male 34 51.1 1.56
## 5 Gentoo female 58 45.6 2.05
## 6 Gentoo male 61 49.5 2.72
## # ... with 29 more variables: bill_length_mm_min <dbl>,
## # bill_length_mm_p25 <dbl>, bill_length_mm_p50 <dbl>,
## # bill_length_mm_p75 <dbl>, bill_length_mm_max <dbl>, bill_depth_mm_n <int>,
## # bill_depth_mm_mean <dbl>, bill_depth_mm_std <dbl>, bill_depth_mm_min <dbl>,
## # bill_depth_mm_p25 <dbl>, bill_depth_mm_p50 <dbl>, bill_depth_mm_p75 <dbl>,
## # bill_depth_mm_max <dbl>, flipper_length_mm_n <int>,
## # flipper_length_mm_mean <dbl>, flipper_length_mm_std <dbl>, ...
データ整形
データ整形
得られた結果は、6行、24列のかたちになっている。最終的には、平均値（標準偏差）のような変数を作成したいため、bill_lengthやbill_depthごとに平均値や標準偏差が別列に入っていると扱いにくい。そのためデータ整形を行った。ここでは head(th4) で示しているような、縦に長く、横にはvariable, species, sex, 各要約統計量のかたちに整形した。
col_name <- c("variable","species","sex","n","mean","std","min","p25","p50","p75","max")
bill_length <- cbind("bill_length", th3[,c(1,2,3:10)])
bill_depth <- cbind("bill_depth", th3[,c(1,2,11:18)])
flipper_length <- cbind("flipper_length", th3[,c(1,2,19:26)])
body_mass <- cbind("body_mass", th3[,c(1,2,27:34)])
colnames(bill_length) <- col_name
colnames(bill_depth) <- col_name
colnames(flipper_length) <- col_name
colnames(body_mass) <- col_name
th4 <- rbind(bill_length,bill_depth,flipper_length,body_mass)
head(th4)
## # A tibble: 6 x 11
## variable species sex n mean std min p25 p50 p75 max
## <chr> <fct> <fct> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 bill_length Adelie female 73 37.3 2.03 32.1 35.9 37 38.8 42.2
## 2 bill_length Adelie male 73 40.4 2.28 34.6 39 40.6 41.5 46
## 3 bill_length Chinstrap female 34 46.6 3.11 40.9 45.4 46.3 47.4 58
## 4 bill_length Chinstrap male 34 51.1 1.56 48.5 50.0 51.0 52.0 55.8
## 5 bill_length Gentoo female 58 45.6 2.05 40.9 43.8 45.5 46.9 50.5
## 6 bill_length Gentoo male 61 49.5 2.72 44.4 48.1 49.5 50.5 59.6
帳簿の形式に調整
事前に定めた帳簿の形式に合うように調整した。具体的には、n、MEAN（SD）、MEDIAN（P25-P75）、MIN、MAXという形で、種別（Adelie, Chinstrap, Gentoo）、性別（male, female）に縦に並んだ形式にすればよい。適当に四捨五入もした。
sqldf('
select
variable,
species,
sex,
n,
(round(mean,1)|| " (" || round(std,2) || ")" ) as mean_std,
(round(p50,1)|| " (" || round(p25,1) || " - " || round(p75,1) || ")" ) as median_IQR,
round(min,1) as min,
round(max,1) as max
from th4
order by 1, 2, 3 desc ;
') -> th5
head(th5)
## variable species sex n mean_std median_IQR min max
## 1 bill_depth Adelie male 73 19.1 (1.02) 18.9 (18.5 - 19.6) 17.0 21.5
## 2 bill_depth Adelie female 73 17.6 (0.94) 17.6 (17.0 - 18.3) 15.5 20.7
## 3 bill_depth Chinstrap male 34 19.3 (0.76) 19.3 (18.8 - 19.8) 17.5 20.8
## 4 bill_depth Chinstrap female 34 17.6 (0.78) 17.7 (17.0 - 18.1) 16.4 19.4
## 5 bill_depth Gentoo male 61 15.7 (0.74) 15.7 (15.2 - 16.1) 14.1 17.3
## 6 bill_depth Gentoo female 58 14.2 (0.54) 14.3 (13.8 - 14.6) 13.1 15.5
Excelに出力
出力したいExcelファイルをwbにいれ、getSheets(wb)で指定したファイルのシート名を取得している。sheets$bill_lengthで"bill_length"というシートへの出力を意味しており、シート内の位置についてはstartRowとstartColumnで指定している。
最後にsaveWorkbookで保存すればExcelが更新されている。
（見やすいように、Excelの横幅等はExcel上で調整した）
wb <- loadWorkbook(file = "path/table_after.xlsx")
sheets <- getSheets(wb)
addDataFrame(filter(th5,variable == "bill_length")[,c(-1,-2,-3)],
sheets$bill_length, row.names = F, col.names = F, startRow = 2, startColumn = 3)
addDataFrame(filter(th5,variable == "bill_depth")[,c(-1,-2,-3)],
sheets$bill_depth, row.names = F, col.names = F, startRow = 2, startColumn = 3)
addDataFrame(filter(th5,variable == "flipper_length")[,c(-1,-2,-3)],
sheets$flipper_length, row.names = F, col.names = F, startRow = 2, startColumn = 3)
addDataFrame(filter(th5,variable == "body_mass")[,c(-1,-2,-3)],
sheets$body_mass, row.names = F, col.names = F, startRow = 2, startColumn = 3)
saveWorkbook(wb, file = "path/table_after.xlsx")
出力した結果
まとめ
本記事では、xlsxを用いてRの解析結果を既存のExcelに出力する方法を紹介した。
（メモ）今後、追加する可能性のある内容
- 細かいExcelの指定（例えばセル幅などをRで指定する方法）
- 図の出力
サポートして頂けるとモチベーションに繋がりますのでぜひ宜しくお願いします。データ解析や臨床研究でのご相談があれば、お気軽にTwiiterもしくはメールにてご連絡下さい。
作成者：Masahiro Kondo
作成日：2022/9/2
連絡先：m.kondo1042(at)gmail.com
Discussion