---
title: "Data Cleaning Lab Key"
output: html_document
editor_options: 
  chunk_output_type: console
---

# Data used

Bike Lanes Dataset: BikeBaltimore is the Department of Transportation's bike program. 
The data is from http://data.baltimorecity.gov/Transportation/Bike-Lanes/xzfj-gyms

You can Download as a CSV in your current working directory.  Note its also available at: 	http://johnmuschelli.com/intro_to_r/data/Bike_Lanes.csv 

```{r}
library(readr)
library(tidyverse)
library(dplyr)
library(lubridate)
library(jhur)
library(tidyverse)
library(broom)

bike = read_csv(
  "http://johnmuschelli.com/intro_to_r/data/Bike_Lanes.csv")
```

# Part 1

1.  Count the number of rows of the bike data and count the number of complete cases of the bike data.  Use `sum` and `complete.cases`.
```{r}
nrow(bike)
sum(complete.cases(bike))
```

2.  Create a data set called `namat` which is equal to `is.na(bike`).  
What is the class of `namat`?  Run rowSums and colSums on `namat.`
These represent the number of missing values in the rows and columns of
`bike.`  Don't print `rowSums`, but do a table of the `rowSums`
```{r}
namat = is.na(bike)
colSums(namat)
table(rowSums(namat))

```

3.  Filter rows of bike that are NOT missing the `route` variable, assign
this to the object `have_route.`  Do a table of the `subType` using `table`, 
including the missing `subType`s  Get the same frequency distribution
using `group_by(subType)` and `tally()`
```{r}
have_route = bike %>%  filter(!is.na(route))
dim(have_route)

table(have_route$subType, useNA = "always")
have_route %>%  group_by(subType) %>%  tally()
have_route %>%  count(subType)

have_route %>% group_by(subType) %>% 
  summarize(n_obs = n())

tally(   group_by(have_route, subType) )
have_route = group_by(have_route, subType)
tally(have_route)
```

4.  Filter rows of bike that have the type `SIDEPATH` or `BIKE LANE`
using `%in%`.  Call it `side_bike.`   Confirm this gives you the same number of results using the `|` and 
`==`.
```{r}
side_bike = bike %>% filter(type %in% c("SIDEPATH", "BIKE LANE"))
side_bike2 = bike %>% filter(type == "SIDEPATH" | type == "BIKE LANE")
identical(side_bike, side_bike2)
nrow(side_bike)
nrow(side_bike2)

side_bike = filter(bike,type %in% c("SIDEPATH", "BIKE LANE"))
side_bike2 = filter(bike, type == "SIDEPATH" | type == "BIKE LANE")
identical(side_bike, side_bike2)
```

# Part 2

5.  Do a cross tabulation of the bike `type` and the number of lanes.
Call it `tab`.  Do a `prop.table` on the rows and columns margins. 
Try `as.data.frame(tab)` or `broom::tidy(tab)`
```{r}
tab = table(type=bike$type, numLanes=bike$numLanes)
prop.table(tab, 1)
prop.table(tab, 2)
as.data.frame(tab)
tidy(tab)


```

# Part 3

**New Data set**

Download the "Real Property Taxes" Data from my website (via OpenBaltimore):
http://johnmuschelli.com/intro_to_r/data/Real_Property_Taxes.csv.gz
note you don't need to unzip it to read it into R



6. Read the Property Tax data into R and call it the variable `tax`
```{r}
tax = read_csv( "http://johnmuschelli.com/intro_to_r/data/Real_Property_Taxes.csv.gz")
```

7. How many addresses pay property taxes? 
```{r}
dim(tax)
nrow(tax)
length(tax$PropertyID)

sum(is.na(tax$CityTax))
sum(!is.na(tax$CityTax))

```

8. What is the total city and state tax paid?  
You need to remove the `$` from the `CityTax` variable
then you need to make it numeric.   Try `str_replace`, but remember
$ is "special" and you need `fixed()` around it.

a) city
```{r}
head(tax$CityTax)
tax = tax %>% 
  mutate(
    CityTax = str_replace(CityTax, 
      fixed("$"), "") ,
    CityTax = as.numeric(CityTax)
  )

# no piping
tax$CityTax = str_replace(tax$CityTax, fixed("$"), "")
tax$CityTax = as.numeric(tax$CityTax)

```

b) state
```{r}
## useing parse_numbers()
options(digits=12) # so no rounding
tax = tax %>% mutate(StateTax = parse_number(StateTax))
                     
sum(tax$CityTax)
sum(tax$CityTax, na.rm = TRUE)
sum(tax$CityTax, na.rm = TRUE)/1e6

sum(tax$StateTax, na.rm = TRUE)
sum(tax$StateTax, na.rm = TRUE)/1e6

```

9. Using `table()` or `group_by` and `summarize(n())` or `tally()`

a. how many observations/properties are in each ward?
```{r}
table(tax$Ward)

ward_table = tax %>% 
  group_by(Ward) %>% 
  tally()

ward_table = tax %>% 
  group_by(Ward) %>% 
  summarize(number_of_obs = n(),
            mean(StateTax, na.rm = TRUE))


```

b. what is the mean state tax per ward? use `group_by` and `summarize`
```{r}
tax %>%   group_by(Ward) %>% 
  summarize(mean_state = mean(StateTax, na.rm = TRUE))


```

c. what is the maximum amount still due in each ward?  different summarization (`max`)
```{r}
tax$AmountDue = tax$AmountDue %>% 
  str_replace(fixed("$"), "") %>%
  as.numeric

tax = tax %>% mutate(
  AmountDue = as.numeric(str_replace(AmountDue, fixed("$"), ""))
)

tax %>% group_by(Ward) %>% 
  summarize(maxDue = max(AmountDue, na.rm = TRUE))

```

d. What is the 75th percentile of city and state tax paid by Ward? (`quantile`)
```{r}
tax %>% group_by(Ward) %>% 
  summarize(Percentile = quantile(StateTax, prob = 0.75,na.rm = TRUE))


ward_table = tax %>% 
  group_by(Ward) %>% 
  summarize(
    number_of_obs = n(),
    mean_state_tax = mean(StateTax, na.rm = TRUE),
    max_amount_due = max(AmountDue, na.rm = TRUE),
    q75_city = quantile(CityTax, probs = 0.75, na.rm = TRUE),
    q75_state = quantile(StateTax, probs = 0.75, na.rm = TRUE)
  )
```

10. Make boxplots using showing `cityTax` (`y` -variable) by whether the property	is a principal residence (`x`) or not.
```{r}
tax = tax %>% 
  mutate(ResCode = str_trim(ResCode))

qplot(y = log10(CityTax+1), x = ResCode, data = tax, geom = "boxplot")
qplot(y = CityTax, x = ResCode, data = tax, geom = "boxplot")

boxplot(log10(CityTax+1) ~ ResCode, data = tax)
boxplot(CityTax ~ ResCode, data = tax)

tax %>% filter(CityTax == max(CityTax, na.rm = TRUE))

```

11. Subset the data to only retain those houses that are principal residences. 
which command subsets rows? Filter or select?

a) How many such houses are there?
```{r}
pres = tax %>% filter( ResCode %in% "PRINCIPAL RESIDENCE")
pres = tax %>% filter( ResCode == "PRINCIPAL RESIDENCE")
dim(pres)
```

b) Describe the distribution of property taxes on these residences.  Use 
hist with certain breaks or `plot(density(variable))`
```{r}
qplot(x = log10(CityTax+1),data = pres, geom = "histogram")
qplot(x = CityTax, data = pres, geom = "density")

hist(log2(pres$CityTax+1))
hist(pres$CityTax)
head(pres$CityTax)
plot(density(pres$CityTax,  na.rm = TRUE))
```

**New Data Set**

Read in the Salary FY2015 dataset
```{r}

sal = read_csv("http://johnmuschelli.com/intro_to_r/data/Baltimore_City_Employee_Salaries_FY2015.csv")
```

12. Make an object called health.sal using the salaries data set, with only agencies of those with `"fire"` (or any forms), if any, in the name remember `fixed( ignore_case = TRUE)` will ignore cases
```{r}
health.sal = sal %>% 
  filter(str_detect(JobTitle, 
                    fixed("fire", ignore_case = TRUE)))
```

13. Make a data set called `trans` which contains only agencies that contain "TRANS".
```{r}
trans = sal %>% 
  filter(str_detect(JobTitle, "TRANS"))
```

14. What is/are the profession(s) of people who have `"abra"` in their name for Baltimore's Salaries?  Case should be ignored
```{r}
sal %>% 
  filter(str_detect(name, fixed("abra", ignore_case = TRUE)))
```

15. What is the distribution of annual salaries look like? (use `hist`, 20 breaks) What is the IQR?
Hint: first convert to numeric. Try `str_replace`, but remember`$` is "special" and you need `fixed()` around it.
```{r}
sal = sal %>% mutate(AnnualSalary = str_replace(AnnualSalary, fixed("$"), ""))
sal = sal %>% mutate(AnnualSalary = as.numeric(AnnualSalary))
qplot(x = AnnualSalary, data = sal, geom = "histogram", bins = 20)

hist(sal$AnnualSalary, breaks = 20)
quantile(sal$AnnualSalary)
```

16. Convert `HireDate` to the `Date` class - plot Annual Salary vs Hire Date.
Use `AnnualSalary ~ HireDate` with a `data = sal` argument in plot or use 
x, y notation in `scatter.smooth`
Use `lubridate` package.  Is it `mdy(date)` or `dmy(date)` for this data - look at `HireDate`
```{r}
sal = sal %>% mutate(HireDate = lubridate::mdy(HireDate))

q = qplot(y = AnnualSalary, x = HireDate, 
      data = sal, geom = "point")
q + geom_smooth(colour = "red", se = FALSE)
q + geom_smooth(colour = "red", se = FALSE, method = "loess")
q + geom_smooth(colour = "red", se = FALSE, method = "loess", span = 2/3)

plot(AnnualSalary ~ HireDate, data = sal)
scatter.smooth(sal$AnnualSalary, x = sal$HireDate, col = "red")

```

17. Create a smaller dataset that only includes the Police Department,  Fire Department and Sheriff's Office.  Use the Agency variable with string matching. Call this `emer`.  How many employees are in this new dataset?
```{r}
emer = sal %>% filter(
  str_detect(Agency, "Sheriff's Office|Police Department|Fire Department")
)

emer = sal %>% filter(
  str_detect(Agency, "Sheriff's Office") |
    str_detect(Agency, "Police Department") |
    str_detect(Agency, "Fire Department")
)

```

18. Create a variable called `dept` in the emer data set.
`dept = str_extract(Agency, ".*(ment|ice)")`.  E.g. want to extract all characters
up until `ment` or `ice` (we can group in `regex` using parentheses) and then discard
the rest. Replot annual salary versus hire date, color by `dept` (not yet - using ggplot)
```{r}
emer = emer %>% 
  mutate(
    dept = str_extract(Agency, ".*(ment|ice)")
  )
# Replot annual salary versus hire date, color by dept (not yet - using ggplot)
ggplot(aes(x = HireDate, y = AnnualSalary, 
           colour = dept), data = emer) + 
  geom_point() + theme(legend.position = c(0.5, 0.8))
```


19 (Bonus). Convert the 'LotSize' variable to a numeric square feet variable in the tax data set. Some tips: 
a) 1 acre = 43560 square feet
b) The hyphens represent inches (not decimals)


```{r}
tax$LotSize = str_trim(tax$LotSize) # trim to be safe
lot = tax$LotSize # for checking later
```

First lets take care of acres

```{r}
aIndex= which(str_detect(tax$LotSize, "AC.*") | 
            str_detect(tax$LotSize, fixed(" %")))
head(aIndex)
head(lot[aIndex])

acre = tax$LotSize[aIndex] # temporary variable
## find and replace character strings
acre = str_replace_all(acre, " AC.*","")
acre = str_replace_all(acre, " %","")
table(!is.na(as.numeric(acre)))

head(acre[is.na(as.numeric(acre))],50)

## lets clean the rest
acre = str_replace_all(acre, "-",".") # hyphen instead of decimal
head(acre[is.na(as.numeric(acre))])
table(!is.na(as.numeric(acre)))

acre = str_replace_all(acre, "ACRES","")
head(acre[is.na(as.numeric(acre))])

# take care of individual mistakes
acre = str_replace_all(acre, "O","0") # 0 vs O
acre = str_replace_all(acre, "Q","") # Q, oops
acre = str_replace_all(acre, ",.",".") # extra ,
acre = str_replace_all(acre, ",","") # extra ,
acre = str_replace_all(acre, "L","0") # leading L
acre = str_replace_all(acre, "-",".") # hyphen to period
acre[is.na(as.numeric(acre))]

acre2 = as.numeric(acre)*43560 
sum(is.na(acre2)) # all but 3
```

Now let's convert all of the square feet variables

```{r}
library(purrr)
fIndex = which(str_detect(tax$LotSize, "X"))

ft = tax$LotSize[fIndex]

ft = str_replace_all(ft, fixed("&"), "-")
ft = str_replace_all(ft, "IMP ONLY ", "")
ft = str_replace_all(ft, "`","1")

ft= map_chr(str_split(ft, " "), first)

## now get the widths and lengths
width = map_chr(str_split(ft,"X"), first)
length = map_chr(str_split(ft,"X"), nth, 2) 

## width
widthFeet = as.numeric(map_chr(str_split(width, "-"), first))
widthInch = as.numeric(map_chr(str_split(width, "-"),nth,2))/12
widthInch[is.na(widthInch)] = 0 # when no inches present
totalWidth = widthFeet + widthInch # add together

# length
lengthFeet = as.numeric(map_chr(str_split(length, "-"),first))
lengthInch = as.numeric(map_chr(str_split(length, "-",2),nth,2))/12
lengthInch[is.na(lengthInch)] = 0 # when no inches present
totalLength = lengthFeet + lengthInch

# combine together for square feet
sqrtFt = totalWidth*totalLength 
ft[is.na(sqrtFt)] # what is left?
```

And now we combine everything together:

```{r}
tax$sqft = rep(NA)
tax$sqft[aIndex] = acre2
tax$sqft[fIndex] = sqrtFt
mean(!is.na(tax$sqft))

# already in square feet, easy!!
sIndex=which(str_detect(tax$LotSize, "FT") | str_detect(tax$LotSize, "S.*F."))
sf = tax$LotSize[sIndex] # subset temporary variable

sqft2 = map_chr(str_split(sf,"( |SQ|SF)"),first)
sqft2 = as.numeric(str_replace_all(sqft2, ",", "")) # remove , and convert
tax$sqft[sIndex] = sqft2
table(is.na(tax$sqft)) 
## progress!

#what remains?
lot[is.na(tax$sqft)]
```
