Rで解析:セル体裁!大きなデータも大丈夫!エクセル操作の「openxlsx」パッケージ


エクセルを操作するパッケージには「XLConnect」「WriteXLS」などがあります。でも、操作は楽だけれども少し大きなデータだとエラーが出たり、大きいデータを扱えるけど体裁コマンドがなかったり。そんな問題を解決出来るパッケージの紹介です。

コマンドの好みがあると思うので、「XLConnect」「WriteXLS」パッケージの記事も紹介しておきます。

・XLConnectパッケージの紹介
 https://www.karada-good.net/analyticsr/r-51/

・WriteXLSパッケージの紹介
 https://www.karada-good.net/analyticsr/r-138/

作業効率に関わるので、好みにあったパッケージを利用してはいかがでしょうか。なお、本パッケージではJAVAを使用せずRcppを利用しています。

パッケージバージョンは3.0.0。実行コマンドはR version 3.2.2で確認しています。


パッケージのインストール

下記、コマンドを実行してください。

#パッケージのインストール
#windowsでは環境によってRtoolsのインストールが必要かもしれません
#https://cran.r-project.org/bin/windows/Rtools/
install.packages("openxlsx", dependencies = TRUE)

データ例の作成

データ数1,000,000とGoogle Analyticsからのデータ取得に便利な「RGA」パッケージを利用してデータを作成するコマンドを紹介します。詳細はコメント、パッケージのヘルプを確認してください。

・RGAパッケージの紹介
 https://www.karada-good.net/analyticsr/r-141/

###データ例の作成#####
#データ数1,000,000
TestData <- data.frame(Group = paste0("テスト", 1:1000000),
                       Data1 = sample(1:500, 1000000, replace = TRUE),
                       Data2 = sample(200:300, 1000000, replace = TRUE))
########

###RGAパッケージを利用してデータ例を作成#####
#パッケージのインストール
#install.packages("RGA")
#パッケージの読み込み
library("RGA")
#googleアカウントにログインした状況で実行:authorizeコマンド
authorize()
#ページビュー数が多いコンテンツ順に取得
GAData <- get_ga(profile.id = "ga:list_profilesコマンドで取得したid", start.date = "7daysAgo", end.date = "yesterday",
                 dimensions = "ga:pageTitle, ga:hostname, ga:pagePath",
                 metrics = "ga:pageviews, ga:uniquePageviews, ga:bounces, ga:entrances, ga:exits",
                 sort = "-ga:pageviews")
#データ内容を整形
GAData[, 2] <- paste0("http://", GAData[, 2], GAData[, 3])
#必要のないデータを削除
GAData <- GAData[, -3]
########

エクセルファイルの読み書き・体裁を整える

参考までにエクセルファイルへの書き込み・出力の処理時間を紹介します。また、openxlsxパッケージにはセルの体裁だけでなく、表示書式を管理するコマンドも収録されています。RGAパッケージを利用して取得したデータを例に、文字列をハイパーテキスト化、パーセント表示、セル体裁を操作するコマンドを紹介します。

詳細はコメント、パッケージのヘルプを確認してください。

#############################################
###エクセルファイルの読み込み:read.xlsxコマンド
#############################################
library("tcltk")
library("openxlsx")
#ファイルを選択
XLPath <- paste0(as.character(tkgetOpenFile(title = "エクセルファイルを選択",
                                            filetypes = '{"エクセルファイル" {".xlsx" ".xls"}}',
                                            initialfile = c("*.xlsx", "*.xls"))))
#読み込むシートを指定:sheetオプション
XLData <- read.xlsx(XLPath, sheet = 1)

##########################################################
###作業フォルダにデータをxlsxで書き込み出力:write.xlsxコマンド
##########################################################
#データ数1000000を出力
#エクセルのテーブルスタイル9を適応:asTableオプション
#書き込み位置を指定:xyオプション
system.time(write.xlsx(TestData, file = "TestData.xlsx", asTable = TRUE, xy = c(1, 1)))
#約24MBのエクセルファイルが出力されます
#CPU,メモリにもよりますが処理時間の参考
ユーザ   システム       経過  
28.990      1.044     30.183

#GADataを出力
system.time(write.xlsx(GAData, file = "GAData.xlsx", asTable = TRUE, xy = c(1, 1)))
#CPU,メモリにもよりますが処理時間の参考
ユーザ   システム       経過  
0.037      0.014      0.063 

##########################
###GADataを体裁整え出力####
##########################
#文字列のハイパーテキスト化
class(GAData$hostname) <- "hyperlink"
#文字列のパーセント表示
class(GAData$exits) <- "percentage"
#枠線色の指定:optionsコマンド;openxlsx.borderColourオプション
options("openxlsx.borderColour" = "#a87963")
#枠線の種類を指定:optionsコマンド;openxlsx.borderStyleオプション
options("openxlsx.borderStyle" = "thin")
#見出し行の書式を設定:createStyleオプション
hs <- createStyle(fontColour = "#ffffff", fgFill = "#4F80BD",
                  halign = "center", valign = "center", textDecoration = "Bold",
                  border = "TopBottomLeftRight", textRotation = 45)
#各セルの枠線位置を指定:bordersオプション;データエリアの周り;"surrounding",列方向;"columns",行方向;"rows",無し;NULL
#見出し行の書式を適応:headerStyleオプション
write.xlsx(GAData, file = "GAData.xlsx", colNames = TRUE,
           borders = "rows", xy = c(4, 4), headerStyle = hs)

シートが複数のエクセルファイルを作成する

ワークブックの作成、シートの追加、データの書き込み、行列の固定、列の幅に関するコマンドと作成した内容をエクセルを立ち上げて確認、作業フォルダにワークブックを保存するコマンドを紹介します。

#ワークブックの作成:createWorkbookコマンド
#作成者の設定:creatorオプション
NewWb <- createWorkbook(creator = "からだにいいもの")

#シートの追加:addWorksheetコマンド
#枠線の有無:gridLinesオプション
#シート名は日本語も可能です
addWorksheet(wb = NewWb, sheetName = "Karada", gridLines = FALSE)
addWorksheet(wb = NewWb, sheetName = "いいもの", gridLines = TRUE)

#データの書き込み:writeDataコマンド
writeData(wb = NewWb, sheet = "Karada", x = TestData, startCol = 4, startRow = 4)

#データをテーブル形式でシートに書き込む:writeDataTableコマンド
#テーブル体裁を設定:tableStyleオプション;スタイル名はエクセルを参照
writeDataTable(wb = NewWb, sheet = "いいもの", x = TestData, startCol = 4, startRow = 4,
               tableStyle = "TableStyleLight9")

#シート名"Karada"の体裁を調整
#行列の固定:freezePaneコマンド
freezePane(wb = NewWb, sheet = "Karada", firstRow = FALSE, firstCol = FALSE,
           firstActiveRow = 5, firstActiveCol = 5)

#列の幅を指定:setColWidthsコマンド
setColWidths(wb = NewWb, sheet = "Karada", cols = 4, widths = "auto")

#作成したワークブックをエクセルで確認する:openXLコマンド
#エクセルがインストールされている必要があります
#エクセルから保存できます
openXL(NewWb)

#ワークブックを作業ディレクトリに保存:saveWorkbookコマンド
saveWorkbook(wb = NewWb, "Test.xlsx", overwrite = TRUE)

エクセルの出力例

・GADataを体裁整え出力
一部データを隠しています。
GAData

・シートが複数あるエクセルファイルを作成するコマンド例
NewWb


少しでも、あなたのウェブや実験の解析が楽になりますように!!

スポンサードリンク

おすすめコンテンツ


スポンサードリンク