Exploratory Analysis on MyLA311 Service Request Data 2020

### Overview
Analyze [MyLA311 Service Request Data 2020](https://data.lacity.org/City-Infrastructure-Service-Requests/MyLA311-Service-Request-Data-2020/rq3b-xjk8), a public dataset available for preview and download (1,491,773 records, 34 feature columns), to understand what kind of stories (insight) we could tell based on it

### Action Items

- [x] Dataset preprocessing, overview check NA, duplicated records, and looking for any potential issues with the dataset
- [x] EDA on interesting features

### ** possible next step analysis: focus on graffiti (NEW) **
put the data into context & dig in to understand graffiti from "in-depth" (data literacy) data analysis:
(need to find other datasets)
- [ ] relation with income level (wealth, safety, crime)
- [ ] relation with population (maybe only useful to compare similar areas (define "area") )
- [ ] aggregate by council districts
- [ ] possibly tag some areas (university, tourism, downtown, residency, commercial) -> could be difficult to find the dictionary

### Analysis & Findings
1. 104 NC, 203 NC names, more than the actual 99 NC's
2. Find that `ServiceDate` and `ClosedDate` have NA values
3. Find one request that has a typo of `ServiceDate` and `ClosedDate` (year=3020)

------- (Analysis below is based on requests (94.96%) with valid `CreatedDate`, `UpdatedDate`, `ServiceDate` and `ClosedDate` )-------

4. Compare distribution of `CreatedDate`, `UpdatedDate`, `ServiceDate` and `ClosedDate` by hours
- 1,278,193 (90.23%) requests have `ServiceDate` during 00:00 to 00:59
5. Compute new columns: duration between `CreatedDate` and each of `UpdatedDate`, `ServiceDate` and `ClosedDate`
6. Find that there are requests with process hours <0
7. Compare
- ratios of requests with `ServiceData` on the same day as `CreatedDay` in total requests of a type
- ratios of requests of a type in total requests
8. Analysis on Graffiti Removal, the request type that has the most `ServiceDate` later than `CreatedDate`
- by processing hours
- by processing days
9. Count of `RequestType`, `RequestSource` and `Status`

### Stories

- Make Suggestions to the database (based on merely analyzing if the values of certain features make sense):
1. NC names need more consistency: e.g ''GRANADA HILLS NORTH NC" & "Granada Hills North" ; "SYLMAR NC" & "Sylmar"
2. Convert timestamp to 24-hour time to facilitate data analysis
3. New mechanism for ServiceDate & ClosedDate (possibly, some systems record the ServiceDate to 00:00 on the date of service by default, creating problems when considering processing days or hours if w/o explanations)
- `RequestSource`: Driver Self Report, Voicemail
- `RequestType`: Graffiti Removal, Multiple Streetlight Issue, Single Streetlight Issue
- `Status`: all
- `PolicePrecinct`: CENTRAL, NEWTON
- `AssignTo`:
- `Owner`: BSL, OCB

- Graffiti Removal Processing Days:
- Understand how to look at boxplot and find outliers by IQR
- Aggregate hours to days to have more insights
- 91.40% Graffiti Removal requests are processed within 2 days after the day request created
- 67.68% Graffiti Removal requests are processed on the same day
- 3.2% Graffiti Removal requests are processed after a week
- 0.79% Graffiti Removal requests are processed after a month
- 0.09% Graffiti Removal requests are processed after a year

------------------------------------__UPDATE__------------------------------------
### Data cleaning
each record has 4 timestamp columns: CreatedDate, UpdatedDate, ServiceDate, ClosedDate
- `valid data`: all the timestamp columns are in 2020 and ServiceDate>CreatedDate and ClosedDate>CreatedDate
- `NA data`: records with NA (missing value)
- `NC`: some NC's are not in the LA NC's area but still in the data and 1 NC is misclassified: remove NC's not in the LA NC's area and correct the misclassified one

### Analytical concepts:
- how outliers affect data and the difficulty of dealing with it; average, median
- how to define, find and remove outliers
- by comparing simple bar plots, we could derive many insights
- differences between a bar plot and histogram
- how to deal with missing values and invalid data: don't make assumpting of missing data before knowing why they are missing. missing values in categorical features could be treated as another level, which can be used to derive insights
- understanding meaning of a column before analysis

### Google Colab Notebook
[data cleaning](https://colab.research.google.com/drive/1FiL56X-2V7_qDKfROjuJW6rqgsunf4rS?authuser=1)
[EDA](https://colab.research.google.com/drive/1bR1Q7DTtxgRaTnawv1An8A0bxfgXQ_j8?authuser=1)