exercises.qmd

---
title: "Data Cleaning: Exercises"
format: 
  html:
    code-fold: true
    code-summary: "Show the code solution"
---

# Instructions

**Recommended:** Complete these exercises in the dedicated Posit Cloud work space, which comes with

1.  all packages pre-installed, and

2.  a Quarto document to fill in.

It will be helpful to peek here to verify that your tables generated in Posit Cloud match the desired output and code solutions.

Join R/Medicine Posit cloud work space for Cleaning Medical Data with R: **[bit.ly/posit-cloud-cleaning-medical-data](https://bit.ly/posit-cloud-cleaning-medical-data){target="_blank"}**

**Otherwise:** Follow along this document, work on your personal computer, and challenge yourself **not to peek** at the code solutions until you have completed the exercise.

# Packages

```{r}
#| label: packages
#| message: false
#| echo: true
#| code-fold: false
#| warning: false
library(tidyverse) # general use
library(janitor)   # handy data cleaning functions
library(readxl)    # import excel files
library(here)      # find files in a project
library(gtsummary) # presentation ready summary tables  
```

# The Data

The exercises use [`messy_uc.xlsx`](https://github.com/shannonpileggi/rmedicine-data-cleaning-2023/blob/main/data/messy_uc.xlsx).

The data for each section will start out as messy Excel files of the type your well-meaning clinical colleague will have used to collect data from the local electronic medical record (EMR), with typos, data problems, and often untidy data structures.

Essentially, your job is to turn the messy Excel data beast (on the left) into the tidy data on the right.

![Taming the Data Beast, by Allison Horst](images/data_beast_allison_horst.jpeg)

# ![Crystal Lewis](images/Crystal_circle.png){width="15%"} CL: Principles of Data Management

## CL1

1. Import `messy_uc.xlsx` using `readxl::read_excel()`.

```{r eval = FALSE, error = TRUE}
#| code-fold: false
#| message: false
#| warning: false

# import raw data
df_raw <- read_excel(
  path = "data/messy_uc.xlsx",
  sheet = "__",
  skip = __
)

```

```{r}
#| code-fold: true
#| message: false
#| warning: false

# import raw data
df_raw <- read_excel(
  path = "data/messy_uc.xlsx",
  sheet = "Data",
  skip = 6
)

```



## CL2

2. Use an exploratory function to review the data.

```{r}
#| eval: false
#| message: false
#| warning: false

dplyr::glimpse(df_raw)

skimr::skim(df_raw)

base::summary(df_raw)

visdat::vis_dat(df_raw)

summarytools::view(summarytools::dfSummary(df_raw))

DataExplorer::create_report(df_raw)

Hmisc::describe(df_raw)

```



# ![Shannon Pileggi](images/Shannon_circle.png){width="15%"} SP: Stage 1 Data Cleaning

## Set up

```{r}
#| code-fold: false
#| message: false
#| warning: false

# do initial cleaning of variable names and removing empty rows/columns
df_clean <- df_raw |> 
  janitor::clean_names() |> 
  janitor::remove_empty(which = c("rows", "cols"))

```


## SP1

::: panel-tabset

### Explore

1. Explore the values of `race`. 

```{r}
df_clean |> count(race)
```

### Clean

2. In the `df_clean` data set, create a new variable named `race_clean` that cleans the
coding of `race` (combine "African-American" & "afromerican"; "H/API" & "Mixed" & "Other").


```{r}
df_clean <- df_raw |> 
  janitor::clean_names() |> 
  janitor::remove_empty(which = c("rows", "cols")) |> 
  mutate(
    race_clean = case_when(
      race %in% c("African-American", "afromerican") ~ "African-American",
      race %in% c("H/API", "Mixed", "Other") ~ "Other",
      .default = race
    )
  )
```

### Confirm

3. Confirm the new `race_clean` variable is coded correctly.

```{r}
df_clean |> 
  count(race_clean, race)
```

:::


## SP2

::: panel-tabset

### Explore

1. Explore the type of and values of `start_plt`. 

```{r}
df_clean |> 
  count(start_plt)

df_clean |> 
  select(start_plt) |> 
  glimpse()

df_clean[["start_plt"]]
```

### Clean

2. In the `df_clean` data set, create a new variable named `start_plt_clean` that corrects any unusual
values and assigns the correct variable type.

```{r}
#| warning: false
df_clean <- df_raw |> 
  janitor::clean_names() |> 
  janitor::remove_empty(which = c("rows", "cols")) |> 
  mutate(
    race_clean = case_when(
      race %in% c("African-American", "afromerican") ~ "African-American",
      race %in% c("H/API", "Mixed", "Other") ~ "Other",
      .default = race
    ),
    start_plt_clean = parse_number(start_plt, na = "clumped") 
  )
```

### Confirm

3. Confirm the new `start_plt_clean` variable is coded correctly.

```{r}
#| warning: false
df_clean |> 
  count(start_plt_clean, start_plt)

df_clean |> 
  select(start_plt, start_plt_clean) |> 
  glimpse()

df_clean[["start_plt_clean"]]
```

:::

## SP3

::: panel-tabset

### Explore

1. Explore the type of and values of `race_clean`. 

```{r}
df_clean |> 
  count(race_clean)
```

### Clean

2. Convert the `race_clean` variable to a factor such that the most common values present in
order in a summary table.

```{r}
#| warning: false
df_clean <- df_raw |> 
  janitor::clean_names() |> 
  janitor::remove_empty(which = c("rows", "cols")) |> 
  mutate(
    race_clean = case_when(
      race %in% c("African-American", "afromerican") ~ "African-American",
      race %in% c("H/API", "Mixed", "Other") ~ "Other",
      .default = race
    ) |> fct_infreq(),
    start_plt_clean = parse_number(start_plt) 
  )
```

### Confirm

3. Confirm the new coding of `race_clean`.

```{r}
df_clean |> 
  count(race_clean, race)


df_clean |> 
  count(race_clean)
```

:::

# ![Peter Higgins](images/Peter_circle.jpg){width="15%"} PH: Stage 2 Data Cleaning

## PH1

## Pivoting Longer

-   Your Turn with endo_data
-   Measurements of Trans-Epithelial Electrical Resistance (TEER, the inverse of leakiness) in biopsies of 3 segments of intestine.
-   This could be affected by portal hypertension in patients with liver cirrhosis
- Let's find out!

- Here is the code to load the data if you are doing this on a local computer. Use the clipboard icon at the top right to copy the code.

```{r}
#| code-fold: false
endo_data <- tibble::tribble(
  ~pat_id, ~portal_htn, ~duod_teer, ~ileal_teer, ~colon_teer,
  001, 1, 4.33, 14.57, 16.23,
  002, 0, 11.67, 15.99, 18.97,
  003, 1, 4.12, 13.77, 15.22,
  004, 1, 4.62, 16.37, 18.12,
  005, 0, 12.43, 15.84, 19.04,
  006, 0, 13.05, 16.23, 18.81,
  007, 0, 11.88, 15.72, 18.31,
  008, 1, 4.87, 16.59, 18.77,
  009, 1, 4.23, 15.04, 16.87,
  010, 0, 12.77, 16.73, 19.12
)
endo_data
```

## Pivoting Longer with endo_data

::: panel-tabset
### Dataset

```{r, echo=FALSE}
endo_data
```

### Arguments

-   What values do you want to use for these arguments to `pivot_longer`:
    -   cols
    -   names_pattern = "(.+)\_teer"
    -   names_to
    -   values_to
-   Note that we are giving you the correct value for `names_pattern`, which will ask for what we want - to keep the characters of the name (of whatever length) before "\_teer"

### Code

-   Fill in the blanks to pivot this dataset to tall format, with columns for the intestinal location and the teer value.
-   Note that we are giving you the correct answer for the `names_pattern` argument.

```{r, error=TRUE, eval=FALSE}
#| code-fold: false
endo_data |> 
  pivot_longer(
    cols = ___ ,
    names_pattern = "(.+)_teer",
    names_to =  ___ ,
    values_to = ___
  )
```

### Solution

-   Fill in the blanks to pivot this dataset to tall format, with columns for the intestinal location and the teer value.

```{r, error=TRUE, eval=FALSE}
endo_data |> 
  pivot_longer(
    cols = "duod_teer":"colon_teer",
    names_pattern = "(.+)_teer",
    names_to = c("location"),
    values_to = "teer"
  )
```

-   Run the code, and look at the resulting table. Use the clipboard icon at the top right to copy the code.

### Result

```{r, echo=FALSE}
endo_data |> 
  pivot_longer(
    cols = "duod_teer":"colon_teer",
    names_pattern = "(.+)_teer",
    names_to = c("location"),
    values_to = "teer"
  )
```
-   Do you think that portal hypertension has an effect on TEER and (its inverse) epithelial leakiness?

:::

## PH2

## Patient Demographics with Lab results (Your Turn to Join)

::: columns
::: {.column width="40%"}
-   We have some basic Patient Demographics in one table

```{r}
#| echo: false
demo <- tibble::tribble(
  ~pat_id, ~name, ~age,
  '001', "Arthur Blankenship", 67,
  '002', "Britney Jonas", 23,
  '003', "Sally Davis", 63,
  '004', "Al Jones", 44,
  '005', "Gary Hamill", 38,
  '006', "Ken Bartoletti", 33,
  '007', "Ike Gerhold", 52,
  '008', "Tatiana Grant", 42,
  '009', "Antione Delacroix", 27,
)

demo
```
:::

::: {.column width="60%" .fragment}
and potassium levels and creatinine levels in 2 other tables

```{r}
#| echo: false
pot <- tibble::tribble(
  ~pat_id, ~k,
  '001', 3.2,
  '002', 3.7,
  '003', 4.2,
  '004', 4.4,
  '005', 4.1,
  '006', 4.0,
  '007', 3.6,
  '008', 4.2,
  '009', 4.9,
)

head(pot)
```

```{r}
#| echo: false
cr <- tibble::tribble(
  ~pat_id, ~cr,
  '001', 0.2,
  '002', 0.5,
  '003', 0.9,
  '004', 1.5,
  '005', 0.7,
  '006', 0.9,
  '007', 0.7,
  '008', 1.0,
  '009', 1.7,
)

head(cr)
```
:::
:::

## Need to Load the Data?

If you are trying this on your local computer, copy the code below with the clipboard icon to get the data into your computer.

```{r}
demo <- tibble::tribble(
  ~pat_id, ~name, ~age,
  '001', "Arthur Blankenship", 67,
  '002', "Britney Jonas", 23,
  '003', "Sally Davis", 63,
  '004', "Al Jones", 44,
  '005', "Gary Hamill", 38,
  '006', "Ken Bartoletti", 33,
  '007', "Ike Gerhold", 52,
  '008', "Tatiana Grant", 42,
  '009', "Antoine Delacroix", 27,
)

pot <- tibble::tribble(
  ~pat_id, ~k,
  '001', 3.2,
  '002', 3.7,
  '003', 4.2,
  '004', 4.4,
  '005', 4.1,
  '006', 4.0,
  '007', 3.6,
  '008', 4.2,
  '009', 4.9,
)

cr <- tibble::tribble(
  ~pat_id, ~cr,
  '001', 0.2,
  '002', 0.5,
  '003', 0.9,
  '004', 1.5,
  '005', 0.7,
  '006', 0.9,
  '007', 0.7,
  '008', 1.0,
  '009', 1.7,
)
```

## Your Turn to Join

-   We want to join the correct labs (9 rows each) to the correct patients.
-   The unique identifier (called the uniqid or key or recordID) is pat_id.
  -   It only occurs once for each patient/row
  -   It appears in each table we want to join
-   The pat_id is of the character type in each (a common downfall if one is character, one is numeric, but they can  **look** the same - but don't match)
-   We want to start with demographics, then add datasets that match to the right.
-   We will use *demo* as our base dataset on the left hand side (LHS), and first join the potassium (*pot*) results (RHS)

## What the Left Join Looks Like

   <div class="animations"><img alt="gif here" width="80%" height="80%" src="https://raw.githubusercontent.com/gadenbuie/tidyexplain/main/images/left-join.gif"> </div>  
   
## Your Turn to Join

::: panel-tabset
### The Problem

-   Joining *demo* to *pot* with a left_join
-   left_join(data_x, data_y, by = "uniqid")

### The Code

-   replace the generic arguments below with the correct ones to join *demo* to *pot* and produce *new_data*.

```{r, error=TRUE, eval=FALSE}
#| code-fold: false
new_data <- left_join(data_x, data_y, by = "uniqid")
new_data
```

### The Solution

```{r, eval=FALSE}
new_data <- left_join(demo, pot, by = "pat_id")
new_data
```

### The Result

```{r, echo=FALSE}
new_data <- left_join(demo, pot, by = "pat_id")
new_data
```

:::


## Now add Creatinine (cr) to new_data

::: panel-tabset
### The Problem

-   Joining new_data and cr with a left_join
-   left_join(data_x, data_y, by = "uniqid")

### The Code

-   Replace the generic arguments with the correct ones to join new_data and cr and produce new_data2.

```{r, error=TRUE, eval=FALSE}
#| code-fold: false
new_data2 <- left_join(data_x, data_y, by = "uniqid")
new_data2
```

### The Solution

```{r, eval=FALSE}
new_data2 <- left_join(new_data, cr, by = "pat_id")
new_data2
```

### The Result

```{r, echo=FALSE}
new_data2 <- left_join(new_data, cr, by = "pat_id")
new_data2
```

- Al has HTN and DM2
- Antoine has early stage FSGS
:::

# Session info

```{r}
#| label: session-info
#| eval: true
#| code-fold: false
devtools::session_info()
```