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

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

Excelファイルの読み込み

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

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

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

Excelファイル書き出し

単純にテーブルをそのまま出力するだけ

書式など細かいカスタマイズをせず、単にテーブルを出力するだけであればread.xlsx()関数を使えばいい。
出力したxlsxファイルを開くのがopenXL()関数である。

複数のテーブルをシート別にまとめて1個のファイルで出力し、それを開く場合

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

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

mail_delivery.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()

書き込むと同時にファイルを開く。

回帰分析の結果も

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

いくつかの分析結果オブジェクトを直接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)

データをExcel上で編集する

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

XLConnect::xlcEdit(iris)

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

データの加工や分析で使うRの使い方 の記事一覧