Working with the xlsx package Exercises (part 2)

This exercise set provides (further) practice in writing Excel documents using the xlsx 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 here.

We will use a subset of commuting data from the Dublin area from AIRO and the 2011 Irish census.

Solutions are available here.

Exercise 1
Load the xlsx package. If necessary install it as indicated in the previous xlsx exercise set.

Exercise 2
Download the data to your computer and read into your R workspace as commuting using read.xlsx2() or the slower alternative read.xlsx(). Use colClasses to set relevant classes as we will be manipulating the data later on.

Exercise 3
Clean the data a bit by removing 'Population_Aged_5_Over_By_' and 'To_Work_School_College_' from the column names.

Exercise 4
Sum the 'population aged 5 and over' variables by electoral division name using for instance aggregate() or data.table and save the result as commuting_ed.

< aside class='stb-icon'>

Learn more about working with excel and R in the online course Learn By Example: Statistics and Data Science in R. In this course you will learn how to:

• Learn some of the differences between working in Excel with regression modelling and R
• Learn about different statistical concepts
• And much more

Exercise 5
Create an xlsx workbook object in your R workspace and call it wb.

Exercise 6
Create three sheets objects in wb named sheet1, sheet2, sheet3 in wb and your workspace. Use a loop.

Exercise 7
Make a data.frame 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.

Exercise 8
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.

Exercise 9
Add some great title to the first row of each sheet and apply some style to it.

Exercise 10
Save your workbook to your working directory and open using Excel. Go back to R and continue formatting and adding information to your workbook at will.