<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="http://www.r-bloggers.com/using-xda-with-googlesheets-in-r/"></div></div><div><a data-url="http://www.r-bloggers.com/using-xda-with-googlesheets-in-r/" data-counturl="http://www.r-bloggers.com/using-xda-with-googlesheets-in-r/" data-count="horizontal" data-text="Using xda with googlesheets in R" 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="https://qualityandinnovation.com/2016/06/19/using-xda-with-googlesheets-in-r/"> R – Quality and Innovation</a></strong>, and kindly contributed to <a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers)</a> </div>
<div data-shortcode="caption" class="wp-caption"><img height="300" alt="Image Credit: Doug Buckley of http://hyperactive.to" width="221" class="wp-image-3034" src="https://qualityandinnovation.files.wordpress.com/2015/03/doug-ref-frame.jpg?w=221&h=300">
<p class="wp-caption-text">Image Credit: Doug Buckley of <a target="_blank" rel="nofollow" href="http://hyperactive.to/">http://hyperactive.to</a></p>
</div>
<p><span>Want to do a quick, exploratory data analysis in R of your data that’s stored in a spreadsheet on Google Drive? You’re in luck, because now you can use the new <a target="_blank" href="https://github.com/ujjwalkarn/xda">xda</a> package in conjunction with <a target="_blank" href="https://twitter.com/JennyBryan">Jenny Bryan</a>‘s <a target="_blank" href="https://github.com/jennybc/googlesheets">googlesheets</a>. <em>There are some quirks, though, and that’s what this post is all about.</em></span></p>
<p>Before proceeding, you should review this recent article from R-Bloggers called <a target="_blank" href="http://www.r-bloggers.com/introducing-xda-r-package-for-exploratory-data-analysis/">“Introducing xda”</a>.</p>
<p>First, be sure to install the <span>googlesheets</span> and <span>xda</span> packages. Although <span>googlesheets</span> is on CRAN, <span>xda</span> <em>is not</em>, and you’ll have to bring it in directly from github. You can actually do the same for <span>googlesheets</span> if you like:</p>
<pre>
install.packages(&quot;devtools&quot;)
library(devtools)
install_github(&quot;jennybc/googlesheets&quot;)
install_github(&quot;ujjwalkarn/xda&quot;)
library(googlesheets)
library(xda)
</pre>
<p>Next, you’ll have to show R how to access your Google spreadsheet. While you are looking at your spreadsheet, go to File -> Publish to the Web. <em>The URL that’s in the text box is the one you want to capture.</em> Just to make sure it works, copy and paste it into a new browser address window and see if you can display your spreadsheet in your browser.</p>
<p>If you want to import the data at <span><a target="_blank" rel="nofollow" href="https://docs.google.com/spreadsheets/d/1DO0ksD8d-rn_j2Yn7DQKZDPBrhrvZTpgszewxokjWKU/pubhtml">https://docs.google.com/spreadsheets/d/1DO0ksD8d-rn_j2Yn7DQKZDPBrhrvZTpgszewxokjWKU/pubhtml</a></span> into R, for example, you’ll need to know the spreadsheet’s <b>key</b>. That’s the long string of unintelligible numbers and letters between the “d” and the “pubhtml”. So, my key would be <span>“1DO0ksD8d-rn_j2Yn7DQKZDPBrhrvZTpgszewxokjWKU”</span> — which you’ll see in this next block of code:</p>
<pre>
> my.gs &lt;- gs_key(&quot;1DO0ksD8d-rn_j2Yn7DQKZDPBrhrvZTpgszewxokjWKU&quot;) 
> my.data &lt;- gs_read(my.gs) # Retrieves data from googlesheets and places it into an R object. 
> my.df &lt;- as.data.frame(my.data)  # Important! xda needs you to extract only the data in a data frame.
</pre>
<p>Now, you can access your data. Try <span>head(my.df)</span> to make sure you’ve imported it properly.</p>
<p>Next, it’s time for exploratory data analysis. There are three commands available:</p>
<ul>
<li><b>numSummary</b> – takes a data frame as an argument, provides descriptive statistics, quantiles, and missing data info for quantitative variables</li>
<li><b>charSummary</b> – takes a data frame as an argument, provides counts, missing data info, and number of unique factors for quantitative variables</li>
<li><b>bivariate</b> – takes a data frame and two quantitative variables as an argument, and performs a quick bivariate analysis (giving this categorical variables, or giving this one categorical and one quantitative variable, <em>will</em> throw an error)</li>
</ul>
<p>Here’s what happens when you run those commands on the data you just loaded in from your Google spreadsheet:</p>
<pre>
> numSummary(my.df)
               n   mean    sd median   max  min  mode miss miss%    1%   5%   25%   50%   75%   90%   95%   99%
obs          200 100.50 57.88  100.5 200.0  1.0   1.0    0     0  2.99 11.0  50.8 100.5 150.2 180.1 190.0 198.0
heartrates   200  73.01  7.43   73.0  96.3 53.4  71.2    0     0 56.49 61.2  68.4  73.0  77.4  82.6  85.7  90.3
systolics    200 139.27 29.27  138.0 221.0 59.0 139.0    0     0 79.98 96.0 117.0 138.0 160.0 177.2 188.1 205.0
diastolics   200  87.76  9.74   87.7 116.4 62.4  85.2    0     0 66.01 72.2  81.9  87.7  93.7 100.3 104.5 108.3
bmis         200  25.53  3.06   25.0  33.1 18.4  24.7    0     0 19.00 21.0  23.5  25.0  27.7  29.6  31.2  32.8
ages         200  44.41 14.59   45.0  70.0 18.0  30.0    0     0 18.00 22.0  32.0  45.0  57.0  64.1  67.0  70.0
heartpm      200  72.26  3.55   72.2  83.8 64.2  74.2    0     0 64.72 66.2  69.8  72.2  74.2  76.4  78.7  81.4
fitnesslevel 200   2.62  1.17    3.0   4.0  1.0   4.0    0     0  1.00  1.0   2.0   3.0   4.0   4.0   4.0   4.0

> charSummary(my.df)
          n miss miss% unique
genders 200    0     0      2
smokers 200    0     0      2
group   200    0     0      4

> bivariate(my.df,'heartrates','bmis')
     bin_bmis min_heartrates max_heartrates mean_heartrates
1   (18.3,22]          53.40          85.60           72.80
2   (22,25.7]          55.70          90.70           72.87
3 (25.7,29.4]          60.30          96.30           73.45
4 (29.4,33.1]          56.50          90.30           72.46

</pre>
<p>Observations:</p>
<ul>
<li>There is a fourth “Plot” command but I couldn’t get it to work on any <span>googlesheets</span>data. The xda package is looking for class(range) to be anything other than “function”, which it was for every sheet I attempted to load.</li>
<li>There really should be an extra column in xda that displays the enumeration of all the unique values for the factors. It felt great to know how many unique values there were, but I would love to be reminded of <em>what</em> they are too, unless there are too many of them.</li>
</ul>
<p>Please share your experiences using <span>xda</span> &amp; <span>googlesheets </span>together in the comments! Thanks!</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/qualityandinnovation.wordpress.com/3405/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/qualityandinnovation.wordpress.com/3405/" border="0"></a> <img height="1" alt="" width="1" src="https://pixel.wp.com/b.gif?host=qualityandinnovation.com&blog=5081318&%23038;post=3405&%23038;subd=qualityandinnovation&%23038;ref=&%23038;feed=1" border="0"></p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="http://www.r-bloggers.com/using-xda-with-googlesheets-in-r/"></div></div><div><a data-url="http://www.r-bloggers.com/using-xda-with-googlesheets-in-r/" data-counturl="http://www.r-bloggers.com/using-xda-with-googlesheets-in-r/" data-count="vertical" data-text="Using xda with googlesheets in R" 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="https://qualityandinnovation.com/2016/06/19/using-xda-with-googlesheets-in-r/"> R – Quality and Innovation</a></strong>.</div>
<hr>
<a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="http://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="http://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="http://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="http://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="http://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="http://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="http://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="http://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="http://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="http://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="http://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="http://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="http://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="http://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="http://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="http://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/vewZSrNKAOE"></a></div>