---
title: "Intermediate Data Management with dplyr"
author: "Rian DeFaccio, for BH Analytics LLC"
date: "`r Sys.Date()`"
output: 
  html_document:
    toc: true
    toc_depth: 3
---

```{r}
knitr::opts_knit$set(warning = FALSE, message = FALSE)
knitr::opts_chunk$set(warning = FALSE, message = FALSE)
```

```{r}
# load libraries and adjust settings
library(tidyverse)
```

## Introduction

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

This lesson introduces us to some more intermediate data management tasks and shows how to accomplish them using Tidyverse packages such as readr and dplyr.

## Importing Data

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

You can use base R functions like `read.csv()` to import data. However, R has to try and guess what each column should be (unless you specify it) which can also make importing data slow on larger data sets.

For this reason, there are a few different packages implemented to make reading data a bit easier. We will focus on the readr package, which is part of the tidyverse collection of packages.

For these simple cases, we can just use `read_csv()` to replace `read.csv()`.

The main difference you will notice is:

-   The print method for the data frame is nicer (it won't print the whole data frame and overwhelm your screen). This is because the return object is actually a `tbl` (pronounced tibble), which is just a fancy version of a data frame.
-   larger data sets should read in much more quickly, because `readr` uses a very efficient package called vroom in the background.
-   readr prints the column types to your console by default.
-   you can select which columns you want to import (very useful with larger data sets).

```{r}
# import the simulated patient data set
df_patient <- 
  read_csv('../data/patient_traits.csv')

# Examine the file after you import (as always!)
df_patient
```

```{r}
str(df_patient)
```

```{r}
class(df_patient)
```

```{r}
# import pipe delimited file
df2 <- 
  read_delim(file = '../data/chickweight.txt',
             delim = '|')
df2
```

### 🛠️Coding Practice: Fix the Import Code

Can you fix the above example? We accidentally imported the first row of data as column names. You could call `?read_delim` to get some help finding the right argument to change.

As hint, look at the documentation for this data set to see what the column names should be:

🌐 <https://www.rdocumentation.org/packages/datasets/versions/3.6.2/topics/ChickWeight>

```{r}
# FIX THIS CODE...
df2 <- 
  read_delim(file = '../data/chickweight.txt',
             delim = '|')
df2
```

**END OF EXERCISE**

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

### Only Import Certain Columns

```{r}
# only import certain columns
df_patient_small <- 
  read_csv('../data/patient_traits.csv',
           col_select = c(p_id, Age))
df_patient_small
```

### Import Data from Other Sources

One other way to get data is from R packages. Data inside of R packages is usually saved as a *.rds* file, which we can access from the package using the `data()` function.

```{r}
data(starwars, package = "dplyr")
head(starwars)
```

You can also import Excel files:

-   the `read_excel()` function from the readxl package reads both *.xls* and *.xlsx* files.
-   It is sometimes easier to open any excel file in Excel, save it as a CSV, and then import it into R using `read_table()`.

```{r}
xl_df <- readxl::read_excel("../data/datasets.xlsx")
head(xl_df, 3)
```

If you need to import from SAS, SPSS, or STATA you can use the haven package.

```{r, eval=FALSE}
# library(haven)
# ?haven::read_sas
# ?haven::read_sav
# ?haven::read_stata
```

Importing data from relational databases is the final import method we will mention.

-   The RODBC package is what I always use when importing from Microsoft SQL Server.
-   you must set up DSN through your operating system. Once the connection (DSN) is set up, you can easily write SQL queries and submit them, returning an R data frame.
-   The RMySQL package works well for MySQL data sources.

## Working with Column Names

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

Next let's consider how we interact with the column names? Possibly rename them?

You can access the column names of the data frame with `names()` like so:

```{r}
names(df_patient)

# also...
# colnames(df_patient)
```

You can even update the names object by assigning values into it:

```{r}
# save names
df_pat_names <- names(df_patient)
df_pat_names
```

```{r}
# assign new names as upper case
names(df_patient) <- 
  toupper(df_pat_names)
head(df_patient)
```

```{r}
# restore original case
names(df_patient) <- 
  df_pat_names
head(df_patient)
```

If you want to change them one by one, you have to subset accordingly:

```{r}
# change p_id to patient_id
names(df_patient)[names(df_patient) == 'p_id'] <- 'pid'
names(df_patient)[2] <- 'pid'
# check result
names(df_patient)

# put back to the original names
names(df_patient) <- df_pat_names
```

### 🛠️Coding Practice: Convert Lab Value Column Names

Can you convert just the lab value column names to uppercase? Print the `head()` of the data frame after you do this conversion.

```{r}
# ANSWER HERE

```

**END OF EXERCISE**

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

We can also use dplyr `rename()` to rename variables. The input is a data frame, and the output is the data frame with the new variable names. This does not perform the update in place... you have to save the new copy of the data frame.

```{r}
df_new <- df_patient %>% rename(person_id = p_id)

# old data is unchanged
names(df_patient)
```

```{r}
# new object has new variable name
names(df_new)
```

```{r}
df_IDs <- df_patient %>% select(Clust_id, person_id = p_id)
df_IDs
```

## Base R `ifelse()` Function

The base R `ifelse()` function is super useful for creating new columns. See the example below for the syntax.

We are also going to use the `%in%` operator.

```{r}
# using %in%
'WA' %in% c('WA','OR','ID')
```

```{r}
df_patient %>% 
  mutate(income_group = ifelse(Income > 60000, "high", "low"),
         region = ifelse(State %in% c('WA','ID','OR'), 'PNW', 'Other')) %>%
  select(Income, income_group, State, region)
```

### Missing Data

Missing data in R is specified with the `NA` value. Below we show you how to identify a single missing value, and how to filter for observations where missing values are present. We will cover missing data more comprehensively in a future lesson.

```{r}
# how to check for a missing value
is.na(NA)

# how to check if a value is NOT missing
!is.na(NA)
```

```{r}
df_patient %>% 
  mutate(income_group = ifelse(Income > 60000, "high", "low"),
         region = ifelse(State %in% c('WA','ID','OR'), 'PNW', 'Other')) %>%
  select(Income, income_group, State, region) %>%
  filter(is.na(Income)) %>%
  head()
```

### 🔍 Explore! Missing Data and `ifelse()`

What happens if there is a missing value when you use `ifelse()`?

**Answer here:**

## Using dplyr's `case_when()`

The dplyr package has a fantastic function called `case_when()` we will explore now.

-   The `case_when()` function is like a switch statement.
-   It uses the `~` as an operator for two-sided formulas.
-   The left hand side has to evaluate to a logical vector.
-   All right hand sides must evaluate to a common type of value (all numeric, all character, ...).
-   It can be useful for mapping values to `NA`.

```{r}
df_patient %>%
  mutate(region = case_when(
    State %in% c('WA','ID','OR')      ~ 'PNW',
    State == 'CA'                     ~ 'West Coast',
    State %in% c('IL','IN','OH','WI') ~ 'Midwest',
    TRUE                              ~ NA
  )) %>%
  select(State, region)
```

Sometimes you might save vectors of values as variables first, then pass those variables into `case_when()`.

```{r}
pnw <- c('WA','ID','OR')
midwest <- c('IL','IN','OH','WI')
df_patient %>%
  mutate(region = case_when(
    State %in% pnw     ~ 'PNW',
    State == 'CA'      ~ 'West Coast',
    State %in% midwest ~ 'Midwest',
    TRUE               ~ NA
  )) %>%
  select(State, region)
```

You can also consider conditions that span multiple columns!

```{r}
pnw <- c('WA','ID','OR')
midwest <- c('IL','IN','OH','WI')
df_patient %>%
  mutate(region = case_when(
    State %in% pnw & Income > 100      ~ 'PNW High Income',
    State %in% pnw & Income <= 100     ~ 'PNW Low Income',
    State == 'CA' | State %in% midwest ~ 'Other Regions',
    TRUE                               ~ NA
  )) %>%
  select(State, Income, region)
```

### 🛠️Coding Practice: Practicing `case_when()`

Categorize patients from `df_patient` into one of three groups:

-   **High Risk:** `lab_value_3` is greater than 10 and `Age` is greater than 45.
-   **Medium Risk:** `lab_value_3` is greater than 10 and `Age` is less than or equal to 45.
-   **Low Risk:** `lab_value_3` is less than or equal to 10. Age doesn't matter if the value of `lab_value_3` is less than or equal to 10.

Create a new variable called `risk_strata` and print it along with the values in `Age` and `lab_value_3`. Show the first 8 rows.

```{r}
# ANSWER HERE

```

**END OF EXERCISE**

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

## Discretizing Data (Numeric to Categorical)

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

A common task is to turn continuous numeric data into discrete categories. We can do that with the `cut()` function in R, but will create age categories using the `cut2()` function from `Hmisc` this time, which is just a little easier to use than the base R `cut()` function.

```{r}
# check original age column
summary(df_patient$Age)
```

```{r}
# create categories for age
df_patient$age_cat <-
  Hmisc::cut2(df_patient$Age,
              cuts = c(25, 35, 65))

# discretized age column
summary(df_patient$age_cat)
```

### 🛠️Coding Practice: Discretize with dplyr

Can you repeat the above procedure of discretizing the age column, but use the dplyr syntax of creating a new column? Just build the new `age_cat` column and display it alongside the original `Age` column. Show just the first 5 rows.

```{r}
# ANSWER HERE

```

And now, can you repeat the above procedure using `case_when()`?

```{r}
# ANSWER HERE

```

Which method do you prefer?

**END OF EXERCISE**

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

## 📈 EDA: Single Discrete Variables

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

We take a different approach to EDA for categorical data. We consider the distribution of categorical data to be:

-   The counts of the unique levels.
-   Or, alternatively the observed probabilities for each level.

### Frequency Tables

Remember, the goal is to understand the relative probabilities of each level occurring. *How many patients live in urban vs. rural locations?*

The base R `table()` function works great for this.

```{r}
table(df_patient$Location)
```

```{r}
## should also check if there are any missing values
table(df_patient$Location, useNA = "ifany")
```

We can also sort the output by the counts of observations in either ascending or descending order.

```{r}
table(df_patient$Location, useNA = 'ifany') %>% 
  sort(decreasing = TRUE)
```

Sometimes we may want to see proportions of each level instead of the raw counts. We use the `prop.table()` function to convert the table of counts into a table of proportions.

```{r}
table(df_patient$Location, useNA = 'ifany') %>% 
  sort(decreasing = TRUE) %>%
  prop.table() %>%
  round(3)
```

### Bar Plots

The appropriate visualization for a categorical variable is a bar plot.

```{r}
## build bar plot with base R
table(df_patient$Location) %>% 
  barplot(main = "Patient Residences")
```

When we use ggplot to make a bar plot, it does the counting for us internally. The `geom_bar()` function takes care of the counting.

```{r}
## same plot with ggplot2
ggplot(df_patient, aes(x=Location)) + 
  geom_bar() +
  ggtitle("Patient Residences")
```

### 🛠️Coding Practice: Base R Bar Plot with `NA`s

Get the above base R bar plots to show the `NA`s.

```{r}
# ANSWER HERE

```

Let's also review some data frame manipulation. Can you set all the values of `999` to become `NA`? Create a new column called `location_clean` and then repeat the previous plot. Do this with either base R or tidyverse tools.

```{r}
# ANSWER HERE

```

**END OF EXERCISE**

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

### Proportions Plots

You can also normalize the y-axis to see the proportions. First, see how to do this with base R:

```{r}
table(df_patient$Location, useNA = 'ifany') %>% 
  sort(decreasing = TRUE) %>%
  prop.table() %>%
  barplot(main = "Patient Residences")
```

The ggplot version of this is a little less intuitive. There are internal variables calculated by ggplot that we can access, including proportions. We use the `after_stat()` function to access these internal ggplot variables. We also have to specify `group = 1` to keep ggplot from considering each continent a separate group. Experiment a little with the ggplot call:

-   See what happens if you remove the `group = 1` argument.
-   What happens if you use `y = after_stat(count)` instead of `y = after_stat(prop)`?

```{r}
colnames(df_patient)
```

```{r}
## same plot with ggplot2
ggplot(df_patient, aes(x = Location, y = after_stat(prop), group = 1)) + 
  geom_bar() +
  ggtitle("Patient Residences")
```

## Conclusion

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

That wraps up our discussion of some common intermediate data management tasks and exploring discrete variables!

## Answers to Questions

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

### 🛠️Coding Practice: Fix the Import Code

```{r}
# import pipe delimited file
df2 <- 
  read_delim(file = '../data/chickweight.txt',
             delim = '|',
             col_names = c('weight','time','chick','diet'))
df2
```

### 🛠️Coding Practice: Convert Lab Value Column Names

Can you convert just the lab value column names to uppercase? Print the `head()` of the data frame after you do this conversion.

```{r}
# ANSWER HERE
names(df_patient)[8:10] <- toupper(names(df_patient)[8:10])
names(df_patient)
```

### 🔍 Explore! Missing Data and `ifelse()`

What happens if there is a missing value when you use `ifelse()`? **The function also populates missing values!**

### 🛠️Coding Practice: Practicing `case_when()`

```{r}
df_patient %>%
  mutate(Age, 
         lab_value_3 = LAB_VALUE_3,
         risk_strata = case_when(
          lab_value_3 > 10 & Age > 45  ~ 'High Risk',
          lab_value_3 > 10 & Age <= 45 ~ 'Medium Risk',
          lab_value_3 <= 10            ~ 'Low Risk'       
        )) %>%
  slice(1:8)
```

### 🛠️Coding Practice: Discretize with dplyr

```{r}
df_patient %>%
  transmute(Age,
            age_cat = Hmisc::cut2(Age, cuts = c(25, 35, 65))) %>%
  head()
```

```{r}
# same as above but using case_when()
df_patient %>%
  transmute(Age,
            age_cat = case_when(
              Age >= 25 & Age < 35 ~ '[25, 35)',
              Age >= 35 & Age < 65 ~ '[35, 65)',
              Age >= 65 & Age < 219 ~ '[65, 219)')) %>%
  head()
```

### 🛠️Coding Practice: Base R Bar Plot with `NA`s

```{r}
table(df_patient$Location, useNA = 'ifany') %>% 
  barplot(main = "Patient Residences")
```

```{r}
# method 1 with base R
df_patient$location_clean <- df_patient$Location
df_patient$location_clean[df_patient$Location == '999'] <- NA
table(df_patient$location_clean, useNA = 'ifany') %>% 
  barplot(main = "Patient Residences")

# method 2 dplyr + ggplot2
df_patient %>%
  mutate(location_clean = ifelse(Location == '999', NA, Location)) %>%
  ggplot(aes(location_clean)) + 
  geom_bar() +
  ggtitle("Patient Residences")
```
