jenny Sun Feb 28 10:42:37 2016
The second installment in a series: I want to make purrr and dplyr and tidyr play nicely with each other. How can I use purrr for iteration, while still using dplyr and tidyr to manage the data frame side of of the house? The first installment is here: How to obtain a bunch of GitHub issues or pull requests with R. It has a distinct JSON flavor.
This time I'm using those packages, and a nested data frame in particular, to tame some annoying XML from Google spreadsheets.
This is a glorified note-to-self. It might be interesting to a few other people. But I presume a lot of experience with R and a full-on embrace of %>%, dplyr, etc.
At first I tried to NOT use googlesheets here but Sheet registration is so much easier if I use a prepackaged function and there's nothing to learn by doing it by hand. I'm also using @hrbrmstr's xmlview package to pretty print some XML, but note that it really shines for interactive work.
## devtools::install_github("jennybc/googlesheets")
library(googlesheets)
library(httr)
library(xml2)
suppressMessages(library(dplyr))
suppressMessages(library(purrr))
library(tidyr)
## devtools::install_github("hrbrmstr/xmlview")
library(xmlview) ## highly optional for this example!There are three ways to get information out of a Google spreadsheet and today we are dealing with the "list feed", which is arguably the most annoying of the bunch. Why do we use it? Let's just assume it's a job that needs to be done.
What makes it so annoying? It extracts Sheet data row-wise. Each row comes back as an XML node, embedded in all sorts of other nodes that redundantly store information we don't need. But, hey, at least it is all very thoroughly namespaced!
The node for a row contains nodes for each cell, among lots of other junk we don't need. The problem is there will be no node for an empty cell. So even though the list feed talks big about being for the consumption of rectangular data, it doesn't actually implement that very well. The number of cell nodes can vary across the rows. So we need to address that on the R side, as we create a beautiful data frame for the user.
Here is a testing sheet I've made. The purpose of the specific worksheet embedded_empty_cells is to study and test all 3 methods of consumption w/r/t empty cells.
Key features of this pathological Sheet:
- Empty cells in the all important header row
- Random embedded empty cells in the data
- An entirely empty row
- An entirely empty column
- A column with no header BUT with some data in it
FYI: the list feed stops reading once it hits an empty row, though that is not true for other consumption methods.
Register the testing Sheet, get the URL for the list feed of the embedded_empty_cells worksheet, retrieve the data, pull out the XML for the cell contents. Futz with namespaces.
pts_ws_feed <- "https://spreadsheets.google.com/feeds/worksheets/1amnxLg9VVDoE6KSIZvutYkEGNgQyJSnLJgHthehruy8/public/full"
(ss <- gs_ws_feed(pts_ws_feed))
#> Spreadsheet title: test-gs-public-testing-sheet
#> Spreadsheet author: rpackagetest
#> Date of googlesheets registration: 2016-02-28 18:42:38 GMT
#> Date of last spreadsheet update: 2016-02-28 17:21:22 GMT
#> visibility: public
#> permissions: rw
#> version: new
#>
#> Contains 7 worksheets:
#> (Title): (Nominal worksheet extent as rows x columns)
#> embedded_empty_cells: 8 x 7
#> special_chars: 1000 x 26
#> diabolical_column_names: 4 x 8
#> shipwrecks: 1000 x 26
#> for_resizing: 1799 x 30
#> for_updating: 1000 x 26
#> empty: 1000 x 26
#>
#> Key: 1amnxLg9VVDoE6KSIZvutYkEGNgQyJSnLJgHthehruy8
#> Browser URL: https://docs.google.com/spreadsheets/d/1amnxLg9VVDoE6KSIZvutYkEGNgQyJSnLJgHthehruy8/
col_names <- TRUE
ws <- "embedded_empty_cells"
index <- match(ws, ss$ws$ws_title)
the_url <- ss$ws$listfeed[index]
req <- GET(the_url)
rc <- read_xml(content(req, as = "text", encoding = "UTF-8"))
ns <- xml_ns_rename(xml_ns(rc), d1 = "feed")Behold the XML. We'll be going after those entry nodes -- specifically the sub-nodes associated with the gsx namespace. OK not clear if this works in markdown, but leaving here for anyone who actually walks through the example. To see what I see, check out rc.html via rawgit.
xml_view(rc)Create a list. Each component holds a nodeset for one spreadsheet row. Use purrr::map() to do more XPath work to isolate just the nodes that give cell data.
(rows <- rc %>%
xml_find_all("//feed:entry", ns) %>%
map(~ xml_find_all(.x, xpath = "./gsx:*", ns = ns)))
#> [[1]]
#> {xml_nodeset (6)}
#> [1] <gsx:country>Argentina</gsx:country>
#> [2] <gsx:year>1952</gsx:year>
#> [3] <gsx:pop>17876956</gsx:pop>
#> [4] <gsx:_chk2m>Americas</gsx:_chk2m>
#> [5] <gsx:lifeexp>62.485</gsx:lifeexp>
#> [6] <gsx:gdppercap>5911.315053</gsx:gdppercap>
#>
#> [[2]]
#> {xml_nodeset (5)}
#> [1] <gsx:country>Argentina</gsx:country>
#> [2] <gsx:year>1957</gsx:year>
#> [3] <gsx:pop>19610538</gsx:pop>
#> [4] <gsx:lifeexp>64.399</gsx:lifeexp>
#> [5] <gsx:gdppercap>6856.856212</gsx:gdppercap>
#>
#> [[3]]
#> {xml_nodeset (6)}
#> [1] <gsx:country/>
#> [2] <gsx:year>1962</gsx:year>
#> [3] <gsx:pop>21283783</gsx:pop>
#> [4] <gsx:_chk2m>Americas</gsx:_chk2m>
#> [5] <gsx:lifeexp>65.142</gsx:lifeexp>
#> [6] <gsx:gdppercap>7133.166023</gsx:gdppercap>
#>
#> [[4]]
#> {xml_nodeset (6)}
#> [1] <gsx:country>Argentina</gsx:country>
#> [2] <gsx:year>1967</gsx:year>
#> [3] <gsx:pop>22934225</gsx:pop>
#> [4] <gsx:_chk2m>Americas</gsx:_chk2m>
#> [5] <gsx:lifeexp>65.634</gsx:lifeexp>
#> [6] <gsx:gdppercap/>Put this list of nodesets into a list-column of a data frame, along with a variable for row number. Is this really the best way to add row number? dplyr::row_number() was a bit of a disappointment here.
(rows_df <- data_frame(row = seq_along(rows),
nodeset = rows))
#> Source: local data frame [4 x 2]
#>
#> row nodeset
#> (int) (list)
#> 1 1 <S3:xml_nodeset>
#> 2 2 <S3:xml_nodeset>
#> 3 3 <S3:xml_nodeset>
#> 4 4 <S3:xml_nodeset>Here's the problem with the list feed: the number of cells and the implicit column names vary by row, due to the empty cells in the header row.
lengths(rows_df$nodeset)
#> [1] 6 5 6 6The main manipulation:
- Use
purrr::map()insidedplyr::mutate()to unpack the XML.- extract the (API-mangled) column name from the node name
- extract the cell text from the node contents (yes, with the list feed it's always text)
- create a new variable
ias a within-row cell counter
- Use
dplyr::select()to retain only needed variables. This is where we can drop thenodesetlist-column. It has served its purpose. - Use
tidyr::unnest()to achieve this:- one data frame row per row of spreadsheet --> one data frame row per nonempty cell of spreadsheet
(cells_df <- rows_df %>%
mutate(col_name_raw = nodeset %>% map(~ xml_name(.)),
cell_text = nodeset %>% map(~ xml_text(.)),
i = nodeset %>% map(~ seq_along(.))) %>%
select(row, i, col_name_raw, cell_text) %>%
unnest())
#> Source: local data frame [23 x 4]
#>
#> row i col_name_raw cell_text
#> (int) (int) (chr) (chr)
#> 1 1 1 country Argentina
#> 2 1 2 year 1952
#> 3 1 3 pop 17876956
#> 4 1 4 _chk2m Americas
#> 5 1 5 lifeexp 62.485
#> 6 1 6 gdppercap 5911.315053
#> 7 2 1 country Argentina
#> 8 2 2 year 1957
#> 9 2 3 pop 19610538
#> 10 2 4 lifeexp 64.399
#> .. ... ... ... ...We'll stop this note here, because that's the end of the purrr + dplyr + tidyr + xml2 stuff. In real life, there's still alot of manipulation needed to make the data frame a user expects, but it's pretty standard.
devtools::session_info()
#> Session info --------------------------------------------------------------
#> setting value
#> version R version 3.2.3 (2015-12-10)
#> system x86_64, darwin13.4.0
#> ui X11
#> language (EN)
#> collate en_CA.UTF-8
#> tz America/Vancouver
#> date 2016-02-28
#> Packages ------------------------------------------------------------------
#> package * version date
#> assertthat 0.1 2013-12-06
#> cellranger 1.0.0 2015-06-20
#> curl 0.9.5 2016-01-23
#> DBI 0.3.1 2014-09-24
#> devtools 1.10.0 2016-01-23
#> digest 0.6.9 2016-01-08
#> dplyr * 0.4.3.9000 2015-11-24
#> evaluate 0.8 2015-09-18
#> formatR 1.2.1 2015-09-18
#> googlesheets * 0.1.0.9001 2016-02-26
#> htmltools 0.3 2015-12-29
#> htmlwidgets 0.6 2016-02-20
#> httr * 1.1.0.9000 2016-02-12
#> jsonlite 0.9.19 2015-11-28
#> knitr 1.12.6 2016-02-06
#> lazyeval 0.1.10 2015-01-02
#> magrittr 1.5 2014-11-22
#> memoise 0.2.1 2014-04-22
#> purrr * 0.2.0.9000 2016-01-31
#> R6 2.1.2 2016-01-26
#> Rcpp 0.12.3 2016-01-10
#> rmarkdown 0.9.5 2016-02-06
#> stringi 1.0-1 2015-10-22
#> stringr 1.0.0 2015-04-30
#> tidyr * 0.4.1 2016-02-05
#> xml2 * 0.1.2.9000 2016-01-24
#> xmlview * 0.4.7 2016-02-20
#> yaml 2.1.13 2014-06-12
#> source
#> CRAN (R 3.2.0)
#> CRAN (R 3.2.0)
#> CRAN (R 3.2.3)
#> CRAN (R 3.2.0)
#> CRAN (R 3.2.3)
#> CRAN (R 3.2.3)
#> Github (hadley/dplyr@4f2d7f8)
#> CRAN (R 3.2.0)
#> CRAN (R 3.2.0)
#> local
#> CRAN (R 3.2.3)
#> Github (ramnathv/htmlwidgets@5f86cea)
#> Github (hadley/httr@7261a52)
#> CRAN (R 3.2.2)
#> Github (yihui/knitr@37f0531)
#> CRAN (R 3.2.0)
#> CRAN (R 3.2.0)
#> CRAN (R 3.2.0)
#> Github (hadley/purrr@9312764)
#> CRAN (R 3.2.3)
#> CRAN (R 3.2.3)
#> Github (rstudio/rmarkdown@5e0ff09)
#> CRAN (R 3.2.0)
#> CRAN (R 3.2.0)
#> CRAN (R 3.2.3)
#> Github (hadley/xml2@4c4a448)
#> Github (hrbrmstr/xmlview@4e93801)
#> CRAN (R 3.2.0)
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent