<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/working-with-the-xlsx-package-exercises-part-2/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.r-exercises.com/2017/06/28/working-with-the-xlsx-package-part-2/?utm_source=rss&utm_medium=rss&utm_campaign=working-with-the-xlsx-package-part-2"> R-exercises</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p><a rel="nofollow" href="https://i2.wp.com/www.r-exercises.com/wp-content/uploads/2017/06/screensh.jpg" target="_blank"><img sizes="(max-width: 300px) 100vw, 300px" src="https://i1.wp.com/www.r-exercises.com/wp-content/uploads/2017/06/screensh-300x147.jpg?resize=300%2C147" alt="" class="wp-image-6590" data-recalc-dims="1"></a></p>
<p>This exercise set provides (further) practice in writing Excel documents using the <code>xlsx</code> package as well as importing and general data manipulation. Specifically we have loops in order for you to practice scaling. A previous exercise set focused on writing a simple sheet with the same package, see <a rel="nofollow" href="http://www.r-exercises.com/2017/06/17/xlsx1/" target="_blank">here</a>.</p>
<p>We will use a subset of <a rel="nofollow" href="http://airo.maynoothuniversity.ie/datastore/commuting-t11-sa" target="_blank">commuting data</a> from the Dublin area from AIRO and the 2011 Irish census.</p>
<p>Solutions are available <a rel="nofollow" href="http://www.r-exercises.com/2017/06/28/working-with-the-xlsx-package-solutions-part-2" target="_blank">here</a>.</p>
<p><strong>Exercise 1</strong><br>
Load the <code>xlsx</code> package. If necessary install it as indicated in <a rel="nofollow" href="http://www.r-exercises.com/2017/06/17/xlsx1/" target="_blank">the previous xlsx exercise set</a>.</p>
<p><strong>Exercise 2</strong><br>
Download <a rel="nofollow" href="http://www.r-exercises.com/wp-content/uploads/2017/06/theme_11_small_areas.xlsx" target="_blank">the data</a> to your computer and read into your R workspace as <code>commuting</code> using <code>read.xlsx2()</code> or the slower alternative <code>read.xlsx()</code>. Use <code>colClasses</code> to set relevant classes as we will be manipulating the data later on.</p>
<p><strong>Exercise 3</strong><br>
Clean the data a bit by removing <code>'Population_Aged_5_Over_By_'</code> and <code>'To_Work_School_College_'</code> from the column names.</p>
<p><strong>Exercise 4</strong><br>
Sum the <code>'population aged 5 and over'</code> variables by electoral division name using for instance <code>aggregate()</code> or <code>data.table</code> and save the result as <code>commuting_ed</code>.</p>
<div>
<aside><img src="http://www.r-exercises.com/2017/06/28/working-with-the-xlsx-package-part-2/?utm_source=rss&utm_medium=rss&utm_campaign=working-with-the-xlsx-package-part-2"></aside>
<div><strong>Learn more</strong> about working with excel and R in the online course <a rel="nofollow" href="http://www.r-exercises.com/product/learn-by-example-statistics-and-data-science-in-r/" target="_blank">Learn By Example: Statistics and Data Science in R</a>. In this course you will learn how to:
<ul>
<li>Learn some of the differences between working in Excel with regression modelling and R</li>
<li>Learn about different statistical concepts</li>
<li>And much more</li>
</ul>
</div>
</div>
<p><strong>Exercise 5</strong><br>
Create an <code>xlsx</code> workbook object in your R workspace and call it <code>wb</code>.</p>
<p><strong>Exercise 6</strong><br>
Create three sheets objects in <code>wb</code> named <code>sheet1, sheet2, sheet3</code> in <code>wb</code> and your workspace. Use a loop.</p>
<p><strong>Exercise 7</strong><br>
Make a <code>data.frame</code> that lists proportion of respondents in each of the following category by electoral division: travel on foot, travel on bicycle, leave home before 6:30.</p>
<p><strong>Exercise 8</strong><br>
Add the top 5 electoral division in each category to a previously created sheets with all the proportions using a loop. Leave the first row free.</p>
<p><strong>Exercise 9</strong><br>
Add some great title to the first row of each sheet and apply some style to it.</p>
<p><strong>Exercise 10</strong><br>
Save your workbook to your working directory and open using Excel. Go back to <code>R</code> and continue formatting and adding information to your workbook at will.</p>
<div>
<h3>Related exercise sets:</h3>
<ol>
<li><a rel="nofollow" href="http://www.r-exercises.com/2017/06/17/xlsx1/" title="Using the xlsx package to create an Excel file" target="_blank">Using the xlsx package to create an Excel file </a></li>
<li><a rel="nofollow" href="http://www.r-exercises.com/2016/12/12/building-shiny-app-exercises-part-1/" title="Building Shiny App exercises part 1" target="_blank">Building Shiny App exercises part 1 </a></li>
<li><a rel="nofollow" href="http://www.r-exercises.com/2016/12/18/building-shiny-app-exercises-part-2/" title="Building Shiny App exercises part 2" target="_blank">Building Shiny App exercises part 2 </a></li>
<li><a rel="nofollow" href="http://www.r-exercises.com/start-here-to-learn-r/" target="_blank">Explore all our (>1000) R exercises</a></li>
<li><a rel="nofollow" href="http://www.r-exercises.com/r-courses/" target="_blank">Find an R course using our R Course Finder</a> directory</li>
</ol>
</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="http://www.r-exercises.com/2017/06/28/working-with-the-xlsx-package-part-2/?utm_source=rss&utm_medium=rss&utm_campaign=working-with-the-xlsx-package-part-2"> R-exercises</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/ITYfGSWIuIQ" height="1"></a></div>