chapter_3_data_transformation
Alex Tan
2025-09-11
3.1 Introduction
- using the dplyr package
3.1.1 Prerequisites
- using data from the nycflights13 package and use ggplot2
library(nycflights13)
library(tidyverse)## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.4 ✔ readr 2.1.5
## ✔ forcats 1.0.0 ✔ stringr 1.5.1
## ✔ ggplot2 3.5.2 ✔ tibble 3.3.0
## ✔ lubridate 1.9.4 ✔ tidyr 1.3.1
## ✔ purrr 1.1.0
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors3.1.2 nycflights13
- dataset of flights leaving NYC
flights## # A tibble: 336,776 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 336,766 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>view(flights)
glimpse(flights)## Rows: 336,776
## Columns: 19
## $ year <int> 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2…
## $ month <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ day <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ dep_time <int> 517, 533, 542, 544, 554, 554, 555, 557, 557, 558, 558, …
## $ sched_dep_time <int> 515, 529, 540, 545, 600, 558, 600, 600, 600, 600, 600, …
## $ dep_delay <dbl> 2, 4, 2, -1, -6, -4, -5, -3, -3, -2, -2, -2, -2, -2, -1…
## $ arr_time <int> 830, 850, 923, 1004, 812, 740, 913, 709, 838, 753, 849,…
## $ sched_arr_time <int> 819, 830, 850, 1022, 837, 728, 854, 723, 846, 745, 851,…
## $ arr_delay <dbl> 11, 20, 33, -18, -25, 12, 19, -14, -8, 8, -2, -3, 7, -1…
## $ carrier <chr> "UA", "UA", "AA", "B6", "DL", "UA", "B6", "EV", "B6", "…
## $ flight <int> 1545, 1714, 1141, 725, 461, 1696, 507, 5708, 79, 301, 4…
## $ tailnum <chr> "N14228", "N24211", "N619AA", "N804JB", "N668DN", "N394…
## $ origin <chr> "EWR", "LGA", "JFK", "JFK", "LGA", "EWR", "EWR", "LGA",…
## $ dest <chr> "IAH", "IAH", "MIA", "BQN", "ATL", "ORD", "FLL", "IAD",…
## $ air_time <dbl> 227, 227, 160, 183, 116, 150, 158, 53, 140, 138, 149, 1…
## $ distance <dbl> 1400, 1416, 1089, 1576, 762, 719, 1065, 229, 944, 733, …
## $ hour <dbl> 5, 5, 5, 5, 6, 5, 6, 6, 6, 6, 6, 6, 6, 6, 6, 5, 6, 6, 6…
## $ minute <dbl> 15, 29, 40, 45, 0, 58, 0, 0, 0, 0, 0, 0, 0, 0, 0, 59, 0…
## $ time_hour <dttm> 2013-01-01 05:00:00, 2013-01-01 05:00:00, 2013-01-01 0…- in views, variable names are followed by abbreviations that tell you
the type of variable
- interger - double (aka real numbers) - character (aka strings) - date-time
- this is key because operations you can perform depend heavily on type of variable
3.1.3 dplyr basics
- primary dplyr verbs (functions) - for data manipulation: what do
they all have in common?
- the first argument is always a data frame
- subsequent arguments typically describe which columns operate on using the variable names (without quote)
- output is alwasy a new dataframe
- Each verb does one thing well, solving complex problems will usually
require combining multiple verbs using the pipe “|>”
- pipe takes the thing on its left and passis it along to the function on the right
- pronounce pipe as “then”
flights |>
filter(dest == "IAH") |>
group_by(year, month, day) |>
summarize(
arr_delay = mean(arr_delay, na.rm = TRUE)
)## `summarise()` has grouped output by 'year', 'month'. You can override using the
## `.groups` argument.## # A tibble: 365 × 4
## # Groups: year, month [12]
## year month day arr_delay
## <int> <int> <int> <dbl>
## 1 2013 1 1 17.8
## 2 2013 1 2 7
## 3 2013 1 3 18.3
## 4 2013 1 4 -3.2
## 5 2013 1 5 20.2
## 6 2013 1 6 9.28
## 7 2013 1 7 -7.74
## 8 2013 1 8 7.79
## 9 2013 1 9 18.1
## 10 2013 1 10 6.68
## # ℹ 355 more rows- the above code I would pronounce as taking the dataset flights then filtering by destination IAH, then grouping by year, month, day, then summarizing the mean arr_delay
- dplyr’s verbs are organized into four groups based on what they operate on, rows, columns, groups, or tables
3.2 Rows
- filter()
- changes which rows are present without changing their order
- arrange()
- changes the order of the rows without changing which are present
- distinct()
- finds rows with qunique values - can also optionally modify the columns
3.2.1 filter()
- filter() keep rows basked on the calues of the columns.
- first argurment is the data frame
- second and subsequent arguments are the conditions that must be true to keep the row
- Example - find all flights that departed more than 120 minutes late:
flights |>
filter(dep_delay > 120)## # A tibble: 9,723 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 848 1835 853 1001 1950
## 2 2013 1 1 957 733 144 1056 853
## 3 2013 1 1 1114 900 134 1447 1222
## 4 2013 1 1 1540 1338 122 2020 1825
## 5 2013 1 1 1815 1325 290 2120 1542
## 6 2013 1 1 1842 1422 260 1958 1535
## 7 2013 1 1 1856 1645 131 2212 2005
## 8 2013 1 1 1934 1725 129 2126 1855
## 9 2013 1 1 1938 1703 155 2109 1823
## 10 2013 1 1 1942 1705 157 2124 1830
## # ℹ 9,713 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>- Basic arithmetic symbols are used - “==” is equal to and “!=” is not equal to
- & or , can be used to indicate “and” (check for both conditions)
- is used to indicate or (check for either condition)
# flights that departed on January 1
flights |>
filter(month == 1 & day == 1)## # A tibble: 842 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 832 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm># flights that departed in January or February
flights |>
filter(month == 1 | month == 2)## # A tibble: 51,955 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 51,945 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>- For the above case there is a useful shortcut when combining | and == -“%in%” - keeps rows where the variable equals one of the values on the right
# a shorter way to select flights that departed in Jan or Feb
flights |>
filter(month %in% c(1, 2))## # A tibble: 51,955 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 51,945 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>- when running filter() dplyr executes the filtering operation
creating a new data frame and then prints - it doesn’t modify the
existing flights dataset
- to modify the input you need to use the assignment operator to create a new data drame
jan1 <- flights |>
filter(month == 1 & day == 1)3.2.2 Common mistakes
- will often use = instead of ==
- another mistake could me writing or statements like you would in english
3.2.3 arrange()
- arrange()
- changes the order of the rows based on the value of the columns
- takes a data frame and a set of column names (or more complicated
expressions) to order by
- when including more than one column name each additional column will be used to break ties in the values of the preceding columns
- example: following code sorts by departure time, which is spread over four columns. we get the earliest years, then withing, a year, then month, then days, etc.
flights |>
arrange(year, month, day, dep_time)## # A tibble: 336,776 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 336,766 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>- you can use desc() on a column inside of arrange() to reorder the data frame based on htat column in descending (big-to-small) order
- example: flights ordered from most to least delayed
flights |>
arrange(desc(dep_delay))## # A tibble: 336,776 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 9 641 900 1301 1242 1530
## 2 2013 6 15 1432 1935 1137 1607 2120
## 3 2013 1 10 1121 1635 1126 1239 1810
## 4 2013 9 20 1139 1845 1014 1457 2210
## 5 2013 7 22 845 1600 1005 1044 1815
## 6 2013 4 10 1100 1900 960 1342 2211
## 7 2013 3 17 2321 810 911 135 1020
## 8 2013 6 27 959 1900 899 1236 2226
## 9 2013 7 22 2257 759 898 121 1026
## 10 2013 12 5 756 1700 896 1058 2020
## # ℹ 336,766 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>- note: we are not deleting data we are simply rearranging
3.2.4 distinct()
- distinct() finds all unique rows in a dataset
- technically primarily operates on the rows
- you’ll mostly want distinct combination of some variables, so you can also optionally supply column names
# remove duplicate rows, if any
flights |>
distinct()## # A tibble: 336,776 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 336,766 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm># find all unique origin and destination pairs
flights |>
distinct(origin, dest)## # A tibble: 224 × 2
## origin dest
## <chr> <chr>
## 1 EWR IAH
## 2 LGA IAH
## 3 JFK MIA
## 4 JFK BQN
## 5 LGA ATL
## 6 EWR ORD
## 7 EWR FLL
## 8 LGA IAD
## 9 JFK MCO
## 10 LGA ORD
## # ℹ 214 more rows- alternatively, if you want to take the complement (keep the other
columns when filtering for unique rows) you can use the
- .keep_all = TRUE
flights |>
distinct(origin, dest, .keep_all = TRUE)## # A tibble: 224 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 214 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>- In the output, all the flights are jan1, that’s because distinct will find the first occurence of a unique row and discard the rest
- If you want to find the number of occurences
- swap distinct() for count()
- sort = TRUE argument, you can arrange them in descending order of the number of occurences
flights |>
count(origin, dest, sort = TRUE)## # A tibble: 224 × 3
## origin dest n
## <chr> <chr> <int>
## 1 JFK LAX 11262
## 2 LGA ATL 10263
## 3 LGA ORD 8857
## 4 JFK SFO 8204
## 5 LGA CLT 6168
## 6 EWR ORD 6100
## 7 JFK BOS 5898
## 8 LGA MIA 5781
## 9 JFK MCO 5464
## 10 EWR BOS 5327
## # ℹ 214 more rows#3.3 Columns - There are four important verbs that affect columns without changing the rows: - mutate() - creates new columns that are derived from the existing columns - select() - changes which columns are present - rename() - changes the names of columns - relocate() - changes the positions of the columns
3.3.1 mutate()
- mutate()
- add new columns that are calculated from the existing columns
- will learn how to transform large amount of data in the transform data
- for now will stick to basic algebra
- add new columns that are calculated from the existing columns
- example: compute for gain (how much time a delayed flight made up in the air), and the speed in mph:
flights |>
mutate(
gain = dep_delay - arr_delay,
speed = distance / air_time * 60,
.before = 1 # mutate() will by default add new column to the right hand side - can be hard to see this line will move it to the left hand side of the dataset
)## # A tibble: 336,776 × 21
## gain speed year month day dep_time sched_dep_time dep_delay arr_time
## <dbl> <dbl> <int> <int> <int> <int> <int> <dbl> <int>
## 1 -9 370. 2013 1 1 517 515 2 830
## 2 -16 374. 2013 1 1 533 529 4 850
## 3 -31 408. 2013 1 1 542 540 2 923
## 4 17 517. 2013 1 1 544 545 -1 1004
## 5 19 394. 2013 1 1 554 600 -6 812
## 6 -16 288. 2013 1 1 554 558 -4 740
## 7 -24 404. 2013 1 1 555 600 -5 913
## 8 11 259. 2013 1 1 557 600 -3 709
## 9 5 405. 2013 1 1 557 600 -3 838
## 10 -10 319. 2013 1 1 558 600 -2 753
## # ℹ 336,766 more rows
## # ℹ 12 more variables: sched_arr_time <int>, arr_delay <dbl>, carrier <chr>,
## # flight <int>, tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>,
## # distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>- the “.” in “.before” indicates that this is an argument to the
function, not the name of a third variable
- you can use .after to add the column in after a variable - using both the varaible name or the # position
flights |>
mutate(
gain = dep_delay - arr_delay,
speed = distance / air_time * 60,
.after = day
)## # A tibble: 336,776 × 21
## year month day gain speed dep_time sched_dep_time dep_delay arr_time
## <int> <int> <int> <dbl> <dbl> <int> <int> <dbl> <int>
## 1 2013 1 1 -9 370. 517 515 2 830
## 2 2013 1 1 -16 374. 533 529 4 850
## 3 2013 1 1 -31 408. 542 540 2 923
## 4 2013 1 1 17 517. 544 545 -1 1004
## 5 2013 1 1 19 394. 554 600 -6 812
## 6 2013 1 1 -16 288. 554 558 -4 740
## 7 2013 1 1 -24 404. 555 600 -5 913
## 8 2013 1 1 11 259. 557 600 -3 709
## 9 2013 1 1 5 405. 557 600 -3 838
## 10 2013 1 1 -10 319. 558 600 -2 753
## # ℹ 336,766 more rows
## # ℹ 12 more variables: sched_arr_time <int>, arr_delay <dbl>, carrier <chr>,
## # flight <int>, tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>,
## # distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>- If you want to print a dataset with only certain variables you can use the “.keep” argument
- .keep = “used”
- specifies that only columns that were involved in the mutate step will be kept in the dataset
flights |>
mutate(
gain = dep_delay - arr_delay,
hours = air_time / 60,
gain_per_hour = gain / hours,
.keep = "used"
)## # A tibble: 336,776 × 6
## dep_delay arr_delay air_time gain hours gain_per_hour
## <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 2 11 227 -9 3.78 -2.38
## 2 4 20 227 -16 3.78 -4.23
## 3 2 33 160 -31 2.67 -11.6
## 4 -1 -18 183 17 3.05 5.57
## 5 -6 -25 116 19 1.93 9.83
## 6 -4 12 150 -16 2.5 -6.4
## 7 -5 19 158 -24 2.63 -9.11
## 8 -3 -14 53 11 0.883 12.5
## 9 -3 -8 140 5 2.33 2.14
## 10 -2 8 138 -10 2.3 -4.35
## # ℹ 336,766 more rows- Note: we have not created a new data frame to be saved - just printed out the set. If we want to store the data frame for future use we need to think carefully about assigning back to flights which will overwrite the old dataframe or to create a new object which is most likely the route to go unless special cases
3.3.2 select()
- datasets can be large - focus on the variables you are interested in
- select()
- allows you to rapidly zoom in on a useful subset using operations based on the names of the variables
# select columns by name:
flights |>
select(year, month, day)## # A tibble: 336,776 × 3
## year month day
## <int> <int> <int>
## 1 2013 1 1
## 2 2013 1 1
## 3 2013 1 1
## 4 2013 1 1
## 5 2013 1 1
## 6 2013 1 1
## 7 2013 1 1
## 8 2013 1 1
## 9 2013 1 1
## 10 2013 1 1
## # ℹ 336,766 more rows# select columns between year and day (inclusive)
flights |>
select(year:day)## # A tibble: 336,776 × 3
## year month day
## <int> <int> <int>
## 1 2013 1 1
## 2 2013 1 1
## 3 2013 1 1
## 4 2013 1 1
## 5 2013 1 1
## 6 2013 1 1
## 7 2013 1 1
## 8 2013 1 1
## 9 2013 1 1
## 10 2013 1 1
## # ℹ 336,766 more rows# select columns except those from year to day (inclusice)
flights |>
select(!year:day)## # A tibble: 336,776 × 16
## dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay carrier
## <int> <int> <dbl> <int> <int> <dbl> <chr>
## 1 517 515 2 830 819 11 UA
## 2 533 529 4 850 830 20 UA
## 3 542 540 2 923 850 33 AA
## 4 544 545 -1 1004 1022 -18 B6
## 5 554 600 -6 812 837 -25 DL
## 6 554 558 -4 740 728 12 UA
## 7 555 600 -5 913 854 19 B6
## 8 557 600 -3 709 723 -14 EV
## 9 557 600 -3 838 846 -8 B6
## 10 558 600 -2 753 745 8 AA
## # ℹ 336,766 more rows
## # ℹ 9 more variables: flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## # air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm># select columns that are characters:
flights |>
select(where(is.character))## # A tibble: 336,776 × 4
## carrier tailnum origin dest
## <chr> <chr> <chr> <chr>
## 1 UA N14228 EWR IAH
## 2 UA N24211 LGA IAH
## 3 AA N619AA JFK MIA
## 4 B6 N804JB JFK BQN
## 5 DL N668DN LGA ATL
## 6 UA N39463 EWR ORD
## 7 B6 N516JB EWR FLL
## 8 EV N829AS LGA IAD
## 9 B6 N593JB JFK MCO
## 10 AA N3ALAA LGA ORD
## # ℹ 336,766 more rows- helper functions with select()
- starts_with(“abc”): matches names that begin with “abc”.
- ends_with(“xyz”): matches names that end with “xyz”.
- contains(“ijk”): matches names that contain “ijk”.
- num_range(“x”, 1:3): matches x1, x2 and x3.
- you can rename varaibles as you use select
- using = the new name appears on the left hand side and the old variable on the right
flights |>
select(tail_num = tailnum)## # A tibble: 336,776 × 1
## tail_num
## <chr>
## 1 N14228
## 2 N24211
## 3 N619AA
## 4 N804JB
## 5 N668DN
## 6 N39463
## 7 N516JB
## 8 N829AS
## 9 N593JB
## 10 N3ALAA
## # ℹ 336,766 more rows3.3.3 rename()
- if you want to keep all existing variables and just rename a few, you can use rename() instead of select():
flights|>
rename(tail_num = tailnum)## # A tibble: 336,776 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 336,766 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tail_num <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>- A bunch of inconsistently named columns check out
- janitor::clean_names()
- automated cleaning
3.3.4 relocate()
- use relocate() to move variables around
- useful to collected related variables together or move imprtant variables to the front
- by default relocate() moves variables to the front
- you can also specify by using the .before and .after arguments just like in mutate()
3.4 The pipe
- the power of the pipe arises when you start to combine multiple verbs
- example:
- want to find the fastest flights to Houston’s IAH airport: you need to combine filter(), mutate(), select(), and arrange()
flights |>
filter(dest == "IAH") |> # filtering for the rows that contain IAH as the destination
mutate(speed = distance / air_time * 60) |> # creating a new column that calculates speed
select(year:day, dep_time, carrier, flight, speed) |> # selecting the columns we want to see
arrange(desc(speed)) #arranging by the fastest to slowest speed## # A tibble: 7,198 × 7
## year month day dep_time carrier flight speed
## <int> <int> <int> <int> <chr> <int> <dbl>
## 1 2013 7 9 707 UA 226 522.
## 2 2013 8 27 1850 UA 1128 521.
## 3 2013 8 28 902 UA 1711 519.
## 4 2013 8 28 2122 UA 1022 519.
## 5 2013 6 11 1628 UA 1178 515.
## 6 2013 8 27 1017 UA 333 515.
## 7 2013 8 27 1205 UA 1421 515.
## 8 2013 8 27 1758 UA 302 515.
## 9 2013 9 27 521 UA 252 515.
## 10 2013 8 28 625 UA 559 515.
## # ℹ 7,188 more rows- pipe makes things so much easier to read!
- changed rstudio options to create pipe shortcut
- ctrl + shift + M
3.5 Groups
- Working in groups is a powerful part of dplyr
- focus on group_by and summarize
group_by()
- Use group_by() to divide your dataset into groups that are meaningful for your analysis
flights |>
group_by(month)## # A tibble: 336,776 × 19
## # Groups: month [12]
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 336,766 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>- group_by doesn’t change the data, but it does change the output to
be grouped by month
- subsequent operations will now work by month
- grouped feature (now referred to as class) can be changed with subsequent verbs applied to the data
3.5.2 summarize()
- Most important grouped operation is a summary
- If being used to calculate a single summary statistic, reduces the data frame to have a single row for each group
- Example: Average Departure Delay by month
flights |>
group_by(month) |>
summarize(
avg_delay = mean(dep_delay)
)## # A tibble: 12 × 2
## month avg_delay
## <int> <dbl>
## 1 1 NA
## 2 2 NA
## 3 3 NA
## 4 4 NA
## 5 5 NA
## 6 6 NA
## 7 7 NA
## 8 8 NA
## 9 9 NA
## 10 10 NA
## 11 11 NA
## 12 12 NA- what went wrong?
- some of the flights had missing data in the delay column - so when we calculated the mean including those values, we got an NA result
- try again using na.rm = True
flights |>
group_by(month) |>
summarize(
avg_delay = mean(dep_delay, na.rm = TRUE)
)## # A tibble: 12 × 2
## month avg_delay
## <int> <dbl>
## 1 1 10.0
## 2 2 10.8
## 3 3 13.2
## 4 4 13.9
## 5 5 13.0
## 6 6 20.8
## 7 7 21.7
## 8 8 12.6
## 9 9 6.72
## 10 10 6.24
## 11 11 5.44
## 12 12 16.6- You can create any number of summaries in a single call to summarize() - various useful summaries such as n() which returns the number of rows in each group
flights |>
group_by(month) |>
summarize(
avg_delay = mean(dep_delay, na.rm = TRUE),
n = n()
) # The outputted table tells us the number of flights with delay data by month and the mean of the delay data## # A tibble: 12 × 3
## month avg_delay n
## <int> <dbl> <int>
## 1 1 10.0 27004
## 2 2 10.8 24951
## 3 3 13.2 28834
## 4 4 13.9 28330
## 5 5 13.0 28796
## 6 6 20.8 28243
## 7 7 21.7 29425
## 8 8 12.6 29327
## 9 9 6.72 27574
## 10 10 6.24 28889
## 11 11 5.44 27268
## 12 12 16.6 281353.5.3 The slice_ functions
There are five handy functions that allow you to extract specific rows within each group: - df |> slice_head(n = 1) takes the first row from each group. - df |> slice_tail(n = 1) takes the last row in each group. - df |> slice_min(x, n = 1) takes the row with the smallest value of column x. - df |> slice_max(x, n = 1) takes the row with the largest value of column x. - df |> slice_sample(n = 1) takes one random row.
You can vary n to select more than row or select for a proportion of rows in that group - you can use prop = 0.1 to select for 10 percent of rows in each group - Example: following code finds the flights that are most delayed upon the arrival at each destination
flights |>
group_by(dest) |>
slice_max(arr_delay, n = 1) |>
relocate(dest) # moving destination to the left most column for easy viewing## # A tibble: 108 × 19
## # Groups: dest [105]
## dest year month day dep_time sched_dep_time dep_delay arr_time
## <chr> <int> <int> <int> <int> <int> <dbl> <int>
## 1 ABQ 2013 7 22 2145 2007 98 132
## 2 ACK 2013 7 23 1139 800 219 1250
## 3 ALB 2013 1 25 123 2000 323 229
## 4 ANC 2013 8 17 1740 1625 75 2042
## 5 ATL 2013 7 22 2257 759 898 121
## 6 AUS 2013 7 10 2056 1505 351 2347
## 7 AVL 2013 8 13 1156 832 204 1417
## 8 BDL 2013 2 21 1728 1316 252 1839
## 9 BGR 2013 12 1 1504 1056 248 1628
## 10 BHM 2013 4 10 25 1900 325 136
## # ℹ 98 more rows
## # ℹ 11 more variables: sched_arr_time <int>, arr_delay <dbl>, carrier <chr>,
## # flight <int>, tailnum <chr>, origin <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>- if you have rows tied for value - you may want one row per group in which you will have to use with_ties = FALSE modifier
Grouping by multiple variables
Creating groups using more than one variable
daily <- flights |>
group_by(year, month, day)
daily## # A tibble: 336,776 × 19
## # Groups: year, month, day [365]
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 336,766 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>When you summarize a tibble grouped by more than one variable, each summarry peels off the last group - causes problems in making the function work - dplyr will display message on how to change the behavior
daily_flights <- daily |>
summarize(n = n())## `summarise()` has grouped output by 'year', 'month'. You can override using the
## `.groups` argument.3.5.5 Ungrouping
remove grouping from a data frame without using summarize() - ungroup()
daily |>
ungroup()## # A tibble: 336,776 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ℹ 336,766 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>now let’s summzarize an ungrouped data frame
daily |>
ungroup() |>
summarize(
avg_delay = mean(dep_delay, na.rm = TRUE),
flights = n()
)## # A tibble: 1 × 2
## avg_delay flights
## <dbl> <int>
## 1 12.6 336776You get a single row back because dplyr treats all the rows in an ungrouped data frame as belonging to one group
3.5.6 .by
new experimental syntax for per-operation grouping, the .by argument - used to group within a single operation
flights |>
summarize(
delay = mean(dep_delay, na.rm = TRUE),
n = n(),
.by = month
)## # A tibble: 12 × 3
## month delay n
## <int> <dbl> <int>
## 1 1 10.0 27004
## 2 10 6.24 28889
## 3 11 5.44 27268
## 4 12 16.6 28135
## 5 2 10.8 24951
## 6 3 13.2 28834
## 7 4 13.9 28330
## 8 5 13.0 28796
## 9 6 20.8 28243
## 10 7 21.7 29425
## 11 8 12.6 29327
## 12 9 6.72 27574- .by is more flexible and you don’t need to use the .groups argument to supress the goruping message or ungroup() to ungroup when you are done
- Example for grouping by multiple variables
flights |>
summarize(
delay = mean(dep_delay, na.rm = TRUE),
n = n(),
.by = c(origin, dest)
)## # A tibble: 224 × 4
## origin dest delay n
## <chr> <chr> <dbl> <int>
## 1 EWR IAH 11.8 3973
## 2 LGA IAH 9.06 2951
## 3 JFK MIA 9.34 3314
## 4 JFK BQN 6.67 599
## 5 LGA ATL 11.4 10263
## 6 EWR ORD 14.6 6100
## 7 EWR FLL 13.5 3793
## 8 LGA IAD 16.7 1803
## 9 JFK MCO 10.6 5464
## 10 LGA ORD 10.7 8857
## # ℹ 214 more rows3.6 Case study: aggregates and sample size
Aggregation of data it is always good to include a count n() - importing baseball data from lahman package - compare proportion of times a player gets a hit (H) vs the number of times they put the ball in play
batters <- Lahman::Batting |>
group_by(playerID) |>
summarize(
performance = sum(H, na.rm = TRUE) / sum(AB, na.rm = TRUE),
n = sum(AB, na.rm = TRUE)
)
View(batters)- when we plot the skill of the batter (batting average/performance)
against the number of opportunities to hit the ball (mesured by times at
bat, n) you see two patterns
- The variation in performance is larger in players with fewer at-bats. principle in that variation decreases as the sample size increases
- positive correlation between skill(performance) and opportunities to hit the ball
- teams want their best batter the most opportunities to hit the ball
batters |>
filter(n > 100) |> # filtering for batters who have more than 100 at bats
ggplot(aes(x = n, y = performance)) + #plotting at bats on the x and batting average on the y
geom_point(alpha = 1 / 10) + # geom_point is scatterplot with alpha being the opacity of the graph from a scale of 0-1
geom_smooth(se = FALSE) # adding a mean line for the data and se false means no confidence intercal ribbon is displayed## `geom_smooth()` using method = 'gam' and formula = 'y ~ s(x, bs = "cs")'
Note the handy pattern for combining ggplot and dplyr - You have to remember to switch from |> (dataset processing) to + (adding layers) to your plot
Understand the implications for using this dataset for ranking.
Those with the highest variance and therefore least power for of their
Batting Average will be the ones with very few at bats. It is not a real
indication of skill