4.1 dplyr: Rows
filter(), slice(), and arrange() choose and order rows. Use nycflights13::flights.
1. Why dplyr
Real tables are messy: missing values, extra rows, names that do not
match, numbers stored as text. dplyr is the tidyverse
grammar for one table. The verbs have the same first argument, a data
frame, so they pipe.
library(tidyverse)
library(nycflights13)
data("flights")Install nycflights13 once in the console.
flights is every flight out of New York City in 2013.
?flights.
A data frame is a list of atomic vectors of the same length: columns can differ in type; rows are the observational units. A tibble is the tidyverse data frame. We use the two words as synonyms.
The usual verbs, by what they touch:
- rows:
filter(),slice(),arrange() - columns:
select(),rename(),mutate(),relocate() - groups:
group_by(),summarize()
A first pipeline. Average scheduled departure time, by carrier, in the second half of the year:
flights |>
filter(month >= 7) |>
group_by(carrier) |>
summarize(mean_dep = mean(dep_time, na.rm = TRUE))## # A tibble: 16 × 2
## carrier mean_dep
## <chr> <dbl>
## 1 9E 1467.
## 2 AA 1297.
## 3 AS 1304.
## 4 B6 1380.
## 5 DL 1350.
## 6 EV 1363.
## 7 F9 1466.
## 8 FL 1405.
## 9 HA 958.
## 10 MQ 1391.
## 11 OO 1744.
## 12 UA 1326.
## 13 US 1230.
## 14 VX 1273.
## 15 WN 1279.
## 16 YV 1557.The Base R version is flights[flights$month >= 7, ]
then aggregate(). The pipe is easier to read and to debug:
comment out a line, or insert View().
2. filter() keeps rows by value
filter() keeps rows where a condition is
TRUE. Note 2.1 already compared it to
subset().
flights |>
filter(month == 1)## # A tibble: 27,004 × 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
## # ℹ 26,994 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>Several conditions separated by commas are and.
Write & and | when the logic is mixed.
xor() is exclusive or.
flights |>
filter(month == 1, origin == "JFK")## # A tibble: 9,161 × 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 542 540 2 923 850
## 2 2013 1 1 544 545 -1 1004 1022
## 3 2013 1 1 557 600 -3 838 846
## 4 2013 1 1 558 600 -2 849 851
## 5 2013 1 1 558 600 -2 853 856
## 6 2013 1 1 558 600 -2 924 917
## 7 2013 1 1 559 559 0 702 706
## 8 2013 1 1 606 610 -4 837 845
## 9 2013 1 1 611 600 11 945 931
## 10 2013 1 1 613 610 3 925 921
## # ℹ 9,151 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 |>
filter((month == 1 & origin == "LGA") | (month == 12 & origin == "JFK"))## # A tibble: 17,096 × 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 533 529 4 850 830
## 2 2013 1 1 554 600 -6 812 837
## 3 2013 1 1 557 600 -3 709 723
## 4 2013 1 1 558 600 -2 753 745
## 5 2013 1 1 559 600 -1 941 910
## 6 2013 1 1 600 600 0 851 858
## 7 2013 1 1 600 600 0 837 825
## 8 2013 1 1 602 610 -8 812 820
## 9 2013 1 1 602 605 -3 821 805
## 10 2013 1 1 623 610 13 920 915
## # ℹ 17,086 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>Unknown levels: unique(flights$origin), or
levels(as.factor(flights$origin)).
filter() drops rows where the condition is
NA. Ask for them with is.na().
NA == NA is NA, not TRUE.
dfdat <- data.frame(x = c(1, NA, 2), y = c(2, 4, 1))
dfdat |> filter(x == 1)## x y
## 1 1 2dfdat |> filter(x == 1 | is.na(x))## x y
## 1 1 2
## 2 NA 4For doubles, == is strict. Use
dplyr::near():
sqrt(2)^2 == 2## [1] FALSEnear(sqrt(2)^2, 2)## [1] TRUEIntegers can use ==.
3. slice() keeps rows by position
flights |> slice(5:10)## # A tibble: 6 × 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 554 600 -6 812 837
## 2 2013 1 1 554 558 -4 740 728
## 3 2013 1 1 555 600 -5 913 854
## 4 2013 1 1 557 600 -3 709 723
## 5 2013 1 1 557 600 -3 838 846
## 6 2013 1 1 558 600 -2 753 745
## # ℹ 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 |> slice(c(1, 4, 6))## # A tibble: 3 × 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 544 545 -1 1004 1022
## 3 2013 1 1 554 558 -4 740 728
## # ℹ 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 |> slice(10:n()) # n() is the number of rows## # A tibble: 336,767 × 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 558 600 -2 753 745
## 2 2013 1 1 558 600 -2 849 851
## 3 2013 1 1 558 600 -2 853 856
## 4 2013 1 1 558 600 -2 924 917
## 5 2013 1 1 558 600 -2 923 937
## 6 2013 1 1 559 600 -1 941 910
## 7 2013 1 1 559 559 0 702 706
## 8 2013 1 1 559 600 -1 854 902
## 9 2013 1 1 600 600 0 851 858
## 10 2013 1 1 600 600 0 837 825
## # ℹ 336,757 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>Helpers:
slice_head(n = 3),slice_tail(n = 3)slice_sample(n = 200)orslice_sample(prop = 0.1)slice_min(arr_delay, n = 5),slice_max(arr_delay, n = 5)
Drop NAs in the ordering column first. Ties can return
more rows than n. with_ties = FALSE cuts at
n using the current row order.
flights |>
filter(!is.na(arr_delay)) |>
slice_max(arr_delay, n = 5)## # A tibble: 5 × 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
## # ℹ 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>set.seed() before slice_sample() if you
want a repeatable draw. Two samples of the “same” size
(prop = 0.01 vs n = 3367) are still two
draws.
4. arrange() sorts rows
Default is ascending. desc(x) or -x
(numerics) reverses. Extra arguments break ties. Missing values go last,
even under desc().
flights |> arrange(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 12 7 2040 2123 -43 40 2352
## 2 2013 2 3 2022 2055 -33 2240 2338
## 3 2013 11 10 1408 1440 -32 1549 1559
## 4 2013 1 11 1900 1930 -30 2233 2243
## 5 2013 1 29 1703 1730 -27 1947 1957
## 6 2013 8 9 729 755 -26 1002 955
## 7 2013 10 23 1907 1932 -25 2143 2143
## 8 2013 3 30 2030 2055 -25 2213 2250
## 9 2013 3 2 1431 1455 -24 1601 1631
## 10 2013 5 5 934 958 -24 1225 1309
## # ℹ 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>flights |> arrange(desc(dep_delay), arr_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>Character order follows the locale.
5. Practice
- Flights in odd months, or on even days of even months
(
%%). - Flights that went to Houston (
IAHorHOU), on UA, AA, or DL, in July–September, arrived more than 15 minutes late, and did not leave late. - Rows 10, 100, 1000, 10000, and 100000.
- The last 10 of the first 30 Newark (
EWR) flights. - Longest
distance, ties broken byair_time. - The 50 longest
air_times, then the 10 smallestarr_delays among those. Repeat withair_time < 300. What doeswith_ties = FALSEchange?