Marketechlabo

RとExcel（ファイルの読み書き、データフレームをExcelで編集）

calendar May 17, 2018 / · 1 分で読了 · RExcelopenxlsx  ·
共有する: linkedin copy

概要

RでExcelファイル（.xlsx）を読み書きするライブラリはいくつかあるが、openxlsxが多機能でかつ使いやすい。Javaも不要なのでインストールや動作も軽い。古いファイル（.xls）には非対応だが、現行のファイル（.xlsx）のみ対応でよければこれがおすすめ。

read.xlsx()関数を使う。 たとえばファイルC:/Users/fukuyama/Documents/master.xlsxのシート「ユーザ」を読み込む場合

m_user.df <- read.xlsx('C:/Users/fukuyama/Documents/master.xlsx', na.strings = '', sheet = 'ユーザ')
r

na.stringsを指定しないと「NA」という文字列を欠損値扱いする。基本的にはいつも空文字を欠損値扱いするようにしておいたほうが安全である。

書式など細かいカスタマイズをせず、単にテーブルを出力するだけであればwrite.xlsx()関数を使えばいい。 出力したxlsxファイルを開くのがopenXL()関数である。 複数のテーブルをシート別にまとめて1個のファイルで出力し、それを開く場合

list(
  'IRIS' = iris, 
  'CO2' = CO2, 
  'USArrests' = USArrests
) |> write.xlsx(file = 'data.xlsx', rowNames = F)
openXL('data.xlsx')
r
  • ヘッダ colNames = T/F
    デフォルトでヘッダあり、colNames=Fでヘッダなし。
  • 行番号 rowNames = T/F デフォルトで行番号の列なし、rowNames=Tで行番号を付ける。
  • フィルタ withFilter= T/F デフォルトでフィルタは付けない。withFilter=Tでフィルタを付ける。
  • Excelのテーブルフォーマットにする asTable = T/F
    デフォルトではテーブルフォーマットにしないが、asTable=Tでテーブルにする。

パイプでまとめてこういう使い方もできる。

mail_delivery.dt |>
  lazy_dt() |>
  group_by(segment_id) |>
  summarise(
    n = n(), 
    n_open = sum(!is.na(datetime_open)), 
    n_click = sum(!is.na(datetime_click))
  ) |>
  write.xlsx('mail_response.xlsx') |> openXL()
r

書き込むと同時にファイルを開く。 回帰分析の結果も

write.xlsx(mail.glm, file = 'mail_regression.xlsx')
r

いくつかの分析結果オブジェクトを直接Excelファイルにすることができる。

書式などを細かく指定するなら

  1. createWorkbook()でワークブックインスタンスを作り
  2. addWorksheet()でシートを作り
  3. writeData()でデータを出力し
  4. addStyle()などで書式を設定し
  5. saveWorkbook()で書き込みコミットする
# ワークブックインスタンスの生成
wb <- createWorkbook()

# ワークシートの追加
addWorksheet(wb, 'カテゴリマスタ')
addWorksheet(wb, '商品マスタ')
addWorksheet(wb, 'カテゴリ別新規購入率')
addWorksheet(wb, 'カテゴリ別リピート率')

# データの出力
writeData(wb, sheet = 1, rowNames = T, category.dt)
writeData(wb, sheet = 2, rowNames = T, product.dt)
writeData(wb, sheet = 3, rowNames = T, new_purchase.dt)
writeData(wb, sheet = 4, rowNames = T, repeat.dt)

# 書式（フォントサイズ、カラー、フォント名）
modifyBaseFont(wb, fontSize = 11, fontColour = "#000000", fontName = "MS PGothic")

# 条件付き書式
for (.i in c(3,4)) {
  # 範囲指定して値のフォーマットを指定
  addStyle(wb, .i, cols=2:100, rows=2:100, gridExpand = T, style = createStyle(numFmt = '0.0%'))
  # 条件付き書式
  conditionalFormatting(wb, .i, cols=2:100, rows=2:100, rule="<0.01", gridExpand = T, style = createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE"))
}

# 保存
saveWorkbook(wb, "products.xlsx", overwrite = TRUE)
r

Excelファイルの入出力ではないが、Rのデータ（データフレームや行列）をExcel上で編集することができる。 表計算のグリッドが使えるので操作性は抜群。RのCUIの欠点であるデータが見づらい、編集しにくいという点を完全に補ってくれるので知っておくと大変便利。 パッケージ{XLConnect}をインストールして以下のコマンドを実行するだけ。XLConnectはJavaに依存しており、近年のRユーザ環境ではJavaのセットアップがハードルになることがある。

XLConnect::xlcEdit(iris)
r

Excelが起動するので、編集後保存をしてExcelを閉じればデータが書き換わっている。保存をしなければデータを見るだけになる。

openxlsxは現行でメンテナンスされているが、openxlsx2が後継として開発されている。新規に導入する場合はopenxlsx2の利用も検討するとよい。