BUS5DWR - Assignment 3 (40%)The purpose of this assignment is to develop and assess your skills in R programming including wrangling, summarising and plotting data. Using the tidyverse package is recommended but not compulsory. Please read through the entire assignment and understand the submission format and marking rubrics before starting.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.1. Explain why the data in its current form is not considered to be in ‘tidy’ format. (1 mark)2. 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_unstated,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 formBanyule,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.1. Give the five neighbourhoods with the most listings (list them along with the counts in descending order). (1 mark)2. How many listings contain the following words (upper or lower case or mixed) in the name column?a. Beautifulb. Quietc. Amazingd. (2 marks)3. How many listings are there with last review in 2016? Give month by month counts for the year 2016. (2 marks)4. 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)5. Write a function that inputs a listing id and outputs a score that is the sum of points according to the following criteria:a. Points for the neighbourhood: (average number of bedrooms per dwelling in 2016) × 50 (this comes from the data set in Part 1)b. Points for the room type: 200 for Entire home/apt, 100 for Private room, 0 for Shared roomc. Points for minimum nights: 50 for 1 night, 25 for 2 nights, 0 for 3 or more nightsd. Points for availability: (availability_365) divided by 5e. Points for review frequency: 50 × (reviews per month), but no more than 100f. Points for price: (300 minus price)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]SubmissionYour 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 RubricsPlease 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.