<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/extract-tables-from-messy-spreadsheets-with-jailbreakr/"></div></div><div><a data-url="https://www.r-bloggers.com/extract-tables-from-messy-spreadsheets-with-jailbreakr/" data-counturl="https://www.r-bloggers.com/extract-tables-from-messy-spreadsheets-with-jailbreakr/" data-count="horizontal" data-text="Extract tables from messy spreadsheets with jailbreakr" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://blog.revolutionanalytics.com/2016/08/jailbreakr.html"> Revolutions</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<p>R has some good tools for importing data from spreadsheets, among them the <a target="_blank" href="https://github.com/hadley/readxl">readxl package</a> for Excel and the <a target="_blank" href="https://github.com/jennybc/googlesheets">googlesheets package</a> for Google Sheets. But these only work well when the data in the spreadsheet are arranged as a rectangular table, and not overly encumbered with formatting or generated with formulas. As Jenny Bryan pointed out in her <a target="_blank" href="https://channel9.msdn.com/Events/useR-international-R-User-conference/useR2016/jailbreakr-Get-out-of-Excel-free">recent talk at the useR!2016 conference</a> (and embedded below, or <a target="_blank" href="https://github.com/jennybc/2016-06_spreadsheets/raw/master/2016-06_useR-stanford.pdf">download PDF slides here</a>), in practice few spreadsheets have “a clean little rectangle of data in the upper-left corner”, because most people use spreadsheets not just a file format for data retrieval, but also as a reporting/visualization/analysis tool.</p>
<p><iframe height="281" width="450" allowfullscreen="allowfullscreen" src="https://channel9.msdn.com/Events/useR-international-R-User-conference/useR2016/jailbreakr-Get-out-of-Excel-free/player"></iframe></p>
<p>Nonetheless, for a practicing data scientist, there’s a lot of useful data locked up in these messy spreadsheets that needs to be imported into R before we can begin analysis. As just one example given by Jenny in her talk, this spreadsheet was included as one of 15,000 spreadsheet attachments (one with 175 tabs!) in the <a target="_blank" href="https://gitlab.com/rsheets/enron_corpus">Enron Corpus</a>.</p>
<p><a target="_blank" href="http://revolution-computing.typepad.com/.a/6a010534b1db25970b01bb092b8911970d-pi"><img title="Enron" alt="Enron" src="http://revolution-computing.typepad.com/.a/6a010534b1db25970b01bb092b8911970d-800wi" border="0"></a></p>
<p>To make it easier to import data into R from messy spreadsheets like this, Jenny and co-author Richard G. FitzJohn created the <a target="_blank" href="https://github.com/rsheets/jailbreakr">jailbreakr package</a>. The package is in its early stages, but it can already import Excel (xlsx format) and Google Sheets intro R as a new “linen” objects from which small sub-tables can easily be extracted as data frames. It can also print spreadsheets in a condensed text-based format with one character per cell — useful if you’re trying to figure out why an apparently simple spreadsheet isn’t importing as you expect. (Check out the “weekend getaway winner” story near the end of Jenny’s talk for a great example.)</p>
<p>The jailbreakr package isn’t yet on CRAN, but if you want to try it out you can download it from the Github repository (or even contribute!) at the link below.</p>
<p>Github (rsheets): <a target="_blank" href="https://github.com/rsheets/jailbreakr">jailbreakr</a></p>
</div>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/extract-tables-from-messy-spreadsheets-with-jailbreakr/"></div></div><div><a data-url="https://www.r-bloggers.com/extract-tables-from-messy-spreadsheets-with-jailbreakr/" data-counturl="https://www.r-bloggers.com/extract-tables-from-messy-spreadsheets-with-jailbreakr/" data-count="vertical" data-text="Extract tables from messy spreadsheets with jailbreakr" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://blog.revolutionanalytics.com/2016/08/jailbreakr.html"> Revolutions</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 title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/mAjrVoBWanY"></a></div>