R言語でエクセルファイルを操作する方法を解説します。
ここではパッケージopenxlsxを使ってエクセルファイルの入力や出力を行っていきます。
エクセルファイル中のデータセットの読み込みから、フォントやセルの色の変更などの出力に関する関数とその使い方を紹介していきます。
今回紹介するプログラミングコードは以下からダウンロードすることができます。
ポチップopenxlsx
パッケージopenxlsxを用いると、エクセルファイル入力や出力といったほとんどの操作をRスクリプトから実行することができます。
openxlsxは他のエクセルファイルに関するパッケージとは違い、JAVAを必要としません。他のパッケージはJAVAのバージョンに依存するため、openxlsxを用いるのが無難です。
openxlsxに実装されている主要な関数を紹介します。ここで紹介する関数以外のRdocumentを参考にしてください。
ちなみに、この記事で紹介する関数を用いると、以下の記事のようなエクセルファイルの色塗りや編集をRスクリプトから行うことができます。
-
R言語 エクセルに色を塗る【自作関数】
こんにちは、usagi-sanです。今回は、R言語でエクセルファイルに色を塗る方法を紹介します。 統計解析を行う中で、解析結果の表などをエクセルに保存することがあると思います。解析結果を見やすくするた ...
続きを見る
read.xlsx
関数read.xlsxはエクセルファイル中のデータをデータフレームとして読み込む関数です。以下、関数read.xlsxとその引数の説明です。
| xlsxFile | character型。読み込みたいエクセルファイルのファイル名。 |
| sheet | character型。読み込みたいエクセルシート名。 |
| startRow | numeric型。何行目から読み込むか。 |
| colNames | logical型。列名を含めて読み込むか。 |
| rowNames | logical型。行名を含めて読み込むか。 |
| detectDates | logical型。日付型のデータがある場合はDate型の変数に変更する。 |
| skipEmptyRows | logical型。空行を無視するか。 |
| skipEmptyCols | logical型。空列を無視するか。 |
| rows | numeric型のベクトル。エクセルファイル中のどの行を読み込むか。 |
| cols | numeric型のベクトル。エクセルファイル中のどの列を読み込むか。 |
| check.names | logical型。読み込んだデータフレームの列名がおかしくないか確かめる。 |
| sep.names | character型。空白の列名を何で埋めるか。 |
| namedRegion | 名前付きセル範囲。 |
| na.strings | character型のベクトル。どの文字をNAとするか。 |
| fillMergedCells | logical型。TRUEの場合、結合したセルの値が結合中のすべてのセルに反映される。 |
write.xlsx
関数write.xlsxはデータフレームをエクセルファイルに出力する関数です。以下、関数write.xlsxとその引数の説明です。
| x | 出力したいデータフレーム。 |
| file | character型。エクセルファイルの名前。 |
| asTable | logical型。TRUEの場合、エクセルファイルを出力する際にwriteDataではなくwriteDataTableを用いる。 |
| overwrite | logical型。同じ名前のファイルがある場合上書きするか。 |
loadWorkbook
関数loadWorkbookはエクセルワークブックを読み込むための関数です。以下、関数loadWorkbookとその引数の説明です。
| file | character型。読み込みたいエクセルファイルの名前。 |
| xlsxFile | character型。エクセルファイルのエイリアス。 |
| isUnzipped | logical型。エクセルファイルがすでに解凍されている場合TRUEとする。 |
saveWorkbook
関数saveWorkbookはエクセルワークブックを読み込むための関数です。以下、関数saveWorkbookとその引数の説明です。
| wb | ワークブックオブジェクト。 |
| file | charactre型。出力するエクセルファイルの名前。 |
| overwrite | logical型。同じ名前のエクセルファイルが存在する場合上書きするかどうか。 |
| returnValue | logical型。TRUEの場合、出力に成功したときTRURを返し、反対に失敗した場合FALSEを返す。 |
実行例
パッケージopenxlsxの使用例をいくつか紹介します。上で紹介した関数以外にも様々な関数の実行例を載せました。
read.xlsxとwrite.xlsx
続いてエクセルファイルの入力から出力までの手順を解説していきます。
まずパッケージをインストールしましょう。
1 2 | install.packages("openxlsx") library(openxlsx) |
openxlsxをインストールしライブラリに追加したら、次のようにread.xlsxを用いてエクセルファイルを読みこむことができます。
"iris.xlsx"というirisのデータセットが保存されています。read.xlsxを実行すると次のようirisがdatasetに代入されているのが分かります。
1 2 3 4 5 6 7 8 9 | > dataset <- read.xlsx("iris.xlsx", sheet = "Sheet 1") > head(dataset) Sepal.Length Sepal.Width Petal.Length Petal.Width Species 1 5.1 3.5 1.4 0.2 setosa 2 4.9 3.0 1.4 0.2 setosa 3 4.7 3.2 1.3 0.2 setosa 4 4.6 3.1 1.5 0.2 setosa 5 5.0 3.6 1.4 0.2 setosa 6 5.4 3.9 1.7 0.4 setosa |
注意点
read.xlsxの注意点として、reac.csvとは違いファイル名のほかにシート名を指定する必要があります。
次にwrite.xlsxを用いてエクセルファイルに出力する例を紹介します。
まず、準備としてdatasetのSpeciesがcharacter型になっているため、以下を実行してfactor型に戻してあげます。
1 | dataset$Species <- as.factor(dataset$Species) |
上を実行したら、エクセルファイルに出力するdatasetの集計表や解析結果の表を作成していきます。
次のように関数summaryを用いることで集計表を作成することができます。
1 2 3 4 5 6 7 8 9 | > summaryData <- summary(dataset) > summaryData Sepal.Length Sepal.Width Petal.Length Petal.Width Species Min. :4.300 Min. :2.000 Min. :1.000 Min. :0.100 Length:150 1st Qu.:5.100 1st Qu.:2.800 1st Qu.:1.600 1st Qu.:0.300 Class :character Median :5.800 Median :3.000 Median :4.350 Median :1.300 Mode :character Mean :5.843 Mean :3.057 Mean :3.758 Mean :1.199 3rd Qu.:6.400 3rd Qu.:3.300 3rd Qu.:5.100 3rd Qu.:1.800 Max. :7.900 Max. :4.400 Max. :6.900 Max. :2.500 |
また、Sepal.LengthとSpeciesに関する回帰分析の結果を次のよう作成します。
1 2 3 4 5 6 7 8 | > regression <- lm(formula = Sepal.Length ~ Species, data = dataset) > summaryReg <- summary(regression) > coeffs <- summaryReg$coefficients > coeffs Estimate Std. Error t value Pr(>|t|) (Intercept) 5.006 0.07280222 68.761639 1.134286e-113 Speciesversicolor 0.930 0.10295789 9.032819 8.770194e-16 Speciesvirginica 1.582 0.10295789 15.365506 2.214821e-32 |
データセットと集計表と回帰分析の結果をエクセルファイルの各シートに出力していきます。
別々のシートに保存したい場合、次のようにリストに出力したいデータフレームを格納する必要があります。
1 | results <- list(Dataset = dataset, Summary = summaryData, Results = coeffs) |
リストの名前がシート名になります。上記の場合Dataset、Summary、Resultsという３つのシートが作成されます。
データフレームをリストに保存したら、write.xlsxで先ほど作ったリストを出力することで各データフレームをエクセルファイルに出力することができます。
1 | write.xlsx(results, "iris解析.xlsx", rowNames = TRUE) |
作業ディレクトリに次の画像のような"iris解析.xlsx"が保存されているのが確認できます。
ちゃんと3つのデータフレームが別々のエクセルシートに保存されています。
エクセルワークブックの編集
次にエクセルワークブックの編集方法を紹介します。
openxlsxを用いたフォントやセルの色などの変更方法を解説していきます。
openxlsxにある関数loadWorkbookを使うと、ワークブックの編集ができるようになります。
先ほど作ったエクセルファイル"iris解析.xlsx"を以下のようにして、loadWorkbookで読み込みワークブックオブジェクトwbを作成します。
1 2 | fileName <- "iris解析" wb <- loadWorkbook(paste0(fileName, ".xlsx")) |
エクセルファイルのフォントを変更したい場合、関数modifyBaseFontを使います。引数に変更後のフォントのパラメータを指定し実行します。
セルを編集したい場合、関数createStyleを使います。フォントの種類やフォントのサイズ、セルの色など様々なパラメータを引数で設定できます。
以下を実行すると、フォントサイズを12に、フォントを"Yu Gothic"に変更し、各エクセルシートの表のヘッダーを青色で塗ることができます。
1 2 3 4 5 | modifyBaseFont(wb, fontSize = 12, fontName = "Yu Gothic") st <- createStyle(fontName = "Yu Gothic", fontSize = 12, fgFill = "#00B0F0") for (sheetName in wb$sheet_names) { addStyle(wb, sheetName, style = st, cols = seq_len(ncol(results[[sheetName]]) + 1), rows = 1) } |
ワークブックを編集し終わったら関数saveWorkbookを実行し、編集内容を保存することができます。
1 | saveWorkbook(wb, paste0(fileName, "_編集後1.xlsx")) |
次の画像のように、先ほど作った"iris解析.xlsx"のフォントが変更されて、表のヘッダーが青色に変更されています。
エクセルシートの追加
最後にワークブックの追加方法を紹介します。
先ほど作成した編集後のエクセルファイル"iris解析_編集後1.xlsx"に新しいシートを追加していきます。
まず、loadWorkbookを実行し"iris解析_編集後1.xlsx"のワークブックを読み込みます。
1 | wb <- loadWorkbook(paste0(fileName, "_編集後1.xlsx")) |
次に、重回帰分析の結果の表を作成します。
1 2 3 4 5 6 7 8 9 10 11 | > multiRegression <- lm(formula = Sepal.Length ~., data = dataset) > summaryMultiReg <- summary(multiRegression) > coeffs <- summaryMultiReg$coefficients > coeffs Estimate Std. Error t value Pr(>|t|) (Intercept) 2.1712663 0.27979415 7.760227 1.429502e-12 Sepal.Width 0.4958889 0.08606992 5.761466 4.867516e-08 Petal.Length 0.8292439 0.06852765 12.100867 1.073592e-23 Petal.Width -0.3151552 0.15119575 -2.084418 3.888826e-02 Speciesversicolor -0.7235620 0.24016894 -3.012721 3.059634e-03 Speciesvirginica -1.0234978 0.33372630 -3.066878 2.584344e-03 |
エクセルシートを追加するには関数addWorkbookを使います。次を実行すると"Result_multi-linear_regression"という重回帰分析のエクセルシートがワークブックオブジェクトwbに追加されます。
1 2 | modifyBaseFont(wb, fontSize = 12, fontName = "Yu Gothic") addWorksheet(wb, sheetName = "Result_multi-linear_regression") |
ポイント
エクセルシートの追加が完了したので、関数writeDataにより重回帰分析の結果をwbに書き込みます。
1 | writeData(wb, sheet = "Result_multi-linear_regression", x = coeffs, rowNames = TRUE) |
次に、新しく追加したエクセルシートを編集していきます。まず、前回と同様に表のヘッダーを青に変更します。
1 | addStyle(wb, "Result_multi-linear_regression", style = st, cols = seq_len(ncol(coeffs) + 1), rows = 1) |
p値の大きさをセルの色で分けてあげると検定結果が見やすいため、以下のように各p値に対応する行のlogical型のベクトル、各p値のセルの色を与えて、リストに保存してあげます。
1 2 3 4 5 6 7 8 9 10 11 | is_pvalueLessThan0.1 <- c(FALSE, coeffs[, "Pr(>|t|)"] < 0.1) is_pvalueLessThan0.05 <- c(FALSE, coeffs[, "Pr(>|t|)"] < 0.05) is_pvalueLessThan0.01 <- c(FALSE, coeffs[, "Pr(>|t|)"] < 0.01) color0.1 <- "#CCC0DA" color0.05 <- "#B7DEE8" color0.01 <- "#FCD5B4" conditionAndColor <- list(list(condition = is_pvalueLessThan0.1, color = color0.1), list(condition = is_pvalueLessThan0.05, color = color0.05), list(condition = is_pvalueLessThan0.01, color = color0.01)) |
conditionAndColorにはp値がα以下であるかというlogical型のベクトルとそのαに対応する色が格納されています。
次を実行するとセルの色やフォントの色の変更を行うことができます。<./span>
1 2 3 4 5 6 7 8 9 | for (i in conditionAndColor) { st_pValues <- createStyle(fontName = "Yu Gothic", fontSize = 12, fgFill = i$color) addStyle(wb, sheet = "Result_multi-linear_regression", style = st_pValues, cols = seq_len(ncol(coeffs))[colnames(coeffs) =="Pr(>|t|)"] + 1, rows = seq_len(nrow(coeffs) + 1)[i$condition]) st_rowNames <- createStyle(fontName = "Yu Gothic", fontSize = 12, fontColour = i$color) addStyle(wb, sheet = "Result_multi-linear_regression", style = st_rowNames, cols = 1, rows = seq_len(nrow(coeffs) + 1)[i$condition]) } |
ワークブックの編集ができたため、saveWorkbookで新しいエクセルファイル"iris解析_編集後2.xlsx"に保存してあげます。
1 | saveWorkbook(wb, paste0(fileName, "_編集後2.xlsx")) |
saveWorkbookを実行しワークブックを保存すると、作業ディレクトリに次の画像のエクセルファイル"iris解析_編集後2.xlsx"が保存されます。
重回帰分析の結果のシート"Result_multi-linear_regression"が追加されており、p値が0.1未満、0.05未満、0.01未満でセルとフォントの色が塗り分けられているのが分かります。
まとめ
R言語でのエクセルファイルの操作方法を紹介しました。
Baseなどの標準のパッケージではエクセルファイルを扱うことはできませんが、ここで紹介したパッケージを使用することでcsvファイルと同じ感覚で扱うことができるようになります。
また、csvファイルとは異なりフォントの色やセルの色の変更が可能なため、出力したファイルの編集の可能性がさらに広がります。
