---
title: "Exercise: Intermediate Data Management"
author: "Rian DeFaccio, for BH Analytics LLC"
date: "`r Sys.Date()`"
output: html_document
---

To begin, load any libraries you would like to use.

```{r}
library(tidyverse)
```

### 1. Import Data

------------------------------------------------------------------------

Import the *data/Customer.csv* file. This has information on customers from a simulated baking system. Do a quick check of the data after you import it.

```{r}
# import data
customer <- read.csv('../data/Customer.csv')
# do a quick initial look
head(customer)
```

```{r}
# try another way to do an initial look at the data
str(customer)
```

### 2. Clean Up `UrbanRural` Column

------------------------------------------------------------------------

Next we would like to clean up the `UrbanRural` column.

Begin by examining this variable somehow. How would you examine a single categorical variable like this? Try to do this **numerically** first.

```{r}
# numerical summary
table(customer$UrbanRural, useNA = 'ifany')
```

Do this again, but do it visually this time. Take your choice of base R or ggplot2 plotting functions. Or... try it using both methods!

```{r}
# base R
table(customer$UrbanRural, useNA = 'ifany') %>%
  barplot(main = "UrbanRural Column Counts")

# ggplot2
ggplot(customer, aes(x=UrbanRural)) + 
  geom_bar() +
  ggtitle("UrbanRural Column Counts")
```

This variable needs some cleaning up. Harmonize the levels of this variable to be just **[Rural, Urban, NA]**. Try using the base R `ifelse()` or dplyr `case_when()` function to do this. Save a new version of this variable called `UrbanRuralClean`. There are many ways to do this!

```{r}
# create new column
customer <- customer %>%
  mutate(
    UrbanRuralClean = 
      ifelse(
        UrbanRural %in% c('Urban','Rural'), 
        UrbanRural, 
        NA
      ),
    UrbanRuralClean2 = 
      case_when(
        UrbanRural %in% c('Urban','Rural') ~ UrbanRural
      )
    )

# base R
customer$UrbanRuralClean <- case_when(
  customer$UrbanRural %in% c('Urban','Rural') ~ customer$UrbanRural
)

# check your work
table(customer$UrbanRuralClean, useNA = 'ifany')


```

### 3. Create Discrete Credit Score Variable

------------------------------------------------------------------------

Before we begin working with the credit score variable, let's get some summary numbers on the original credit score column. Can you do something below to learn about the distribution of this data? Do this **numerically** and **visually**. Is there anything striking about this data?

```{r}
# numeric summary
summary(customer$CreditScore)
# whoa, those high scores are technicaly impossible
```

```{r}
# visual summary
ggplot(customer, aes(CreditScore)) + 
  geom_histogram(bins=50) +
  ggtitle("There are Some Implausible Credit Scores...") +
  ggthemes::theme_excel()
```

Now we would like you to create a discrete version of the `CreditScore` column. Call your new variable `CreditScoreCat`. Here are the categories we'd like you to use:

-   800 to 850: **Excellent**
-   740 to 799: **Very good**
-   670 to 739: **Good**
-   580 to 669: **Fair**
-   300 to 579: **Poor**
-   Any number out of this range: **NA**

```{r}
# build your column here
# two versions
# - one uses dplyr::case_when()
# - the other uses base R cut()
customer <- customer %>%
  mutate(
    CreditScoreCat = case_when(
      CreditScore >= 300 & CreditScore <= 579 ~ 'Poor',
      CreditScore >= 580 & CreditScore <= 669 ~ 'Fair',
      CreditScore >= 670 & CreditScore <= 739 ~ 'Good',
      CreditScore >= 740 & CreditScore <= 799 ~ 'Very Good',
      CreditScore >= 800 & CreditScore <= 850 ~ 'Excellent',
      TRUE                                    ~ NA
    ),
    CreditScoreCat2 = cut(
      CreditScore,
      breaks = c(300, 580, 670, 740, 800, 851),
      labels = c('Poor','Fair','Good','Very Good','Excellent'),
      right = FALSE
    )
  )
```

Check your work by examining your new variable `CreditScoreCat`. Do this **numerically** and **visually**.

```{r}
# check your work (numeric summary)
table(customer$CreditScoreCat, useNA = 'ifany') %>% sort()
```

```{r}
# check your work (visual summary)
table(customer$CreditScoreCat, useNA = 'ifany') %>% 
  sort() %>%
  barplot(main = 'Discretized Credit Score')
```

Let's build one more new column. Create a variable called `CreditScoreBinary` which equals either `Excellent` or `Other`. Build this new column and then examine your results.

```{r}
# build new column
customer <- customer %>%
  mutate(
    CreditScoreExcellent = ifelse(
      CreditScoreCat == 'Excellent',
      'Excellent',
      'Other'
    )
  )

# check your work
table(customer$CreditScoreExcellent, useNA = 'ifany')
```

### 4. Build a Couple More Columns

------------------------------------------------------------------------

Let's build a couple of other columns:

-   `CreditScoreBinary`: Equals either `Excellent` or `Other` depending on credit score.

-   `RiskStrata`:

    -   `High`: When customers are `Rural` and have credit scores of lower than 600.
    -   `Medium`: When customers are `Rural` and have credit scores of 600 or higher.
    -   `Low`: All the remaining customers (who are these people?)

```{r}
# build new columns
customer <- customer %>%
  mutate(
    CreditScoreBinary = ifelse(
      CreditScoreCat == "Excellent", 'Excellent', 'Other'
    ),
    RiskStrata = case_when(
      UrbanRural == 'Rural' & CreditScore < 600  ~ 'High',
      UrbanRural == 'Rural' & CreditScore >= 600 ~ 'Medium',
      UrbanRural == 'Urban'                      ~ 'Low'
    )
  )
```

Now check your work pertaining to each of your new columns. A pattern you can use to compare a new column against an old column is as follows:

`table(df$col1, df$col2)`

See if you can interpret the output, your instructor will follow-up on this.

```{r}
# check your work on CreditScoreBinary
table(customer$CreditScoreBinary, 
      customer$CreditScoreCat,
      useNA = 'ifany')
```

```{r}
# check your work on RiskStrata
table(customer$RiskStrata,
      customer$UrbanRuralC,
      useNA = 'ifany')
```
