1--- 2title: "Sheet Geometry" 3output: rmarkdown::html_vignette 4vignette: > 5 %\VignetteIndexEntry{Sheet Geometry} 6 %\VignetteEngine{knitr::rmarkdown} 7 %\VignetteEncoding{UTF-8} 8--- 9 10```{r include = FALSE} 11knitr::opts_chunk$set( 12 collapse = TRUE, 13 comment = "#>" 14) 15options(tibble.print_min = 4, tibble.print_max = 4) 16``` 17 18```{r setup} 19library(readxl) 20``` 21 22`readxl::read_excel()` brings data from a rectangle of cells into R as a data frame or, more specifically, a [tibble](http://tibble.tidyverse.org/reference/tibble.html). 23 24The extent of the data rectangle can be determined in various ways: 25 26 * **Discovered**: By default, `read_excel()` uses the smallest rectangle that contains the non-empty cells. It "shrink wraps" the data. 27 * **Bounded**: The `skip` and `n_max` arguments constrain `read_excel()`'s discovery process with respect to rows. At least `skip` spreadsheet rows will be skipped or ignored and at most `n_max` spreadsheet rows will be considered as data. Compared to the default of **discovery**, these arguments can only lead to making the output tibble smaller. 28 * **Set**: The `range` argument is taken literally, even if that means you will have leading or trailing rows or columns filled with `NA`. If you ask for `range = "A1:D4"`, you are guaranteed to get a tibble with 4 columns (A through D) and either 3 rows (`col_names = TRUE`, default) or 4 rows (`col_names = FALSE`). 29 * **Mixed**: In typical use, `read_excel()`'s geometry arguments often imply that certain limits are **discovered** while others are **bounded** or **set**. This will be more clear in the concrete examples below. 30 31For now, here are a few ways `read_excel()` can look when you take control of the geometry: 32 33```{r eval = FALSE} 34read_excel("yo.xlsx", skip = 5) 35read_excel("yo.xlsx", n_max = 100) 36read_excel("yo.xlsx", skip = 5, n_max = 100) 37read_excel("yo.xlsx", range = "C1:E7") 38read_excel("yo.xlsx", range = cell_rows(6:23)) 39read_excel("yo.xlsx", range = cell_cols("B:D")) 40read_excel("yo.xlsx", range = anchored("C4", dim = c(3, 2))) 41``` 42 43## Little known Excel facts 44 45readxl's behavior and interface may be easier to understand if you understand this about Excel: 46 47> Cells you can see don't necessarily exist. Cells that look blank aren't necessarily so. 48 49Among lots of other information, Excel files obviously must contain information on each cell. Let's use the word "item" to denote one cell's-worth of info. 50 51Just because you see a cell on the screen in Excel, that doesn't mean there's a corresponding item on file. Why? Because Excel presents a huge gridded canvas for you to write on. Until you actually populate a cell, though, it doesn't really exist. 52 53The stream of cell items describes the existing cells, going from upper left to lower right, travelling by row. Blank cells simply do not exist in it. 54 55Ah, but what is a blank cell? Some cells appear blank to the naked eye but are not considered so by Excel and, indeed, are represented by a cell item. This happens when a cell has no content but does have an associated format. This format could have been applied directly to a single cell or, more often, indirectly via formatting applied to an entire row or column. Once a human has spent some quality time with a spreadsheet, many seemingly empty cells will bear a format and will thus have an associated cell item. 56 57### Implications for readxl 58 59readxl only reads cell items that have content. It ignores cell items that exist strictly to convey formatting. 60 61The tibble returned by readxl will often cover cells that are empty in the spreadsheet, filled with `NA`. But only because there was some other reason for the associated row or column to exist: actual data or user-specified geometry. 62 63## `skip` and `n_max` 64 65`skip` and `n_max` are the "entry-level" solution for controlling the data rectangle. They work only in the row direction. Column-wise, you're letting readxl discover which columns are populated. 66 67If you specify `range` (covered below), `skip` and `n_max` are ignored. 68 69### `skip` 70 71The `skip` argument tells `read_excel()` to start looking for populated cells after skipping at least `skip` rows. If the new start point begins with 1 or more empty rows, `read_excel()` will skip even more before it starts reading from the sheet. 72 73Here's a screen shot of the `geometry.xlsx` example sheet that ships with readxl, accessible via `readxl_example("geometry.xlsx")`. 74 75```{r out.width = '70%', echo = FALSE} 76knitr::include_graphics("img/geometry.png") 77``` 78 79By default, `read_excel()` just discovers the data rectangle: 80 81```{r} 82read_excel(readxl_example("geometry.xlsx")) 83``` 84 85If you explicitly skip one row, note that `read_excel()` still skips row 2, which is also empty, leading to the same result as before: 86 87```{r} 88read_excel(readxl_example("geometry.xlsx"), skip = 1) 89``` 90 91You can also use `skip` to skip over populated cells. In real life, this is a mighty weapon against the explanatory text that people like to include at the top of spreadsheets. 92 93```{r} 94read_excel(readxl_example("geometry.xlsx"), skip = 3) 95``` 96 97Summary: `skip` tells `read_excel()` to skip *at least this many* spreadsheet rows before reading anything. 98 99### `n_max` 100 101The `n_max` argument tells `read_excel()` to read at most `n_max` rows, once it has found the data rectangle. Note that `n_max` is specifically about *the data*. You still use `col_names` to express whether the first spreadsheet row should be used to create column names (default is `TRUE`). 102 103`n_max = 2` causes us to ignore the last data row -- the 3rd one -- in `geometry.xlsx`. 104 105```{r} 106read_excel(readxl_example("geometry.xlsx"), n_max = 2) 107``` 108 109`n_max` is an upper bound. It will never cause empty rows to be included in the tibble. Note how we get 3 data rows here, even though `n_max` is much greater. 110 111```{r} 112read_excel(readxl_example("geometry.xlsx"), n_max = 1000) 113``` 114 115## `range` 116 117The `range` argument is the most flexible way to control geometry and is powered by the [cellranger](https://github.com/rsheets/cellranger#readme) package. 118 119One huge difference from `skip` and `n_max` is that `range` is taken literally! Even if it means the returned tibble will have entire rows or columns consisting of `NA`. 120 121You can describe cell limits in a variety of ways: 122 123**Excel-style range**: Specify a fixed rectangle with `range = "A1:D4"` or `range = "R1C1:R4C4"`. You can even prepend the worksheet name like so: `range = "foofy!A1:D4"` and it will be passed along to the `sheet` argument. 124 125The `deaths.xlsx` example sheet features junk rows both before and after the data rectangle. The payoff for specifying the data rectangle precisely is that we get the data frame we want, with correct guesses for the column types. 126 127```{r} 128read_excel(readxl_example("deaths.xlsx"), range = "arts!A5:F15") 129``` 130 131We repeat the screenshot of `geometry.xlsx` as a visual reference. 132 133```{r out.width = '70%', echo = FALSE} 134knitr::include_graphics("img/geometry.png") 135``` 136 137Going back to `geometry.xlsx`, here we specify a rectangle that only partially overlaps the data. Note the use of default column names, because the first row of cells is empty, and the leading column of `NA`s. 138 139```{r} 140read_excel(readxl_example("geometry.xlsx"), range = "A2:C4") 141``` 142 143**Specific range of rows or columns**: Set exact limits on just the rows or just the columns and allow the limits in the other direction to be discovered. Example calls: 144 145```{r eval = FALSE} 146## rows only 147read_excel(..., range = cell_rows(1:10)) 148## is equivalent to 149read_excel(..., range = cell_rows(c(1, 10))) 150 151## columns only 152read_excel(..., range = cell_cols(1:26)) 153## is equivalent to all of these 154read_excel(..., range = cell_cols(c(1, 26))) 155read_excel(..., range = cell_cols("A:Z")) 156read_excel(..., range = cell_cols(LETTERS)) 157read_excel(..., range = cell_cols(c("A", "Z")) 158``` 159 160We use `geometry.xlsx` to demonstrate setting hard limits on the rows, running past the data, while allowing column limits to discovered. Note the trailing rows of `NA`. 161 162```{r} 163read_excel(readxl_example("geometry.xlsx"), range = cell_rows(4:8)) 164``` 165 166**Anchored rectangle**: Helper functions `anchored()` and `cell_limits()` let you specify limits via the corner(s) of the rectangle. 167 168Here we get a 3 by 4 rectangle with cell C5 as the upper left corner: 169 170```{r} 171read_excel( 172 readxl_example("geometry.xlsx"), 173 col_names = paste("var", 1:4, sep = "_"), 174 range = anchored("C5", c(3, 4)) 175) 176``` 177 178Here we set C5 as the upper left corner and allow the other limits to be discovered: 179 180```{r} 181read_excel( 182 readxl_example("geometry.xlsx"), 183 col_names = FALSE, 184 range = cell_limits(c(5, 3), c(NA, NA)) 185) 186``` 187 188