```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```


##Instructions
Complete this assignment in RStudio. Run all of the chunks, then Knit the markdown file to html. Upload that HTML file to the HW6 assignment on Sakai.

###Load the tidyverse package(s)
```{r}
#install.packages("tidyverse")
library(tidyverse)
```


### Load the data
If you have not already loaded the police incident data into R, run the following code chunk. WARNING: If you run this chunk and you already have a dataframe called "MLBPayrolls" then this will overwrite it. That probably won't be a big deal, but I just wanted to make you aware for future cases where it might be a big deal.

```{r}
library(readxl)
url <- "http://mjwebster.github.io/DataJ/spreadsheets/Police_Incidents_2015.xlsx"
destfile <- "Police_Incidents_2015.xlsx"
curl::curl_download(url, destfile)
incidents <- read_excel(destfile, 
    col_types = c("text", "text", "text", 
        "text", "date", "date", "text", "text", 
        "text", "text", "numeric", "numeric", 
        "text"), skip = 2)
View(incidents)
```


### 1. How many crimes were reported in each neighborhood?
```{r}
incidents %>%
  group_by(Neighborhood) %>%
  summarize(crimes=n())
```


### 2. How many of the crimes in each neighborhood were "THEFT"?
```{r}
incidents %>%
  group_by( ) %>%
  summarize(     )
```


### 3. How many crimes happened in Shingle Creek neighborhood in August?
```{r}
# To do this, we have to load a package called "lubridate". We will discuss this much more later.
library(lubridate)
incidents %>%
  filter(month(ReportedDate) == 8, ) %>%
  group_by( ) %>%
  summarize(     )
```

### If you want to see all months, it gets a bit more complicated...
First, you have to group by two columns. First put the rows into Neighborhood buckets and then subndivide each of those neighborhood buckets into month buckets.

```{r}
incidents %>%
  group_by(   ,  ) %>%
  summarize( )
```

But that doesn't make it very easy to compare month to month and neighborhood at the same time. It's much better to have the cross-tab format that we easily get in an Excel pivot table.

To make that same format we have to introduce a new function called `pivot_wider()`. (You may see some older references to spread(), but that started to lose favor within the last year.)

```{r}
incidents %>%
  group_by(     ,     ) %>%
  summarize( ) %>%
  pivot_wider(names_from="month(ReportedDate)", values_from="crimes") 
```

**For the next several questions, we need to load another sheet from the MLBpayrolls.xls file.**
```{r}
library(readxl)
url <- "https://mjwebster.github.io/DataJ/spreadsheets/MLBpayrolls.xls"
destfile <- "MLBplayers.xls"
curl::curl_download(url, destfile)
MLBplayers <- read_excel(destfile, sheet="Twins-Brewers")
View(MLBplayers) 
```


### 4. Display the total salary made by all players at each position.
```{r}
MLBplayers %>%
       %>% #group here
           #summarize here
```

### 5. Use the code from Question 4 and add a column that shows the number of players at each position.
```{r}
MLBplayers %>%
           %>% #group here
  #summarize here
```

### 6. Show the average salary that each team pays for all players at each position
```{r}
MLBplayers %>%
  %>% #group here
  %>% #summarize here
  #pivot_wider here
```

###7. Make it pretty with the scales package and transmute. (But the downside is that you turn a perfectly good numeric data type into a useless character data type.)
```{r}
library(scales)
MLBplayers %>%
           %>% #group here
  %>% #summarize here
  %>% #pivot_wider here
  transmute(Brewers=dollar(Brewers),Twins=dollar(Twins)) #transmute replaces columns rather than adds.
#... and the dollars() function comes from the scales package.
```

###8. How much more (or less) do the Twins pay players at each position?
```{r}
MLBplayers %>%
           %>% #group here
    %>%#summarize here
    %>%#pivot_wider here
    %>%#mutate here
```


###9. How much of each team's salary is spent on each position?
```{r}
MLBplayers %>%
  group_by(           ) %>%
  summarize(          ) %>%
  pivot_wider(             ) %>%
  ungroup %>%
  mutate(              )

#Why do we have to use ungroup() here? Essentially, it converts what we've got into a "real" dataframe. Run the following two statements and look at the differences. When you str(grouped_df) you see additional metadata about how the variables are grouped. In the str(ungrouped_df) that metadata isn't there. The dataframe is WYSIWYG.

#MLBplayers %>%
#  group_by(POSITION, TEAM) %>%
#  summarize(TotalSalary=sum(SALARY)) %>%
#  pivot_wider(names_from=TEAM, values_from=TotalSalary) -> grouped_df

#MLBplayers %>%
#  group_by(POSITION, TEAM) %>%
#  summarize(TotalSalary=sum(SALARY)) %>%
#  pivot_wider(names_from=TEAM, values_from=TotalSalary) %>%
#  ungroup() -> ungrouped_df

#str(ungrouped_df)
#str(grouped_df)

```

##You will do this portion on your own at home.

###10. Load the UNC Salary Data
```{r}

#We need to install janitor, a very useful package we need here to clean column names, but which we will use much later.
#https://www.rdocumentation.org/packages/janitor/versions/1.2.0
#install.packages("janitor")
library(janitor)

#Original source of the data: https://www.newsobserver.com/news/databases/public-salaries/article11863496.html?appSession=1403UK9UD78GOBC8IYH2RA16T4R98H6M0ERC7953FHQ895710Y670SYY3FQ9671YBURD70R8COF824IR86U6Q1X68ZNJDX3VAE3J47S5Z6P71O4Y5OF4ITN748WE5638
#Source of CSV I saved to Sakai on Feb 17, 2020 at 11 a.m.: https://sakai.unc.edu/access/content/group/180aeff4-e971-45c7-a2a6-9f5f9d9c3785/UNC_System_Salaries%20Search%20and%20Report%202.csv


library(readr)
UNCsalaries <- read_csv("https://sakai.unc.edu/access/content/group/180aeff4-e971-45c7-a2a6-9f5f9d9c3785/UNC_System_Salaries%20Search%20and%20Report%202.csv", 
    col_types = cols(`Total salary` = col_number()))

#here is that janitor function that we will use to make pretty column names
UNCsalaries <- clean_names(UNCsalaries)

#since we already have lubridate loaded
#The mdy() function from lubridate "magically" guesses the date format from character text
UNCsalaries$hire_date <- mdy(UNCsalaries$hire_date)

View(UNCsalaries)
```

###11. What is the most common month for UNC to hire employees? (Your result should have two columns -- one named "month" and one named "employees".)
```{r}


```

###12. For each department, show the average salary, the number of employees, the maximum salary, and the minimum salary. Arranged your data from greatest average salary to least. (Answer should have five columns -- one called "department", one called "AvgSalary", one called "employees", one called "maxSalary", one called "minSalary".)
```{r}

```


###13. Come up with your own reporting question, ask it in R and translate the answer back into one English sentence. Add your sentence below the code.
```{r}

```


