<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-introduction/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://statbandit.wordpress.com/2018/05/07/tidying-messy-excel-data-introduction/"> R – Stat Bandit</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<h4>[Re-posted from <a rel="nofollow" href="https://webbedfeet.github.io/posts/tidying-messy-data-1" target="_blank">Abhijit’s blog</a>]</h4>
<h3>Personal expressiveness, or how data is stored in a spreadsheet</h3>
<p>When you get data from a broad research community, the variability in how that data is formatted and stored is truly astonishing. Of course there are the standardized formats that are output from machines, like Next Generation Sequencing and other automated systems. That is a saving grace!</p>
<p>But for smaller data, or data collected in the lab, the possibilities are truly endless! You can get every possiblle color-coding of rows, columns and cells, merged cells, hidden columns and rows, and inopportune blank spaces that convert numbers to characters, and notes where there should be numbers. That’s just from the more organized spreadsheets. Then you get multiple tables in the same spreadsheet, ad hoc computations in some cells, cells copied by hand (with error), and sundry other variations on this theme. In other words, it can be a nightmare scenario for the analyst. To wit,</p>
<div>
<blockquote data-width="450" class="twitter-tweet" data-dnt="true">
<p lang="en" dir="ltr">I feel like some sort of spreadsheet naturalist, where new specimens spotted in the wild must be logged and compared to known species</p>
<p>— Jenny Bryan (@JennyBryan) <a rel="nofollow" href="https://twitter.com/JennyBryan/status/989274016807448577?ref_src=twsrc%5Etfw" target="_blank">April 25, 2018</a></p>
</blockquote>
<p></div>
<div>
<div lang="en">
<div></div>
<div>
<p>In thinking about this post, I went back and looked at the documentation of the <a rel="nofollow" href="http://readxl.tidyverse.org/" target="_blank"><code>readxl</code></a> package, which has made reading Excel files into R a lot easier than before. This package is quite powerful, so as long as data are in a relatively clean tabular form, this tool can pull it into R; see <a rel="nofollow" href="http://readxl.tidyverse.org/articles/sheet-geometry.html" target="_blank">these</a> <a rel="nofollow" href="http://readxl.tidyverse.org/articles/sheet-geometry.html" target="_blank">vignettes</a> to get a real sense of how to process decently behaved Excel files with R.</p>
<blockquote>
<p>On a side note, how many ways of importing Excel files into R can you name, or have you used?</p>
</blockquote>
<p>The <code>readxl</code> package has been my friend for a while, but then I received some well-intentioned spreadsheets that even <code>readxl</code> wouldn’t touch, despite much coaxing. Now, I had two options: ask the collaborator to re-format the spreadsheet (manually, of course <img alt="😄" src="https://i0.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f604.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1">), which in my experience is a disaster waiting to happen; or just take the spreadsheet as is and figure out how to import it into R. I almost always take the latter route, and <code>tidyxl</code> is my bosom friend in this effort. In the next part of this series, I’ll describe my experiences with <code>tidyxl</code> and why, every time I use it, I say a small blessing for the team that created it.</p>
<h3>Resources</h3>
<ol>
<li><a rel="nofollow" href="http://rpubs.com/jennybc/untangle-tidyeval" target="_blank">tidyeval meets Spreadsheet Hell</a></li>
<li><a rel="nofollow" href="http://www.datacarpentry.org/spreadsheet-ecology-lesson/" target="_blank">Carpentries lesson on spreadsheet practices</a></li>
<li><a rel="nofollow" href="http://readxl.tidyverse.org/articles/articles/readxl-workflows.html" target="_blank"><code>readxl</code> workflows</a></li>
</ol>
</div>
</div>
</div>
</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/07/tidying-messy-excel-data-introduction/"> 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/kb28uemVlqU" height="1"></a></div>