Loading googlesheets directly to R

It is getting more and more common to share data or work colaboratively when collecting and/or analysing data. A useful tool when working with collegues are online solutions. And without saying that this is the best or the only one, I often use google sheet, because many people have access and it is easy to use.

On a course a couple of weeks ago, where we collected a lot of data, I had several students type data into the same goole sheet. The question was then, do I download the table and then import it to R or is is there a direct way. And of course it is possible to import a google sheet directly to R. I discoverd the googlesheets package! It is very easy to use.

You need to have google account. The first time, you need to login to your google account and accept the connection with R. In some cases you will get a code that you have to type a code into the console in R (not sure when and why).

And here some useful functions:

  • The “gs_ls()” function will give you an overview over all the tables you have access to. It is sometimes required that you have the table stored in your own folders (in google drive you can move tables to your own folers).
  • With “gs_title()” the metadata of the sheet is registered. The table is not loaded yet!
  • “gs_ws_ls()” gives you a list of all the worksheets in the sheet you want to load.
  • And finally, “gs_read()” reads the sheet and returns it as data frame. Here you define which sheet you want to load (ss = ) and which workshee (ws = ).
#### IMPORT DATA FROM GOOGLESHEET ####

# install (only the first time) and load library
install.packages("googlesheets")
library("googlesheets")

# Check which tables you have access to
gs_ls()

# Register a google sheet (metadata about the sheet)
Sheet <- gs_title("NameOfGooglesheet")

# list worksheets
gs_ws_ls(Sheet)

# reads the googlsheets and returns as data frame
dat <- gs_read(ss = Sheet, ws = "NameOfSheet") %>% as.tibble()

Code based author and affiliation list

“Everything is possible in R”

This might not be an everyday problem, but doing this task by hand would take forever. And when finished, I would have to start all over again, because of a tiny litte change. I realized, R code is the only solution!

Here is the problem: I am writing a paper with 109 authors. This is a challenging task in itself. But a couple of days ago I realized writing the author list and their affiliations, arranged by the authors last name and numbered affiliations would be a very tedious task. And as soon as it was done, one of the author would tell me about a new affiliation and another one that this affiliation was old and so on. It did not need a lot of persuasion before I opened R and started to type.

Lets assume we have three authors (we keep it simple for now). We will also need to load the tidyverse library, which is not shown here.

# Make a data frame with 4 columns
dat <- data.frame(FirstName = c("Harry James", "Fleur", "Viktor"),
           LastName = c("Potter", "Delacour", "Krum"),
           Affiliation1 = c("Hogwarts School of Witchcraft and Wizardry, UK", "Beauxbatons Academy of Magic, France", "Durmstrang Institute for Macigal Learning, Russia"),
           Affiliation2 = c(NA, "Hogwarts School of Witchcraft and Wizardry, UK", "Hogwarts School of Witchcraft and Wizardry, UK"))
dat 
##     FirstName LastName                                      Affiliation1
## 1 Harry James   Potter    Hogwarts School of Witchcraft and Wizardry, UK
## 2       Fleur Delacour              Beauxbatons Academy of Magic, France
## 3      Viktor     Krum Durmstrang Institute for Macigal Learning, Russia
##                                     Affiliation2
## 1                                           <NA>
## 2 Hogwarts School of Witchcraft and Wizardry, UK
## 3 Hogwarts School of Witchcraft and Wizardry, UK

The next step is to prepare the table for what we want to do. Here you can rename columns, filter the table, rearange it etc. For this table we only want to merge the 2 columns containing the affiliations into a single column. We will use “gather” for this.

# Prepare data
dat <- dat %>% 
  # gather all affiliations in one column
  gather(key = Number, value = Affiliation, Affiliation1,  Affiliation2) %>%
  # remove rows with no Affiliations
  filter(!is.na(Affiliation))

Then we need to do the following:

  • Arrange the column by last name
  • Extract the initials and add a dot at the end of each letter
  • Add a column ID to the data frame from 1 to n
  • Then we replace the ID in rows with the same affiliation with the lowest ID number
  • The previous step might have left some gaps in that we could have ID 1, 3 and 4. So the next step is to change the IDs to 1, 2 and 3. For this we use the little function rankID
  • Finally, we paste the last and initials
# Function to get affiliations ranked from 1 to n (this function was found on Stack Overflow)
rankID <- function(x){
  su=sort(unique(x))
  for (i in 1:length(su)) x[x==su[i]] = i
  return(x)
}


NameAffList <- dat %>% 
  arrange(LastName, Affiliation) %>% 
  rowwise() %>% 
  # extract the first letter of each first name and put a dot after each letter
  mutate(
    Initials = paste(stringi::stri_extract_all(regex = "\\b([[:alpha:]])", str = FirstName, simplify = TRUE), collapse = ". "),
    Initials = paste0(Initials, ".")) %>%
  ungroup() %>% 
  # add a column from 1 to n
  mutate(ID = 1:n()) %>%
  group_by(Affiliation) %>% 
  # replace ID with min number (same affiliations become the same number)
  mutate(ID = min(ID)) %>% 
  ungroup() %>% 
  # use function above to assign new ID from 1 to n
  mutate(ID = rankID(ID)) %>%
  #Paste Last and Initials
  mutate(name = paste0(LastName, ", ", Initials)) 

The last thing we need to do is to print a list with all the names + IDs and one with all the affiliations + IDs.

# Create a list with all names
NameAffList %>%   
  group_by(LastName, name) %>% 
  summarise(affs = paste(ID, collapse = ",")) %>% 
  mutate(
    affs = paste0("^", affs, "^"),
    nameID = paste0(name, affs)     
         ) %>% 
  pull(nameID) %>% 
  paste(collapse = ", ")
## [1] "Delacour, F.^1,2^, Krum, V.^3,2^, Potter, H. J.^2^"
# Create a list with all Affiliations
NameAffList %>% 
  distinct(ID, Affiliation) %>% 
  arrange(ID) %>% 
  mutate(ID = paste0("^", ID, "^")) %>% 
  mutate(Affiliation2 = paste(ID, Affiliation, sep = "")) %>% 
  pull(Affiliation2) %>% 
  paste(collapse = ", ")
## [1] "^1^Beauxbatons Academy of Magic, France, ^2^Hogwarts School of Witchcraft and Wizardry, UK, ^3^Durmstrang Institute for Macigal Learning, Russia"

Et voilà! Names and affiliations:

Delacour, F.1,2 Krum, V.3,2 Potter, H. J.2

1Beauxbatons Academy of Magic, France, 2Hogwarts School of Witchcraft and Wizardry, UK, 3Durmstrang Institute for Macigal Learning, Russia

Here is one final trick! If this list is used in a paper, the IDs for the affiliations should be superscripts. This can of course be done manually, but again, with 109 authors… So, this is why I added the ^ before and after the numbers. If you copy the name and affiliation lists into an R markdown file and run it (or produce them directly in an R markdown file), the numbers will become superscript.

Thank you Richard Telford for helping with this code and generally stimulating conversations about coding.

The devil is in the detail

The devil is in the detail: Nonadditive and context‐dependent plant population responses to increasing temperature and precipitation

Plants marked with toothpicks for demographic study. Picture: Siri Lie Olsen

In climate change ecology, simplistic research approaches may yield unrealistically simplistic answers to often more complicated problems. In particular, the complexity of vegetation responses to global climate change begs a better understanding of the impacts of concomitant changes in several climatic drivers, how these impacts vary across different climatic contexts, and of the demographic processes underlying population changes. Using a replicated, factorial, whole‐community transplant experiment, we investigated regional variation in demographic responses of plant populations to increased temperature and/or precipitation. Across four perennial forb species and 12 sites, we found strong responses to both temperature and precipitation change. Changes in population growth rates were mainly due to changes in survival and clonality. In three of the four study species, the combined increase in temperature and precipitation reflected nonadditive, antagonistic interactions of the single climatic changes for population growth rate and survival, while the interactions were additive and synergistic for clonality. This disparity affects the persistence of genotypes, but also suggests that the mechanisms behind the responses of the vital rates differ. In addition, survival effects varied systematically with climatic context, with wetter and warmer + wetter transplants showing less positive or more negative responses at warmer sites. The detailed demographic approach yields important mechanistic insights into how concomitant changes in temperature and precipitation affect plants, which makes our results generalizable beyond the four study species. Our comprehensive study design illustrates the power of replicated field experiments in disentangling the complex relationships and patterns that govern climate change impacts across real‐world species and landscapes.