<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/tidying-messy-excel-data-tidyxl/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://statbandit.wordpress.com/2018/05/08/tidying-messy-excel-data-tidyxl/"> R – Stat Bandit</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Reposted from <a rel="nofollow" href="https://webbedfeet.netlify.com/post/tidying-messy-excel-data-tidyxl/" target="_blank">Abhijit’s blog</a>. Some <code>&lt;-</code> have been replaced by <code>=</code> due to idiosyncracies of the WordPress platform.</p>
<hr>
<p>Well, here’s what I was dealing with:</p>
<div>
<p><img alt="Exemplar Excel file from collaborator" src="https://i1.wp.com/webbedfeet.netlify.com/img/spreadsheet1.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p><em>Exemplar Excel file from collaborator</em></p>
</div>
<p>Notice that we have 3 header rows, first with patient IDs, second with spine region, and third with variable names (A and B, to protect the innocent).</p>
<div>
<h4>Goal</h4>
<p>A dataset that, for each patient and each angle gives us corresponding values of A and B. So this would be a four-column data set with ID, angle, A and B.</p>
</div>
<div>
<h2>Attempt 1 (readxl)</h2>
<pre>
d1 &lt;- readxl::read_excel('spreadsheet1.xlsx')
head(d1)
</pre>
<p>## # A tibble: 6 x 26<br>
## X__1 patient `44` `44__1` `10` `10__1` `3` `3__1` `53` `53__1`<br>
##<br>
## 1 IDS T5/T6 T5/T6 T5/T6 T5/T6 T5/T6 T5/T6 T5/T6 T5/T6<br>
## 2 angles A B A B A B A B<br>
## 3 60 31.83… 1 31.52… 1 32.9… 0 31.8… 0<br>
## 4 65 31.66… 1 31.33… 1 32.2… 0 32.3… 0<br>
## 5 70 31.45… 1 31.09… 0.20200… 31.7… 0 32.5… 0<br>
## 6 75 31.08… 1 30.96… 0.44831… 31.2… 8.641… 32.3… 1<br>
## # … with 16 more variables: `2` , `2__1` `8` ,<br>
## # `8__1` , `6` , `6__1` , `43` , `43__1` ,<br>
## # `48` , `48__1` , `46` , `46__1` , `4` ,<br>
## # `4__1` , `9` , `9__1`</p>
<p>This strategy gives us funky column names, and pushes two of the headers into data rows. Since the headers are in rows, they’re a little harder to extract and work with. More worrisome is the fact that since the headers leaked into the data rows, the columns are all of type <code>character</code> rather than type <code>numeric</code>, which would now require further careful conversion after cleaning. So I don’t think <code>readxl</code> is the way to go here, if there’s a better solution.</div>
<div>
<h2>Attempt 2 (tidyxl)</h2>
<pre>
d2 &lt;- tidyxl::xlsx_cells('spreadsheet1.xlsx')
head(d2)
</pre>
<p>## # A tibble: 6 x 21<br>
## sheet address row col is_blank data_type error logical numeric<br>
##<br>
## 1 T5T6 B1 1 2 FALSE character NA NA<br>
## 2 T5T6 C1 1 3 FALSE numeric NA 44.<br>
## 3 T5T6 D1 1 4 FALSE numeric NA 44.<br>
## 4 T5T6 E1 1 5 FALSE numeric NA 10.<br>
## 5 T5T6 F1 1 6 FALSE numeric NA 10.<br>
## 6 T5T6 G1 1 7 FALSE numeric NA 3.<br>
## # … with 12 more variables: date , character ,<br>
## # character_formatted , formula , is_array ,<br>
## # formula_ref , formula_group , comment , height ,<br>
## # width , style_format , local_format_id</p>
<p>The <code>xlsx_cells</code> captures the data in a tidy fashion, explicitly calling out rows and columns and other metadata within each cell. We can clean up this data using tidyverse functions:</p>
<pre>
library(tidyverse)
cleanData1 = function(d) {
  angle = d %>% filter(row >= 4, col == 1) %>% pull(numeric)
  name = d %>% filter(row %in% c(1,3), col >= 3) %>%
    mutate(character = ifelse(is.na(character),
                              as.character(numeric),
                              character)) %>%
    select(row, col, character) %>%
    filter(!is.na(character)) %>%
    spread(row, character) %>%
    unite(ID, `1`:`3`, sep = '_') %>%
    pull(ID)
  data = d  %>% filter(row >= 4, col >= 3) %>%
    filter(!is.na(numeric)) %>%
    select(row, col, numeric) %>%
    spread(col, numeric) %>%
    select(-row) %>%
    set_names(name) %>%
    cbind(angle) %>%
    gather(variable, value, -angle) %>%
    separate(variable, c('ID','Measure'), sep = '_') %>%
    spread(Measure, value) %>%
    select(ID, angle, A, B) %>%
    arrange(ID, angle)
  return(data)
}

head(cleanData1(d2))
</pre>
<pre>
##   ID angle        A        B
## 1 10    60 31.52867 1.000000
## 2 10    65 31.33477 1.000000
## 3 10    70 31.09272 0.202002
## 4 10    75 30.96078 0.448317
## 5 10    80 30.79397 0.670876
## 6 10    85 30.52185 0.461406
</pre>
<p>This is a lot of data munging, and though <code>dplyr</code> is powerful, it took a lot of trial and error to get the final pipeline done.Nonetheless, I was really psyched about <code>tidyxl</code>, since it automated a job that would have taken manual manipulation (I had 12 spreadsheets like this to process). I was going to write a blog post on this cool package that made my life dealing with messy Excel file a piece of cake. But wait, there’s more…</div>
<div>
<h2>Attempt 3 (tidyxl + unpivotr)</h2>
<p>I didn’t know about <code>unpivotr</code> until this post:</p>
<blockquote class="twitter-tweet">
<p dir="ltr" lang="en">When your spreadsheet is too <img alt="😱" src="https://i1.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f631.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"><img alt="😩" src="https://i0.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f629.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"><img alt="😡" src="https://i0.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f621.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"> for readxl, tidyxl + unpivotr helps you tackle charming features like “data as formatting” and “data in the layout”. <a rel="nofollow" href="https://t.co/ABerpfHT8W" target="_blank">https://t.co/ABerpfHT8W</a></p>
<p>— Jenny Bryan (@JennyBryan) <a rel="nofollow" href="https://twitter.com/JennyBryan/status/938834824688689152?ref_src=twsrc%5Etfw" target="_blank">December 7, 2017</a></p>
</blockquote>
<p>So maybe all that complicated munging can be simplfied.</p>
<pre>
# devtools::install_github('nacnudus/unpivotr')
library(unpivotr)

cleanData2 = function(d){
  bl = d %>% select(row, col, data_type, numeric, character) %>%
    behead('N', ID) %>%
    behead('N', spine) %>%
    behead('N', variable)
  # Extract the angles column
  bl1 = bl %>% filter(variable == 'angles') %>% spatter(variable) %>%
    select(row, angles)
  # Extract the rest of the columns
  bl2 = bl %>% filter(variable %in% c('A','B')) %>% select(-spine, -col) %>%
    spatter(ID) %>% # Spread to columns
    select(-character) %>% # All my variables are numeric
    gather(ID, value, -row, -variable) %>%
    spread(variable, value)
  final = bl1 %>% left_join(bl2) %>% # put things back together
    arrange(ID, angles) %>%
    select(ID, everything(),-row) # re-arrange columns
  return(final)
}

cleanData2(d2)
</pre>
<pre>
## # A tibble: 588 x 4
##    ID    angles     A     B
##
##  1 10       60.  31.5 1.00
##  2 10       65.  31.3 1.00
##  3 10       70.  31.1 0.202
##  4 10       75.  31.0 0.448
##  5 10       80.  30.8 0.671
##  6 10       85.  30.5 0.461
##  7 10       90.  30.3 0.245
##  8 10       95.  30.0 0.159
##  9 10      100.  29.7 0.170
## 10 10      105.  29.2 0.421
## # ... with 578 more rows
</pre>
<p>In this example, I’m using the <code>behead</code> function (available in the development version of <code>unpivotr</code> on GitHub) to extract out the three rows of headers. Then I’m extracting out the <code>angles</code> column separately and merging it with the rest of the columns.</p>
<blockquote>
<p>In case you’re wondering about the “N” in the <code>behead</code> code, <code>unpivotr</code> has a geographic options system as to where the headers are with respect to the main code. This <a rel="nofollow" href="https://nacnudus.github.io/unpivotr/articles/compass-directions.html" target="_blank">vignette</a> explains this nomenclature.</p>
</blockquote>
</div>
<div>
<h2>Attempt 4 (tidyxl + unpivotr)</h2>
<p>After re-reading the <code>unpivotr</code> documentation, I realized that the <code>angles</code> column could be treated as a row header in the <code>unpivotr</code> code. So I further modified the function:</p>
<pre>
cleanData3 = function(d) {
  final = d %>%
    select(row, col, data_type, numeric, character) %>%
    behead('N', ID) %>%  # Extract column headers
    behead('N', spine) %>%
    behead('N', variable) %>%
    behead('W', angles) %>% # angles as row header
    select(numeric, ID:angles, data_type, -spine) %>% # all vars are numeric
    filter(variable %in% c'A','B')) %>% # Kills off some extra columns
    spatter(variable) # Spreads, using data_type, numeric
  return(final)
}

cleanData3(d2)
</pre>
<pre>
## A tibble: 588 x 4
##   ID    angles     A     B
##
## 1 10       60.  31.5 1.00
## 2 10       65.  31.3 1.00
## 3 10       70.  31.1 0.202
## 4 10       75.  31.0 0.448
## 5 10       80.  30.8 0.671
## 6 10       85.  30.5 0.461
## 7 10       90.  30.3 0.245
## 8 10       95.  30.0 0.159
## 9 10      100.  29.7 0.170
##10 10      105.  29.2 0.421
## ... with 578 more rows
</pre>
<p>I get to the same output, but with much cleaner code. This is cool!!I’m going to go deeper into the <code>unpivotr</code> documentation and see what else can be in my regular pipeline. A big thank you to the tool-makers that create these tools that make everyday activies easier and make us stay saner.</div>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://statbandit.wordpress.com/2018/05/08/tidying-messy-excel-data-tidyxl/"> R – Stat Bandit</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/SsevAfvU4io" height="1"></a></div>