Use tidyverse and bash to explore following two data sets.

Q1 LA City Employee Payroll

The /home/m280data/la_payroll/City_Employee_Payroll.csv file on teaching server contains payroll information of LA City employees in years 2013-2018. It was downloaded from LA City Controller’s Office. Make a Shiny app to facilitate visualization of this data.

  1. For efficiency of the Shiny app, you should first pre-process, pare down, tidy, and save the data, e.g., as a compressed RDS file, to be used in the app.

  2. Total payroll by LA City. Visualize the total LA City payroll of each year, with breakdown into base pay, overtime pay, and other pay.

  3. Who earned most? Visualize the payroll information (total payment with breakdown into base pay, overtime pay, and other pay, Department, Job Title) of the top \(n\) highest paid LA City employees in a specific year. User specifies \(n\) (default 10) and year (default 2017).

  4. Which departments earn most? Visualize the mean or median payroll, with breakdown into base pay, overtime pay, and other pay, of top \(n\) earning departments. User specifies \(n\) (default 5), year (default 2017), and method (mean or median, default median).

  5. Which departments cost most? Visualize the total payroll, with breakdown into base pay, overtime pay, and other pay, of top \(n\) expensive departments. User specifies \(n\) (default 5) and year (default 2017).

  6. Visualize any other information you are interested in.

  7. Publish your Shiny app to https://www.shinyapps.io and share the link.

Q2 LA City Parking War

The SQLite database /home/m280data/la_parking/LA_Parking_Citations.sqlite on teaching server contains information about parking tickets in LA City. It was downloaded from LA Open Data Portal. Connect to the database and answer following questions using plots and summary statistics. In this exercise, you are not allowed to load whole data into memory. Use the transform in database, plot in R strategy.

  1. How many tickets are in this data set? Which time period do these tickets span? Which years have most data?

  2. When (which hour, weekday, month day, and month) are you most likely to get a ticket and when are you least likely to get a ticket?

  3. Which car makes received most citations?

  4. How many different colors of cars were ticketed? Which color attracted most tickets?

  5. What are the most common ticket types?

  6. How much money was collected on parking tickets in 2016, 2017 and 2018?

  7. If you’ve been ticketed in LA County, did you find your ticket in this data set?

  8. Read the blog http://www.brettrics.com/9-million-parking-tickets-la/ and try to reproduce plots using your data. pdf