---
title: "Manipulating Data in R"
author: "Introduction to R for Public Health Researchers"
output:
  ioslides_presentation:
    css: ../styles.css
    widescreen: yes
---


```{r, echo = FALSE, include=FALSE}
library(knitr)
library(readr)
opts_chunk$set(comment = "")
library(tidyverse)
```


## Reshaping Data 

```{r, echo = FALSE, message=FALSE}
library(tidyverse)
```

In this module, we will show you how to:

1. Reshaping data from wide (fat) to long (tall)
2. Reshaping data from long (tall) to wide (fat)
3. Merging Data/Joins
4. Perform operations by a grouping variable

## Setup

We will show you how to do each operation in base R then show you how to use the `dplyr` or `tidyr` package to do the same operation (if applicable).  

See the "Data Wrangling Cheat Sheet using `dplyr` and `tidyr`":

* https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf

## What is wide/long data?


```{r, echo = FALSE}
ex_wide = tibble(id = 1:2,
                     visit1 = c(10, 5),
                     visit2 = c(4, 6),
                     visit3 = c(3, NA)
                     )
ex_long = tibble(id = c(rep(1, 3), rep(2, 2)),
                     visit = c(1:3, 1:2),
                     value = c(10, 4, 3, 5, 6))
```

See http://www.cookbook-r.com/Manipulating_data/Converting_data_between_wide_and_long_format/

- Wide - multiple columns per observation 
    - e.g. visit1, visit2, visit3
```{r, echo = FALSE}
ex_wide
```

- Long - multiple rows per observation
```{r, echo = FALSE}
ex_long
```

## What is wide/long data?

<div style="font-size:40pt">
More accurately, data is wide or long **with respect** to certain variables.
</div>

## Data used: Charm City Circulator

http://johnmuschelli.com/intro_to_r/data/Charm_City_Circulator_Ridership.csv

```{r, message = FALSE}
circ = read_csv(
  paste0("http://johnmuschelli.com/intro_to_r/",
         "data/Charm_City_Circulator_Ridership.csv"))
head(circ, 2)
class(circ$date)
```

## Creating a Date class from a character date

```{r, message = FALSE}
library(lubridate) # great for dates!
```

```{r, message= FALSE}
sum(is.na(circ$date))
sum( circ$date == "")
circ = mutate(circ, date = mdy(date))
sum( is.na(circ$date) ) # all converted correctly
head(circ$date, 3)
class(circ$date)
```

## Reshaping data from wide (fat) to long (tall): base R

The `reshape` command exists.  It is a **confusing** function.  Don't use it.

## tidyr package

`tidyr` allows you to "tidy" your data.  We will be talking about:

- `gather` - make multiple columns into variables, (wide to long)
- `spread` - make a variable into multiple columns, (long to wide)
- `separate` - string into multiple columns
- `unite` - multiple columns into one string

- All the "join" functions for mergin are in `dplyr`


## Reshaping data from wide (fat) to long (tall): tidyr 

`tidyr::gather` - puts column data into rows.

We want the column names into "`var`" variable in the output dataset and the value in "`number`" variable. We then describe which columns we want to "gather:" 

```{r}
long = gather(circ, key = "var", value = "number", 
              -day, -date, -daily)
head(long, 4)
```


## Reshaping data from wide (fat) to long (tall): tidyr 

- Could be explicit on what we want to gather

```{r}
long = gather(circ, key = "var", value = "number", 
              starts_with("orange"), starts_with("purple"),
              starts_with("green"), starts_with("banner"))
long
```

## Reshaping data from wide (fat) to long (tall): tidyr 

```{r}
long %>% count(var)
```


## Lab Part 1

[Website](http://johnmuschelli.com/intro_to_r/index.html)


## Making a separator

We will use `str_replace` from `stringr` to put `_` in the names

```{r}
long = long %>% mutate(
  var = var %>% 
    str_replace("Board", "_Board") %>% 
    str_replace("Alight", "_Alight") %>% 
    str_replace("Average", "_Average") 
)
long %>% count(var)
```

## Reshaping data from wide (fat) to long (tall): tidyr

Now each `var` is boardings, averages, or alightings.  We want to separate these so we can have these by line. Remember "." is special character:

```{r}
long = separate(long, var, into = c("line", "type"), sep = "_")
head(long, 2)
unique(long$line)
unique(long$type)
```

## Re-uniting all the lines

If we had the opposite problem, we could use the `unite` function:

```{r}
reunited = long %>% 
  unite(col = var, line, type, sep = "_")  
reunited %>% select(day, var) %>% head(3) %>% print
```
We could also use `paste`/`paste0`.


<!-- ## Making column names a little more separated -->

<!-- Alternative: We could have replaced the column names first **then** reshaped: -->

<!-- ```{r, eval = FALSE} -->
<!-- cn = colnames(circ) -->
<!-- cn = cn %>%  -->
<!--   str_replace("Board", ".Board") %>%  -->
<!--   str_replace("Alight", ".Alight") %>%  -->
<!--   str_replace("Average", ".Average")  -->
<!-- colnames(circ) = cn # then reshape using gather! -->
<!-- ``` -->

<!-- or (with some string working (see data cleaning)): -->

<!-- ```{r, eval = FALSE} -->
<!-- circ = circ %>%  -->
<!--   rename_all(.funs = str_replace, -->
<!--             pattern = "(orange|purple|green|banner)", -->
<!--             replacement = "\\1.") -->
<!-- ``` -->

## Lab Part 2

[Website](http://johnmuschelli.com/intro_to_r/index.html)


## Reshaping data from long (tall) to wide (fat): tidyr

In `tidyr`, the `spread` function spreads rows into columns.  Now we have a long data set, but we want to separate the Average, Alightings and Boardings into different columns:

```{r}
# have to remove missing days
wide = long %>% filter(!is.na(date))
wide = wide %>% spread(type, number)
head(wide)
```


## Lab Part 3

[Website](http://johnmuschelli.com/intro_to_r/index.html)

## Merging: Simple Data

`base` has baseline data for ids `1` to `10` and Age

```{r merging}
base <- tibble(id = 1:10, Age = seq(55,60, length=10))
head(base, 2)
```
`visits` has ids 1 to 8, then 11 (new id), and 3 visits and outcome

```{r}
visits <- tibble(id = c(rep(1:8, 3), 11), visit= c(rep(1:3, 8), 3),
                    Outcome = seq(10,50, length=25))
tail(visits, 2)
```


## Joining in `dplyr`

* Merging/joining data sets together - usually on key variables, usually "id"
* `?join` - see different types of joining for `dplyr`
* Let's look at https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf
* `inner_join(x, y)` - only rows that match for `x` and `y` are kept
* `full_join(x, y)` - all rows of `x` and `y` are kept 
* `left_join(x, y)` - all rows of `x` are kept even if not merged with `y`
* `right_join(x, y)` - all rows of `y` are kept even if not merged with `x`
* `anti_join(x, y)` - all rows from `x` not in `y` keeping just columns from `x`.

## Inner Join

```{r inner_join}
ij = inner_join(base, visits)
dim(ij)
tail(ij)
```


## Left Join

```{r left_join}
lj = left_join(base, visits)
dim(lj)
tail(lj)
```

## Logging the joins

The `tidylog` package can show you log outputs from `dplyr` (newly added).  You will need to install to use.

```{r, include=FALSE}
library(tidylog)
```

```{r left_join_log}
library(tidylog)
left_join(base, visits)
```

## Right Join

```{r right_join}
rj = right_join(base, visits)
tail(rj, 3)
```

## Right Join: Switching arguments

```{r right_join2}
rj2 = right_join(visits, base)
tail(rj2, 3)
```
```{r right_join_arrange, echo = FALSE}
rj2 = arrange(rj2, id, visit) %>% select(id, visit, Outcome, Age)
lj = arrange(lj, id, visit) %>% select(id, visit, Outcome, Age)
```
```{r right_join_arrange_out}
identical(rj2, lj) ## after some rearranging
```

## Full Join

```{r full_join}
fj = full_join(base, visits)
tail(fj, 4)
```

```{r include=FALSE}
unloadNamespace("tidylog")
```

## Using the `by` argument

By default - uses intersection of column names.  If `by` specified, then uses that, but if other columns with same name, adds `suffix`.

```{r use_by}
base = base %>% mutate(x = 5)
visits = visits %>% mutate(x = 4)
head(full_join(base, visits))
head(full_join(base, visits, by = "id"))
head(full_join(base, visits, by = "id", suffix = c("_base", "_visit")))
```

## Duplicated

- The `duplicated` command can give you indications if there are duplications in a **vector**:

```{r}
duplicated(1:5)
duplicated(c(1:5, 1))
fj %>% mutate(dup_id = duplicated(id))
```



## Lab Part 4

[Website](http://johnmuschelli.com/intro_to_r/index.html)

## Finding the First (or Last) record

`pivot_longer` and `pivot_wider` are new (as of 2019) `tidyr` functions.

See link below:

https://tidyr.tidyverse.org/dev/articles/pivot.html

## Website

[Website](http://johnmuschelli.com/intro_to_r/index.html)


## Reshaping data from long (tall) to wide (fat): tidyr

We can use `rowSums` to see if any values in the row is `NA` and keep if the row, which is a combination of date and line type has any non-missing data.

```{r}
head(wide, 3)
not_namat = wide %>% select(Alightings, Average, Boardings)
not_namat = !is.na(not_namat)
head(not_namat, 2)
wide$good = rowSums(not_namat) > 0
```

## Reshaping data from long (tall) to wide (fat): tidyr

Now we can filter only the good rows and delete the `good` column.
```{r}
wide = wide %>% filter(good) %>% select(-good)
head(wide)
```


## Finding the First (or Last) record

- `slice` allows you to select **records** (compared to first/last on a **vector**)

```{r}
long = long %>% filter(!is.na(number) & number > 0)
first_and_last = long %>% arrange(date) %>% # arrange by date
  filter(type == "Boardings") %>% # keep boardings only
  group_by(line) %>% # group by line
  slice( c(1, n())) # select ("slice") first and last (n() command) lines
first_and_last %>%  head(4)
```



# Merging in base R (not covered)

## Data Merging/Append in Base R

* `merge()` is the most common way to do this with data sets
    - we will use the "join" functions from `dplyr`
* `rbind`/`cbind` - row/column bind, respectively
    * `rbind` is the equivalent of "appending" in Stata or "setting" in SAS
    * `cbind` allows you to add columns in addition to the previous ways
* `t()` can transpose data but doesn't make it a `data.frame`

## Merging 

```{r merging2}
merged.data <- merge(base, visits, by = "id")
head(merged.data, 5)
dim(merged.data)
```


## Merging 

```{r mergeall}
all.data <- merge(base, visits, by = "id", all = TRUE)
tail(all.data)
dim(all.data)
```

<!-- # Bonus slides - explore after visualization!  -->

<!-- ## Perform Operations By Multiple Groups: dplyr {.smaller} -->

<!-- We can then easily plot each day over time: -->

<!-- ```{r} -->
<!-- library(ggplot2) -->
<!-- ggplot(aes(x = date, y = Average, colour = line), data = wide) + geom_line() -->
<!-- ``` -->

<!-- ## ggplot2 with piping -->

<!-- ```{r} -->
<!-- wide %>%  -->
<!--   ggplot(aes(x = date, y = Average, colour = line)) + geom_line() -->
<!-- ``` -->

<!-- ## Perform Operations By Multiple Groups: dplyr -->

<!-- Let's create the middle of the month (the 15th for example), and name it -->
<!-- mon. -->

<!-- ```{r} -->
<!-- mon = wide %>%  -->
<!--   mutate(month = month(date), year = year(date)) %>%    -->
<!--   dplyr::group_by(line, month, year) %>% -->
<!--   dplyr::summarise(mean_avg = mean(Average)) -->
<!-- mon = mutate(mon, mid_month = dmy(paste0("15-", month, "-", year))) -->
<!-- head(mon) -->
<!-- ``` -->


<!-- ## Perform Operations By Multiple Groups: dplyr {.smaller} -->

<!-- We can then easily plot the mean of each month to see a smoother output: -->

<!-- ```{r} -->
<!-- ggplot(aes(x = mid_month, -->
<!--                y = mean_avg,  -->
<!--                colour = line), data = mon) + geom_line() -->
<!-- ``` -->



<!-- ## Bonus! Points with a smoother! {.smaller} -->
<!-- ```{r} -->
<!-- ggplot(aes(x = date, y = Average, colour = line),  -->
<!--            data = wide) + geom_smooth(se = FALSE) +  -->
<!--   geom_point(size = .5) -->
<!-- ``` -->
