Filtering rows
Jeff Stevens
2023-02-20
Review
The problem
What’s different between these data sets? What can we use to create data2 from data1?
data1 cond id date
1 1 1 2022-02-01
2 1 2 2022-01-19
3 3 3 2022-02-23
4 2 4 2022-02-02
5 3 5 2022-02-25
6 3 6 2022-02-18
7 3 7 2022-01-05
8 2 8 2022-01-14data2 cond id month date
1 Condition 1 1 February 2022-02-01
2 Condition 1 2 January 2022-01-19
3 Condition 3 3 February 2022-02-23
4 Condition 2 4 February 2022-02-02
5 Condition 3 5 February 2022-02-25
6 Condition 3 6 February 2022-02-18
7 Condition 3 7 January 2022-01-05
8 Condition 2 8 January 2022-01-14The problem
What’s different between these data sets? What can we use to create data3 from data1?
data1 cond id date
1 1 1 2022-02-01
2 1 2 2022-01-19
3 3 3 2022-02-23
4 2 4 2022-02-02
5 3 5 2022-02-25
6 3 6 2022-02-18
7 3 7 2022-01-05
8 2 8 2022-01-14data3 id cond
1 1 1
2 2 1
3 3 3
4 4 2
5 5 3
6 6 3
7 7 3
8 8 2The problem
What’s different between these data sets? What needs to happen to create data4 from data1?
data1 cond id date
1 1 1 2022-02-01
2 1 2 2022-01-19
3 3 3 2022-02-23
4 2 4 2022-02-02
5 3 5 2022-02-25
6 3 6 2022-02-18
7 3 7 2022-01-05
8 2 8 2022-01-14data4 cond id date
1 2 8 2022-01-14
2 1 2 2022-01-19
3 1 1 2022-02-01
4 2 4 2022-02-02Data wrangling
Set-up
library(dplyr)
library(nycflights13)
(flights2 <- select(flights, year:dep_delay, air_time))# A tibble: 336,776 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 1 1 517 515 2 227
2 2013 1 1 533 529 4 227
3 2013 1 1 542 540 2 160
4 2013 1 1 544 545 -1 183
5 2013 1 1 554 600 -6 116
6 2013 1 1 554 558 -4 150
7 2013 1 1 555 600 -5 158
8 2013 1 1 557 600 -3 53
9 2013 1 1 557 600 -3 140
10 2013 1 1 558 600 -2 138
# … with 336,766 more rowsFiltering rows
filter()
filter()
filter(flights2, dep_time == sched_dep_time)# A tibble: 16,514 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 1 1 559 559 0 44
2 2013 1 1 600 600 0 152
3 2013 1 1 600 600 0 134
4 2013 1 1 607 607 0 157
5 2013 1 1 615 615 0 182
6 2013 1 1 615 615 0 120
7 2013 1 1 635 635 0 248
8 2013 1 1 655 655 0 294
9 2013 1 1 739 739 0 249
10 2013 1 1 745 745 0 378
# … with 16,504 more rowsfilter()
Multiple conditions
filter(flights2, dep_delay < 0 & month == 2)# A tibble: 13,397 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 2 1 456 500 -4 98
2 2013 2 1 520 525 -5 209
3 2013 2 1 527 530 -3 233
4 2013 2 1 532 540 -8 195
5 2013 2 1 552 600 -8 58
6 2013 2 1 552 600 -8 227
7 2013 2 1 552 600 -8 42
8 2013 2 1 553 600 -7 134
9 2013 2 1 553 600 -7 125
10 2013 2 1 553 600 -7 49
# … with 13,387 more rowsfilter()
Logical OR
filter(flights2, month == 11 | month == 12)# A tibble: 55,403 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 11 1 5 2359 6 205
2 2013 11 1 35 2250 105 36
3 2013 11 1 455 500 -5 88
4 2013 11 1 539 545 -6 229
5 2013 11 1 542 545 -3 147
6 2013 11 1 549 600 -11 359
7 2013 11 1 550 600 -10 57
8 2013 11 1 554 600 -6 40
9 2013 11 1 554 600 -6 126
10 2013 11 1 554 600 -6 93
# … with 55,393 more rowsfilter()
Logical %in%
# A tibble: 55,403 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 11 1 5 2359 6 205
2 2013 11 1 35 2250 105 36
3 2013 11 1 455 500 -5 88
4 2013 11 1 539 545 -6 229
5 2013 11 1 542 545 -3 147
6 2013 11 1 549 600 -11 359
7 2013 11 1 550 600 -10 57
8 2013 11 1 554 600 -6 40
9 2013 11 1 554 600 -6 126
10 2013 11 1 554 600 -6 93
# … with 55,393 more rowsfilter()
Negating conditional
filter(flights2, month != 1)# A tibble: 309,772 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 10 1 447 500 -13 69
2 2013 10 1 522 517 5 174
3 2013 10 1 536 545 -9 132
4 2013 10 1 539 545 -6 172
5 2013 10 1 539 545 -6 186
6 2013 10 1 544 550 -6 191
7 2013 10 1 549 600 -11 46
8 2013 10 1 550 600 -10 38
9 2013 10 1 550 600 -10 39
10 2013 10 1 551 600 -9 117
# … with 309,762 more rowsfilter()
Negating multiple conditionals
filter(flights2, month != 1 & day != 1)# A tibble: 299,578 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 10 2 449 500 -11 73
2 2013 10 2 513 517 -4 180
3 2013 10 2 537 545 -8 190
4 2013 10 2 540 545 -5 181
5 2013 10 2 543 545 -2 134
6 2013 10 2 546 550 -4 192
7 2013 10 2 548 600 -12 48
8 2013 10 2 548 600 -12 42
9 2013 10 2 550 600 -10 32
10 2013 10 2 552 600 -8 40
# … with 299,568 more rowsfilter()
Check if NA with is.na()
# A tibble: 8,255 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 1 1 NA 1630 NA NA
2 2013 1 1 NA 1935 NA NA
3 2013 1 1 NA 1500 NA NA
4 2013 1 1 NA 600 NA NA
5 2013 1 2 NA 1540 NA NA
6 2013 1 2 NA 1620 NA NA
7 2013 1 2 NA 1355 NA NA
8 2013 1 2 NA 1420 NA NA
9 2013 1 2 NA 1321 NA NA
10 2013 1 2 NA 1545 NA NA
# … with 8,245 more rowsfilter()
Check if NA across multiple columns with is.na() and if_any()
filter(flights2, if_any(everything(), is.na))# A tibble: 9,430 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 1 1 1525 1530 -5 NA
2 2013 1 1 1528 1459 29 NA
3 2013 1 1 1740 1745 -5 NA
4 2013 1 1 1807 1738 29 NA
5 2013 1 1 1939 1840 59 NA
6 2013 1 1 1952 1930 22 NA
7 2013 1 1 2016 1930 46 NA
8 2013 1 1 NA 1630 NA NA
9 2013 1 1 NA 1935 NA NA
10 2013 1 1 NA 1500 NA NA
# … with 9,420 more rowsdrop_na()
Filter column with any NAs with drop_na()
nrow(drop_na(flights, dep_time))[1] 328521Sorting rows
arrange()
arrange()
arrange(flights2, sched_dep_time)# A tibble: 336,776 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 7 27 NA 106 NA NA
2 2013 1 2 458 500 -2 108
3 2013 1 3 458 500 -2 94
4 2013 1 4 456 500 -4 77
5 2013 1 5 458 500 -2 85
6 2013 1 6 458 500 -2 88
7 2013 1 7 454 500 -6 86
8 2013 1 8 454 500 -6 77
9 2013 1 9 457 500 -3 87
10 2013 1 10 450 500 -10 78
# … with 336,766 more rowsarrange()
Sort multiple rows
arrange(flights2, sched_dep_time, dep_time)# A tibble: 336,776 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 7 27 NA 106 NA NA
2 2013 5 8 445 500 -15 78
3 2013 5 5 446 500 -14 90
4 2013 9 4 446 500 -14 79
5 2013 10 1 447 500 -13 69
6 2013 9 19 447 500 -13 81
7 2013 1 29 448 500 -12 88
8 2013 12 27 448 500 -12 92
9 2013 5 7 448 500 -12 80
10 2013 10 2 449 500 -11 73
# … with 336,766 more rowsarrange()
Sort in descending order
# A tibble: 336,776 × 7
year month day dep_time sched_dep_time dep_delay air_time
<int> <int> <int> <int> <int> <dbl> <dbl>
1 2013 10 30 2400 2359 1 182
2 2013 11 27 2400 2359 1 230
3 2013 12 5 2400 2359 1 182
4 2013 12 9 2400 2359 1 195
5 2013 12 9 2400 2250 70 41
6 2013 12 13 2400 2359 1 192
7 2013 12 19 2400 2359 1 193
8 2013 12 29 2400 1700 420 161
9 2013 2 7 2400 2359 1 186
10 2013 2 7 2400 2359 1 194
# … with 336,766 more rowsSolving the problem
What code turns data1 into data4?
data1 cond id date
1 1 1 2022-02-01
2 1 2 2022-01-19
3 3 3 2022-02-23
4 2 4 2022-02-02
5 3 5 2022-02-25
6 3 6 2022-02-18
7 3 7 2022-01-05
8 2 8 2022-01-14data4 cond id date
1 2 8 2022-01-14
2 1 2 2022-01-19
3 1 1 2022-02-01
4 2 4 2022-02-02