Data Transformation With dplyr
Biostat 203B
Dr. Hua Zhou @ UCLA
Jan 28, 2020
A typical data science project:
nycflights13 data
Available from the nycflights13 package.
336,776 flights that departed from New York City in 2013:
library("tidyverse")## ── Attaching packages ───────────────────────────────────────────────────────────────────────── tidyverse 1.3.0 ──## ✓ ggplot2 3.2.1 ✓ purrr 0.3.3 ## ✓ tibble 2.1.3 ✓ dplyr 0.8.3 ## ✓ tidyr 1.0.0 ✓ stringr 1.4.0 ## ✓ readr 1.3.1 ✓ forcats 0.4.0## ── Conflicts ──────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ── ## x dplyr::filter() masks stats::filter() ## x dplyr::lag() masks stats::lag()library("nycflights13") flights## # A tibble: 336,776 x 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 ## # … with 336,766 more rows, and 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>To display more rows:
flights %>% print(n = 20)## # A tibble: 336,776 x 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 ## 11 2013 1 1 558 600 -2 849 851 ## 12 2013 1 1 558 600 -2 853 856 ## 13 2013 1 1 558 600 -2 924 917 ## 14 2013 1 1 558 600 -2 923 937 ## 15 2013 1 1 559 600 -1 941 910 ## 16 2013 1 1 559 559 0 702 706 ## 17 2013 1 1 559 600 -1 854 902 ## 18 2013 1 1 600 600 0 851 858 ## 19 2013 1 1 600 600 0 837 825 ## 20 2013 1 1 601 600 1 844 850 ## # … with 3.368e+05 more rows, and 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
%>%is the pipe is tidyverse. Above command is equivalent toprint(flights, n = 20).To display all rows:
flights %>% print(n = nrow(.))Do not try this on the
flightsdata, which has too many rows.To display more columns (variables):
flights %>% print(width = Inf)## # A tibble: 336,776 x 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 ## arr_delay carrier flight tailnum origin dest air_time distance hour minute ## <dbl> <chr> <int> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> ## 1 11 UA 1545 N14228 EWR IAH 227 1400 5 15 ## 2 20 UA 1714 N24211 LGA IAH 227 1416 5 29 ## 3 33 AA 1141 N619AA JFK MIA 160 1089 5 40 ## 4 -18 B6 725 N804JB JFK BQN 183 1576 5 45 ## 5 -25 DL 461 N668DN LGA ATL 116 762 6 0 ## 6 12 UA 1696 N39463 EWR ORD 150 719 5 58 ## 7 19 B6 507 N516JB EWR FLL 158 1065 6 0 ## 8 -14 EV 5708 N829AS LGA IAD 53 229 6 0 ## 9 -8 B6 79 N593JB JFK MCO 140 944 6 0 ## 10 8 AA 301 N3ALAA LGA ORD 138 733 6 0 ## time_hour ## <dttm> ## 1 2013-01-01 05:00:00 ## 2 2013-01-01 05:00:00 ## 3 2013-01-01 05:00:00 ## 4 2013-01-01 05:00:00 ## 5 2013-01-01 06:00:00 ## 6 2013-01-01 05:00:00 ## 7 2013-01-01 06:00:00 ## 8 2013-01-01 06:00:00 ## 9 2013-01-01 06:00:00 ## 10 2013-01-01 06:00:00 ## # … with 336,766 more rowsThe
widthargument controls the screen width.
dplyr basics
Pick observations (rows) by their values:
filter().Reorder the rows:
arrange().Pick variables (columns) by their names:
select().Create new variables with functions of existing variables:
mutate().Collapse many values down to a single summary:
summarise().
Manipulate rows (cases)
Filter rows with filter()
Flights on Jan 1st:
# same as filter(flights, month == 1 & day == 1) filter(flights, month == 1, day == 1)## # A tibble: 842 x 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 ## # … with 832 more rows, and 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 in Nov or Dec:
filter(flights, month == 11 | month == 12)## # A tibble: 55,403 x 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 11 1 5 2359 6 352 345 ## 2 2013 11 1 35 2250 105 123 2356 ## 3 2013 11 1 455 500 -5 641 651 ## 4 2013 11 1 539 545 -6 856 827 ## 5 2013 11 1 542 545 -3 831 855 ## 6 2013 11 1 549 600 -11 912 923 ## 7 2013 11 1 550 600 -10 705 659 ## 8 2013 11 1 554 600 -6 659 701 ## 9 2013 11 1 554 600 -6 826 827 ## 10 2013 11 1 554 600 -6 749 751 ## # … with 55,393 more rows, and 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>
Remove rows with duplicate values
One row from each month:
distinct(flights, month, .keep_all = TRUE)## # A tibble: 12 x 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 10 1 447 500 -13 614 648 ## 3 2013 11 1 5 2359 6 352 345 ## 4 2013 12 1 13 2359 14 446 445 ## 5 2013 2 1 456 500 -4 652 648 ## 6 2013 3 1 4 2159 125 318 56 ## 7 2013 4 1 454 500 -6 636 640 ## 8 2013 5 1 9 1655 434 308 2020 ## 9 2013 6 1 2 2359 3 341 350 ## 10 2013 7 1 1 2029 212 236 2359 ## 11 2013 8 1 12 2130 162 257 14 ## 12 2013 9 1 9 2359 10 343 340 ## # … with 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>With
.keep_all = FALSE, only distince values of the variable are selected:distinct(flights, month)## # A tibble: 12 x 1 ## month ## <int> ## 1 1 ## 2 10 ## 3 11 ## 4 12 ## 5 2 ## 6 3 ## 7 4 ## 8 5 ## 9 6 ## 10 7 ## 11 8 ## 12 9
Sample rows
Randomly select
nrows:sample_n(flights, 10, replace = TRUE)## # A tibble: 10 x 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 4 21 1139 1130 9 1449 1434 ## 2 2013 2 16 1802 1803 -1 1907 1943 ## 3 2013 3 25 1007 959 8 1126 1124 ## 4 2013 8 19 1753 1800 -7 1932 2012 ## 5 2013 9 11 1545 1545 0 1822 1805 ## 6 2013 6 1 1926 1910 16 2232 2240 ## 7 2013 3 15 1917 1850 27 2207 2206 ## 8 2013 3 11 1716 1644 32 2009 1946 ## 9 2013 12 2 1858 1900 -2 2005 2018 ## 10 2013 7 20 1505 1505 0 NA 1835 ## # … with 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>
Randomly select fraction of rows:
sample_frac(flights, 0.1, replace = TRUE)## # A tibble: 33,678 x 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 10 31 2240 2250 -10 2353 8 ## 2 2013 12 27 1159 1059 60 1319 1214 ## 3 2013 11 14 625 635 -10 902 939 ## 4 2013 8 15 717 721 -4 956 955 ## 5 2013 5 15 1306 1315 -9 1429 1435 ## 6 2013 10 1 754 800 -6 920 949 ## 7 2013 4 22 852 900 -8 1036 1035 ## 8 2013 4 19 1601 1435 86 1748 1644 ## 9 2013 6 22 805 808 -3 1050 1101 ## 10 2013 4 29 1912 1915 -3 2234 2249 ## # … with 33,668 more rows, and 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>
Select rows by position
Select rows by position:
slice(flights, 1:5)## # A tibble: 5 x 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 ## # … with 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>
Top
nrows:top_n(flights, 5)## Selecting by time_hour## # A tibble: 5 x 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 31 13 2359 14 439 437 ## 2 2013 12 31 18 2359 19 449 444 ## 3 2013 12 31 2328 2330 -2 412 409 ## 4 2013 12 31 2355 2359 -4 430 440 ## 5 2013 12 31 2356 2359 -3 436 445 ## # … with 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>Use
wtargument to specify using which varible to order. Default is the last argument.Bottome
nrows:top_n(flights, -5)## Selecting by time_hour## # A tibble: 6 x 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 558 -4 740 728 ## 6 2013 1 1 559 559 0 702 706 ## # … with 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>
Arrange rows with arrange()
Sort in ascending order:
arrange(flights, year, month, day)## # A tibble: 336,776 x 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 ## # … with 336,766 more rows, and 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>
Sort in descending order:
arrange(flights, desc(arr_delay))## # A tibble: 336,776 x 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 7 22 2257 759 898 121 1026 ## 9 2013 12 5 756 1700 896 1058 2020 ## 10 2013 5 3 1133 2055 878 1250 2215 ## # … with 336,766 more rows, and 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>
Manipulate columns (variables)
Select columns with select()
Select columns by variable name:
select(flights, year, month, day)## # A tibble: 336,776 x 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 ## # … with 336,766 more rowsPull values of one column as a vector:
pull(flights, year)
Select columns between two variables:
select(flights, year:day)## # A tibble: 336,776 x 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 ## # … with 336,766 more rows
Select columns except those between two variables:
select(flights, -(year:day))## # A tibble: 336,776 x 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 ## # … with 336,766 more rows, and 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 by positions:
select(flights, seq(1, 10, by = 2))## # A tibble: 336,776 x 5 ## year day sched_dep_time arr_time arr_delay ## <int> <int> <int> <int> <dbl> ## 1 2013 1 515 830 11 ## 2 2013 1 529 850 20 ## 3 2013 1 540 923 33 ## 4 2013 1 545 1004 -18 ## 5 2013 1 600 812 -25 ## 6 2013 1 558 740 12 ## 7 2013 1 600 913 19 ## 8 2013 1 600 709 -14 ## 9 2013 1 600 838 -8 ## 10 2013 1 600 753 8 ## # … with 336,766 more rows
Move variables to the start of data frame:
select(flights, time_hour, air_time, everything())## # A tibble: 336,776 x 19 ## time_hour air_time year month day dep_time sched_dep_time ## <dttm> <dbl> <int> <int> <int> <int> <int> ## 1 2013-01-01 05:00:00 227 2013 1 1 517 515 ## 2 2013-01-01 05:00:00 227 2013 1 1 533 529 ## 3 2013-01-01 05:00:00 160 2013 1 1 542 540 ## 4 2013-01-01 05:00:00 183 2013 1 1 544 545 ## 5 2013-01-01 06:00:00 116 2013 1 1 554 600 ## 6 2013-01-01 05:00:00 150 2013 1 1 554 558 ## 7 2013-01-01 06:00:00 158 2013 1 1 555 600 ## 8 2013-01-01 06:00:00 53 2013 1 1 557 600 ## 9 2013-01-01 06:00:00 140 2013 1 1 557 600 ## 10 2013-01-01 06:00:00 138 2013 1 1 558 600 ## # … with 336,766 more rows, and 12 more variables: dep_delay <dbl>, ## # arr_time <int>, sched_arr_time <int>, arr_delay <dbl>, carrier <chr>, ## # flight <int>, tailnum <chr>, origin <chr>, dest <chr>, distance <dbl>, ## # hour <dbl>, minute <dbl>
Helper functions.
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”.matches("(.)\\1"): selects variables that match a regular expression.num_range("x", 1:3): matches x1, x2 and x3.one_of()
Add new variables with mutate()
Add variables
gainandspeed:flights_sml <- select(flights, year:day, ends_with("delay"), distance, air_time) flights_sml## # A tibble: 336,776 x 7 ## year month day dep_delay arr_delay distance air_time ## <int> <int> <int> <dbl> <dbl> <dbl> <dbl> ## 1 2013 1 1 2 11 1400 227 ## 2 2013 1 1 4 20 1416 227 ## 3 2013 1 1 2 33 1089 160 ## 4 2013 1 1 -1 -18 1576 183 ## 5 2013 1 1 -6 -25 762 116 ## 6 2013 1 1 -4 12 719 150 ## 7 2013 1 1 -5 19 1065 158 ## 8 2013 1 1 -3 -14 229 53 ## 9 2013 1 1 -3 -8 944 140 ## 10 2013 1 1 -2 8 733 138 ## # … with 336,766 more rowsmutate(flights_sml, gain = arr_delay - dep_delay, speed = distance / air_time * 60 )## # A tibble: 336,776 x 9 ## year month day dep_delay arr_delay distance air_time gain speed ## <int> <int> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> ## 1 2013 1 1 2 11 1400 227 9 370. ## 2 2013 1 1 4 20 1416 227 16 374. ## 3 2013 1 1 2 33 1089 160 31 408. ## 4 2013 1 1 -1 -18 1576 183 -17 517. ## 5 2013 1 1 -6 -25 762 116 -19 394. ## 6 2013 1 1 -4 12 719 150 16 288. ## 7 2013 1 1 -5 19 1065 158 24 404. ## 8 2013 1 1 -3 -14 229 53 -11 259. ## 9 2013 1 1 -3 -8 944 140 -5 405. ## 10 2013 1 1 -2 8 733 138 10 319. ## # … with 336,766 more rows
Refer to columns that you’ve just created:
mutate(flights_sml, gain = arr_delay - dep_delay, hours = air_time / 60, gain_per_hour = gain / hours )## # A tibble: 336,776 x 10 ## year month day dep_delay arr_delay distance air_time gain hours ## <int> <int> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> ## 1 2013 1 1 2 11 1400 227 9 3.78 ## 2 2013 1 1 4 20 1416 227 16 3.78 ## 3 2013 1 1 2 33 1089 160 31 2.67 ## 4 2013 1 1 -1 -18 1576 183 -17 3.05 ## 5 2013 1 1 -6 -25 762 116 -19 1.93 ## 6 2013 1 1 -4 12 719 150 16 2.5 ## 7 2013 1 1 -5 19 1065 158 24 2.63 ## 8 2013 1 1 -3 -14 229 53 -11 0.883 ## 9 2013 1 1 -3 -8 944 140 -5 2.33 ## 10 2013 1 1 -2 8 733 138 10 2.3 ## # … with 336,766 more rows, and 1 more variable: gain_per_hour <dbl>
Only keep the new variables by
transmute():transmute(flights, gain = arr_delay - dep_delay, hours = air_time / 60, gain_per_hour = gain / hours )## # A tibble: 336,776 x 3 ## gain hours gain_per_hour ## <dbl> <dbl> <dbl> ## 1 9 3.78 2.38 ## 2 16 3.78 4.23 ## 3 31 2.67 11.6 ## 4 -17 3.05 -5.57 ## 5 -19 1.93 -9.83 ## 6 16 2.5 6.4 ## 7 24 2.63 9.11 ## 8 -11 0.883 -12.5 ## 9 -5 2.33 -2.14 ## 10 10 2.3 4.35 ## # … with 336,766 more rows
mutate_all(): apply funs to all columns.mutate_all(data, funs(log(.), log2(.)))mutate_at(): apply funs to specifc columns.mutate_at(data, vars(-Species), funs(log(.)))mutate_if(): apply funs of one typemutate_if(data, is.numeric, funs(log(.)))
Summaries
Summaries with summarise()
Mean of a variable:
summarise(flights, delay = mean(dep_delay, na.rm = TRUE))## # A tibble: 1 x 1 ## delay ## <dbl> ## 1 12.6
Convert a tibble into a grouped tibble:
(by_day <- group_by(flights, year, month, day))## # A tibble: 336,776 x 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 ## # … with 336,766 more rows, and 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>
Grouped summaries:
summarise(by_day, delay = mean(dep_delay, na.rm = TRUE))## # A tibble: 365 x 4 ## # Groups: year, month [12] ## year month day delay ## <int> <int> <int> <dbl> ## 1 2013 1 1 11.5 ## 2 2013 1 2 13.9 ## 3 2013 1 3 11.0 ## 4 2013 1 4 8.95 ## 5 2013 1 5 5.73 ## 6 2013 1 6 7.15 ## 7 2013 1 7 5.42 ## 8 2013 1 8 2.55 ## 9 2013 1 9 2.28 ## 10 2013 1 10 2.84 ## # … with 355 more rows
Pipe
Consider following analysis:
by_dest <- group_by(flights, dest) delay <- summarise(by_dest, count = n(), dist = mean(distance, na.rm = TRUE), delay = mean(arr_delay, na.rm = TRUE) ) delay <- filter(delay, count > 20, dest != "HNL") delay## # A tibble: 96 x 4 ## dest count dist delay ## <chr> <int> <dbl> <dbl> ## 1 ABQ 254 1826 4.38 ## 2 ACK 265 199 4.85 ## 3 ALB 439 143 14.4 ## 4 ATL 17215 757. 11.3 ## 5 AUS 2439 1514. 6.02 ## 6 AVL 275 584. 8.00 ## 7 BDL 443 116 7.05 ## 8 BGR 375 378 8.03 ## 9 BHM 297 866. 16.9 ## 10 BNA 6333 758. 11.8 ## # … with 86 more rows
Cleaner code using pipe
%>%:delays <- flights %>% group_by(dest) %>% summarise( count = n(), dist = mean(distance, na.rm = TRUE), delay = mean(arr_delay, na.rm = TRUE) ) %>% filter(count > 20, dest != "HNL") delays## # A tibble: 96 x 4 ## dest count dist delay ## <chr> <int> <dbl> <dbl> ## 1 ABQ 254 1826 4.38 ## 2 ACK 265 199 4.85 ## 3 ALB 439 143 14.4 ## 4 ATL 17215 757. 11.3 ## 5 AUS 2439 1514. 6.02 ## 6 AVL 275 584. 8.00 ## 7 BDL 443 116 7.05 ## 8 BGR 375 378 8.03 ## 9 BHM 297 866. 16.9 ## 10 BNA 6333 758. 11.8 ## # … with 86 more rows
ggplot2 accepts pipe too.
delays %>% ggplot(mapping = aes(x = dist, y = delay)) + geom_point(aes(size = count), alpha = 1/3) + geom_smooth(se = FALSE)## `geom_smooth()` using method = 'loess' and formula 'y ~ x'
Other summary functions
Location:
mean(x),median(x).not_cancelled <- flights %>% filter(!is.na(dep_delay), !is.na(arr_delay)) not_cancelled## # A tibble: 327,346 x 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 ## # … with 327,336 more rows, and 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>not_cancelled %>% group_by(year, month, day) %>% summarise( avg_delay1 = mean(arr_delay), avg_delay2 = mean(arr_delay[arr_delay > 0]) # the average positive delay )## # A tibble: 365 x 5 ## # Groups: year, month [12] ## year month day avg_delay1 avg_delay2 ## <int> <int> <int> <dbl> <dbl> ## 1 2013 1 1 12.7 32.5 ## 2 2013 1 2 12.7 32.0 ## 3 2013 1 3 5.73 27.7 ## 4 2013 1 4 -1.93 28.3 ## 5 2013 1 5 -1.53 22.6 ## 6 2013 1 6 4.24 24.4 ## 7 2013 1 7 -4.95 27.8 ## 8 2013 1 8 -3.23 20.8 ## 9 2013 1 9 -0.264 25.6 ## 10 2013 1 10 -5.90 27.3 ## # … with 355 more rows
Spread:
sd(x),IQR(x),mad(x).not_cancelled %>% group_by(dest) %>% summarise(distance_sd = sd(distance)) %>% arrange(desc(distance_sd))## # A tibble: 104 x 2 ## dest distance_sd ## <chr> <dbl> ## 1 EGE 10.5 ## 2 SAN 10.4 ## 3 SFO 10.2 ## 4 HNL 10.0 ## 5 SEA 9.98 ## 6 LAS 9.91 ## 7 PDX 9.87 ## 8 PHX 9.86 ## 9 LAX 9.66 ## 10 IND 9.46 ## # … with 94 more rows
Rank:
min(x),quantile(x, 0.25),max(x).# When do the first and last flights leave each day? not_cancelled %>% group_by(year, month, day) %>% summarise( first = min(dep_time), last = max(dep_time) )## # A tibble: 365 x 5 ## # Groups: year, month [12] ## year month day first last ## <int> <int> <int> <int> <int> ## 1 2013 1 1 517 2356 ## 2 2013 1 2 42 2354 ## 3 2013 1 3 32 2349 ## 4 2013 1 4 25 2358 ## 5 2013 1 5 14 2357 ## 6 2013 1 6 16 2355 ## 7 2013 1 7 49 2359 ## 8 2013 1 8 454 2351 ## 9 2013 1 9 2 2252 ## 10 2013 1 10 3 2320 ## # … with 355 more rows
Position:
first(x),nth(x, 2),last(x).not_cancelled %>% group_by(year, month, day) %>% summarise( first_dep = first(dep_time), last_dep = last(dep_time) )## # A tibble: 365 x 5 ## # Groups: year, month [12] ## year month day first_dep last_dep ## <int> <int> <int> <int> <int> ## 1 2013 1 1 517 2356 ## 2 2013 1 2 42 2354 ## 3 2013 1 3 32 2349 ## 4 2013 1 4 25 2358 ## 5 2013 1 5 14 2357 ## 6 2013 1 6 16 2355 ## 7 2013 1 7 49 2359 ## 8 2013 1 8 454 2351 ## 9 2013 1 9 2 2252 ## 10 2013 1 10 3 2320 ## # … with 355 more rows
Count:
n(x),sum(!is.na(x)),n_distinct(x).# Which destinations have the most carriers? not_cancelled %>% group_by(dest) %>% summarise(carriers = n_distinct(carrier)) %>% arrange(desc(carriers))## # A tibble: 104 x 2 ## dest carriers ## <chr> <int> ## 1 ATL 7 ## 2 BOS 7 ## 3 CLT 7 ## 4 ORD 7 ## 5 TPA 7 ## 6 AUS 6 ## 7 DCA 6 ## 8 DTW 6 ## 9 IAD 6 ## 10 MSP 6 ## # … with 94 more rows
not_cancelled %>% count(dest)## # A tibble: 104 x 2 ## dest n ## <chr> <int> ## 1 ABQ 254 ## 2 ACK 264 ## 3 ALB 418 ## 4 ANC 8 ## 5 ATL 16837 ## 6 AUS 2411 ## 7 AVL 261 ## 8 BDL 412 ## 9 BGR 358 ## 10 BHM 269 ## # … with 94 more rows
not_cancelled %>% count(tailnum, wt = distance)## # A tibble: 4,037 x 2 ## tailnum n ## <chr> <dbl> ## 1 D942DN 3418 ## 2 N0EGMQ 239143 ## 3 N10156 109664 ## 4 N102UW 25722 ## 5 N103US 24619 ## 6 N104UW 24616 ## 7 N10575 139903 ## 8 N105UW 23618 ## 9 N107US 21677 ## 10 N108UW 32070 ## # … with 4,027 more rows
# How many flights left before 5am? (these usually indicate delayed # flights from the previous day) not_cancelled %>% group_by(year, month, day) %>% summarise(n_early = sum(dep_time < 500))## # A tibble: 365 x 4 ## # Groups: year, month [12] ## year month day n_early ## <int> <int> <int> <int> ## 1 2013 1 1 0 ## 2 2013 1 2 3 ## 3 2013 1 3 4 ## 4 2013 1 4 3 ## 5 2013 1 5 3 ## 6 2013 1 6 2 ## 7 2013 1 7 2 ## 8 2013 1 8 1 ## 9 2013 1 9 3 ## 10 2013 1 10 3 ## # … with 355 more rows
# What proportion of flights are delayed by more than an hour? not_cancelled %>% group_by(year, month, day) %>% summarise(hour_perc = mean(arr_delay > 60))## # A tibble: 365 x 4 ## # Groups: year, month [12] ## year month day hour_perc ## <int> <int> <int> <dbl> ## 1 2013 1 1 0.0722 ## 2 2013 1 2 0.0851 ## 3 2013 1 3 0.0567 ## 4 2013 1 4 0.0396 ## 5 2013 1 5 0.0349 ## 6 2013 1 6 0.0470 ## 7 2013 1 7 0.0333 ## 8 2013 1 8 0.0213 ## 9 2013 1 9 0.0202 ## 10 2013 1 10 0.0183 ## # … with 355 more rows
Grouped mutates (and filters)
Recall the
flights_smltibble created earlier:flights_sml## # A tibble: 336,776 x 7 ## year month day dep_delay arr_delay distance air_time ## <int> <int> <int> <dbl> <dbl> <dbl> <dbl> ## 1 2013 1 1 2 11 1400 227 ## 2 2013 1 1 4 20 1416 227 ## 3 2013 1 1 2 33 1089 160 ## 4 2013 1 1 -1 -18 1576 183 ## 5 2013 1 1 -6 -25 762 116 ## 6 2013 1 1 -4 12 719 150 ## 7 2013 1 1 -5 19 1065 158 ## 8 2013 1 1 -3 -14 229 53 ## 9 2013 1 1 -3 -8 944 140 ## 10 2013 1 1 -2 8 733 138 ## # … with 336,766 more rowsFind the worst members of each group:
flights_sml %>% group_by(year, month, day) %>% filter(rank(desc(arr_delay)) < 10)## # A tibble: 3,306 x 7 ## # Groups: year, month, day [365] ## year month day dep_delay arr_delay distance air_time ## <int> <int> <int> <dbl> <dbl> <dbl> <dbl> ## 1 2013 1 1 853 851 184 41 ## 2 2013 1 1 290 338 1134 213 ## 3 2013 1 1 260 263 266 46 ## 4 2013 1 1 157 174 213 60 ## 5 2013 1 1 216 222 708 121 ## 6 2013 1 1 255 250 589 115 ## 7 2013 1 1 285 246 1085 146 ## 8 2013 1 1 192 191 199 44 ## 9 2013 1 1 379 456 1092 222 ## 10 2013 1 2 224 207 550 94 ## # … with 3,296 more rows
Find all groups bigger than a threshold:
(popular_dests <- flights %>% group_by(dest) %>% filter(n() > 365))## # A tibble: 332,577 x 19 ## # Groups: dest [77] ## 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 ## # … with 332,567 more rows, and 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>
Standardise to compute per group metrics:
popular_dests %>% filter(arr_delay > 0) %>% mutate(prop_delay = arr_delay / sum(arr_delay)) %>% select(year:day, dest, arr_delay, prop_delay)## # A tibble: 131,106 x 6 ## # Groups: dest [77] ## year month day dest arr_delay prop_delay ## <int> <int> <int> <chr> <dbl> <dbl> ## 1 2013 1 1 IAH 11 0.000111 ## 2 2013 1 1 IAH 20 0.000201 ## 3 2013 1 1 MIA 33 0.000235 ## 4 2013 1 1 ORD 12 0.0000424 ## 5 2013 1 1 FLL 19 0.0000938 ## 6 2013 1 1 ORD 8 0.0000283 ## 7 2013 1 1 LAX 7 0.0000344 ## 8 2013 1 1 DFW 31 0.000282 ## 9 2013 1 1 ATL 12 0.0000400 ## 10 2013 1 1 DTW 16 0.000116 ## # … with 131,096 more rows
Combine tables
nycflights13 package has >1 tables:
We already know a lot about flights:
flights %>% print(width = Inf)## # A tibble: 336,776 x 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 ## arr_delay carrier flight tailnum origin dest air_time distance hour minute ## <dbl> <chr> <int> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> ## 1 11 UA 1545 N14228 EWR IAH 227 1400 5 15 ## 2 20 UA 1714 N24211 LGA IAH 227 1416 5 29 ## 3 33 AA 1141 N619AA JFK MIA 160 1089 5 40 ## 4 -18 B6 725 N804JB JFK BQN 183 1576 5 45 ## 5 -25 DL 461 N668DN LGA ATL 116 762 6 0 ## 6 12 UA 1696 N39463 EWR ORD 150 719 5 58 ## 7 19 B6 507 N516JB EWR FLL 158 1065 6 0 ## 8 -14 EV 5708 N829AS LGA IAD 53 229 6 0 ## 9 -8 B6 79 N593JB JFK MCO 140 944 6 0 ## 10 8 AA 301 N3ALAA LGA ORD 138 733 6 0 ## time_hour ## <dttm> ## 1 2013-01-01 05:00:00 ## 2 2013-01-01 05:00:00 ## 3 2013-01-01 05:00:00 ## 4 2013-01-01 05:00:00 ## 5 2013-01-01 06:00:00 ## 6 2013-01-01 05:00:00 ## 7 2013-01-01 06:00:00 ## 8 2013-01-01 06:00:00 ## 9 2013-01-01 06:00:00 ## 10 2013-01-01 06:00:00 ## # … with 336,766 more rows
airlines:
airlines## # A tibble: 16 x 2 ## carrier name ## <chr> <chr> ## 1 9E Endeavor Air Inc. ## 2 AA American Airlines Inc. ## 3 AS Alaska Airlines Inc. ## 4 B6 JetBlue Airways ## 5 DL Delta Air Lines Inc. ## 6 EV ExpressJet Airlines Inc. ## 7 F9 Frontier Airlines Inc. ## 8 FL AirTran Airways Corporation ## 9 HA Hawaiian Airlines Inc. ## 10 MQ Envoy Air ## 11 OO SkyWest Airlines Inc. ## 12 UA United Air Lines Inc. ## 13 US US Airways Inc. ## 14 VX Virgin America ## 15 WN Southwest Airlines Co. ## 16 YV Mesa Airlines Inc.
airports:
airports## # A tibble: 1,458 x 8 ## faa name lat lon alt tz dst tzone ## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <chr> <chr> ## 1 04G Lansdowne Airport 41.1 -80.6 1044 -5 A America/New_Yo… ## 2 06A Moton Field Municipal A… 32.5 -85.7 264 -6 A America/Chicago ## 3 06C Schaumburg Regional 42.0 -88.1 801 -6 A America/Chicago ## 4 06N Randall Airport 41.4 -74.4 523 -5 A America/New_Yo… ## 5 09J Jekyll Island Airport 31.1 -81.4 11 -5 A America/New_Yo… ## 6 0A9 Elizabethton Municipal … 36.4 -82.2 1593 -5 A America/New_Yo… ## 7 0G6 Williams County Airport 41.5 -84.5 730 -5 A America/New_Yo… ## 8 0G7 Finger Lakes Regional A… 42.9 -76.8 492 -5 A America/New_Yo… ## 9 0P2 Shoestring Aviation Air… 39.8 -76.6 1000 -5 U America/New_Yo… ## 10 0S9 Jefferson County Intl 48.1 -123. 108 -8 A America/Los_An… ## # … with 1,448 more rows
planes:
planes## # A tibble: 3,322 x 9 ## tailnum year type manufacturer model engines seats speed engine ## <chr> <int> <chr> <chr> <chr> <int> <int> <int> <chr> ## 1 N10156 2004 Fixed wing m… EMBRAER EMB-1… 2 55 NA Turbo-… ## 2 N102UW 1998 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 3 N103US 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 4 N104UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 5 N10575 2002 Fixed wing m… EMBRAER EMB-1… 2 55 NA Turbo-… ## 6 N105UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 7 N107US 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 8 N108UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 9 N109UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## 10 N110UW 1999 Fixed wing m… AIRBUS INDUST… A320-… 2 182 NA Turbo-… ## # … with 3,312 more rows
Weather:
weather## # A tibble: 26,115 x 15 ## origin year month day hour temp dewp humid wind_dir wind_speed ## <chr> <int> <int> <int> <int> <dbl> <dbl> <dbl> <dbl> <dbl> ## 1 EWR 2013 1 1 1 39.0 26.1 59.4 270 10.4 ## 2 EWR 2013 1 1 2 39.0 27.0 61.6 250 8.06 ## 3 EWR 2013 1 1 3 39.0 28.0 64.4 240 11.5 ## 4 EWR 2013 1 1 4 39.9 28.0 62.2 250 12.7 ## 5 EWR 2013 1 1 5 39.0 28.0 64.4 260 12.7 ## 6 EWR 2013 1 1 6 37.9 28.0 67.2 240 11.5 ## 7 EWR 2013 1 1 7 39.0 28.0 64.4 240 15.0 ## 8 EWR 2013 1 1 8 39.9 28.0 62.2 250 10.4 ## 9 EWR 2013 1 1 9 39.9 28.0 62.2 260 15.0 ## 10 EWR 2013 1 1 10 41 28.0 59.6 260 13.8 ## # … with 26,105 more rows, and 5 more variables: wind_gust <dbl>, precip <dbl>, ## # pressure <dbl>, visib <dbl>, time_hour <dttm>
Relational data
For the MIMIC-III data, the relation structure can be explored at https://mit-lcp.github.io/mimic-schema-spy/.
Keys
A primary key uniquely identifies an observation in its own table.
A foreign key uniquely identifies an observation in another table.
Combine variables (columns)
Demo tables
(x <- tribble( ~key, ~val_x, 1, "x1", 2, "x2", 3, "x3" ))## # A tibble: 3 x 2 ## key val_x ## <dbl> <chr> ## 1 1 x1 ## 2 2 x2 ## 3 3 x3(y <- tribble( ~key, ~val_y, 1, "y1", 2, "y2", 4, "y3" ))## # A tibble: 3 x 2 ## key val_y ## <dbl> <chr> ## 1 1 y1 ## 2 2 y2 ## 3 4 y3
Inner join
- An inner join matches pairs of observations whenever their keys are equal:
inner_join(x, y, by = "key")## # A tibble: 2 x 3 ## key val_x val_y ## <dbl> <chr> <chr> ## 1 1 x1 y1 ## 2 2 x2 y2Same as
x %>% inner_join(y, by = "key")
Outer join
An outer join keeps observations that appear in at least one of the tables.
Three types of outer joins:
- A left join keeps all observations in
x.
left_join(x, y, by = "key")## # A tibble: 3 x 3 ## key val_x val_y ## <dbl> <chr> <chr> ## 1 1 x1 y1 ## 2 2 x2 y2 ## 3 3 x3 <NA>- A right join keeps all observations in
y.
right_join(x, y, by = "key")## # A tibble: 3 x 3 ## key val_x val_y ## <dbl> <chr> <chr> ## 1 1 x1 y1 ## 2 2 x2 y2 ## 3 4 <NA> y3- A full join keeps all observations in
xory.
full_join(x, y, by = "key")## # A tibble: 4 x 3 ## key val_x val_y ## <dbl> <chr> <chr> ## 1 1 x1 y1 ## 2 2 x2 y2 ## 3 3 x3 <NA> ## 4 4 <NA> y3
- A left join keeps all observations in
Duplicate keys
One table has duplicate keys.
x <- tribble( ~key, ~val_x, 1, "x1", 2, "x2", 2, "x3", 1, "x4" ) y <- tribble( ~key, ~val_y, 1, "y1", 2, "y2" ) left_join(x, y, by = "key")## # A tibble: 4 x 3 ## key val_x val_y ## <dbl> <chr> <chr> ## 1 1 x1 y1 ## 2 2 x2 y2 ## 3 2 x3 y2 ## 4 1 x4 y1
Both tables have duplicate keys. You get all possible combinations, the Cartesian product:
x <- tribble( ~key, ~val_x, 1, "x1", 2, "x2", 2, "x3", 3, "x4" ) y <- tribble( ~key, ~val_y, 1, "y1", 2, "y2", 2, "y3", 3, "y4" ) left_join(x, y, by = "key")## # A tibble: 6 x 3 ## key val_x val_y ## <dbl> <chr> <chr> ## 1 1 x1 y1 ## 2 2 x2 y2 ## 3 2 x2 y3 ## 4 2 x3 y2 ## 5 2 x3 y3 ## 6 3 x4 y4
Let’s create a narrower table from the flights data:
flights2 <- flights %>% select(year:day, hour, origin, dest, tailnum, carrier) flights2## # A tibble: 336,776 x 8 ## year month day hour origin dest tailnum carrier ## <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> ## 1 2013 1 1 5 EWR IAH N14228 UA ## 2 2013 1 1 5 LGA IAH N24211 UA ## 3 2013 1 1 5 JFK MIA N619AA AA ## 4 2013 1 1 5 JFK BQN N804JB B6 ## 5 2013 1 1 6 LGA ATL N668DN DL ## 6 2013 1 1 5 EWR ORD N39463 UA ## 7 2013 1 1 6 EWR FLL N516JB B6 ## 8 2013 1 1 6 LGA IAD N829AS EV ## 9 2013 1 1 6 JFK MCO N593JB B6 ## 10 2013 1 1 6 LGA ORD N3ALAA AA ## # … with 336,766 more rowsWe want to merge with the
weathertable:weather## # A tibble: 26,115 x 15 ## origin year month day hour temp dewp humid wind_dir wind_speed ## <chr> <int> <int> <int> <int> <dbl> <dbl> <dbl> <dbl> <dbl> ## 1 EWR 2013 1 1 1 39.0 26.1 59.4 270 10.4 ## 2 EWR 2013 1 1 2 39.0 27.0 61.6 250 8.06 ## 3 EWR 2013 1 1 3 39.0 28.0 64.4 240 11.5 ## 4 EWR 2013 1 1 4 39.9 28.0 62.2 250 12.7 ## 5 EWR 2013 1 1 5 39.0 28.0 64.4 260 12.7 ## 6 EWR 2013 1 1 6 37.9 28.0 67.2 240 11.5 ## 7 EWR 2013 1 1 7 39.0 28.0 64.4 240 15.0 ## 8 EWR 2013 1 1 8 39.9 28.0 62.2 250 10.4 ## 9 EWR 2013 1 1 9 39.9 28.0 62.2 260 15.0 ## 10 EWR 2013 1 1 10 41 28.0 59.6 260 13.8 ## # … with 26,105 more rows, and 5 more variables: wind_gust <dbl>, precip <dbl>, ## # pressure <dbl>, visib <dbl>, time_hour <dttm>
Defining the key columns
by = NULL(default): use all variables that appear in both tables:# same as: flights2 %>% left_join(weather) left_join(flights2, weather)## Joining, by = c("year", "month", "day", "hour", "origin")## # A tibble: 336,776 x 18 ## year month day hour origin dest tailnum carrier temp dewp humid ## <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> ## 1 2013 1 1 5 EWR IAH N14228 UA 39.0 28.0 64.4 ## 2 2013 1 1 5 LGA IAH N24211 UA 39.9 25.0 54.8 ## 3 2013 1 1 5 JFK MIA N619AA AA 39.0 27.0 61.6 ## 4 2013 1 1 5 JFK BQN N804JB B6 39.0 27.0 61.6 ## 5 2013 1 1 6 LGA ATL N668DN DL 39.9 25.0 54.8 ## 6 2013 1 1 5 EWR ORD N39463 UA 39.0 28.0 64.4 ## 7 2013 1 1 6 EWR FLL N516JB B6 37.9 28.0 67.2 ## 8 2013 1 1 6 LGA IAD N829AS EV 39.9 25.0 54.8 ## 9 2013 1 1 6 JFK MCO N593JB B6 37.9 27.0 64.3 ## 10 2013 1 1 6 LGA ORD N3ALAA AA 39.9 25.0 54.8 ## # … with 336,766 more rows, and 7 more variables: wind_dir <dbl>, ## # wind_speed <dbl>, wind_gust <dbl>, precip <dbl>, pressure <dbl>, ## # visib <dbl>, time_hour <dttm>
by = "x": use the common variablex:# same as: flights2 %>% left_join(weather) left_join(flights2, planes, by = "tailnum")## # A tibble: 336,776 x 16 ## year.x month day hour origin dest tailnum carrier year.y type ## <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <int> <chr> ## 1 2013 1 1 5 EWR IAH N14228 UA 1999 Fixe… ## 2 2013 1 1 5 LGA IAH N24211 UA 1998 Fixe… ## 3 2013 1 1 5 JFK MIA N619AA AA 1990 Fixe… ## 4 2013 1 1 5 JFK BQN N804JB B6 2012 Fixe… ## 5 2013 1 1 6 LGA ATL N668DN DL 1991 Fixe… ## 6 2013 1 1 5 EWR ORD N39463 UA 2012 Fixe… ## 7 2013 1 1 6 EWR FLL N516JB B6 2000 Fixe… ## 8 2013 1 1 6 LGA IAD N829AS EV 1998 Fixe… ## 9 2013 1 1 6 JFK MCO N593JB B6 2004 Fixe… ## 10 2013 1 1 6 LGA ORD N3ALAA AA NA <NA> ## # … with 336,766 more rows, and 6 more variables: manufacturer <chr>, ## # model <chr>, engines <int>, seats <int>, speed <int>, engine <chr>
by = c("a" = "b"): match variableain tablexto the variablebin tabley.# same as: flights2 %>% left_join(weather) left_join(flights2, airports, by = c("dest" = "faa"))## # A tibble: 336,776 x 15 ## year month day hour origin dest tailnum carrier name lat lon alt ## <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> ## 1 2013 1 1 5 EWR IAH N14228 UA Geor… 30.0 -95.3 97 ## 2 2013 1 1 5 LGA IAH N24211 UA Geor… 30.0 -95.3 97 ## 3 2013 1 1 5 JFK MIA N619AA AA Miam… 25.8 -80.3 8 ## 4 2013 1 1 5 JFK BQN N804JB B6 <NA> NA NA NA ## 5 2013 1 1 6 LGA ATL N668DN DL Hart… 33.6 -84.4 1026 ## 6 2013 1 1 5 EWR ORD N39463 UA Chic… 42.0 -87.9 668 ## 7 2013 1 1 6 EWR FLL N516JB B6 Fort… 26.1 -80.2 9 ## 8 2013 1 1 6 LGA IAD N829AS EV Wash… 38.9 -77.5 313 ## 9 2013 1 1 6 JFK MCO N593JB B6 Orla… 28.4 -81.3 96 ## 10 2013 1 1 6 LGA ORD N3ALAA AA Chic… 42.0 -87.9 668 ## # … with 336,766 more rows, and 3 more variables: tz <dbl>, dst <chr>, ## # tzone <chr>
Combine cases (rows)
Top 10 most popular destinations:
top_dest <- flights %>% count(dest, sort = TRUE) %>% head(10) top_dest## # A tibble: 10 x 2 ## dest n ## <chr> <int> ## 1 ORD 17283 ## 2 ATL 17215 ## 3 LAX 16174 ## 4 BOS 15508 ## 5 MCO 14082 ## 6 CLT 14064 ## 7 SFO 13331 ## 8 FLL 12055 ## 9 MIA 11728 ## 10 DCA 9705How to filter the cases that fly to these destinations?
Semi-join
semi_join(x, y)keesp the rows inxthat have a match iny.
Useful to see what will be joined.
semi_join(flights, top_dest)## Joining, by = "dest"## # A tibble: 141,145 x 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 554 600 -6 812 837 ## 3 2013 1 1 554 558 -4 740 728 ## 4 2013 1 1 555 600 -5 913 854 ## 5 2013 1 1 557 600 -3 838 846 ## 6 2013 1 1 558 600 -2 753 745 ## 7 2013 1 1 558 600 -2 924 917 ## 8 2013 1 1 558 600 -2 923 937 ## 9 2013 1 1 559 559 0 702 706 ## 10 2013 1 1 600 600 0 851 858 ## # … with 141,135 more rows, and 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>
Anti-join
anti_join(x, y)keeps the rows that don’t have a match.
Useful to see what will not be joined.
flights %>% anti_join(planes, by = "tailnum") %>% count(tailnum, sort = TRUE)## # A tibble: 722 x 2 ## tailnum n ## <chr> <int> ## 1 <NA> 2512 ## 2 N725MQ 575 ## 3 N722MQ 513 ## 4 N723MQ 507 ## 5 N713MQ 483 ## 6 N735MQ 396 ## 7 N0EGMQ 371 ## 8 N534MQ 364 ## 9 N542MQ 363 ## 10 N531MQ 349 ## # … with 712 more rows
Set operations
Generate two tables:
(df1 <- tribble( ~x, ~y, 1, 1, 2, 1 ))## # A tibble: 2 x 2 ## x y ## <dbl> <dbl> ## 1 1 1 ## 2 2 1(df2 <- tribble( ~x, ~y, 1, 1, 1, 2 ))## # A tibble: 2 x 2 ## x y ## <dbl> <dbl> ## 1 1 1 ## 2 1 2
bind_rows(x, y)stacks tablexone on top ofy.bind_rows(df1, df2)## # A tibble: 4 x 2 ## x y ## <dbl> <dbl> ## 1 1 1 ## 2 2 1 ## 3 1 1 ## 4 1 2intersect(x, y)returns rows that appear in bothxandy.intersect(df1, df2)## # A tibble: 1 x 2 ## x y ## <dbl> <dbl> ## 1 1 1
union(x, y)returns unique observations inxandy.union(df1, df2)## # A tibble: 3 x 2 ## x y ## <dbl> <dbl> ## 1 1 1 ## 2 2 1 ## 3 1 2
setdiff(x, y)returns rows that appear inxbut not iny.setdiff(df1, df2)## # A tibble: 1 x 2 ## x y ## <dbl> <dbl> ## 1 2 1setdiff(df2, df1)## # A tibble: 1 x 2 ## x y ## <dbl> <dbl> ## 1 1 2
Cheat sheet
RStudio cheat sheet is extremely helpful.