Loading data from Excel

Sometimes the data at the start of your preprocessing pipeline will be an Excel file, rather than a CSV file. As a proprietary format, this is not ideal. However, people often use Excel to keep related data in a single file, by using “subsheets”. Whilst you could save each subsheet to its own CSV file, R can also read the data directly from Excel.

To demonstrate this, we’ll read the data from an experiment where the results of four different tests were recorded on subsheets of an Excel spreadsheet. There was also a subsheet containing demographic data.

We’ll read data from all of the subsheets into a single data frame.

rm(list = ls()) # clear the environment
library(tidyverse)

# read data from Excel
library(readxl)
path <- 'case-studies/allegra-cattani/picture-naming.xlsx'
data <- path %>%
  excel_sheets() %>%
  set_names() %>%
  map_df(~ read_excel(path = path, sheet = .x, range = "A1:V20"),
         .id = "sheet")

Explanation of commands:

  1. We clear the workspace and load tidyverse.
  2. library(readxl) loads a package containing functions for reading Excel spreadsheets.
  3. path <- 'going-further/picture-naming.xlsx' defines the location of our spreadsheet.
  4. path %>% excel_sheets() creates a vector (a list) containing the names of the subsheets.
  5. Items in a vector can be given names. Without an argument, set_names() sets the name the same as the value in the vector, thereby naming each item after its subsheet. It will become clear why we do this in the next step.
  6. map_df() processes each item in the vector, by assigning it to .x and then running the function read_excel(path = path, sheet = .x, range = "A1:V20"). For each subsheet of the spreadsheet file (path), this reads data from the range A1:V20 on the subsheet (the cells containing the data we need). The argument .id = "sheet" creates a column named sheet with the name associated with the current vector item .x. This is the name that we set in the previous step. The results are returned as a data frame.
  7. Because map_df() adds the results of each of function call to the end of the previously created data frame, data is assigned a single data frame containing the data from all subsheets.

Here’s a selection of rows from data:

sheet Code Parental_Education(M) Parental_Education(F) Highest_Education Gender DOB Test_Date Age (M) CDI_U CDI_S …11 …12 …13 …14 …15 …16 …17 …18 …19 …20 …21 …22 Cards Beach Lorry Seal Bag Banana Picture Hen Book Socks Comb Table Roof Umberella Fork Lion Flags Glass Nappy Radiator Gloves Mountain Motorbike Penguin Box Apple Iron Cow Doll Hat Watch Sofa Clouds Hammer Glasses Elephant Backyard Spoon Bib Sink Wellyboots Small Spinning Swimming Empty Heavy Inside Kissing Clean Falling Eating Washing Smiling In-front Playing Phoning Far-apart Driving Opening Long Pushing Big Swinging Climbing Full Tall Outside Waving Dirty Running Drinking Hugging Biting Behind Walking Building Near Sweeping Combing Short Pulling
Demographic WinG30-01 NA NA NA Female NA NA NA 62 38 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Demographic WinG30-02 2 2 2 Male 2017-06-25 2020-01-09 30 60 59 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Noun Production WinG30-01 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA english NR NTS NT NR C* NR C NT C NTS NT NT NT NTS NTS NT NTS NT NR C NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Noun Production WinG30-02 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA italian NT-S C NR C C NT C NT C NTS C NTS C C C C C NT NT C NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Noun Comprehension WinG30-01 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA english NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA D C C C C P C C C P D C D C C P C P P C NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Noun Comprehension WinG30-02 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA italian NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA C C C C C C C C C C C C C NR C D C C C C NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Predicate Production WinG30-01 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA english NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NT NT N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Predicate Production WinG30-02 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA italian NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NTS NTS C NT NT NT C NT NTS C C C NT C C NT C NT NT C NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Predicate Comprehension WinG30-01 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA english NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA P C N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A
Predicate Comprehension WinG30-02 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA italian NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA C C C C C D C C C C C C C C C C C D C P

This material is distributed under a Creative Commons licence. CC-BY-SA 4.0.