---
title: "Merging and Reshaping Data"
author: "Evan Carey, Rian DeFaccio , for BH Analytics LLC"
output: 
  html_document:
    toc: true
    toc_depth: 3
    css: '../common/css/leonids.css'
---

```{r setup, include=FALSE}
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 focuses on two key data management tasks, reshaping and combining data sets. We'll discuss:

-   How to Combine data frames.
-   The difference between merging and concatenation.
-   Long and Wide transformations.

## Merging Data Frames

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

We sometimes need to merge data sets together, especially when dealing with relational databases.

Do you ever do this with your data?

For example:

-   We have a data frame of patients including their county of residence.
-   We have a separate data frame of counties including the average income of the county.
-   We want to connect the two data frames together.

It is easy to do this in R with the merge functions from dplyr.

### Understanding Merges

Consider the following hypothetical data sets. We can actually answer many questions with just this information, but will need to combine the two tables in most cases.

![](../common/Merging_1_v2.png){width="600"}

Below we present the major kinds of SQL-style merges.

![](../common/Merging_2.png){width="600"}

Return to the previous hypothetical data sets and predict what the result should be for a right join, left join, inner join, and outer join for these two tables.

## Patient Traits Data

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

We will use the simulated patient traits data set in this lesson.

```{r}
df_patient <- 
  read.csv('../data/patient_traits.csv')
head(df_patient)
```

We will also grab some information about the hospitals where these patients were seen.

```{r}
df_hosp <- 
  read_csv('../data/hospital_affiliations.csv')
head(df_hosp)
```

Before we implement these merges, we will examine the keys from both tables and assess how much overlap there is.

```{r}
## uniques per table
dplyr::n_distinct(df_patient$Clust_id)
dplyr::n_distinct(df_hosp$hosp_id)
```

```{r}
## look at overlap
intersect(df_patient$Clust_id, df_hosp$hosp_id)
```

```{r}
## which IDs are only in patient file?
setdiff(df_patient$Clust_id, df_hosp$hosp_id)
## which IDs are only in hospital file?
setdiff(df_hosp$hosp_id, df_patient$Clust_id)
```

### 🛠️Coding Practice: How Much Overlap

In the previous code cells we examined which cluster IDs were in both data sets (aka the union), and which cluster IDs were only in one or the other (aka the intersection). In the following code cell build on this idea but count how many cluster IDs were in both sets or one or the other sets.

```{r}
# ANSWER HERE
# how many hospital IDs only in the patient file?

# how many hospital IDs only in the hospital file?

# how many IDs common to both data sets?
```

**END OF EXERCISE**

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

## Implement Merges in R

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

We can use a series of functions to accomplish the merges previously described. These are all from the dplyr package.

To demonstrate, we first make some small subsets of the data.

```{r}
df_X <- df_patient %>%
  filter(p_id %in% c(100154,101108,101347,104819,105308)) %>%
  select(p_id, Age, Location, Clust_id)
df_X
```

```{r}
df_Y <- df_hosp %>%
  filter(hosp_id %in% c('A1','A27','A53','E5','ZZZ')) %>%
  select(hosp_id, academic)
df_Y
```

Now we will try out some different merges!

```{r}
# inner join
inner_join(x = df_X, 
           y = df_Y,
           by = c('Clust_id' = 'hosp_id'))
```

```{r}
# outer (full) join
full_join(x = df_X, 
          y = df_Y,
          by = c('Clust_id' = 'hosp_id'))
```

```{r}
# left join
left_join(x = df_X, 
          y = df_Y,
          by = c('Clust_id' = 'hosp_id'))
```

```{r}
# what if the join columns have the same name?
df_Y_alt <- df_Y %>% rename(Clust_id = hosp_id)

# right join
right_join(x = df_X, 
           y = df_Y_alt,
           by = 'Clust_id')

# dplyr will even guess what the join column should be
# (NOT A GOOD PRACTICE)
right_join(x = df_X, y = df_Y_alt)
```

### ✍️ Your Turn! Which Merge?

If you would like to keep information about all of the patients and whether or not the hospital they were seen at was academic or not, which kind of merge would be most appropriate? Assume the patient file is X and hospital file is Y.

**Answer here:**

## Concatenation

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

A different way to combine data frames is to simply stack them on top of each other. Perhaps your data was split into two different files, but has the same structure. You might have information on patients from last year, then a new table of data on patients for this year.

This is called concatenation. I will show you this by first splitting our data into two pieces, then sticking it back together.

```{r}
# Get two small pieces of the data
df_patient_sub1 <- df_patient[1:5, ]
df_patient_sub2 <- df_patient[6:10, ]

df_patient_sub1
df_patient_sub2
```

```{r}
# Concatenate (stack)!
bind_rows(df_patient_sub1, df_patient_sub2)
```

### ✍️ Your Turn! Package Identification

Can you determine which package the `bind_rows()` function came from? How would you go about finding out?

**Answer here:**

## Long Versus Wide Data

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

Let's now consider the difference between long and wide data. In this example, we are going to represent data points of surveys at three different time points for three different customers.

There are multiple ways we could represent this data. Let's start by representing it in what we call a wide format. In a wide format, every time point has its own column of data.

```{r}
# create wide sample data
df_wide <- 
  data.frame(customer_id = c(101, 102, 103),
             survey_t1   = c(5, 3, 4),
             survey_t2   = c(4, NA, 3),
             survey_t3   = c(5, 2, 1))
df_wide
```

The wide format is convenient for making certain types of plots, and also for calculating correlations between time points. If I wanted to know what is the correlation between surveys at time one and surveys at time 3, I would need my data in this form to answer that question.

However, another way we can store this data is what is referred to as a long format. In a long format for every new survey, we add another row to the table. So that means there is a single column indicating time and another single column indicating survey period notice in this format, we don't need to include a row for customer ID 102 and survey time 2.

```{r}
# create long sample data
df_long <- 
  data.frame(customer_id = c(101, 101, 101,
                             102, 102,
                             103, 103, 103),
             time = c(1, 2, 3, 1, 3, 1, 2, 3),
             survey = c(5, 4, 5, 3, 2, 4, 3, 1))
df_long
```

We usually have a data set in either a long or a wide format. One data transformation we then need to make is to turn it either from long to wide, or from wide to long.

We use `pivot_wider()` from the tidyr package to go from long to wide.

-   the first argument is the data
-   the `id_cols` argument specifies which columns to pivot around
-   the `names_from` argument specifies which column from the long data becomes the new column names in the wide data
-   the `values_from` argument specifies which columns contain the values that will populate the new wide data

```{r}
## go from long to wide

# first add a prefix to the time values
df_long$time <- paste('t', df_long$time, sep = '_')

pivot_wider(df_long,
            id_cols = customer_id, 
            names_from = time, 
            values_from = survey)
```

Some possible combinations of `time` and `customer_id` weren't present in the long form of the data, they are `NA` right now. We can specify a value to fill the missing combinations if we want. It may not make sense in this data, but in cases where `NA` = a count of zero then it would be appropriate.

```{r}
pivot_wider(df_long,
            id_cols = customer_id, 
            names_from = time, 
            values_from = survey,
            values_fill = 0)
```

We use `pivot_longer()` (also from tidyr) to go from wide to long.

-   the first argument is `data`
-   the `cols` argument is the columns to pivot to a longer format
-   the `names_to` argument specifies where the new column, imagine taking all the column names not specified in the `cols` argument and swinging them down into a new column
-   the `values_to` argument specifies the name of the new column that contains the values

```{r}
df_wide

## go from wide to long
pivot_longer(df_wide, 
             cols = -customer_id, 
             names_to = "time", 
             values_to = "survey")
```

we don't need to record the combination of `customer_id` = 102 and survey time 2.

```{r}
df_wide
pivot_longer(data = df_wide, 
             cols = -customer_id, 
             names_to = "time", 
             values_to = "survey", 
             values_drop_na = TRUE)
```

See the help on `pivot_longer()` or `pivot_wider()`:

🌐 <https://tidyr.tidyverse.org/reference/pivot_longer.html>

🌐 <https://tidyr.tidyverse.org/reference/pivot_wider.html>

There is also a vignette about pivoting, which you can access via the internet, or find via R since these helpful documents are also included in the metadata for the `tidyr` package:

🌐 <https://tidyr.tidyverse.org/articles/pivot.html>

```{r}
# vignette("pivot")
```

### 🛠️Coding Practice: Melting and Plotting Labs

Try to melt (aka pivot longer) the patient traits data set to get it to look like the following:

![](images/lab_values_long.png){width="375"}

Save your result to a new data frame called `df_labs_long`.

```{r}
# ANSWER HERE

```

Now we can practice a little with ggplot, which likes to receive long-form data. Build a facetted histogram, where each facet represents the uni-variate distribution of one of the lab values. Your plot should look like the following image. See how much of the formatting you can match too!

![](images/lab_values_plot.png){width="550"}

🗝️**HINT:** Ask any instructor for hints :)

```{r}
# ANSWER HERE

```

**END OF EXERCISE**

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

## Random Sampling

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

Finally, here is one topic that may come up for many people, randomly sampling from a data frame.

We use the `sample_n()` and `sample_frac()` functions from `dplyr` to randomly sample from our data frame.

```{r}
set.seed(12345)

## sample 10 random rows
rand_10 <- sample_n(df_patient, size = 10)
rand_10

## sample 25% random rows
rand_quarter <- sample_frac(df_patient,
                            size = 0.25)
head(rand_quarter)
nrow(rand_quarter)
```

## Conclusion

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

This wraps up our discussion on combining and reshaping data frames! We hope this will be useful to you, as it is something we find ourselves doing most days :)

## Answers to Questions

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

### 🛠️Coding Practice: How Much Overlap

In the previous code cells we examined which cluster IDs were in both data sets (aka the union), and which cluster IDs were only in one or the other (aka the intersection). In the following code cell build on this idea but count how many cluster IDs were in both sets or one or the other sets.

```{r}
# ANSWER HERE
# how many hospital IDs only in the patient file? 
setdiff(df_patient$Clust_id, df_hosp$hosp_id) %>% length()  

# how many hospital IDs only in the hospital file? 
setdiff(df_hosp$hosp_id, df_patient$Clust_id) %>% length()  

# how many IDs common to both data sets? 
intersect(df_patient$Clust_id, df_hosp$hosp_id) %>% length()
```

### ✍️ Your Turn! Which Merge?

If you would like to keep information about all of the patients and whether or not the hospital they were seen at was academic or not, which kind of merge would be most appropriate? Assume the patient file is X and hospital file is Y. **This is a left merge.**

### ✍️ Your Turn! Package Identification

Can you determine which package the `bind_rows()` function came from? How would you go about finding out? **It's from dplyr. Try typing `?bind_rows`** **and look in the upper left corner of the help document.**

### 🛠️Coding Practice: Melting and Plotting Labs

```{r}
# ANSWER HERE
df_labs_long <- df_patient %>%
  pivot_longer(cols = contains('lab'),
               names_to  = 'lab_type',
               values_to = 'lab_value') %>%
  select(p_id, lab_type, lab_value)
df_labs_long
```

```{r}
# first build a factored version of [lab_type]
# then build and format the plot
df_labs_long %>%
  mutate(lab_type_f = 
           factor(lab_type,
                  levels = c("lab_value_1","lab_value_2","lab_value_3"),
                  labels = c("Lab 1","Lab 2","Lab 3"))) %>%
  ggplot(aes(lab_value)) +
  geom_histogram(bins = 30) +
  facet_grid( ~ lab_type_f, scales = "free_x") +
  ggtitle('Distribution of Lab Values in Patients') +
  xlab("Lab Values") +
  ylab("Count of Observations") +
  theme_light() +
  theme(text = element_text(family='serif'),
        plot.title = element_text(hjust = 0.5, size = 15))
```
