Part 1 [19 marks]
The spreadsheet titled ‘censusdata.xlsx’ contains information about the number of bedrooms in occupied
private dwellings for local government areas in Melbourne for the years 2011 and 2016. You will see that it is
far from being ready for analysis and needs to be ‘wrangled’. Additionally a few errors have been deliberately
introduced into the first two columns so these will need to be corrected by initial analysis.
- Explain why the data in its current form is not considered to be in ‘tidy’ format. (1 mark)
-
Write R code to read in the data (readxl package), manipulate it and output it to a single csv file
having the following header row.
region,year,br_count_0,br_count_1,br_count_2,br_count_3,br_count_4_or_more,br_count_unstate
d,av_per_dwelling,av_per_household
Your code will have the following sections (not necessarily in the order given and the process may be
iterative as you find more things to do). Please include comments in the code to separate each
segment and explain your steps.- Read in the data sets into two data frames df2011 and df2016. (1 mark)
-
Compare the layout of each of the two data frames, then remove appropriate rows of one data
frame to match the format of the other. (2 marks) -
Write a function that takes in a table of the original form and outputs a table in the desired form
with columns specified above. (6 marks)
o Remove unwanted rows or columns [1 mark]
o Split values into multiple columns to make them atomic [1 mark]
o Appropriately transform the data into the desired form [3 marks]
o Rename columns [1 mark]
- Apply the function to each table to create two tables in the desired format. (1 mark)
- Do a summary of each table to look for unusual values. (1 mark)
- Correct those values until the two tables have the same dimensions and format. (2 marks)
-
Merge the two tables into a single table so that we see data in the form
Banyule,2011,78,1287,8457,21865,11366,645,3.1,2.6
Banyule,2016,…
Bayside,2011,…
Bayside,2016,…
…
Victoria,2011,…
Victoria,2016,…
Australia,2011,…
Australia,2016,…
(listed alphabetically by region, then by year, with Victoria and Australia at the end) (2 marks)
• Write the result to a csv file (it should have 65 rows including the header). (1 mark)
3. Which region(s) (ignoring Victoria and Australia) had the largest increase in the number of occupied
dwellings with 3 or more bedrooms between 2011 and 2016? (Ignore the unstated counts.) (2 marks)
Part 2 [10 marks]
The online hospitality company Airbnb has made publicly available a number of datasets. This part of the
assignment makes use of the listings.csv dataset which is available at
http://data.insideairbnb.com/australia/vic/melbourne/2019-04-08/visualisations/listings.csv.
It consists of a number of parameters related to properties available for lodging in the Melbourne
metropolitan area and can be visualised at http://insideairbnb.com/melbourne.
Write R code to answer the following.
-
Give the five neighbourhoods with the most listings (list them along with the counts in descending
order). (1 mark) -
How many listings contain the following words (upper or lower case or mixed) in the name column?
- Beautiful
- Quiet
- Amazing
- <another adjective of your choice with at least 200 instances> (2 marks)
-
How many listings are there with last review in 2016? Give month by month counts for the year 2016.
(2 marks) -
Create a new column of the table which calculates the number of ids that correspond to the given
host_id . Your answer will match the calculated_host_listings_count column (only use this column to
check your answer). (2 marks) -
Write a function that inputs a listing id and outputs a score that is the sum of points according to the
following criteria:-
Points for the neighbourhood: (average number of bedrooms per dwelling in 2016) × 50
(this comes from the data set in Part 1) - Points for the room type: 200 for Entire home/apt, 100 for Private room, 0 for Shared room
- Points for minimum nights: 50 for 1 night, 25 for 2 nights, 0 for 3 or more nights
- Points for availability: (availability_365) divided by 5
- Points for review frequency: 50 × (reviews per month), but no more than 100
- Points for price: (300 minus price)
-
Points for the neighbourhood: (average number of bedrooms per dwelling in 2016) × 50
Which id (ids if more than one) has the highest score according to the above system? (3 marks)
Part 3 [11 marks]
Write a short report summarising the variables in the two (processed) datasets from parts 1 and 2 through
tables [2 marks] and plots with R including the following:
- A histogram showing the distribution of a variable of interest [2 marks]
- A plot of one or more variables with time on the x axis (e.g. month, year or date) [2 marks]
-
A word cloud of the words in the name column of the listings table. You may follow the
instructions and use the packages referred to in this link:
http://www.sthda.com/english/wiki/text-mining-and-word-cloud-fundamentals-in-r-5-simple-
steps-you-should-know [2 marks] -
A map showing the price of listings by colour (e.g. a dot plot or heat map – you will need to use
an R package that can map geospatial data) [3 marks]
Point out any interesting patterns (e.g. trends) you see from your plots and summaries.
[Total: 40 marks]
Submission
Your submission to this assignment will consist of two files.
- 1) A single .R file with all the code used for this assignment (all parts), including comments that contain
the answers for parts 1 and 2. - 2) A document (pdf or docx) for part 3 (including code here is optional, only code in the R file will be
assessed). Do not include the answers for parts 1 or 2 here.
Note: please keep the data files in the same directory as your scripts so that you do not specify directories in
your code. This will make your R code easier to assess.
Marking Rubrics
Please note the following as it shows how marks may be deducted.
For question 1 of part 1 up to 1 mark will be deducted for any incomplete explanation.
Marks will be deducted if the R code does not work easily on the marker’s R studio installation and if you need
to be offered an opportunity to show the marker that it does work on your installation. This means all
references to directories have to be removed and packages being used are to be specified clearly. It will be
assumed that the tidyverse, readxl and packages used for the word cloud as per the link above have been
installed.
For each coding question, 0.5 marks will be deducted for each minor mistake in the code, 1 mark deducted for
each major mistake up to the full number allocated for that question.
In part 3, 2 marks will be deducted if there is little or no explanation of what is being plotted, or if no patterns
in the data are noted.
In part 3, 1 or more marks will be deducted for each plot that lacks colour or is displayed inappropriately (e.g.
missing axis labels, messy view).
Overall 2 marks will be deducted if the R code is poorly commented with lack of explanation.
0 comments