Data tidying and transforming

Package: Tidyr

Tidy data is a standard way of mapping the meaning of a dataset to its structure. A dataset is messy or tidy depending on how rows, columns and tables are matched up with observations, variables and types.

In tidy data:

1. Each variable forms a column
2. Each observation forms a row
3. Each data set contains information information about a single observational unit of analysis (e.g., families, participants, participant visits)

Tidy data makes it easy for an analyst or a computer to extract needed variables because it provides a standard way of structuring a dataset.

pew <- read.delim(
  file = "http://stat405.had.co.nz/data/pew.txt",
  header = TRUE,
  stringsAsFactors = FALSE,
  check.names = F,
  encoding = "UTF-8"
)

tidytable<-gather(pew,income,frequency, -religion)

tidytable<-gather(pew,income,frequency, -religion)

kable(head(tidytable))

Package: DPLYR

Why use dplyr?

• Great for data exploration and transformation.
• Intuitive to write and easy to read.
• Fast on data frames.

• Five basic verbs
  • filter
  • select
  • arrange
  • mutate
  • summarise along side with group_by.
• Can work with data stored in databases and data tables.
• Joins: inner join, left join, semi-join, anti-join.
• Window functions for calculating ranking, offsets, and more.

library(dplyr)
library(nycflights13)

1. To SFO or OAK.
2. In January.
3. Delayed by more than an hour.
4. That departed between midnight and five am.
5. Where the arrival delay was more than twice the departure delay.

## (1)
filter(flights, dest %in% c("SFO", "OAK")) 
filter(flights, dest == "SFO" | dest == "OAK")
filter(flights, dest == "SFO" | "OAK") ##Error

## (2)
filter(flights, month == 1)

## (3)
filter(flights, dep_delay > 60)

## (4)
filter(flights, hour >= 0, hour <= 5) 
filter(flights, hour >= 0 & hour <= 5)

## (5)
filter(flights, arr_delay > 2 * dep_delay)

1. Read the help for select(). What other ways can you select variables?
2. Write down three ways to select the two delay variables.

select(flights, arr_delay, dep_delay)
select(flights, arr_delay:dep_delay)
select(flights, ends_with("delay"))
select(flights, contains("delay"))

1. Order the flights by departure date and time.
2. Which flights were most delayed?

##(1)
arrange(flights, year ,month, day, hour, minute)
##(2)
arrange(flights, desc(dep_delay)) 
arrange(flights, desc(arr_delay))

1. Compute speed in mph from time (in minutes) and distance (in miles). Which flight flew the fastest?
2. Add a new variable that shows how much time was made up or lost in flight.
3. How did I compute hour and minute from dep_time?

hint: you may need to use select() or View() to see your new variable

## (1)
fl <- mutate(flights, speed = distance / (air_time / 60))
arrange(fl, desc(speed))

## (2)
mutate(fl, delta = dep_delay - arr_delay)

## (3)
mutate(fl,
 hour = dep_time %/% 100,
 minute = dep_time %% 100)

by_month <- group_by(flights, month)
kable(summarise(by_month,Number_flights=n()))

How might you summarise dep_delay for each day?

hint: mean, sd, median, max, min, quantile, sum, n

by_date <- group_by(flights, month, day)
delays <- summarise(by_date,
  mean = mean(dep_delay, na.rm = TRUE),
  median = median(dep_delay, na.rm = TRUE),
  q75 = quantile(dep_delay, 0.75, na.rm = TRUE),
  over_15 = mean(dep_delay > 15, na.rm = TRUE),
  over_30 = mean(dep_delay > 30, na.rm = TRUE),
  over_60 = mean(dep_delay > 60, na.rm = TRUE)
)

hourly_delay <- filter(
  summarise(
    group_by(
      filter(
flights,
        !is.na(dep_delay)
      ),
month,day, hour ),
    delay = mean(dep_delay),
n = n() ),
n > 10 )

hourly_delay <- flights %>%
  filter(!is.na(dep_delay)) %>%
  group_by(month,day, hour) %>%
  summarise(delay = mean(dep_delay), n = n()) %>%
  filter(n > 10)

Hint: pronounce %>% as then

Create data pipelines to answer the following questions:

1. Which destinations have the highest average delays?
2. Which flights (i.e. carrier + flight) happen every day?
   • Where do they fly to?
3. On average, how do delays vary over the course of a day?

(Hint: hour + minute / 60)

flights %>%
  group_by(dest) %>%
  summarise(
    arr_delay = mean(arr_delay, na.rm = TRUE),
    n = n()) %>%
  arrange(desc(arr_delay))

flights %>%
  group_by(carrier, flight, dest) %>%
  tally(sort = TRUE) %>% 
  filter(n == 365)

OR

flights %>%
  group_by(carrier, flight, dest) %>%
  summarise(n = n()) %>%
  arrange(desc(n)) %>%
  filter(n == 365)

per_hour <- flights %>%
  mutate(time = hour + minute / 60) %>%
  group_by(time) %>%
  summarise(
    arr_delay = mean(arr_delay, na.rm = TRUE),n = n() )

• Creating new variables within a group is also often useful.

planes <- flights %>%
        filter(!is.na(arr_delay)) %>%
        group_by(tail_num) %>%
        filter(n() > 30)

planes %>%
  mutate(z_delay =
    (arr_delay - mean(arr_delay)) / sd(arr_delay)) %>%
  filter(z_delay > 5)

planes %>% filter(min_rank(arr_delay) < 5)

What’s the difference between min_rank(), row_number() and dense_rank()?

For each plane, find the two most delayed flights. Which of the three rank functions is most appropriate?

hourly_delay <- flights %>%
        group_by(month, day, hour) %>%
        filter(!is.na(dep_delay)) %>%
        summarise(
                delay = mean(dep_delay),
                n = n() ) %>%
        filter

delay_weather <- hourly_delay %>% left_join(weather)

Are older planes more likely to be delayed?

Introduction to dplyr https://cran.rstudio.com/web/packages/dplyr/vignettes/introduction.html

Data Processing with dplyr & tidyr https://rpubs.com/bradleyboehmke/data_wrangling

Data manipulation with tidyr http://datascienceplus.com/data-manipulation-with-tidyr/