ReadXL

Readxl, the tidyverse excel readig package, has some incredibly useful features and loads all the data immediatly into the succint tbl format. You can list sheet names with excel_sheets(), specify ranges to choose how many rows or columns to load in with excel_sheets(data, range = cell_rows(1:10)).

I’ve found this feature extremely useful in order to load all sheets of a file into my global environment with the names intact. See the function below.

All Excel sheets into Global Environment with original names

# Read all excel sheets at once###

read_excel_allsheets <- function(filename) {
    sheets <- readxl::excel_sheets(filename)
    x <-    lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
    names(x) <- paste0(sheets) #Use paste0 function to add to your
    x
}

# Create list of tibbles
MyData <- read_excel_allsheets("~/Your/File/Location/dataset.xls")

# Unlist all tibbles into environment
list2env(MyData ,.GlobalEnv)

Data Table

data.table's fread() and fwrite() functions are fast. Really fast. Which is incredibly useful if you have large files. Recently I came across a file that was incredibly large. Too large for my laptop’s RAM to handle. There are many ways to increase your computer’s computing power like Hadoop or more recently Spark. Sparklyr by Rstudio is a good and useful alternative.

However, I did not have that luxury, considering I was working on a Remote Access server. I turned my attention towards data.table. I did not need the entire file. I only needed a subset of the file, filtered by the contents of another file. This was my approach:

library(data.table)
library(dplyr)

#-------------------------------------------
# Loop to filter the large dataset in parts
#-----------------------------------------
for(i in 1:10){
  FilterData <- fread("Path/To/FilterData.csv")
  
  #Create sequence to split the dataset. In this case 10 folds. 
  #specify the by if you want to split it more ways.
  Div <- seq(from = 0.1, to = 1, by = 0.1) 
  
  #Since we are splitting the .csv file, the first row will no longer be the header. 
  #Specify your own colnames (most of the time this is better anyway)
  ColNames <- c("colname1", "colname2", "etc.")
  
  #Find out the total number of rows in your file
  No_Rows <- "total number of rows"
  
  #Specify x and y
  x <- round(No_Rows * 0.1, 0)
  
  if(Div[i] == 0.1){
    y <- 0}
  else{
    y <- round(No_Rows - (Div[i]-0.1),0)
  }
  
  df <- fread("Path/To/LargeDataFile.csv", 
              nrow = x, skip = y, col.names = ColNames)
  # Filter dataset
  df %>%
  inner_join(y = FilterData, by = "FilterColumnName") %>%
  # Write part of the dataset
  fwrite(file = Paste0("Path/To/Output/Location/", "Part_", i, ".csv")) 
  
  # Clear Environment to make space for next file
  rm(list=ls())
}
#--------------------------------------------------------------
# Binding the dataset back together and writing the final file
#--------------------------------------------------------------
FileNames = list.files(path = "Path/To/Output/Location", pattern = ".csv", full.names = T)

## Create List of Files in the Folder
Parts <- lapply(FileNames, fread)
## Bind dataframes together
Complete <- do.call(rbind, Parts)
## Write the output
fwrite(Complete, file = "Path/To/Output/FinalDataset.csv")

Haven

Haven is another package by Hadley Wickham & co, which is also party of the tidyverse. Just like readxl, this package converts the loaded files into the tibble format. Unlike many other SPSS reading packages, haven can also write files back into an SPSS format with write_sav(). This might come in handy if your researchers have only ever heard of R as a letter in the alphabet. Besides .sav, haven also reads .sas7bdat (SAS) and .dta (Stata) files.

Although Haven is great, I had a similar issue when I came across a file of 20 GB, with approximetly 170 million rows. This led me to the package:

Memisc

memisc is a package created for the Management of Survey Data and Presentation of Analysis Results. Memisc can find information about the SPSS file without having to load the complete file. Which could be very useful. For instance, SPSSFile <- spss.system.file("Path/To/File.sav") creats an importer object. Calling a print(), or just typing the object name gives us this:

## 
## SPSS system file 'C:/Users/Stefan/Documents/Projects/Personal-Website/static/data/Excel_SPSS/testdata.sav' 
##  with 17 variables and 5 observations

While the function description() gives us this:

If we know which columns we need from the SPSS file, we can specify this with the subset() function, eg:

df <- as.data.table(subset(SPSSFile, 
                     select = C("colname1",
                                "colname2",
                                "colname7",
                                "etc")))

memisc has a lot more functionalities, so be sure to check out the documentation.