data-manip.Rmd

---
title: "Data Manipulation in R"
author: "Mark Dunning"
date: '`r format(Sys.time(), "Last modified: %d %b %Y")`'
output: html_document
---

# Introduction to Data Manipulation with R

## Motivation


![tolstoy](images/tolstoy.jpg)


![hadley](images/hadley.jpg)

> Like families, tidy datasets are all alike but every messy dataset is messy in its own way - (Hadley Wickham)

http://vimeo.com/33727555

http://vita.had.co.nz/papers/tidy-data.pdf

## Working with data frames

## Tidy data principles

Two guiding principles:

- Each column is a variable
- Each row is an observation

However,

- Un-tidy data are easier for humans to read
- Tidy data are easier for computers to analyse

    
## Example (from tidyr paper)

```{r echo=FALSE,results='asis'}
library(knitr)
untidy <- data.frame(Name = c("John Smith","Jane Doe","Mary Johnson"),treatmenta = c("-",16,3),treatmentb=c(2,11,1))
kable(untidy)
```

http://vita.had.co.nz/papers/tidy-data.pdf

## Re-arranging the data

```{r echo=FALSE,results='asis'}
untidy2 <- t(untidy)
colnames(untidy2) <- untidy2[1,]
untidy2 <- untidy2[-1,]
kable(untidy2)
```

## Definitions

- A dataset is made up of ***values***
- Each value is associated with a ***variable** and an ***observation***
    + variable contains all values that measure the same underlying variable
    + an observation contains all values measured on the same unit
- How many values, variables and observations do we have?

## Answer

- 18 values
- 3 variables
    + Person 
    + Treatment
    + Result
- 6 observations

## Tidy form of the data

- We now have one column for each variable
```{r echo=FALSE,warning=FALSE}
library(tidyr)
suppressPackageStartupMessages(library(dplyr))
gather(untidy, Treatment, Result, treatmenta:treatmentb) %>% mutate(Treatment = gsub("treatment","",Treatment))
```

## The tidyr package

- Two main functions
    + `gather()` and `spread()`
    
## How to use gather


```{r}
untidy <- read.delim("tidyr-example.txt")
untidy
```

## How to use gather

- Creates a column of 'Treatment' keys
    + the variables to use in the original data frame are treatmenta and treatmentb
```{r warning=FALSE}
gather(untidy, Treatment, Result, c(treatmenta,treatmentb))
```


## Example: Florence Nightingale data

```{r results='asis',echo=FALSE}
ngale <- read.delim("Nightingale.txt")
library(knitr)
kable(head(ngale))
```

- What variables and observations do we have?
- What might a 'tidy' version of the dataset look like?

## Tidy form

```{r echo=FALSE, results='asis',message=FALSE}
library(tidyr)
ngale <- gather(ngale,Cause, Deaths, Disease:Other)
```

- Have a go at tidying these data
    + "Nightingale.txt"
    
## Tidy solution

```{r eval=FALSE}
library(tidyr)
ngale <- read.delim("Nightingale.txt")
ngale <- gather(ngale,Cause, Deaths, Disease:Other)
```

## Spread

- spread has the opposite effect to gather and will translate tidy data back into human-readable form

```{r}

spread(ngale, Cause,Deaths)
```


## More data-cleaning tasks
There are some tasks we might wish to perform before analysing the data

- Extract year, month information from the date
- Calculate death rates
- Subset according to year

## Introducing dplyr

- dplyr is an all-purpose data wrangling tool
- It introduces several 'verbs' that can be used to perform any data manipulation task
    + select: select *columns*
    + filter: select *rows*
    + arrange: re-order arrange rows
    + mutate: create new columns (variables)
    + summarise: summarise values
- Essential reference
    + [dplyr cheatsheet](https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf)

## Example

We are going to explore some of the basic features of R using some patient data; the kind of data that we might encounter in the wild. However, rather than using real-life data we are going to make some up. There is a package called `wakefield` that is particularly convenient for this task.

Various patient characteristics can be generated. The following is a function that uses the package to create a *data frame* with various clinical characteristics. The number of patients we want to simulate is an argument.

Don't worry about what the function does, you can just paste the following into the R console, 


```{r}
library(wakefield)
random_patients <- function(n) {
  r_data_frame(
    n,
    id,
    name,
    race,
    sex,
    smokes,
    height,
    birth(random = TRUE, x = NULL, start = Sys.Date() - 365 * 45, k = 365*2,by = "1 days"),
    state,
    pet,
    grade_level(x=1:3),
    died,    
    normal(name="Count"),
    date_stamp)
}
```

We can now use the `random_patients` function to generate a data frame of 250 fictitious patients

```{r}
patients <- random_patients(250)
```

In Rstudio , you can view the contents of this data frame in a tab.

```{r eval=FALSE}
View(patients)
```

******

## Q. What are the dimensions of the data frame?

## Q. What columns are available?

******

`patients` is a special kind of data frame. The main difference is that the whole data frame does not get printed to screen

```{r}
patients
```


The `tbl_df` function in dplyr creates a new object with more-convenient default display

- The object behaves in exactly the same way as a data frame


## select


```{r}
select(patients, Name, Race)
```


## omitting columns

```{r}
select(patients, -Name)
```

## selecting range of columns

```{r}
select(patients, Name:Sex)
```

## select, helper functions

```{r}
select(patients, starts_with("Grade"))
```

see also
- contains, matches, ends_with

## filter

```{r}
filter(patients, Count >= 1.5)
```

## filter
```{r}
filter(patients, Count >= 1.5, Died)
```

## filter

```{r}
filter(patients, Pet %in% c("Cat", "Dog"))
```

## Combining operations

```{r}
patients <- select(patients, Name, Count,Died)
patients2 <- filter(patients,Count>1.5,Died)
patients2
```

## Alternative code

We could nest the expressions
```{r}
patients2 <- filter(select(patients,Name, Count,Died),
                  Count>1.5,Died)
patients2
```

## Introducing piping

The output of one operations gets used as the input of the next

In computing, this is referring to as *piping* 
- unix commands use the `|` symbol

## magrittr

![not-a-pipe](images/pipe.jpeg)

![also-not-a-pipe](https://upload.wikimedia.org/wikipedia/en/b/b9/MagrittePipe.jpg)

- the magrittr library implements this in R


## Simple example

```{r}
head(patients)
patients %>% head
```


## Re-write with 'pipes'

```{r}
patients <- random_patients(250) %>% 
      select(Name, Count,Died) %>% 
      filter(Count>1.5,Died)
patients
```


##arrange: order rows

```{r}
patients <- random_patients(250) %>% 
      select(Name, Count,Died) %>% 
      filter(Count>1.5,Died) %>% 
      arrange(Count)
patients
```


## descending order

```{r}
patients <- random_patients(250) %>% 
      select(Name, Count,Died) %>% 
      filter(Count>1.5,Died) %>% 
      arrange(desc(Count))
patients
```

## mutate: create new columns

- Can use standard arithmetic operations

```{r}
random_patients(250) %>% 
  mutate(Rank = rank(Count))
```


## character manipulation

```{r}
library(stringr)
random_patients(250) %>% 
      mutate(Abrv=str_sub(State,1,3))
```


```{r}
random_patients(250) %>% 
    summarise(avg_cnt = mean(Count))
```

##Grouping

summarise can be used in conjunction with a group-by operation

```{r}
random_patients(250) %>% 
    group_by(Died) %>% 
    summarise(avg_count = mean(Count),
              sd_count = sd(Count),
              total = n())
```


## Exercise

- Re-write the code to read and tidy the Nightingale data using tidyr and dplyr

```{r}
ngale <- read.delim("Nightingale.txt")
ngale <- gather(ngale,Cause, Deaths, Disease:Other)
```

- Arrange the rows in the data so then are ordered by
    + Date
    + Cause of Death
    

## Solution

```{r}
ngale <- read.delim("Nightingale.txt") %>%  
  tbl_df %>% 
  gather(Cause, Deaths, Disease:Other) %>%  
  arrange(Date, Cause)
ngale
```


## Exercise

- Add a new Rate column

```{r echo=FALSE}
ngale %>% mutate(Rate = round(12 * 1000 * Deaths /Army,1))
```

## Exercise

Extract month / year information

```{r echo=FALSE}
library(lubridate)
ngale %>% mutate(Date = ymd(Date),Year = year(Date), Month = month.abb[month(Date)])
```

## Exercise

- Which dates are after the change of regime?

```{r echo=FALSE}
ngale %>% 
  mutate(Date = ymd(Date),Year = year(Date)) %>% 
  mutate(Date = ymd(Date),Year = year(Date), Month = month.abb[month(Date)]) %>% 
  mutate(Regime = ifelse(Date > ymd("1855-03-01"), "After","Before"))

```


## References

http://stat545-ubc.github.io/topics.html

http://garrettgman.github.io/tidying/

https://cran.r-project.org/web/packages/tidyr/vignettes/tidy-data.html

http://kbroman.org/dataorg/