シートの幾何学

library(readxl)

readxl::read_excel() は、セルの矩形からデータをデータフレームとして、より具体的には [tibble] (http://tibble.tidyverse.org/reference/tibble.html) として R に取り込みます。

データ矩形の範囲は，様々な方法で決定することができます．

今のところ、幾何学を制御するときに read_excel() がどのように見るかをいくつか挙げておきます。

read_excel("yo.xlsx", skip = 5)
read_excel("yo.xlsx", n_max = 100)
read_excel("yo.xlsx", skip = 5, n_max = 100)
read_excel("yo.xlsx", range = "C1:E7")
read_excel("yo.xlsx", range = cell_rows(6:23))
read_excel("yo.xlsx", range = cell_cols("B:D"))
read_excel("yo.xlsx", range = anchored("C4", dim = c(3, 2)))

Excel の知られざる事実

readxl の動作やインターフェースは、Excel について以下のことを理解していると、より理解しやすくなるかもしれません。

見えているセルが必ずしも存在するとは限らない。空白に見えるセルは、必ずしもそうではない。

Excel のファイルには、多くの情報の中で、当然、各セルの情報が含まれていなければならない。ここでは、1セル分の情報を「項目」という言葉で表現することにする。

Excel の画面上にセルが表示されているからといって、ファイルに対応する項目があるとは限らない。なぜか？なぜなら、エクセルは巨大なグリッドキャンバスに書き込むようになっているからです。しかし、実際にセルにデータを書き込むまでは、そのセルは実在しないのです。

セルアイテムの流れは、既存のセルを左上から右下へ、行を追って記述していきます。空白のセルは、その中に存在しないだけです。

でも、空白のセルってなんだろう？肉眼では空白に見えるセルもありますが、Excel ではそうみなされず、実際にセルアイテムで表現されています。これは、セルが内容を持たず、関連する書式を持っている場合に起こります。この書式は、1つのセルに直接適用されていることもあれば、行や列全体に適用された書式を経由して間接的に適用されていることもよくあります。人間がスプレッドシートにある程度の時間を費やすと、一見何もないように見えるセルの多くに書式が適用され、その結果、関連するセル項目が存在するようになります。

readxl の意味するところ

readxl は、内容を持つセル項目のみを読み取ります。書式を厳密に伝えるために存在するセル項目は無視される。

readxl が返す tibble は、スプレッドシート内で空のセルをカバーし、NA で埋め尽くします。しかし、それは、関連する行や列が存在する他の理由、つまり実際のデータやユーザが指定した形状があったからに他なりません。

skip と n_max について

skip と n_max は、データの矩形を制御するための “入門レベル” のソリューションです。これらは行方向にのみ動作します。列方向では、readxl にどの列にデータが格納されるかを判断させることになります。

もし range (以下で説明) を指定した場合は、 skip と n_max は無視されます。

n_max

n_max 引数は、 read_excel() がデータの矩形を見つけたら、最大で n_max 行だけ読み込むように指示します。n_max は特にデータについてであることに注意してください。また、 col_names を使用して、スプレッドシートの最初の行を使用して列名を作成するかどうかを指定することができます (デフォルトは TRUE です)。

n_max = 2 とすると、 geometry.xlsx の最後のデータ行（3行目）は無視されます。

read_excel(readxl_example("geometry.xlsx"), n_max = 2)
#> # A tibble: 2 × 3
#>   B3    C3    D3   
#>   <chr> <chr> <chr>
#> 1 B4    C4    D4   
#> 2 B5    C5    D5

n_max は上限の指定です。ただし、tibble に空行が含まれることはありません。n_max の方がはるかに大きいにもかかわらず、ここでは 3 つのデータ行を取得していることに注意してください。

read_excel(readxl_example("geometry.xlsx"), n_max = 1000)
#> # A tibble: 3 × 3
#>   B3    C3    D3   
#>   <chr> <chr> <chr>
#> 1 B4    C4    D4   
#> 2 B5    C5    D5   
#> 3 B6    C6    D6

range

range 引数はジオメトリを制御する最も柔軟な方法で、cellranger パッケージによって提供されています。

skip や n_max との大きな違いは、 range は文字通りの意味を持っていることです。たとえそれが、返されるティブルが行や列全体が NA で構成されていることを意味するとしてもです。

セルリミットは様々な方法で記述することができます。

Excel 形式の範囲。range = "A1:D4" または range = "R1C1:R4C4" で固定矩形を指定します。ワークシート名を前につけることもできます。range = "foofy!A1:D4"とすると、 sheet 引数にその名前が渡されます。

サンプルシートの deaths.xlsx は、データ矩形の前後にジャンクな行があるのが特徴です。データ矩形を正確に指定することの利点は、列のタイプを正しく推測して、欲しいデータフレームを得ることができることです。

read_excel(readxl_example("deaths.xlsx"), range = "arts!A5:F15")
#> # A tibble: 10 × 6
#>   Name       Profession   Age `Has kids` `Date of birth`     `Date of death`    
#>   <chr>      <chr>      <dbl> <lgl>      <dttm>              <dttm>             
#> 1 David Bow… musician      69 TRUE       1947-01-08 00:00:00 2016-01-10 00:00:00
#> 2 Carrie Fi… actor         60 TRUE       1956-10-21 00:00:00 2016-12-27 00:00:00
#> 3 Chuck Ber… musician      90 TRUE       1926-10-18 00:00:00 2017-03-18 00:00:00
#> 4 Bill Paxt… actor         61 TRUE       1955-05-17 00:00:00 2017-02-25 00:00:00
#> # … with 6 more rows

視覚的な参考のために geometry.xlsx のスクリーンショットを繰り返します。

geometry.xlsxに戻り、ここではデータの一部だけが重なる矩形を指定します。最初の行のセルは空で、先頭の列は NA であるため、デフォルトの列名を使用していることに注意してください。

read_excel(readxl_example("geometry.xlsx"), range = "A2:C4")
#> New names:
#> * `` -> ...1
#> * `` -> ...2
#> * `` -> ...3
#> # A tibble: 2 × 3
#>   ...1  ...2  ...3 
#>   <lgl> <chr> <chr>
#> 1 NA    B3    C3   
#> 2 NA    B4    C4

行または列の特定範囲。行または列のみに正確なリミットを設定し、他の方向のリミットは発見できるようにします。呼び出しの例

## 行のみ
read_excel(..., range = cell_rows(1:10))
## これと同じです
read_excel(..., range = cell_rows(c(1, 10)))

## 列のみ
read_excel(..., range = cell_cols(1:26))
## 以下のものと同じです
read_excel(..., range = cell_cols(c(1, 26)))
read_excel(..., range = cell_cols("A:Z"))
read_excel(..., range = cell_cols(LETTERS))
read_excel(..., range = cell_cols(c("A", "Z"))

ここでは、geometry.xlsx を使って、行にハードリミットを設定し、列のリミットを発見できるようにしながら、データを過去に走らせることをデモします。最後の行が NA であることに注意してください。

read_excel(readxl_example("geometry.xlsx"), range = cell_rows(4:8))
#> # A tibble: 4 × 3
#>   B4    C4    D4   
#>   <chr> <chr> <chr>
#> 1 B5    C5    D5   
#> 2 B6    C6    D6   
#> 3 <NA>  <NA>  <NA> 
#> 4 <NA>  <NA>  <NA>

アンカー付き矩形。ヘルパー関数 anchored() と cell_limits() により、矩形のコーナーでリミットを指定することができます。

ここでは、セル C5 を左上隅とする3×4の矩形を得ます。

read_excel(
  readxl_example("geometry.xlsx"),
  col_names = paste("var", 1:4, sep = "_"),
  range = anchored("C5", c(3, 4))
)
#> # A tibble: 3 × 4
#>   var_1 var_2 var_3 var_4
#>   <chr> <chr> <lgl> <lgl>
#> 1 C5    D5    NA    NA   
#> 2 C6    D6    NA    NA   
#> 3 <NA>  <NA>  NA    NA

ここでは、C5 を左上に設定し、その他のリミットを発見できるようにしました。

read_excel(
  readxl_example("geometry.xlsx"),
  col_names = FALSE,
  range = cell_limits(c(5, 3), c(NA, NA))
)
#> New names:
#> * `` -> ...1
#> * `` -> ...2
#> # A tibble: 2 × 2
#>   ...1  ...2 
#>   <chr> <chr>
#> 1 C5    D5   
#> 2 C6    D6