I won’t write a very long introduction; we all know that Excel is ubiquitous in business, and that
it has a lot of very nice features, especially for business practitioners that do not know any
programming. However, when people use Excel for purposes it was not designed for, it can be a
hassle. Often, people use Excel as a reporting tool, which it is not; they create very elaborated
and complicated spreadsheets that are human readable, but impossible to import within any other tool.
In this blog post (which will probably be part of a series), I show you how you can go from this:
to this:
You can find the data I will use here. Click on the “Time use” folder and you can download the workbook.
The Excel workbook contains several sheets (in French and English) of the amount of time Luxembourguish
citizens spend from Monday to Sunday. For example, on average, people that are in employment spend
almost 8 hours sleeping during the week days, and 8:45 hours on Saturday.
As you can see from the screenshot, each sheet contains several tables that have lots of headers
and these tables are next to one another. Trying to import these sheets with good ol’ readxl::read_excel()
produces a monster.
This is where {tidyxl} comes into play. Let’s import the workbook with {tidyxl}:
library(tidyverse)
library(tidyxl)
time_use_xl %
filter(row %in% seq(6,58)) %__%
rename(activities = character) %__%
select(-row)
cols_to_extract % filter(grepl(&quot;Population who completed.*&quot;, character)) %__% pull(col)
headers_pos %
pull(character)
cols_to_extract %__% map(~filter(sheet, col %in% .)) %__%
map(~select(., sheet, address, row, col, character)) %__%
map(~filter(., row %in% seq(6,58))) %__%
map(~select(., character)) %__%
map2(.x = ., .y = headers, ~mutate(.x, &quot;population&quot; = .y)) %__%
map(., ~bind_cols(activities, .)) %__%
bind_rows()
}
Let’s study it step by step and see how it works. First, there’s the argument, sheet. This function
will be mapped to each sheet of the workbook. Then, the first block I wrote, extracts the
activities:
activities %
filter(col == 2) %__%
select(row, character) %__%
filter(row %in% seq(6,58)) %__%
rename(activities = character) %__%
select(-row)
I only keep the second column (filter(col == 2)); col is a column of the tibble and if you
look inside the workbook, you will notice that the activities are on the second column, or the B
column. Then, I select two columns, the row and the character column. row is self-explanatory
and character actually contains whatever is written inside the cells. Then, I only keep rows
6 to 58, because that is what interests me; the rest is either empty cells, or unneeded. Finally,
I rename the character column to activities and remove the row column.
The second block:
cols_to_extract % filter(grepl(&quot;Population who completed.*&quot;, character)) %__% pull(col)
returns the index of the columns I want to extract. I am only interested in the people that have
completed the activities, so using grepl() inside filter(), I located these columns, and use
pull()… to pull them out of the data frame! cols_to_extract is thus a nice atomic vector of
columns that I want to keep.
In the third block, I extract the headers:
headers_pos %
pull(character)
Headers are always on the third row, but on different columns, hence the col %in% headers_pos. I
then pull out the values inside the cells with pull(character). So my headers object will be
an atomic vector with “All”, “Male”, “Female”, “10 - 19 years”, etc… everything on row 3.
Finally, the last block, actually extracts the data:
cols_to_extract %__% map(~filter(sheet, col %in% .)) %__%
map(~select(., sheet, address, row, col, character)) %__%
map(~filter(., row %in% seq(6,58))) %__%
map(~select(., character)) %__%
map2(.x = ., .y = headers, ~mutate(.x, &quot;population&quot; = .y)) %__%
map(., ~bind_cols(activities, .)) %__%
bind_rows()
cols_to_extract is a vector with the positions of the columns that interest me. So for example
“4”, “7”, “10” and so on. I map this vector to the sheet, which returns me a list of extracted
data frames. I pass this down to a select() (which is inside map()… why? Because the input
parameter is a list of data frames). So for each data frame inside the list, I select the columns
sheet, address, row, col and character. Then, for each data frame inside the list,
I use filter() to only keep the rows from position 6 to 58. Then, I only select the character
column, which actually contains the text inside the cell. Then, using map2(), I add the values
inside the headers object as a new column, called population. Then, I bind the activities
column to the data frame and bind all the rows together.
Time to use this function! Let’s see:
clean_data %
map(~filter(time_use_xl, sheet %in% .)) %__%
set_names(sheets) %__%
map(extract_data) %__%
map2(.x = ., .y = sheets, ~mutate(.x, &quot;day&quot; = .y)) %__%
bind_rows() %__%
select(day, population, activities, time = character)
glimpse(clean_data)
## Observations: 2,968
## Variables: 4
## $ day &quot;Year 2014_Monday til Friday&quot;, &quot;Year 2014_Monday ti...
## $ population &quot;All&quot;, &quot;All&quot;, &quot;All&quot;, &quot;All&quot;, &quot;All&quot;, &quot;All&quot;, &quot;All&quot;, &quot;A...
## $ activities &quot;Personal care&quot;, &quot;Sleep&quot;, &quot;Eating&quot;, &quot;Other personal...
## $ time &quot;11:07&quot;, &quot;08:26&quot;, &quot;01:47&quot;, &quot;00:56&quot;, &quot;07:37&quot;, &quot;07:47...
So I map my list of sheets to the tibble I imported with readxl, use set_names to
name the elements of my list (which is superfluous, but I wanted to show this; might interest you!)
and then map this result to my little function. I could stop here,
but I then add a new column to each data frame that contains the day on which the data was
measured, bind the rows together and reorder the columns. Done!
Now, how did I come up with this function? I did not start with a function. I started by writing
some code that did what I wanted for one table only, inside one sheet only. Only when I got
something that worked, did I start to generalize to several tables and then to several sheets. Most
of the time spent was actually in trying to find patterns in the Excel sheet that I could use
to write my function (for example noticing that the headers I wanted where always one column before
the column I was interested in). This is my advice when working with function programming; always
solve the issue for one element, wrap this code inside a function, and then simply map this function
to a list of elements!
If you found this blog post useful, you might want to follow me on twitter
for blog post updates.