How to import a bunch of Excel files with multiple sheets

The Problem

I have been recently conducting an evaluation study in the field of social work with elderly people. With the purpose to provide advice to elderly peoply regarding age-related questions about housing, home care etc, 10 offices for senior citizens were founded. Each of the offices is requested to document its activities (number of persons, events etc.) on a monthly basis. The data need to be entered in a prestructured Excel table. Since the offices started working about 2.5 years ago, I needed to handle 300 Excel sheets (30 months * 10 offices).

In a first step, I decided to create one Excel file for every office each containing 30 sheets (one sheet per month). While the files are named after the offices (abbreviated with 4 characters), the sheets are named after the following pattern: YYYY.MM (year followed by month).

The Solution

Since I did not find a solution for my problem with the packages I usually use to import Excel files into R (xlsx, readxl), I searched the internet for help. Fortunatelly, I found the paper “How to import and merge many Excel files; each with multiple sheets of data for statistical analysis.” by Jon Starkweather. The paper is really worth reading and gives a very comprehensive description on the subject matter. The following code snippets stem from Starkweather's paper.

In a first step, we have to load the following packages:

library(rJava)
library(XLConnect, pos = 4)

In a second step, we define the file type, we want to import (.xls), save the sheet names of the Excel files into a new vector called sheet.names (since the sheet names in each of the files are identical, we may extract them from any of the 10 files) and create another vector (e.names) containing the names for the variables we want to import (in this case 28).

file.names <- list.files(pattern='*.xls')
sheet.names <- getSheets(loadWorkbook('Name.xls'))
e.names <- paste0(rep('v', 28), c(1:28))

In a thirt step, we create a data frame with 28 variables, named
v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15, v16, v17, v18, v19, v20, v21, v22, v23, v24, v25, v26, v27, v28
and one row containing NAs only.

data.1 <- data.frame(matrix(rep(NA,length(e.names)),
                            ncol = length(e.names)))
names(data.1) <- e.names

Finally, we use 2 for-loops to import all the files and sheets and bind them to a data frame we can use for analysis.

for (i in 1:length(file.names)) {
    wb <- loadWorkbook(file.names[i])
    for (j in 1:length(sheet.names)) {
        ss <- readWorksheet(wb, sheet.names[j], startCol = 2, header = TRUE)
        condition <- rep(sheet.names[j], nrow(ss))
        sub.id <- rep(file.names[i], nrow(ss))
        s.frame <- seq(1:nrow(ss))
        df.1 <- data.frame(sub.id, condition, s.frame, ss)
        names(df.1) <- e.names
        data.1 <- rbind(data.1, df.1)
        rm(ss, condition, s.frame, sub.id, df.1)
    }
    rm(wb)
}

In the mentioned paper, Jon Starkweather elaborates in detail on what each line of each for-loop is doing.

Import von Google-Trends-Zeitreihen nach R

Eine der zahlreichen Anwendungen, die Google kostenlos zur Verfügung stellt, heißt Google Trends. Wie man auf Wikipedia nachlesen kann, handelt es sich dabei um einen Internetdienst, der “Informationen darüber bereitstellt, welche Suchbegriffe von Nutzern der Suchmaschine Google wie oft eingegeben wurden. Die Ergebnisse werden in Relation zum totalen Suchaufkommen gesetzt und sind in wöchentlicher Auflösung seit Anfang 2004 für die gesamte Welt oder einzelne Regionen verfügbar.”

Die von Google Trends errechneten Skalenwerte können – abhängig von der Popularität des Begriffs – einen Wert zwischen 0 und 100 annehmen, wobei höhere Werte eine größere Popularität anzeigen. Darüber hinaus finden sich für Interpretation der Skalenwerte auf den Seiten von Google Trends die folgenden Hinweise:

The numbers […] show total searches for a term relative to the total number of searches done on Google over time. A line trending downward means that a search term’s relative popularity is decreasing. But that does not necessarily mean the total number of searches for that term is decreasing. It just means its popularity is decreasing compared to other searches.

Für die Forschung ist Google Trends interessant, da sich mit dessen Hilfe die Popularität einzelner Begriffe im Zeitablauf abbilden lässt. Zum Beispiel lässt sich somit, wie eine Studie zeigte, der Auspruch einer Grippe-Epidemie erkennen.

Erleichtert wird die Analyse dieser Daten durch eine von Google bereitgestellte Schnittstelle (API), die sich mit dem Statistikprogramm GNU R ansteuern lässt.

In der sehr lesenswerten Blogpost GTrendsR package to Explore Google trending for Field Dependent Terms erklärt Tyler Rinker, wie man mit GNU R Google Trends-Daten importiert und graphisch darstellt.

In dieser Blogpost zeige ich, wie man Google Trends-Daten in R importiert und in einem Dataframe abspeichert. An der Analyse der Daten arbeite ich gerade.

Installieren der R-Pakete

Da die beiden für den Zugriff auf die Google Trends-API benötigten R-Pakete sich nicht auf dem CRAN-, sondern auf dem Github-Repository befinden, benötigt man für die Installation das devtools-Paket. Sobald dieses Paket installiert ist, lassen sich auch die Pakete GTrendsR und gtrend installieren.

install.packages('devtools', dep=TRUE)
devtools::install_github("dvanclev/GTrendsR")
devtools::install_github("trinker/gtrend")

Laden der R-Pakete

Im nächsten Schritt werden die für den Zugriff auf die Google Trends-API benötigten Pakete (gtrend und GTrendsR) geladen.

library(gtrend)
library(GTrendsR)

Datenimport

Im nächsten Code-Schnipsel werden die für die Analyse relevanten Begriffe festgelegt und in dem Vektor terms gespeichert. Mit der Funktion gtrend_scraper werden nun die Google Trends-Daten importiert. Diese Funktion erforert zwingend die Angabe einer Gmail-Adresse und des zugehörigen Passworts sowie der Suchbegriffe (terms). Darüber hinaus lässt sich mit der Angabe geo einstellen, auf welches Land sich die Suche beschränken soll. In diesem Beispiel habe ich mit ‘DE’ Deutschland ausgewählt (andere Länder: ‘US’=USA, ‘FR’=Frankreich, ‘UK’=Großbritannien, etc.).

terms <- c("Kino", "Theater", "Oper")
out <- gtrend_scraper("youremail@gmail.com", "password", terms, geo = 'DE')

Abschließend wird das Ergebnis der Suchabfrage in einem dataframe abgespeichert.

df.trends <- trend2long(out)

Mit der Funktion kable() aus dem R-Paket knitr und der head()-Funktion lassen sich die ersten 10 Zeilen des Datensatzes tabellarisch darstellen:

knitr::kable(head(df.trends), align = 'c')
term start end trend
Kino 2004-01-04 2004-01-10 64
Kino 2004-01-11 2004-01-17 49
Kino 2004-01-18 2004-01-24 48
Kino 2004-01-25 2004-01-31 50
Kino 2004-02-01 2004-02-07 51
Kino 2004-02-08 2004-02-14 52

Wie man sieht, enthält der Datensatz 4 Variablen:

  • term enthält die Suchbegriffe;
  • start und end sind Datumsvariablen und markieren Anfang und Ende des Messzeitraums;
  • trend speichert den Scorewert mit einem Wertebereich von 0 bis 100.

readxl – a new r package to import data from excel

There are many ways to import data from excel spreadsheets into R (see ‘R Tutorial on Reading and Importing Excel Files into R’).

With Hadley Wickham’s recent r-package readxl, it is possible to import large excel files very quickly.

library(readxl)
# example 1
mydata <- read_excel("excelfile.xls", sheet = "sheet1")

# example 2
mydata <- read_excel("C:/myfiles/excelfile.xlsx", sheet = 1)

# example 3
mydata <- read_excel("excelfile.xlsx", sheet = 1, na = "99")

Example 1 loads the sheet sheet1 from excel file named excelfile.xls
The code examples are quite straightforward and easy to understand. The sheet we wish to import maybe specified either as string (the name of a sheet, see example 1) or as integer (the position of the sheet, see example 2).

In case the excel file we wish to import is not located in the current working directory, we need to specify the path to the excel file (see example 2).

Moreover, it is possible to specify a missing value (only one). In example 3, the value 99 will be treated as missing value.

Since the readxl package has been published only very recently, it is rather likely that the author will add some more features in the future.