I'm using the googlesheets package to get data from a Google Sheet and want to append the sheet name as a column in the data.frame.
For instance, with the public Gapminder sheet:
library(googlesheets)
library(dplyr)
my_dat <- gs_gap() %>% # register public Gapminder practice sheet
gs_read(ws = 1) %>%
mutate(source_dat = "Africa")
My last line does this manually, but I want to do it automatically, i.e., populate a variable source_dat with the name of the sheet. (Yes, I know there's already a column with that info - this is just an example). Is there a way to reference the active sheet name?
Right now only cell feed reading functions attach the worksheet name as an attribute. The second solution shows how you could use that. If you open an issue on github, I could add that to all read methods.
You could also store the name of the ws in advance, as in the first solution.
library(googlesheets)
library(dplyr)
ws <- "Africa"
my_dat <- gs_gap() %>% # register public Gapminder practice sheet
gs_read(ws = ws) %>%
mutate(source_dat = ws)
#> Accessing worksheet titled 'Africa'.
#> No encoding supplied: defaulting to UTF-8.
my_dat
#> Source: local data frame [624 x 7]
#>
#> country continent year lifeExp pop gdpPercap source_dat
#> (chr) (chr) (int) (dbl) (int) (dbl) (chr)
#> 1 Algeria Africa 1952 43.077 9279525 2449.008 Africa
#> 2 Algeria Africa 1957 45.685 10270856 3013.976 Africa
#> 3 Algeria Africa 1962 48.303 11000948 2550.817 Africa
#> 4 Algeria Africa 1967 51.407 12760499 3246.992 Africa
#> 5 Algeria Africa 1972 54.518 14760787 4182.664 Africa
#> 6 Algeria Africa 1977 58.014 17152804 4910.417 Africa
#> 7 Algeria Africa 1982 61.368 20033753 5745.160 Africa
#> 8 Algeria Africa 1987 65.799 23254956 5681.359 Africa
#> 9 Algeria Africa 1992 67.744 26298373 5023.217 Africa
#> 10 Algeria Africa 1997 69.152 29072015 4797.295 Africa
#> .. ... ... ... ... ... ... ...
## currently only gs_read_cellfeed stores ws_title as an attribute
my_dat <- gs_gap() %>% # register public Gapminder practice sheet
gs_read_cellfeed() %>%
{
ws <- attr(., "ws_title")
gs_reshape_cellfeed(.) %>%
mutate(source_dat = ws)
}
#> Accessing worksheet titled 'Africa'.
my_dat
#> Source: local data frame [624 x 7]
#>
#> country continent year lifeExp pop gdpPercap source_dat
#> (chr) (chr) (int) (dbl) (int) (dbl) (chr)
#> 1 Algeria Africa 1952 43.077 9279525 2449.008 Africa
#> 2 Algeria Africa 1957 45.685 10270856 3013.976 Africa
#> 3 Algeria Africa 1962 48.303 11000948 2550.817 Africa
#> 4 Algeria Africa 1967 51.407 12760499 3246.992 Africa
#> 5 Algeria Africa 1972 54.518 14760787 4182.664 Africa
#> 6 Algeria Africa 1977 58.014 17152804 4910.417 Africa
#> 7 Algeria Africa 1982 61.368 20033753 5745.160 Africa
#> 8 Algeria Africa 1987 65.799 23254956 5681.359 Africa
#> 9 Algeria Africa 1992 67.744 26298373 5023.217 Africa
#> 10 Algeria Africa 1997 69.152 29072015 4797.295 Africa
#> .. ... ... ... ... ... ... ...