Data transformation¶

Introduction¶

Visualisation is an important tool for insight generation, but it is rare that you get the data in exactly the right form you need. Often you'll need to create some new variables or summaries, or maybe you just want to rename the variables or reorder the observations in order to make the data a little easier to work with. You'll learn how to do all that (and more!) in this chapter, which will teach you how to transform your data using the dplyr package and a new dataset on flights departing New York City in 2013.

Prerequisites¶

In this chapter we're going to focus on how to use the dplyr package, another core member of the tidyverse. We'll illustrate the key ideas using data from the nycflights13 package, and use ggplot2 to help us understand the data.

In [5]:

install.packages("nycflights13")
library(nycflights13)
library(tidyverse)

install.packages("nycflights13") library(nycflights13) library(tidyverse)

Installing package into ‘/usr/local/lib/R/site-library’
(as ‘lib’ is unspecified)

Take careful note of the conflicts message that's printed when you load the tidyverse. It tells you that dplyr overwrites some functions in base R. If you want to use the base version of these functions after loading dplyr, you'll need to use their full names: stats::filter() and stats::lag().

nycflights13¶

To explore the basic data manipulation verbs of dplyr, we'll use nycflights13::flights. This data frame contains all r format(nrow(nycflights13::flights), big.mark = ",") flights that departed from New York City in 2013. The data comes from the US Bureau of Transportation Statistics, and is documented in ?flights.

In [6]:

flights

flights

A tibble: 336776 × 19

year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	1	517	515	2	830	819	11	UA	1545	N14228	EWR	IAH	227	1400	5	15	2013-01-01 05:00:00
2013	1	1	533	529	4	850	830	20	UA	1714	N24211	LGA	IAH	227	1416	5	29	2013-01-01 05:00:00
2013	1	1	542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	160	1089	5	40	2013-01-01 05:00:00
2013	1	1	544	545	-1	1004	1022	-18	B6	725	N804JB	JFK	BQN	183	1576	5	45	2013-01-01 05:00:00
2013	1	1	554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	116	762	6	0	2013-01-01 06:00:00
2013	1	1	554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	150	719	5	58	2013-01-01 05:00:00
2013	1	1	555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	158	1065	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	709	723	-14	EV	5708	N829AS	LGA	IAD	53	229	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	140	944	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	138	733	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	849	851	-2	B6	49	N793JB	JFK	PBI	149	1028	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	853	856	-3	B6	71	N657JB	JFK	TPA	158	1005	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	345	2475	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	361	2565	6	0	2013-01-01 06:00:00
2013	1	1	559	600	-1	941	910	31	AA	707	N3DUAA	LGA	DFW	257	1389	6	0	2013-01-01 06:00:00
2013	1	1	559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	44	187	5	59	2013-01-01 05:00:00
2013	1	1	559	600	-1	854	902	-8	UA	1187	N76515	EWR	LAS	337	2227	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	152	1076	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	134	762	6	0	2013-01-01 06:00:00
2013	1	1	601	600	1	844	850	-6	B6	343	N644JB	EWR	PBI	147	1023	6	0	2013-01-01 06:00:00
2013	1	1	602	610	-8	812	820	-8	DL	1919	N971DL	LGA	MSP	170	1020	6	10	2013-01-01 06:00:00
2013	1	1	602	605	-3	821	805	16	MQ	4401	N730MQ	LGA	DTW	105	502	6	5	2013-01-01 06:00:00
2013	1	1	606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	152	1085	6	10	2013-01-01 06:00:00
2013	1	1	606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	128	760	6	10	2013-01-01 06:00:00
2013	1	1	607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	157	1085	6	7	2013-01-01 06:00:00
2013	1	1	608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	139	719	6	0	2013-01-01 06:00:00
2013	1	1	611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	366	2586	6	0	2013-01-01 06:00:00
2013	1	1	613	610	3	925	921	4	B6	135	N635JB	JFK	RSW	175	1074	6	10	2013-01-01 06:00:00
2013	1	1	615	615	0	1039	1100	-21	B6	709	N794JB	JFK	SJU	182	1598	6	15	2013-01-01 06:00:00
2013	1	1	615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	120	746	6	15	2013-01-01 06:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	2123	2125	-2	2223	2247	-24	EV	5489	N712EV	LGA	CHO	45	305	21	25	2013-09-30 21:00:00
2013	9	30	2127	2129	-2	2314	2323	-9	EV	3833	N16546	EWR	CLT	72	529	21	29	2013-09-30 21:00:00
2013	9	30	2128	2130	-2	2328	2359	-31	B6	97	N807JB	JFK	DEN	213	1626	21	30	2013-09-30 21:00:00
2013	9	30	2129	2059	30	2230	2232	-2	EV	5048	N751EV	LGA	RIC	45	292	20	59	2013-09-30 20:00:00
2013	9	30	2131	2140	-9	2225	2255	-30	MQ	3621	N807MQ	JFK	DCA	36	213	21	40	2013-09-30 21:00:00
2013	9	30	2140	2140	0	10	40	-30	AA	185	N335AA	JFK	LAX	298	2475	21	40	2013-09-30 21:00:00
2013	9	30	2142	2129	13	2250	2239	11	EV	4509	N12957	EWR	PWM	47	284	21	29	2013-09-30 21:00:00
2013	9	30	2145	2145	0	115	140	-25	B6	1103	N633JB	JFK	SJU	192	1598	21	45	2013-09-30 21:00:00
2013	9	30	2147	2137	10	30	27	3	B6	1371	N627JB	LGA	FLL	139	1076	21	37	2013-09-30 21:00:00
2013	9	30	2149	2156	-7	2245	2308	-23	UA	523	N813UA	EWR	BOS	37	200	21	56	2013-09-30 21:00:00
2013	9	30	2150	2159	-9	2250	2306	-16	EV	3842	N10575	EWR	MHT	39	209	21	59	2013-09-30 21:00:00
2013	9	30	2159	1845	194	2344	2030	194	9E	3320	N906XJ	JFK	BUF	50	301	18	45	2013-09-30 18:00:00
2013	9	30	2203	2205	-2	2339	2331	8	EV	5311	N722EV	LGA	BGR	61	378	22	5	2013-09-30 22:00:00
2013	9	30	2207	2140	27	2257	2250	7	MQ	3660	N532MQ	LGA	BNA	97	764	21	40	2013-09-30 21:00:00
2013	9	30	2211	2059	72	2339	2242	57	EV	4672	N12145	EWR	STL	120	872	20	59	2013-09-30 20:00:00
2013	9	30	2231	2245	-14	2335	2356	-21	B6	108	N193JB	JFK	PWM	48	273	22	45	2013-09-30 22:00:00
2013	9	30	2233	2113	80	112	30	42	UA	471	N578UA	EWR	SFO	318	2565	21	13	2013-09-30 21:00:00
2013	9	30	2235	2001	154	59	2249	130	B6	1083	N804JB	JFK	MCO	123	944	20	1	2013-09-30 20:00:00
2013	9	30	2237	2245	-8	2345	2353	-8	B6	234	N318JB	JFK	BTV	43	266	22	45	2013-09-30 22:00:00
2013	9	30	2240	2245	-5	2334	2351	-17	B6	1816	N354JB	JFK	SYR	41	209	22	45	2013-09-30 22:00:00
2013	9	30	2240	2250	-10	2347	7	-20	B6	2002	N281JB	JFK	BUF	52	301	22	50	2013-09-30 22:00:00
2013	9	30	2241	2246	-5	2345	1	-16	B6	486	N346JB	JFK	ROC	47	264	22	46	2013-09-30 22:00:00
2013	9	30	2307	2255	12	2359	2358	1	B6	718	N565JB	JFK	BOS	33	187	22	55	2013-09-30 22:00:00
2013	9	30	2349	2359	-10	325	350	-25	B6	745	N516JB	JFK	PSE	196	1617	23	59	2013-09-30 23:00:00
2013	9	30	NA	1842	NA	NA	2019	NA	EV	5274	N740EV	LGA	BNA	NA	764	18	42	2013-09-30 18:00:00
2013	9	30	NA	1455	NA	NA	1634	NA	9E	3393	NA	JFK	DCA	NA	213	14	55	2013-09-30 14:00:00
2013	9	30	NA	2200	NA	NA	2312	NA	9E	3525	NA	LGA	SYR	NA	198	22	0	2013-09-30 22:00:00
2013	9	30	NA	1210	NA	NA	1330	NA	MQ	3461	N535MQ	LGA	BNA	NA	764	12	10	2013-09-30 12:00:00
2013	9	30	NA	1159	NA	NA	1344	NA	MQ	3572	N511MQ	LGA	CLE	NA	419	11	59	2013-09-30 11:00:00
2013	9	30	NA	840	NA	NA	1020	NA	MQ	3531	N839MQ	LGA	RDU	NA	431	8	40	2013-09-30 08:00:00

You might notice that this data frame prints a little differently from other data frames you might have used in the past: it only shows the first few rows and all the columns that fit on one screen. It also displays the number of rows (r format(nrow(nycflights13::flights), big.mark = ",")) and columns (r ncol(nycflights13::flights)). (To see the whole dataset, you can run View(flights) which will open the dataset in the RStudio viewer). It prints differently because it's a tibble. Tibbles are data frames, but slightly tweaked to work better in the tidyverse. For now, you don’t need to worry about the differences; we’ll come back to tibbles in more detail in wrangle.

You might also have noticed the row of three (or four) letter abbreviations under the column names. These describe the type of each variable:

• int stands for integers.

• dbl stands for doubles, or real numbers.

• chr stands for characters, or strings.

• dttm stands for date-times (a date + a time).

There are three other common types of variables that aren't used in this dataset but you'll encounter later in the book:

• lgl stands for logical, vectors that contain only TRUE or FALSE.

• fctr stands for factors, which R uses to represent categorical variables with fixed possible values.

• date stands for dates.

dplyr basics¶

In this chapter you are going to learn the five key dplyr functions that allow you to solve the vast majority of your data manipulation challenges:

• Pick observations by their values (filter()).
• Reorder the rows (arrange()).
• Pick variables by their names (select()).
• Create new variables with functions of existing variables (mutate()).
• Collapse many values down to a single summary (summarise()).

These can all be used in conjunction with group_by() which changes the scope of each function from operating on the entire dataset to operating on it group-by-group. These six functions provide the verbs for a language of data manipulation.

All verbs work similarly:

1. The first argument is a data frame.

2. The subsequent arguments describe what to do with the data frame, using the variable names (without quotes).

3. The result is a new data frame.

Together these properties make it easy to chain together multiple simple steps to achieve a complex result. Let's dive in and see how these verbs work.

Filter rows with filter()¶

filter() allows you to subset observations based on their values. The first argument is the name of the data frame. The second and subsequent arguments are the expressions that filter the data frame. For example, we can select all flights on January 1st with:

In [7]:

filter(flights, month == 1, day == 1)

filter(flights, month == 1, day == 1)

A tibble: 842 × 19

year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	1	517	515	2	830	819	11	UA	1545	N14228	EWR	IAH	227	1400	5	15	2013-01-01 05:00:00
2013	1	1	533	529	4	850	830	20	UA	1714	N24211	LGA	IAH	227	1416	5	29	2013-01-01 05:00:00
2013	1	1	542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	160	1089	5	40	2013-01-01 05:00:00
2013	1	1	544	545	-1	1004	1022	-18	B6	725	N804JB	JFK	BQN	183	1576	5	45	2013-01-01 05:00:00
2013	1	1	554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	116	762	6	0	2013-01-01 06:00:00
2013	1	1	554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	150	719	5	58	2013-01-01 05:00:00
2013	1	1	555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	158	1065	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	709	723	-14	EV	5708	N829AS	LGA	IAD	53	229	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	140	944	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	138	733	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	849	851	-2	B6	49	N793JB	JFK	PBI	149	1028	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	853	856	-3	B6	71	N657JB	JFK	TPA	158	1005	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	345	2475	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	361	2565	6	0	2013-01-01 06:00:00
2013	1	1	559	600	-1	941	910	31	AA	707	N3DUAA	LGA	DFW	257	1389	6	0	2013-01-01 06:00:00
2013	1	1	559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	44	187	5	59	2013-01-01 05:00:00
2013	1	1	559	600	-1	854	902	-8	UA	1187	N76515	EWR	LAS	337	2227	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	152	1076	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	134	762	6	0	2013-01-01 06:00:00
2013	1	1	601	600	1	844	850	-6	B6	343	N644JB	EWR	PBI	147	1023	6	0	2013-01-01 06:00:00
2013	1	1	602	610	-8	812	820	-8	DL	1919	N971DL	LGA	MSP	170	1020	6	10	2013-01-01 06:00:00
2013	1	1	602	605	-3	821	805	16	MQ	4401	N730MQ	LGA	DTW	105	502	6	5	2013-01-01 06:00:00
2013	1	1	606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	152	1085	6	10	2013-01-01 06:00:00
2013	1	1	606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	128	760	6	10	2013-01-01 06:00:00
2013	1	1	607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	157	1085	6	7	2013-01-01 06:00:00
2013	1	1	608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	139	719	6	0	2013-01-01 06:00:00
2013	1	1	611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	366	2586	6	0	2013-01-01 06:00:00
2013	1	1	613	610	3	925	921	4	B6	135	N635JB	JFK	RSW	175	1074	6	10	2013-01-01 06:00:00
2013	1	1	615	615	0	1039	1100	-21	B6	709	N794JB	JFK	SJU	182	1598	6	15	2013-01-01 06:00:00
2013	1	1	615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	120	746	6	15	2013-01-01 06:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	1	1	2140	2135	5	210	224	-14	B6	701	N284JB	JFK	SJU	189	1598	21	35	2013-01-01 21:00:00
2013	1	1	2157	2155	2	43	41	2	B6	43	N537JB	JFK	MCO	140	944	21	55	2013-01-01 21:00:00
2013	1	1	2158	2200	-2	2254	2307	-13	EV	4103	N14998	EWR	BWI	36	169	22	0	2013-01-01 22:00:00
2013	1	1	2205	1720	285	46	2040	246	AA	1999	N5DNAA	EWR	MIA	146	1085	17	20	2013-01-01 17:00:00
2013	1	1	2209	2145	24	58	37	21	B6	35	N608JB	JFK	PBI	143	1028	21	45	2013-01-01 21:00:00
2013	1	1	2209	2155	14	2400	2337	23	B6	1109	N216JB	JFK	RDU	86	427	21	55	2013-01-01 21:00:00
2013	1	1	2211	2145	26	2339	2311	28	B6	104	N228JB	JFK	BUF	64	301	21	45	2013-01-01 21:00:00
2013	1	1	2217	2229	-12	249	315	-26	B6	713	N547JB	JFK	SJU	191	1598	22	29	2013-01-01 22:00:00
2013	1	1	2217	2130	47	140	27	73	B6	21	N516JB	JFK	TPA	163	1005	21	30	2013-01-01 21:00:00
2013	1	1	2221	2000	141	2331	2124	127	EV	4462	N13566	EWR	BUF	56	282	20	0	2013-01-01 20:00:00
2013	1	1	2224	2200	24	2324	2316	8	EV	4206	N16561	EWR	PWM	47	284	22	0	2013-01-01 22:00:00
2013	1	1	2229	2159	30	149	100	49	B6	11	N531JB	JFK	FLL	153	1069	21	59	2013-01-01 21:00:00
2013	1	1	2240	2245	-5	2340	2356	-16	B6	608	N279JB	JFK	PWM	44	273	22	45	2013-01-01 22:00:00
2013	1	1	2250	2255	-5	2352	2359	-7	B6	1018	N521JB	JFK	BOS	37	187	22	55	2013-01-01 22:00:00
2013	1	1	2302	2200	62	2342	2253	49	EV	4276	N13903	EWR	BDL	24	116	22	0	2013-01-01 22:00:00
2013	1	1	2306	2245	21	28	5	23	B6	30	N281JB	JFK	ROC	59	264	22	45	2013-01-01 22:00:00
2013	1	1	2307	2245	22	32	2357	35	B6	128	N178JB	JFK	BTV	59	266	22	45	2013-01-01 22:00:00
2013	1	1	2310	2255	15	24	15	9	B6	112	N646JB	JFK	BUF	57	301	22	55	2013-01-01 22:00:00
2013	1	1	2312	2000	192	21	2110	191	EV	4312	N13958	EWR	DCA	44	199	20	0	2013-01-01 20:00:00
2013	1	1	2323	2200	83	22	2313	69	EV	4257	N13538	EWR	BTV	44	266	22	0	2013-01-01 22:00:00
2013	1	1	2326	2130	116	131	18	73	B6	199	N594JB	JFK	LAS	290	2248	21	30	2013-01-01 21:00:00
2013	1	1	2327	2250	37	32	2359	33	B6	22	N639JB	JFK	SYR	45	209	22	50	2013-01-01 22:00:00
2013	1	1	2343	1724	379	314	1938	456	EV	4321	N21197	EWR	MCI	222	1092	17	24	2013-01-01 17:00:00
2013	1	1	2353	2359	-6	425	445	-20	B6	739	N591JB	JFK	PSE	195	1617	23	59	2013-01-01 23:00:00
2013	1	1	2353	2359	-6	418	442	-24	B6	707	N794JB	JFK	SJU	185	1598	23	59	2013-01-01 23:00:00
2013	1	1	2356	2359	-3	425	437	-12	B6	727	N588JB	JFK	BQN	186	1576	23	59	2013-01-01 23:00:00
2013	1	1	NA	1630	NA	NA	1815	NA	EV	4308	N18120	EWR	RDU	NA	416	16	30	2013-01-01 16:00:00
2013	1	1	NA	1935	NA	NA	2240	NA	AA	791	N3EHAA	LGA	DFW	NA	1389	19	35	2013-01-01 19:00:00
2013	1	1	NA	1500	NA	NA	1825	NA	AA	1925	N3EVAA	LGA	MIA	NA	1096	15	0	2013-01-01 15:00:00
2013	1	1	NA	600	NA	NA	901	NA	B6	125	N618JB	JFK	FLL	NA	1069	6	0	2013-01-01 06:00:00

When you run that line of code, dplyr executes the filtering operation and returns a new data frame. dplyr functions never modify their inputs, so if you want to save the result, you'll need to use the assignment operator, <-:

In [8]:

jan1 <- filter(flights, month == 1, day == 1)

jan1 <- filter(flights, month == 1, day == 1)

R either prints out the results, or saves them to a variable. If you want to do both, you can wrap the assignment in parentheses:

In [9]:

(dec25 <- filter(flights, month == 12, day == 25))

(dec25 <- filter(flights, month == 12, day == 25))

A tibble: 719 × 19

year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	12	25	456	500	-4	649	651	-2	US	1895	N156UW	EWR	CLT	98	529	5	0	2013-12-25 05:00:00
2013	12	25	524	515	9	805	814	-9	UA	1016	N32404	EWR	IAH	203	1400	5	15	2013-12-25 05:00:00
2013	12	25	542	540	2	832	850	-18	AA	2243	N5EBAA	JFK	MIA	146	1089	5	40	2013-12-25 05:00:00
2013	12	25	546	550	-4	1022	1027	-5	B6	939	N665JB	JFK	BQN	191	1576	5	50	2013-12-25 05:00:00
2013	12	25	556	600	-4	730	745	-15	AA	301	N3JLAA	LGA	ORD	123	733	6	0	2013-12-25 06:00:00
2013	12	25	557	600	-3	743	752	-9	DL	731	N369NB	LGA	DTW	88	502	6	0	2013-12-25 06:00:00
2013	12	25	557	600	-3	818	831	-13	DL	904	N397DA	LGA	ATL	118	762	6	0	2013-12-25 06:00:00
2013	12	25	559	600	-1	855	856	-1	B6	371	N608JB	LGA	FLL	147	1076	6	0	2013-12-25 06:00:00
2013	12	25	559	600	-1	849	855	-6	B6	605	N536JB	EWR	FLL	149	1065	6	0	2013-12-25 06:00:00
2013	12	25	600	600	0	850	846	4	B6	583	N746JB	JFK	MCO	137	944	6	0	2013-12-25 06:00:00
2013	12	25	600	600	0	715	723	-8	EV	5747	N835AS	LGA	IAD	47	229	6	0	2013-12-25 06:00:00
2013	12	25	601	600	1	855	905	-10	AA	1175	N3EJAA	LGA	MIA	152	1096	6	0	2013-12-25 06:00:00
2013	12	25	601	600	1	846	849	-3	B6	353	N766JB	JFK	PBI	143	1028	6	0	2013-12-25 06:00:00
2013	12	25	604	611	-7	1051	1055	-4	B6	1403	N779JB	JFK	SJU	190	1598	6	11	2013-12-25 06:00:00
2013	12	25	605	600	5	912	937	-25	UA	303	N596UA	JFK	SFO	345	2586	6	0	2013-12-25 06:00:00
2013	12	25	610	610	0	922	915	7	AA	1205	N5BTAA	EWR	MIA	145	1085	6	10	2013-12-25 06:00:00
2013	12	25	610	605	5	718	723	-5	EV	6177	N11189	EWR	IAD	44	212	6	5	2013-12-25 06:00:00
2013	12	25	611	611	0	908	912	-4	B6	601	N804JB	JFK	FLL	153	1069	6	11	2013-12-25 06:00:00
2013	12	25	612	615	-3	859	852	7	DL	479	N3769L	JFK	ATL	127	760	6	15	2013-12-25 06:00:00
2013	12	25	615	615	0	802	818	-16	US	1963	N169UW	EWR	CLT	87	529	6	15	2013-12-25 06:00:00
2013	12	25	620	615	5	818	817	1	US	840	N151UW	JFK	CLT	96	541	6	15	2013-12-25 06:00:00
2013	12	25	621	610	11	902	905	-3	UA	1627	N76269	EWR	PBI	142	1023	6	10	2013-12-25 06:00:00
2013	12	25	627	625	2	1010	1009	1	US	690	N672AW	EWR	PHX	288	2133	6	25	2013-12-25 06:00:00
2013	12	25	628	630	-2	932	938	-6	B6	929	N712JB	JFK	RSW	162	1074	6	30	2013-12-25 06:00:00
2013	12	25	628	630	-2	949	919	30	B6	1099	N571JB	LGA	MCO	136	950	6	30	2013-12-25 06:00:00
2013	12	25	630	631	-1	924	958	-34	UA	398	N505UA	JFK	LAX	335	2475	6	31	2013-12-25 06:00:00
2013	12	25	632	635	-3	927	929	-2	B6	525	N556JB	JFK	TPA	151	1005	6	35	2013-12-25 06:00:00
2013	12	25	632	620	12	922	919	3	UA	596	N556UA	EWR	TPA	147	997	6	20	2013-12-25 06:00:00
2013	12	25	635	614	21	921	925	-4	UA	1701	N38417	EWR	FLL	151	1065	6	14	2013-12-25 06:00:00
2013	12	25	643	630	13	904	859	5	DL	807	N977AT	EWR	ATL	115	746	6	30	2013-12-25 06:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	12	25	2126	2129	-3	24	22	2	B6	2043	N599JB	EWR	PBI	144	1023	21	29	2013-12-25 21:00:00
2013	12	25	2129	2053	36	1	2333	28	EV	4388	N17984	EWR	JAX	133	820	20	53	2013-12-25 20:00:00
2013	12	25	2130	2135	-5	25	55	-30	AA	185	N332AA	JFK	LAX	328	2475	21	35	2013-12-25 21:00:00
2013	12	25	2130	2115	15	2322	2315	7	MQ	3384	N520MQ	LGA	CLT	90	544	21	15	2013-12-25 21:00:00
2013	12	25	2131	2046	45	24	2349	35	UA	236	N406UA	EWR	TPA	151	997	20	46	2013-12-25 20:00:00
2013	12	25	2140	2141	-1	44	37	7	B6	425	N579JB	JFK	TPA	160	1005	21	41	2013-12-25 21:00:00
2013	12	25	2144	2145	-1	27	19	8	B6	2227	N273JB	EWR	MCO	144	937	21	45	2013-12-25 21:00:00
2013	12	25	2149	2155	-6	2328	2334	-6	B6	985	N339JB	JFK	RDU	80	427	21	55	2013-12-25 21:00:00
2013	12	25	2150	2159	-9	103	45	18	B6	1183	N634JB	JFK	MCO	151	944	21	59	2013-12-25 21:00:00
2013	12	25	2152	2155	-3	100	56	4	B6	1901	N665JB	JFK	FLL	154	1069	21	55	2013-12-25 21:00:00
2013	12	25	2153	2155	-2	50	46	4	B6	2053	N613JB	JFK	PBI	148	1028	21	55	2013-12-25 21:00:00
2013	12	25	2153	2135	18	22	19	3	B6	611	N519JB	JFK	LAS	305	2248	21	35	2013-12-25 21:00:00
2013	12	25	2153	2030	83	46	2347	59	UA	1682	N78524	EWR	FLL	151	1065	20	30	2013-12-25 20:00:00
2013	12	25	2203	2136	27	2327	2259	28	B6	702	N198JB	JFK	BUF	59	301	21	36	2013-12-25 21:00:00
2013	12	25	2203	2130	33	2309	2245	24	MQ	3621	N844MQ	JFK	DCA	42	213	21	30	2013-12-25 21:00:00
2013	12	25	2219	2110	69	102	2339	83	B6	775	N258JB	JFK	MSY	192	1182	21	10	2013-12-25 21:00:00
2013	12	25	2231	2219	12	311	304	7	B6	1203	N618JB	JFK	SJU	194	1598	22	19	2013-12-25 22:00:00
2013	12	25	2302	2245	17	15	3	12	B6	486	N203JB	JFK	ROC	54	264	22	45	2013-12-25 22:00:00
2013	12	25	2308	2245	23	10	2355	15	B6	234	N316JB	JFK	BTV	43	266	22	45	2013-12-25 22:00:00
2013	12	25	2312	2250	22	10	2356	14	B6	1816	N358JB	JFK	SYR	44	209	22	50	2013-12-25 22:00:00
2013	12	25	2314	2245	29	15	2353	22	B6	108	N279JB	JFK	PWM	46	273	22	45	2013-12-25 22:00:00
2013	12	25	2315	2330	-15	348	409	-21	B6	1389	N554JB	EWR	SJU	199	1608	23	30	2013-12-25 23:00:00
2013	12	25	2352	2359	-7	429	440	-11	B6	1503	N531JB	JFK	SJU	198	1598	23	59	2013-12-25 23:00:00
2013	12	25	2353	2355	-2	442	438	4	DL	2380	N713TW	JFK	SJU	196	1598	23	55	2013-12-25 23:00:00
2013	12	25	2357	2359	-2	446	445	1	B6	745	N636JB	JFK	PSE	204	1617	23	59	2013-12-25 23:00:00
2013	12	25	2357	2359	-2	433	437	-4	B6	839	N784JB	JFK	BQN	194	1576	23	59	2013-12-25 23:00:00
2013	12	25	NA	1630	NA	NA	1838	NA	DL	2231	N312US	LGA	DTW	NA	502	16	30	2013-12-25 16:00:00
2013	12	25	NA	1525	NA	NA	1720	NA	MQ	2840	N8EGMQ	LGA	STL	NA	888	15	25	2013-12-25 15:00:00
2013	12	25	NA	1100	NA	NA	1210	NA	MQ	3230	N524MQ	JFK	DCA	NA	213	11	0	2013-12-25 11:00:00
2013	12	25	NA	1930	NA	NA	2115	NA	MQ	3535	N509MQ	JFK	CMH	NA	483	19	30	2013-12-25 19:00:00

Comparisons¶

To use filtering effectively, you have to know how to select the observations that you want using the comparison operators. R provides the standard suite: >, >=, <, <=, != (not equal), and == (equal).

When you're starting out with R, the easiest mistake to make is to use = instead of == when testing for equality. When this happens you'll get an informative error:

In [10]:

filter(flights, month = 1)

filter(flights, month = 1)

Error in `filter()`:
! We detected a named input.
ℹ This usually means that you've used `=` instead of `==`.
ℹ Did you mean `month == 1`?
Traceback:

1. filter(flights, month = 1)
2. filter.data.frame(flights, month = 1)
3. filter_rows(.data, ..., caller_env = caller_env())
4. check_filter(dots, error_call = error_call)
5. abort(bullets, call = error_call)
6. signal_abort(cnd, .file)

There's another common problem you might encounter when using ==: floating point numbers. These results might surprise you!

In [11]:

(sqrt(2) ^ 2) == 2

(sqrt(2) ^ 2) == 2

FALSE

In [12]:

(1 / 49 * 49) == 1

(1 / 49 * 49) == 1

FALSE

Computers use finite precision arithmetic (they obviously can't store an infinite number of digits!) so remember that every number you see is an approximation. Instead of relying on ==, use near():

In [13]:

near(sqrt(2) ^ 2,  2)

near(sqrt(2) ^ 2, 2)

TRUE

In [14]:

near(1 / 49 * 49, 1)

near(1 / 49 * 49, 1)

TRUE

Logical operators¶

Multiple arguments to filter() are combined with "and": every expression must be true in order for a row to be included in the output. For other types of combinations, you'll need to use Boolean operators yourself: & is "and", | is "or", and ! is "not". The following figure shows the complete set of Boolean operations.

Complete set of boolean operations. `x` is the left-hand circle, `y` is the right-hand circle, and the shaded region show which parts each operator selects.

The following code finds all flights that departed in November or December:

filter(flights, month == 11 | month == 12)

The order of operations doesn't work like English. You can't write filter(flights, month == (11 | 12)), which you might literally translate into "finds all flights that departed in November or December". Instead it finds all months that equal 11 | 12, an expression that evaluates to TRUE. In a numeric context (like here), TRUE becomes 1, so this finds all flights in January, not November or December. This is quite confusing!

A useful short-hand for this problem is x %in% y. This will select every row where x is one of the values in y. We could use it to rewrite the code above:

nov_dec <- filter(flights, month %in% c(11, 12))

Sometimes you can simplify complicated subsetting by remembering De Morgan's law: !(x & y) is the same as !x | !y, and !(x | y) is the same as !x & !y. For example, if you wanted to find flights that weren't delayed (on arrival or departure) by more than two hours, you could use either of the following two filters:

filter(flights, !(arr_delay > 120 | dep_delay > 120))
filter(flights, arr_delay <= 120, dep_delay <= 120)

As well as & and |, R also has && and ||. Don't use them here! You'll learn when you should use them in Section functions (conditional execution).

Whenever you start using complicated, multipart expressions in filter(), consider making them explicit variables instead. That makes it much easier to check your work. You'll learn how to create new variables shortly.

Missing values¶

One important feature of R that can make comparison tricky is missing values, or NAs ("not availables"). NA represents an unknown value so missing values are "contagious": almost any operation involving an unknown value will also be unknown.

In [15]:

NA > 5

NA > 5

<NA>

In [16]:

10 == NA

10 == NA

<NA>

In [17]:

NA + 10

NA + 10

<NA>

In [18]:

NA / 2

NA / 2

<NA>

The most confusing result is this one:

In [19]:

NA == NA

NA == NA

<NA>

It's easiest to understand why this is true with a bit more context:

In [20]:

# Let x be Mary's age. We don't know how old she is.
x <- NA

# Let y be John's age. We don't know how old he is.
y <- NA

# Are John and Mary the same age?
x == y
# We don't know!

# Let x be Mary's age. We don't know how old she is. x <- NA # Let y be John's age. We don't know how old he is. y <- NA # Are John and Mary the same age? x == y # We don't know!

<NA>

If you want to determine if a value is missing, use is.na():

In [21]:

is.na(x)

is.na(x)

TRUE

filter() only includes rows where the condition is TRUE; it excludes both FALSE and NA values. If you want to preserve missing values, ask for them explicitly:

In [22]:

df <- tibble(x = c(1, NA, 3))
filter(df, x > 1)

df <- tibble(x = c(1, NA, 3)) filter(df, x > 1)

A tibble: 1 × 1

x
<dbl>
3

In [23]:

filter(df, is.na(x) | x > 1)

filter(df, is.na(x) | x > 1)

A tibble: 2 × 1

x
<dbl>
NA
3

Exercises¶

1. Find all flights that

   a. Had an arrival delay of two or more hours b. Flew to Houston (IAH or HOU) c. Were operated by United, American, or Delta d. Departed in summer (July, August, and September) e. Arrived more than two hours late, but didn't leave late f. Were delayed by at least an hour, but made up over 30 minutes in flight g. Departed between midnight and 6am (inclusive)

2. Another useful dplyr filtering helper is between(). What does it do? Can you use it to simplify the code needed to answer the previous challenges?

3. How many flights have a missing dep_time? What other variables are missing? What might these rows represent?

4. Why is NA ^ 0 not missing? Why is NA | TRUE not missing? Why is FALSE & NA not missing? Can you figure out the general rule? (NA * 0 is a tricky counterexample!)

Arrange rows with arrange()¶

arrange() works similarly to filter() except that instead of selecting rows, it changes their order. It takes a data frame and a set of column names (or more complicated expressions) to order by. If you provide more than one column name, each additional column will be used to break ties in the values of preceding columns:

In [24]:

arrange(flights, year, month, day)

arrange(flights, year, month, day)

A tibble: 336776 × 19

year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	1	517	515	2	830	819	11	UA	1545	N14228	EWR	IAH	227	1400	5	15	2013-01-01 05:00:00
2013	1	1	533	529	4	850	830	20	UA	1714	N24211	LGA	IAH	227	1416	5	29	2013-01-01 05:00:00
2013	1	1	542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	160	1089	5	40	2013-01-01 05:00:00
2013	1	1	544	545	-1	1004	1022	-18	B6	725	N804JB	JFK	BQN	183	1576	5	45	2013-01-01 05:00:00
2013	1	1	554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	116	762	6	0	2013-01-01 06:00:00
2013	1	1	554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	150	719	5	58	2013-01-01 05:00:00
2013	1	1	555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	158	1065	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	709	723	-14	EV	5708	N829AS	LGA	IAD	53	229	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	140	944	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	138	733	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	849	851	-2	B6	49	N793JB	JFK	PBI	149	1028	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	853	856	-3	B6	71	N657JB	JFK	TPA	158	1005	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	345	2475	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	361	2565	6	0	2013-01-01 06:00:00
2013	1	1	559	600	-1	941	910	31	AA	707	N3DUAA	LGA	DFW	257	1389	6	0	2013-01-01 06:00:00
2013	1	1	559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	44	187	5	59	2013-01-01 05:00:00
2013	1	1	559	600	-1	854	902	-8	UA	1187	N76515	EWR	LAS	337	2227	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	152	1076	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	134	762	6	0	2013-01-01 06:00:00
2013	1	1	601	600	1	844	850	-6	B6	343	N644JB	EWR	PBI	147	1023	6	0	2013-01-01 06:00:00
2013	1	1	602	610	-8	812	820	-8	DL	1919	N971DL	LGA	MSP	170	1020	6	10	2013-01-01 06:00:00
2013	1	1	602	605	-3	821	805	16	MQ	4401	N730MQ	LGA	DTW	105	502	6	5	2013-01-01 06:00:00
2013	1	1	606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	152	1085	6	10	2013-01-01 06:00:00
2013	1	1	606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	128	760	6	10	2013-01-01 06:00:00
2013	1	1	607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	157	1085	6	7	2013-01-01 06:00:00
2013	1	1	608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	139	719	6	0	2013-01-01 06:00:00
2013	1	1	611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	366	2586	6	0	2013-01-01 06:00:00
2013	1	1	613	610	3	925	921	4	B6	135	N635JB	JFK	RSW	175	1074	6	10	2013-01-01 06:00:00
2013	1	1	615	615	0	1039	1100	-21	B6	709	N794JB	JFK	SJU	182	1598	6	15	2013-01-01 06:00:00
2013	1	1	615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	120	746	6	15	2013-01-01 06:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	12	31	2155	2039	76	253	2355	NA	B6	1205	N627JB	JFK	PDX	NA	2454	20	39	2013-12-31 20:00:00
2013	12	31	2155	2150	5	110	51	19	B6	1901	N729JB	JFK	FLL	164	1069	21	50	2013-12-31 21:00:00
2013	12	31	2159	2155	4	55	46	9	B6	2053	N593JB	JFK	PBI	155	1028	21	55	2013-12-31 21:00:00
2013	12	31	2206	2110	56	44	2339	65	B6	775	N184JB	JFK	MSY	195	1182	21	10	2013-12-31 21:00:00
2013	12	31	2211	2159	12	100	45	15	B6	1183	N715JB	JFK	MCO	148	944	21	59	2013-12-31 21:00:00
2013	12	31	2218	2219	-1	315	304	11	B6	1203	N625JB	JFK	SJU	202	1598	22	19	2013-12-31 22:00:00
2013	12	31	2235	2245	-10	2351	2355	-4	B6	234	N355JB	JFK	BTV	49	266	22	45	2013-12-31 22:00:00
2013	12	31	2245	2250	-5	2359	2356	3	B6	1816	N318JB	JFK	SYR	51	209	22	50	2013-12-31 22:00:00
2013	12	31	2310	2255	15	7	2356	11	B6	718	N279JB	JFK	BOS	40	187	22	55	2013-12-31 22:00:00
2013	12	31	2321	2250	31	46	8	38	B6	2002	N179JB	JFK	BUF	66	301	22	50	2013-12-31 22:00:00
2013	12	31	2328	2330	-2	412	409	3	B6	1389	N651JB	EWR	SJU	198	1608	23	30	2013-12-31 23:00:00
2013	12	31	2332	2245	47	58	3	55	B6	486	N334JB	JFK	ROC	60	264	22	45	2013-12-31 22:00:00
2013	12	31	2355	2359	-4	430	440	-10	B6	1503	N509JB	JFK	SJU	195	1598	23	59	2013-12-31 23:00:00
2013	12	31	2356	2359	-3	436	445	-9	B6	745	N665JB	JFK	PSE	200	1617	23	59	2013-12-31 23:00:00
2013	12	31	NA	1520	NA	NA	1705	NA	AA	341	N568AA	LGA	ORD	NA	733	15	20	2013-12-31 15:00:00
2013	12	31	NA	2025	NA	NA	2205	NA	AA	371	N482AA	LGA	ORD	NA	733	20	25	2013-12-31 20:00:00
2013	12	31	NA	1932	NA	NA	2305	NA	B6	161	N516JB	JFK	SMF	NA	2521	19	32	2013-12-31 19:00:00
2013	12	31	NA	1505	NA	NA	1725	NA	EV	4181	N24103	EWR	MCI	NA	1092	15	5	2013-12-31 15:00:00
2013	12	31	NA	1000	NA	NA	1252	NA	UA	1124	NA	EWR	EGE	NA	1725	10	0	2013-12-31 10:00:00
2013	12	31	NA	840	NA	NA	1205	NA	UA	1151	NA	EWR	SEA	NA	2402	8	40	2013-12-31 08:00:00
2013	12	31	NA	754	NA	NA	1118	NA	UA	1455	NA	EWR	LAX	NA	2454	7	54	2013-12-31 07:00:00
2013	12	31	NA	2000	NA	NA	2146	NA	UA	1482	NA	EWR	ORD	NA	719	20	0	2013-12-31 20:00:00
2013	12	31	NA	1500	NA	NA	1817	NA	UA	1483	NA	EWR	AUS	NA	1504	15	0	2013-12-31 15:00:00
2013	12	31	NA	1430	NA	NA	1750	NA	UA	1493	NA	EWR	LAX	NA	2454	14	30	2013-12-31 14:00:00
2013	12	31	NA	855	NA	NA	1142	NA	UA	1506	NA	EWR	JAC	NA	1874	8	55	2013-12-31 08:00:00
2013	12	31	NA	705	NA	NA	931	NA	UA	1729	NA	EWR	DEN	NA	1605	7	5	2013-12-31 07:00:00
2013	12	31	NA	825	NA	NA	1029	NA	US	1831	NA	JFK	CLT	NA	541	8	25	2013-12-31 08:00:00
2013	12	31	NA	1615	NA	NA	1800	NA	MQ	3301	N844MQ	LGA	RDU	NA	431	16	15	2013-12-31 16:00:00
2013	12	31	NA	600	NA	NA	735	NA	UA	219	NA	EWR	ORD	NA	719	6	0	2013-12-31 06:00:00
2013	12	31	NA	830	NA	NA	1154	NA	UA	443	NA	JFK	LAX	NA	2475	8	30	2013-12-31 08:00:00

Use desc() to re-order by a column in descending order:

In [25]:

arrange(flights, desc(dep_delay))

arrange(flights, desc(dep_delay))

A tibble: 336776 × 19

year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	9	641	900	1301	1242	1530	1272	HA	51	N384HA	JFK	HNL	640	4983	9	0	2013-01-09 09:00:00
2013	6	15	1432	1935	1137	1607	2120	1127	MQ	3535	N504MQ	JFK	CMH	74	483	19	35	2013-06-15 19:00:00
2013	1	10	1121	1635	1126	1239	1810	1109	MQ	3695	N517MQ	EWR	ORD	111	719	16	35	2013-01-10 16:00:00
2013	9	20	1139	1845	1014	1457	2210	1007	AA	177	N338AA	JFK	SFO	354	2586	18	45	2013-09-20 18:00:00
2013	7	22	845	1600	1005	1044	1815	989	MQ	3075	N665MQ	JFK	CVG	96	589	16	0	2013-07-22 16:00:00
2013	4	10	1100	1900	960	1342	2211	931	DL	2391	N959DL	JFK	TPA	139	1005	19	0	2013-04-10 19:00:00
2013	3	17	2321	810	911	135	1020	915	DL	2119	N927DA	LGA	MSP	167	1020	8	10	2013-03-17 08:00:00
2013	6	27	959	1900	899	1236	2226	850	DL	2007	N3762Y	JFK	PDX	313	2454	19	0	2013-06-27 19:00:00
2013	7	22	2257	759	898	121	1026	895	DL	2047	N6716C	LGA	ATL	109	762	7	59	2013-07-22 07:00:00
2013	12	5	756	1700	896	1058	2020	878	AA	172	N5DMAA	EWR	MIA	149	1085	17	0	2013-12-05 17:00:00
2013	5	3	1133	2055	878	1250	2215	875	MQ	3744	N523MQ	EWR	ORD	112	719	20	55	2013-05-03 20:00:00
2013	1	1	848	1835	853	1001	1950	851	MQ	3944	N942MQ	JFK	BWI	41	184	18	35	2013-01-01 18:00:00
2013	2	10	2243	830	853	100	1106	834	F9	835	N203FR	LGA	DEN	233	1620	8	30	2013-02-10 08:00:00
2013	5	19	713	1700	853	1007	1955	852	AA	257	N3HEAA	JFK	LAS	323	2248	17	0	2013-05-19 17:00:00
2013	12	19	734	1725	849	1046	2039	847	DL	1223	N375NC	EWR	SLC	290	1969	17	25	2013-12-19 17:00:00
2013	12	17	705	1700	845	1026	2020	846	AA	172	N5EMAA	EWR	MIA	145	1085	17	0	2013-12-17 17:00:00
2013	12	14	830	1845	825	1210	2154	856	DL	2391	N939DL	JFK	TPA	173	1005	18	45	2013-12-14 18:00:00
2013	4	19	912	1940	812	1228	2247	821	DL	1435	N900DE	LGA	TPA	174	1010	19	40	2013-04-19 19:00:00
2013	6	27	753	1830	803	937	2015	802	AA	2019	N571AA	LGA	STL	134	888	18	30	2013-06-27 18:00:00
2013	3	18	1020	2100	800	1336	32	784	DL	2363	N624AG	JFK	LAX	335	2475	21	0	2013-03-18 21:00:00
2013	11	3	603	1645	798	829	1913	796	DL	2042	N990AT	EWR	ATL	109	746	16	45	2013-11-03 16:00:00
2013	4	19	617	1700	797	858	1955	783	AA	257	N3GJAA	JFK	LAS	313	2248	17	0	2013-04-19 17:00:00
2013	6	27	615	1705	790	853	2004	769	DL	503	N372DA	JFK	SAN	312	2446	17	5	2013-06-27 17:00:00
2013	2	19	2324	1016	788	114	1227	767	DL	2319	N324US	LGA	MSP	136	1020	10	16	2013-02-19 10:00:00
2013	6	27	732	1825	787	932	2032	780	DL	1715	N335NB	LGA	MSY	160	1183	18	25	2013-06-27 18:00:00
2013	2	24	1921	615	786	2135	842	773	DL	575	N348NW	EWR	ATL	111	746	6	15	2013-02-24 06:00:00
2013	4	19	606	1725	761	923	2020	783	AA	1901	N3DGAA	JFK	IAH	222	1417	17	25	2013-04-19 17:00:00
2013	4	19	758	1925	753	1049	2225	744	DL	1485	N927DA	LGA	MCO	149	950	19	25	2013-04-19 19:00:00
2013	2	16	757	1930	747	1013	2149	744	9E	3798	N8940E	JFK	CLT	85	541	19	30	2013-02-16 19:00:00
2013	10	14	2042	900	702	2255	1127	688	DL	502	N943DL	EWR	ATL	98	746	9	0	2013-10-14 09:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	24	NA	1625	NA	NA	1750	NA	MQ	3622	N524MQ	LGA	BNA	NA	764	16	25	2013-09-24 16:00:00
2013	9	25	NA	1259	NA	NA	1507	NA	EV	5207	N615QX	LGA	CLT	NA	544	12	59	2013-09-25 12:00:00
2013	9	25	NA	845	NA	NA	1018	NA	EV	5286	N615QX	LGA	BTV	NA	258	8	45	2013-09-25 08:00:00
2013	9	25	NA	1755	NA	NA	1932	NA	EV	5287	N722EV	LGA	MSN	NA	812	17	55	2013-09-25 17:00:00
2013	9	25	NA	600	NA	NA	716	NA	EV	5716	N877AS	JFK	IAD	NA	228	6	0	2013-09-25 06:00:00
2013	9	25	NA	836	NA	NA	944	NA	B6	2280	N258JB	EWR	BOS	NA	200	8	36	2013-09-25 08:00:00
2013	9	25	NA	1300	NA	NA	1409	NA	US	2148	NA	LGA	BOS	NA	184	13	0	2013-09-25 13:00:00
2013	9	25	NA	1900	NA	NA	2014	NA	US	2160	NA	LGA	BOS	NA	184	19	0	2013-09-25 19:00:00
2013	9	25	NA	1300	NA	NA	1450	NA	MQ	3388	N817MQ	LGA	CMH	NA	479	13	0	2013-09-25 13:00:00
2013	9	25	NA	1655	NA	NA	1840	NA	MQ	3411	N735MQ	LGA	RDU	NA	431	16	55	2013-09-25 16:00:00
2013	9	25	NA	1559	NA	NA	1719	NA	MQ	3748	N530MQ	EWR	ORD	NA	719	15	59	2013-09-25 15:00:00
2013	9	26	NA	915	NA	NA	1141	NA	EV	5109	N748EV	LGA	CHS	NA	641	9	15	2013-09-26 09:00:00
2013	9	26	NA	1400	NA	NA	1512	NA	US	2183	NA	LGA	DCA	NA	214	14	0	2013-09-26 14:00:00
2013	9	26	NA	1240	NA	NA	1525	NA	WN	4720	N691WN	EWR	HOU	NA	1411	12	40	2013-09-26 12:00:00
2013	9	27	NA	600	NA	NA	730	NA	AA	301	N584AA	LGA	ORD	NA	733	6	0	2013-09-27 06:00:00
2013	9	27	NA	2100	NA	NA	2211	NA	US	2164	NA	LGA	BOS	NA	184	21	0	2013-09-27 21:00:00
2013	9	27	NA	1329	NA	NA	1444	NA	MQ	3760	N505MQ	EWR	ORD	NA	719	13	29	2013-09-27 13:00:00
2013	9	27	NA	1600	NA	NA	1739	NA	UA	269	NA	LGA	ORD	NA	733	16	0	2013-09-27 16:00:00
2013	9	28	NA	1803	NA	NA	1927	NA	EV	5563	N724EV	LGA	BTV	NA	258	18	3	2013-09-28 18:00:00
2013	9	28	NA	910	NA	NA	1220	NA	AA	1	N320AA	JFK	LAX	NA	2475	9	10	2013-09-28 09:00:00
2013	9	28	NA	1635	NA	NA	1827	NA	US	581	NA	EWR	CLT	NA	529	16	35	2013-09-28 16:00:00
2013	9	29	NA	2054	NA	NA	2302	NA	EV	4536	N13988	EWR	CVG	NA	569	20	54	2013-09-29 20:00:00
2013	9	29	NA	1830	NA	NA	2010	NA	MQ	3134	N508MQ	EWR	ORD	NA	719	18	30	2013-09-29 18:00:00
2013	9	29	NA	700	NA	NA	833	NA	UA	331	NA	LGA	ORD	NA	733	7	0	2013-09-29 07:00:00
2013	9	30	NA	1842	NA	NA	2019	NA	EV	5274	N740EV	LGA	BNA	NA	764	18	42	2013-09-30 18:00:00
2013	9	30	NA	1455	NA	NA	1634	NA	9E	3393	NA	JFK	DCA	NA	213	14	55	2013-09-30 14:00:00
2013	9	30	NA	2200	NA	NA	2312	NA	9E	3525	NA	LGA	SYR	NA	198	22	0	2013-09-30 22:00:00
2013	9	30	NA	1210	NA	NA	1330	NA	MQ	3461	N535MQ	LGA	BNA	NA	764	12	10	2013-09-30 12:00:00
2013	9	30	NA	1159	NA	NA	1344	NA	MQ	3572	N511MQ	LGA	CLE	NA	419	11	59	2013-09-30 11:00:00
2013	9	30	NA	840	NA	NA	1020	NA	MQ	3531	N839MQ	LGA	RDU	NA	431	8	40	2013-09-30 08:00:00

Missing values are always sorted at the end:

In [26]:

df <- tibble(x = c(5, 2, NA))
arrange(df, x)

df <- tibble(x = c(5, 2, NA)) arrange(df, x)

A tibble: 3 × 1

x
<dbl>
2
5
NA

In [27]:

arrange(df, desc(x))

arrange(df, desc(x))

A tibble: 3 × 1

x
<dbl>
5
2
NA

Exercises¶

1. Sort flights to find the flights with longest departure delays. Find the flights that left earliest.

2. Sort flights to find the fastest (highest speed) flights.

3. Which flights travelled the farthest? Which travelled the shortest?

4. How could you use arrange() to sort all missing values to the start? (Hint: use !is.na()).

Select columns with select()¶

It's not uncommon to get datasets with hundreds or even thousands of variables. In this case, the first challenge is often narrowing in on the variables you're actually interested in. select() allows you to rapidly zoom in on a useful subset using operations based on the names of the variables.

select() is not terribly useful with the flights data because we only have 19 variables, but you can still get the general idea:

In [28]:

# Select columns by name
select(flights, year, month, day)

# Select columns by name select(flights, year, month, day)

A tibble: 336776 × 3

year	month	day
<int>	<int>	<int>
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
⋮	⋮	⋮
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30

In [29]:

# Select all columns between year and day (inclusive)
select(flights, year:day)

# Select all columns between year and day (inclusive) select(flights, year:day)

A tibble: 336776 × 3

year	month	day
<int>	<int>	<int>
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
2013	1	1
⋮	⋮	⋮
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30
2013	9	30

In [30]:

# Select all columns except those from year to day (inclusive)
select(flights, -(year:day))

# Select all columns except those from year to day (inclusive) select(flights, -(year:day))

A tibble: 336776 × 16

dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
517	515	2	830	819	11	UA	1545	N14228	EWR	IAH	227	1400	5	15	2013-01-01 05:00:00
533	529	4	850	830	20	UA	1714	N24211	LGA	IAH	227	1416	5	29	2013-01-01 05:00:00
542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	160	1089	5	40	2013-01-01 05:00:00
544	545	-1	1004	1022	-18	B6	725	N804JB	JFK	BQN	183	1576	5	45	2013-01-01 05:00:00
554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	116	762	6	0	2013-01-01 06:00:00
554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	150	719	5	58	2013-01-01 05:00:00
555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	158	1065	6	0	2013-01-01 06:00:00
557	600	-3	709	723	-14	EV	5708	N829AS	LGA	IAD	53	229	6	0	2013-01-01 06:00:00
557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	140	944	6	0	2013-01-01 06:00:00
558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	138	733	6	0	2013-01-01 06:00:00
558	600	-2	849	851	-2	B6	49	N793JB	JFK	PBI	149	1028	6	0	2013-01-01 06:00:00
558	600	-2	853	856	-3	B6	71	N657JB	JFK	TPA	158	1005	6	0	2013-01-01 06:00:00
558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	345	2475	6	0	2013-01-01 06:00:00
558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	361	2565	6	0	2013-01-01 06:00:00
559	600	-1	941	910	31	AA	707	N3DUAA	LGA	DFW	257	1389	6	0	2013-01-01 06:00:00
559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	44	187	5	59	2013-01-01 05:00:00
559	600	-1	854	902	-8	UA	1187	N76515	EWR	LAS	337	2227	6	0	2013-01-01 06:00:00
600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	152	1076	6	0	2013-01-01 06:00:00
600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	134	762	6	0	2013-01-01 06:00:00
601	600	1	844	850	-6	B6	343	N644JB	EWR	PBI	147	1023	6	0	2013-01-01 06:00:00
602	610	-8	812	820	-8	DL	1919	N971DL	LGA	MSP	170	1020	6	10	2013-01-01 06:00:00
602	605	-3	821	805	16	MQ	4401	N730MQ	LGA	DTW	105	502	6	5	2013-01-01 06:00:00
606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	152	1085	6	10	2013-01-01 06:00:00
606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	128	760	6	10	2013-01-01 06:00:00
607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	157	1085	6	7	2013-01-01 06:00:00
608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	139	719	6	0	2013-01-01 06:00:00
611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	366	2586	6	0	2013-01-01 06:00:00
613	610	3	925	921	4	B6	135	N635JB	JFK	RSW	175	1074	6	10	2013-01-01 06:00:00
615	615	0	1039	1100	-21	B6	709	N794JB	JFK	SJU	182	1598	6	15	2013-01-01 06:00:00
615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	120	746	6	15	2013-01-01 06:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2123	2125	-2	2223	2247	-24	EV	5489	N712EV	LGA	CHO	45	305	21	25	2013-09-30 21:00:00
2127	2129	-2	2314	2323	-9	EV	3833	N16546	EWR	CLT	72	529	21	29	2013-09-30 21:00:00
2128	2130	-2	2328	2359	-31	B6	97	N807JB	JFK	DEN	213	1626	21	30	2013-09-30 21:00:00
2129	2059	30	2230	2232	-2	EV	5048	N751EV	LGA	RIC	45	292	20	59	2013-09-30 20:00:00
2131	2140	-9	2225	2255	-30	MQ	3621	N807MQ	JFK	DCA	36	213	21	40	2013-09-30 21:00:00
2140	2140	0	10	40	-30	AA	185	N335AA	JFK	LAX	298	2475	21	40	2013-09-30 21:00:00
2142	2129	13	2250	2239	11	EV	4509	N12957	EWR	PWM	47	284	21	29	2013-09-30 21:00:00
2145	2145	0	115	140	-25	B6	1103	N633JB	JFK	SJU	192	1598	21	45	2013-09-30 21:00:00
2147	2137	10	30	27	3	B6	1371	N627JB	LGA	FLL	139	1076	21	37	2013-09-30 21:00:00
2149	2156	-7	2245	2308	-23	UA	523	N813UA	EWR	BOS	37	200	21	56	2013-09-30 21:00:00
2150	2159	-9	2250	2306	-16	EV	3842	N10575	EWR	MHT	39	209	21	59	2013-09-30 21:00:00
2159	1845	194	2344	2030	194	9E	3320	N906XJ	JFK	BUF	50	301	18	45	2013-09-30 18:00:00
2203	2205	-2	2339	2331	8	EV	5311	N722EV	LGA	BGR	61	378	22	5	2013-09-30 22:00:00
2207	2140	27	2257	2250	7	MQ	3660	N532MQ	LGA	BNA	97	764	21	40	2013-09-30 21:00:00
2211	2059	72	2339	2242	57	EV	4672	N12145	EWR	STL	120	872	20	59	2013-09-30 20:00:00
2231	2245	-14	2335	2356	-21	B6	108	N193JB	JFK	PWM	48	273	22	45	2013-09-30 22:00:00
2233	2113	80	112	30	42	UA	471	N578UA	EWR	SFO	318	2565	21	13	2013-09-30 21:00:00
2235	2001	154	59	2249	130	B6	1083	N804JB	JFK	MCO	123	944	20	1	2013-09-30 20:00:00
2237	2245	-8	2345	2353	-8	B6	234	N318JB	JFK	BTV	43	266	22	45	2013-09-30 22:00:00
2240	2245	-5	2334	2351	-17	B6	1816	N354JB	JFK	SYR	41	209	22	45	2013-09-30 22:00:00
2240	2250	-10	2347	7	-20	B6	2002	N281JB	JFK	BUF	52	301	22	50	2013-09-30 22:00:00
2241	2246	-5	2345	1	-16	B6	486	N346JB	JFK	ROC	47	264	22	46	2013-09-30 22:00:00
2307	2255	12	2359	2358	1	B6	718	N565JB	JFK	BOS	33	187	22	55	2013-09-30 22:00:00
2349	2359	-10	325	350	-25	B6	745	N516JB	JFK	PSE	196	1617	23	59	2013-09-30 23:00:00
NA	1842	NA	NA	2019	NA	EV	5274	N740EV	LGA	BNA	NA	764	18	42	2013-09-30 18:00:00
NA	1455	NA	NA	1634	NA	9E	3393	NA	JFK	DCA	NA	213	14	55	2013-09-30 14:00:00
NA	2200	NA	NA	2312	NA	9E	3525	NA	LGA	SYR	NA	198	22	0	2013-09-30 22:00:00
NA	1210	NA	NA	1330	NA	MQ	3461	N535MQ	LGA	BNA	NA	764	12	10	2013-09-30 12:00:00
NA	1159	NA	NA	1344	NA	MQ	3572	N511MQ	LGA	CLE	NA	419	11	59	2013-09-30 11:00:00
NA	840	NA	NA	1020	NA	MQ	3531	N839MQ	LGA	RDU	NA	431	8	40	2013-09-30 08:00:00

There are a number of helper functions you can use within 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".

• matches("(.)\\1"): selects variables that match a regular expression. This one matches any variables that contain repeated characters. You'll learn more about regular expressions in [strings].

• num_range("x", 1:3): matches x1, x2 and x3.

See ?select for more details.

select() can be used to rename variables, but it's rarely useful because it drops all of the variables not explicitly mentioned. Instead, use rename(), which is a variant of select() that keeps all the variables that aren't explicitly mentioned:

In [31]:

rename(flights, tail_num = tailnum)

rename(flights, tail_num = tailnum)

A tibble: 336776 × 19

year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tail_num	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	1	517	515	2	830	819	11	UA	1545	N14228	EWR	IAH	227	1400	5	15	2013-01-01 05:00:00
2013	1	1	533	529	4	850	830	20	UA	1714	N24211	LGA	IAH	227	1416	5	29	2013-01-01 05:00:00
2013	1	1	542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	160	1089	5	40	2013-01-01 05:00:00
2013	1	1	544	545	-1	1004	1022	-18	B6	725	N804JB	JFK	BQN	183	1576	5	45	2013-01-01 05:00:00
2013	1	1	554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	116	762	6	0	2013-01-01 06:00:00
2013	1	1	554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	150	719	5	58	2013-01-01 05:00:00
2013	1	1	555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	158	1065	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	709	723	-14	EV	5708	N829AS	LGA	IAD	53	229	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	140	944	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	138	733	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	849	851	-2	B6	49	N793JB	JFK	PBI	149	1028	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	853	856	-3	B6	71	N657JB	JFK	TPA	158	1005	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	345	2475	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	361	2565	6	0	2013-01-01 06:00:00
2013	1	1	559	600	-1	941	910	31	AA	707	N3DUAA	LGA	DFW	257	1389	6	0	2013-01-01 06:00:00
2013	1	1	559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	44	187	5	59	2013-01-01 05:00:00
2013	1	1	559	600	-1	854	902	-8	UA	1187	N76515	EWR	LAS	337	2227	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	152	1076	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	134	762	6	0	2013-01-01 06:00:00
2013	1	1	601	600	1	844	850	-6	B6	343	N644JB	EWR	PBI	147	1023	6	0	2013-01-01 06:00:00
2013	1	1	602	610	-8	812	820	-8	DL	1919	N971DL	LGA	MSP	170	1020	6	10	2013-01-01 06:00:00
2013	1	1	602	605	-3	821	805	16	MQ	4401	N730MQ	LGA	DTW	105	502	6	5	2013-01-01 06:00:00
2013	1	1	606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	152	1085	6	10	2013-01-01 06:00:00
2013	1	1	606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	128	760	6	10	2013-01-01 06:00:00
2013	1	1	607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	157	1085	6	7	2013-01-01 06:00:00
2013	1	1	608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	139	719	6	0	2013-01-01 06:00:00
2013	1	1	611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	366	2586	6	0	2013-01-01 06:00:00
2013	1	1	613	610	3	925	921	4	B6	135	N635JB	JFK	RSW	175	1074	6	10	2013-01-01 06:00:00
2013	1	1	615	615	0	1039	1100	-21	B6	709	N794JB	JFK	SJU	182	1598	6	15	2013-01-01 06:00:00
2013	1	1	615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	120	746	6	15	2013-01-01 06:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	2123	2125	-2	2223	2247	-24	EV	5489	N712EV	LGA	CHO	45	305	21	25	2013-09-30 21:00:00
2013	9	30	2127	2129	-2	2314	2323	-9	EV	3833	N16546	EWR	CLT	72	529	21	29	2013-09-30 21:00:00
2013	9	30	2128	2130	-2	2328	2359	-31	B6	97	N807JB	JFK	DEN	213	1626	21	30	2013-09-30 21:00:00
2013	9	30	2129	2059	30	2230	2232	-2	EV	5048	N751EV	LGA	RIC	45	292	20	59	2013-09-30 20:00:00
2013	9	30	2131	2140	-9	2225	2255	-30	MQ	3621	N807MQ	JFK	DCA	36	213	21	40	2013-09-30 21:00:00
2013	9	30	2140	2140	0	10	40	-30	AA	185	N335AA	JFK	LAX	298	2475	21	40	2013-09-30 21:00:00
2013	9	30	2142	2129	13	2250	2239	11	EV	4509	N12957	EWR	PWM	47	284	21	29	2013-09-30 21:00:00
2013	9	30	2145	2145	0	115	140	-25	B6	1103	N633JB	JFK	SJU	192	1598	21	45	2013-09-30 21:00:00
2013	9	30	2147	2137	10	30	27	3	B6	1371	N627JB	LGA	FLL	139	1076	21	37	2013-09-30 21:00:00
2013	9	30	2149	2156	-7	2245	2308	-23	UA	523	N813UA	EWR	BOS	37	200	21	56	2013-09-30 21:00:00
2013	9	30	2150	2159	-9	2250	2306	-16	EV	3842	N10575	EWR	MHT	39	209	21	59	2013-09-30 21:00:00
2013	9	30	2159	1845	194	2344	2030	194	9E	3320	N906XJ	JFK	BUF	50	301	18	45	2013-09-30 18:00:00
2013	9	30	2203	2205	-2	2339	2331	8	EV	5311	N722EV	LGA	BGR	61	378	22	5	2013-09-30 22:00:00
2013	9	30	2207	2140	27	2257	2250	7	MQ	3660	N532MQ	LGA	BNA	97	764	21	40	2013-09-30 21:00:00
2013	9	30	2211	2059	72	2339	2242	57	EV	4672	N12145	EWR	STL	120	872	20	59	2013-09-30 20:00:00
2013	9	30	2231	2245	-14	2335	2356	-21	B6	108	N193JB	JFK	PWM	48	273	22	45	2013-09-30 22:00:00
2013	9	30	2233	2113	80	112	30	42	UA	471	N578UA	EWR	SFO	318	2565	21	13	2013-09-30 21:00:00
2013	9	30	2235	2001	154	59	2249	130	B6	1083	N804JB	JFK	MCO	123	944	20	1	2013-09-30 20:00:00
2013	9	30	2237	2245	-8	2345	2353	-8	B6	234	N318JB	JFK	BTV	43	266	22	45	2013-09-30 22:00:00
2013	9	30	2240	2245	-5	2334	2351	-17	B6	1816	N354JB	JFK	SYR	41	209	22	45	2013-09-30 22:00:00
2013	9	30	2240	2250	-10	2347	7	-20	B6	2002	N281JB	JFK	BUF	52	301	22	50	2013-09-30 22:00:00
2013	9	30	2241	2246	-5	2345	1	-16	B6	486	N346JB	JFK	ROC	47	264	22	46	2013-09-30 22:00:00
2013	9	30	2307	2255	12	2359	2358	1	B6	718	N565JB	JFK	BOS	33	187	22	55	2013-09-30 22:00:00
2013	9	30	2349	2359	-10	325	350	-25	B6	745	N516JB	JFK	PSE	196	1617	23	59	2013-09-30 23:00:00
2013	9	30	NA	1842	NA	NA	2019	NA	EV	5274	N740EV	LGA	BNA	NA	764	18	42	2013-09-30 18:00:00
2013	9	30	NA	1455	NA	NA	1634	NA	9E	3393	NA	JFK	DCA	NA	213	14	55	2013-09-30 14:00:00
2013	9	30	NA	2200	NA	NA	2312	NA	9E	3525	NA	LGA	SYR	NA	198	22	0	2013-09-30 22:00:00
2013	9	30	NA	1210	NA	NA	1330	NA	MQ	3461	N535MQ	LGA	BNA	NA	764	12	10	2013-09-30 12:00:00
2013	9	30	NA	1159	NA	NA	1344	NA	MQ	3572	N511MQ	LGA	CLE	NA	419	11	59	2013-09-30 11:00:00
2013	9	30	NA	840	NA	NA	1020	NA	MQ	3531	N839MQ	LGA	RDU	NA	431	8	40	2013-09-30 08:00:00

If you want to move certain variables to the start of the data frame but not drop the others, you can do this in two ways: using select() in conjunction with the everything() helper or using relocate().

In [32]:

select(flights, time_hour, air_time, everything())

select(flights, time_hour, air_time, everything())

A tibble: 336776 × 19

time_hour	air_time	year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	distance	hour	minute
<dttm>	<dbl>	<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>
2013-01-01 05:00:00	227	2013	1	1	517	515	2	830	819	11	UA	1545	N14228	EWR	IAH	1400	5	15
2013-01-01 05:00:00	227	2013	1	1	533	529	4	850	830	20	UA	1714	N24211	LGA	IAH	1416	5	29
2013-01-01 05:00:00	160	2013	1	1	542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	1089	5	40
2013-01-01 05:00:00	183	2013	1	1	544	545	-1	1004	1022	-18	B6	725	N804JB	JFK	BQN	1576	5	45
2013-01-01 06:00:00	116	2013	1	1	554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	762	6	0
2013-01-01 05:00:00	150	2013	1	1	554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	719	5	58
2013-01-01 06:00:00	158	2013	1	1	555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	1065	6	0
2013-01-01 06:00:00	53	2013	1	1	557	600	-3	709	723	-14	EV	5708	N829AS	LGA	IAD	229	6	0
2013-01-01 06:00:00	140	2013	1	1	557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	944	6	0
2013-01-01 06:00:00	138	2013	1	1	558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	733	6	0
2013-01-01 06:00:00	149	2013	1	1	558	600	-2	849	851	-2	B6	49	N793JB	JFK	PBI	1028	6	0
2013-01-01 06:00:00	158	2013	1	1	558	600	-2	853	856	-3	B6	71	N657JB	JFK	TPA	1005	6	0
2013-01-01 06:00:00	345	2013	1	1	558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	2475	6	0
2013-01-01 06:00:00	361	2013	1	1	558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	2565	6	0
2013-01-01 06:00:00	257	2013	1	1	559	600	-1	941	910	31	AA	707	N3DUAA	LGA	DFW	1389	6	0
2013-01-01 05:00:00	44	2013	1	1	559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	187	5	59
2013-01-01 06:00:00	337	2013	1	1	559	600	-1	854	902	-8	UA	1187	N76515	EWR	LAS	2227	6	0
2013-01-01 06:00:00	152	2013	1	1	600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	1076	6	0
2013-01-01 06:00:00	134	2013	1	1	600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	762	6	0
2013-01-01 06:00:00	147	2013	1	1	601	600	1	844	850	-6	B6	343	N644JB	EWR	PBI	1023	6	0
2013-01-01 06:00:00	170	2013	1	1	602	610	-8	812	820	-8	DL	1919	N971DL	LGA	MSP	1020	6	10
2013-01-01 06:00:00	105	2013	1	1	602	605	-3	821	805	16	MQ	4401	N730MQ	LGA	DTW	502	6	5
2013-01-01 06:00:00	152	2013	1	1	606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	1085	6	10
2013-01-01 06:00:00	128	2013	1	1	606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	760	6	10
2013-01-01 06:00:00	157	2013	1	1	607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	1085	6	7
2013-01-01 06:00:00	139	2013	1	1	608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	719	6	0
2013-01-01 06:00:00	366	2013	1	1	611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	2586	6	0
2013-01-01 06:00:00	175	2013	1	1	613	610	3	925	921	4	B6	135	N635JB	JFK	RSW	1074	6	10
2013-01-01 06:00:00	182	2013	1	1	615	615	0	1039	1100	-21	B6	709	N794JB	JFK	SJU	1598	6	15
2013-01-01 06:00:00	120	2013	1	1	615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	746	6	15
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013-09-30 21:00:00	45	2013	9	30	2123	2125	-2	2223	2247	-24	EV	5489	N712EV	LGA	CHO	305	21	25
2013-09-30 21:00:00	72	2013	9	30	2127	2129	-2	2314	2323	-9	EV	3833	N16546	EWR	CLT	529	21	29
2013-09-30 21:00:00	213	2013	9	30	2128	2130	-2	2328	2359	-31	B6	97	N807JB	JFK	DEN	1626	21	30
2013-09-30 20:00:00	45	2013	9	30	2129	2059	30	2230	2232	-2	EV	5048	N751EV	LGA	RIC	292	20	59
2013-09-30 21:00:00	36	2013	9	30	2131	2140	-9	2225	2255	-30	MQ	3621	N807MQ	JFK	DCA	213	21	40
2013-09-30 21:00:00	298	2013	9	30	2140	2140	0	10	40	-30	AA	185	N335AA	JFK	LAX	2475	21	40
2013-09-30 21:00:00	47	2013	9	30	2142	2129	13	2250	2239	11	EV	4509	N12957	EWR	PWM	284	21	29
2013-09-30 21:00:00	192	2013	9	30	2145	2145	0	115	140	-25	B6	1103	N633JB	JFK	SJU	1598	21	45
2013-09-30 21:00:00	139	2013	9	30	2147	2137	10	30	27	3	B6	1371	N627JB	LGA	FLL	1076	21	37
2013-09-30 21:00:00	37	2013	9	30	2149	2156	-7	2245	2308	-23	UA	523	N813UA	EWR	BOS	200	21	56
2013-09-30 21:00:00	39	2013	9	30	2150	2159	-9	2250	2306	-16	EV	3842	N10575	EWR	MHT	209	21	59
2013-09-30 18:00:00	50	2013	9	30	2159	1845	194	2344	2030	194	9E	3320	N906XJ	JFK	BUF	301	18	45
2013-09-30 22:00:00	61	2013	9	30	2203	2205	-2	2339	2331	8	EV	5311	N722EV	LGA	BGR	378	22	5
2013-09-30 21:00:00	97	2013	9	30	2207	2140	27	2257	2250	7	MQ	3660	N532MQ	LGA	BNA	764	21	40
2013-09-30 20:00:00	120	2013	9	30	2211	2059	72	2339	2242	57	EV	4672	N12145	EWR	STL	872	20	59
2013-09-30 22:00:00	48	2013	9	30	2231	2245	-14	2335	2356	-21	B6	108	N193JB	JFK	PWM	273	22	45
2013-09-30 21:00:00	318	2013	9	30	2233	2113	80	112	30	42	UA	471	N578UA	EWR	SFO	2565	21	13
2013-09-30 20:00:00	123	2013	9	30	2235	2001	154	59	2249	130	B6	1083	N804JB	JFK	MCO	944	20	1
2013-09-30 22:00:00	43	2013	9	30	2237	2245	-8	2345	2353	-8	B6	234	N318JB	JFK	BTV	266	22	45
2013-09-30 22:00:00	41	2013	9	30	2240	2245	-5	2334	2351	-17	B6	1816	N354JB	JFK	SYR	209	22	45
2013-09-30 22:00:00	52	2013	9	30	2240	2250	-10	2347	7	-20	B6	2002	N281JB	JFK	BUF	301	22	50
2013-09-30 22:00:00	47	2013	9	30	2241	2246	-5	2345	1	-16	B6	486	N346JB	JFK	ROC	264	22	46
2013-09-30 22:00:00	33	2013	9	30	2307	2255	12	2359	2358	1	B6	718	N565JB	JFK	BOS	187	22	55
2013-09-30 23:00:00	196	2013	9	30	2349	2359	-10	325	350	-25	B6	745	N516JB	JFK	PSE	1617	23	59
2013-09-30 18:00:00	NA	2013	9	30	NA	1842	NA	NA	2019	NA	EV	5274	N740EV	LGA	BNA	764	18	42
2013-09-30 14:00:00	NA	2013	9	30	NA	1455	NA	NA	1634	NA	9E	3393	NA	JFK	DCA	213	14	55
2013-09-30 22:00:00	NA	2013	9	30	NA	2200	NA	NA	2312	NA	9E	3525	NA	LGA	SYR	198	22	0
2013-09-30 12:00:00	NA	2013	9	30	NA	1210	NA	NA	1330	NA	MQ	3461	N535MQ	LGA	BNA	764	12	10
2013-09-30 11:00:00	NA	2013	9	30	NA	1159	NA	NA	1344	NA	MQ	3572	N511MQ	LGA	CLE	419	11	59
2013-09-30 08:00:00	NA	2013	9	30	NA	840	NA	NA	1020	NA	MQ	3531	N839MQ	LGA	RDU	431	8	40

Exercises¶

1. Brainstorm as many ways as possible to select dep_time, dep_delay, arr_time, and arr_delay from flights.

2. What happens if you include the name of a variable multiple times in a select() call?

3. What does the any_of() function do? Why might it be helpful in conjunction with this vector?

   variables <- c("year", "month", "day", "dep_delay", "arr_delay")
   

1. Does the result of running the following code surprise you? How do the select helpers deal with case by default? How can you change that default?

   select(flights, contains("TIME"))
   

Add new variables with mutate()¶

Besides selecting sets of existing columns, it's often useful to add new columns that are functions of existing columns. That's the job of mutate().

mutate() always adds new columns at the end of your dataset so we'll start by creating a narrower dataset so we can see the new variables. Remember that when you're in RStudio, the easiest way to see all the columns is View().

In [33]:

flights_sml <- select(flights, 
  year:day, 
  ends_with("delay"), 
  distance, 
  air_time
)

flights_sml <- select(flights, year:day, ends_with("delay"), distance, air_time )

In [34]:

mutate(flights_sml,
  gain = dep_delay - arr_delay,
  speed = distance / air_time * 60
)

mutate(flights_sml, gain = dep_delay - arr_delay, speed = distance / air_time * 60 )

A tibble: 336776 × 9

year	month	day	dep_delay	arr_delay	distance	air_time	gain	speed
<int>	<int>	<int>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>
2013	1	1	2	11	1400	227	-9	370.0441
2013	1	1	4	20	1416	227	-16	374.2731
2013	1	1	2	33	1089	160	-31	408.3750
2013	1	1	-1	-18	1576	183	17	516.7213
2013	1	1	-6	-25	762	116	19	394.1379
2013	1	1	-4	12	719	150	-16	287.6000
2013	1	1	-5	19	1065	158	-24	404.4304
2013	1	1	-3	-14	229	53	11	259.2453
2013	1	1	-3	-8	944	140	5	404.5714
2013	1	1	-2	8	733	138	-10	318.6957
2013	1	1	-2	-2	1028	149	0	413.9597
2013	1	1	-2	-3	1005	158	1	381.6456
2013	1	1	-2	7	2475	345	-9	430.4348
2013	1	1	-2	-14	2565	361	12	426.3158
2013	1	1	-1	31	1389	257	-32	324.2802
2013	1	1	0	-4	187	44	4	255.0000
2013	1	1	-1	-8	2227	337	7	396.4985
2013	1	1	0	-7	1076	152	7	424.7368
2013	1	1	0	12	762	134	-12	341.1940
2013	1	1	1	-6	1023	147	7	417.5510
2013	1	1	-8	-8	1020	170	0	360.0000
2013	1	1	-3	16	502	105	-19	286.8571
2013	1	1	-4	-12	1085	152	8	428.2895
2013	1	1	-4	-8	760	128	4	356.2500
2013	1	1	0	-17	1085	157	17	414.6497
2013	1	1	8	32	719	139	-24	310.3597
2013	1	1	11	14	2586	366	-3	423.9344
2013	1	1	3	4	1074	175	-1	368.2286
2013	1	1	0	-21	1598	182	21	526.8132
2013	1	1	0	-9	746	120	9	373.0000
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	-2	-24	305	45	22	406.6667
2013	9	30	-2	-9	529	72	7	440.8333
2013	9	30	-2	-31	1626	213	29	458.0282
2013	9	30	30	-2	292	45	32	389.3333
2013	9	30	-9	-30	213	36	21	355.0000
2013	9	30	0	-30	2475	298	30	498.3221
2013	9	30	13	11	284	47	2	362.5532
2013	9	30	0	-25	1598	192	25	499.3750
2013	9	30	10	3	1076	139	7	464.4604
2013	9	30	-7	-23	200	37	16	324.3243
2013	9	30	-9	-16	209	39	7	321.5385
2013	9	30	194	194	301	50	0	361.2000
2013	9	30	-2	8	378	61	-10	371.8033
2013	9	30	27	7	764	97	20	472.5773
2013	9	30	72	57	872	120	15	436.0000
2013	9	30	-14	-21	273	48	7	341.2500
2013	9	30	80	42	2565	318	38	483.9623
2013	9	30	154	130	944	123	24	460.4878
2013	9	30	-8	-8	266	43	0	371.1628
2013	9	30	-5	-17	209	41	12	305.8537
2013	9	30	-10	-20	301	52	10	347.3077
2013	9	30	-5	-16	264	47	11	337.0213
2013	9	30	12	1	187	33	11	340.0000
2013	9	30	-10	-25	1617	196	15	495.0000
2013	9	30	NA	NA	764	NA	NA	NA
2013	9	30	NA	NA	213	NA	NA	NA
2013	9	30	NA	NA	198	NA	NA	NA
2013	9	30	NA	NA	764	NA	NA	NA
2013	9	30	NA	NA	419	NA	NA	NA
2013	9	30	NA	NA	431	NA	NA	NA

Note that you can refer to columns that you've just created:

In [35]:

mutate(flights_sml,
  gain = dep_delay - arr_delay,
  hours = air_time / 60,
  gain_per_hour = gain / hours
)

mutate(flights_sml, gain = dep_delay - arr_delay, hours = air_time / 60, gain_per_hour = gain / hours )

A tibble: 336776 × 10

year	month	day	dep_delay	arr_delay	distance	air_time	gain	hours	gain_per_hour
<int>	<int>	<int>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>
2013	1	1	2	11	1400	227	-9	3.7833333	-2.3788546
2013	1	1	4	20	1416	227	-16	3.7833333	-4.2290749
2013	1	1	2	33	1089	160	-31	2.6666667	-11.6250000
2013	1	1	-1	-18	1576	183	17	3.0500000	5.5737705
2013	1	1	-6	-25	762	116	19	1.9333333	9.8275862
2013	1	1	-4	12	719	150	-16	2.5000000	-6.4000000
2013	1	1	-5	19	1065	158	-24	2.6333333	-9.1139241
2013	1	1	-3	-14	229	53	11	0.8833333	12.4528302
2013	1	1	-3	-8	944	140	5	2.3333333	2.1428571
2013	1	1	-2	8	733	138	-10	2.3000000	-4.3478261
2013	1	1	-2	-2	1028	149	0	2.4833333	0.0000000
2013	1	1	-2	-3	1005	158	1	2.6333333	0.3797468
2013	1	1	-2	7	2475	345	-9	5.7500000	-1.5652174
2013	1	1	-2	-14	2565	361	12	6.0166667	1.9944598
2013	1	1	-1	31	1389	257	-32	4.2833333	-7.4708171
2013	1	1	0	-4	187	44	4	0.7333333	5.4545455
2013	1	1	-1	-8	2227	337	7	5.6166667	1.2462908
2013	1	1	0	-7	1076	152	7	2.5333333	2.7631579
2013	1	1	0	12	762	134	-12	2.2333333	-5.3731343
2013	1	1	1	-6	1023	147	7	2.4500000	2.8571429
2013	1	1	-8	-8	1020	170	0	2.8333333	0.0000000
2013	1	1	-3	16	502	105	-19	1.7500000	-10.8571429
2013	1	1	-4	-12	1085	152	8	2.5333333	3.1578947
2013	1	1	-4	-8	760	128	4	2.1333333	1.8750000
2013	1	1	0	-17	1085	157	17	2.6166667	6.4968153
2013	1	1	8	32	719	139	-24	2.3166667	-10.3597122
2013	1	1	11	14	2586	366	-3	6.1000000	-0.4918033
2013	1	1	3	4	1074	175	-1	2.9166667	-0.3428571
2013	1	1	0	-21	1598	182	21	3.0333333	6.9230769
2013	1	1	0	-9	746	120	9	2.0000000	4.5000000
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	-2	-24	305	45	22	0.7500000	29.333333
2013	9	30	-2	-9	529	72	7	1.2000000	5.833333
2013	9	30	-2	-31	1626	213	29	3.5500000	8.169014
2013	9	30	30	-2	292	45	32	0.7500000	42.666667
2013	9	30	-9	-30	213	36	21	0.6000000	35.000000
2013	9	30	0	-30	2475	298	30	4.9666667	6.040268
2013	9	30	13	11	284	47	2	0.7833333	2.553191
2013	9	30	0	-25	1598	192	25	3.2000000	7.812500
2013	9	30	10	3	1076	139	7	2.3166667	3.021583
2013	9	30	-7	-23	200	37	16	0.6166667	25.945946
2013	9	30	-9	-16	209	39	7	0.6500000	10.769231
2013	9	30	194	194	301	50	0	0.8333333	0.000000
2013	9	30	-2	8	378	61	-10	1.0166667	-9.836066
2013	9	30	27	7	764	97	20	1.6166667	12.371134
2013	9	30	72	57	872	120	15	2.0000000	7.500000
2013	9	30	-14	-21	273	48	7	0.8000000	8.750000
2013	9	30	80	42	2565	318	38	5.3000000	7.169811
2013	9	30	154	130	944	123	24	2.0500000	11.707317
2013	9	30	-8	-8	266	43	0	0.7166667	0.000000
2013	9	30	-5	-17	209	41	12	0.6833333	17.560976
2013	9	30	-10	-20	301	52	10	0.8666667	11.538462
2013	9	30	-5	-16	264	47	11	0.7833333	14.042553
2013	9	30	12	1	187	33	11	0.5500000	20.000000
2013	9	30	-10	-25	1617	196	15	3.2666667	4.591837
2013	9	30	NA	NA	764	NA	NA	NA	NA
2013	9	30	NA	NA	213	NA	NA	NA	NA
2013	9	30	NA	NA	198	NA	NA	NA	NA
2013	9	30	NA	NA	764	NA	NA	NA	NA
2013	9	30	NA	NA	419	NA	NA	NA	NA
2013	9	30	NA	NA	431	NA	NA	NA	NA

If you only want to keep the new variables, use transmute():

In [36]:

transmute(flights,
  gain = dep_delay - arr_delay,
  hours = air_time / 60,
  gain_per_hour = gain / hours
)

transmute(flights, gain = dep_delay - arr_delay, hours = air_time / 60, gain_per_hour = gain / hours )

A tibble: 336776 × 3

gain	hours	gain_per_hour
<dbl>	<dbl>	<dbl>
-9	3.7833333	-2.3788546
-16	3.7833333	-4.2290749
-31	2.6666667	-11.6250000
17	3.0500000	5.5737705
19	1.9333333	9.8275862
-16	2.5000000	-6.4000000
-24	2.6333333	-9.1139241
11	0.8833333	12.4528302
5	2.3333333	2.1428571
-10	2.3000000	-4.3478261
0	2.4833333	0.0000000
1	2.6333333	0.3797468
-9	5.7500000	-1.5652174
12	6.0166667	1.9944598
-32	4.2833333	-7.4708171
4	0.7333333	5.4545455
7	5.6166667	1.2462908
7	2.5333333	2.7631579
-12	2.2333333	-5.3731343
7	2.4500000	2.8571429
0	2.8333333	0.0000000
-19	1.7500000	-10.8571429
8	2.5333333	3.1578947
4	2.1333333	1.8750000
17	2.6166667	6.4968153
-24	2.3166667	-10.3597122
-3	6.1000000	-0.4918033
-1	2.9166667	-0.3428571
21	3.0333333	6.9230769
9	2.0000000	4.5000000
⋮	⋮	⋮
22	0.7500000	29.333333
7	1.2000000	5.833333
29	3.5500000	8.169014
32	0.7500000	42.666667
21	0.6000000	35.000000
30	4.9666667	6.040268
2	0.7833333	2.553191
25	3.2000000	7.812500
7	2.3166667	3.021583
16	0.6166667	25.945946
7	0.6500000	10.769231
0	0.8333333	0.000000
-10	1.0166667	-9.836066
20	1.6166667	12.371134
15	2.0000000	7.500000
7	0.8000000	8.750000
38	5.3000000	7.169811
24	2.0500000	11.707317
0	0.7166667	0.000000
12	0.6833333	17.560976
10	0.8666667	11.538462
11	0.7833333	14.042553
11	0.5500000	20.000000
15	3.2666667	4.591837
NA	NA	NA
NA	NA	NA
NA	NA	NA
NA	NA	NA
NA	NA	NA
NA	NA	NA

Useful creation functions¶

There are many functions for creating new variables that you can use with mutate(). The key property is that the function must be vectorised: it must take a vector of values as input, return a vector with the same number of values as output. There's no way to list every possible function that you might use, but here's a selection of functions that are frequently useful:

• Arithmetic operators: +, -, *, /, ^. These are all vectorised, using the so called "recycling rules". If one parameter is shorter than the other, it will be automatically extended to be the same length. This is most useful when one of the arguments is a single number: air_time / 60, hours * 60 + minute, etc.

  Arithmetic operators are also useful in conjunction with the aggregate functions you'll learn about later. For example, x / sum(x) calculates the proportion of a total, and y - mean(y) computes the difference from the mean.

• Modular arithmetic: %/% (integer division) and %% (remainder), where x == y * (x %/% y) + (x %% y). Modular arithmetic is a handy tool because it allows you to break integers up into pieces. For example, in the flights dataset, you can compute hour and minute from dep_time with:

In [37]:

transmute(flights,
  dep_time,
  hour = dep_time %/% 100,
  minute = dep_time %% 100
)

transmute(flights, dep_time, hour = dep_time %/% 100, minute = dep_time %% 100 )

A tibble: 336776 × 3

dep_time	hour	minute
<int>	<dbl>	<dbl>
517	5	17
533	5	33
542	5	42
544	5	44
554	5	54
554	5	54
555	5	55
557	5	57
557	5	57
558	5	58
558	5	58
558	5	58
558	5	58
558	5	58
559	5	59
559	5	59
559	5	59
600	6	0
600	6	0
601	6	1
602	6	2
602	6	2
606	6	6
606	6	6
607	6	7
608	6	8
611	6	11
613	6	13
615	6	15
615	6	15
⋮	⋮	⋮
2123	21	23
2127	21	27
2128	21	28
2129	21	29
2131	21	31
2140	21	40
2142	21	42
2145	21	45
2147	21	47
2149	21	49
2150	21	50
2159	21	59
2203	22	3
2207	22	7
2211	22	11
2231	22	31
2233	22	33
2235	22	35
2237	22	37
2240	22	40
2240	22	40
2241	22	41
2307	23	7
2349	23	49
NA	NA	NA
NA	NA	NA
NA	NA	NA
NA	NA	NA
NA	NA	NA
NA	NA	NA

• Logs: log(), log2(), log10(). Logarithms are an incredibly useful transformation for dealing with data that ranges across multiple orders of magnitude. They also convert multiplicative relationships to additive.

  All else being equal, I recommend using log2() because it's easy to interpret: a difference of 1 on the log scale corresponds to doubling on the original scale and a difference of -1 corresponds to halving.

• Offsets: lead() and lag() allow you to refer to leading or lagging values. This allows you to compute running differences (e.g. x - lag(x)) or find when values change (x != lag(x)). They are most useful in conjunction with group_by(), which you'll learn about shortly.

In [38]:

(x <- 1:10)

(x <- 1:10)

1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. 7
8. 8
9. 9
10. 10

In [39]:

lag(x)

lag(x)

1. <NA>
2. 1
3. 2
4. 3
5. 4
6. 5
7. 6
8. 7
9. 8
10. 9

In [40]:

lead(x)

lead(x)

1. 2
2. 3
3. 4
4. 5
5. 6
6. 7
7. 8
8. 9
9. 10
10. <NA>

• Cumulative and rolling aggregates: R provides functions for running sums, products, mins and maxes: cumsum(), cumprod(), cummin(), cummax(); and dplyr provides cummean() for cumulative means. If you need rolling aggregates (i.e. a sum computed over a rolling window), try the RcppRoll package.

In [41]:

x

x

1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. 7
8. 8
9. 9
10. 10

In [42]:

cumsum(x)

cumsum(x)

1. 1
2. 3
3. 6
4. 10
5. 15
6. 21
7. 28
8. 36
9. 45
10. 55

In [43]:

cummean(x)

cummean(x)

1. 1
2. 1.5
3. 2
4. 2.5
5. 3
6. 3.5
7. 4
8. 4.5
9. 5
10. 5.5

• Logical comparisons: <, <=, >, >=, !=, and ==, which you learned about earlier. If you're doing a complex sequence of logical operations it's often a good idea to store the interim values in new variables so you can check that each step is working as expected.

• Ranking: there are a number of ranking functions, but you should start with min_rank(). It does the most usual type of ranking (e.g. 1st, 2nd, 2nd, 4th). The default gives smallest values the small ranks; use desc(x) to give the largest values the smallest ranks.

In [44]:

y <- c(1, 2, 2, NA, 3, 4)

y <- c(1, 2, 2, NA, 3, 4)

In [45]:

min_rank(y)

min_rank(y)

1. 1
2. 2
3. 2
4. <NA>
5. 4
6. 5

In [46]:

min_rank(desc(y))

min_rank(desc(y))

1. 5
2. 3
3. 3
4. <NA>
5. 2
6. 1

If min_rank() doesn't do what you need, look at the variants row_number(), dense_rank(), percent_rank(), cume_dist(), ntile(). See their help pages for more details.

In [47]:

row_number(y)

row_number(y)

1. 1
2. 2
3. 3
4. <NA>
5. 4
6. 5

In [48]:

dense_rank(y)

dense_rank(y)

1. 1
2. 2
3. 2
4. <NA>
5. 3
6. 4

In [49]:

percent_rank(y)

percent_rank(y)

1. 0
2. 0.25
3. 0.25
4. <NA>
5. 0.75
6. 1

In [50]:

cume_dist(y)

cume_dist(y)

1. 0.2
2. 0.6
3. 0.6
4. <NA>
5. 0.8
6. 1

Exercises¶

1. Currently dep_time and sched_dep_time are convenient to look at, but hard to compute with because they're not really continuous numbers. Convert them to a more convenient representation of number of minutes since midnight.

2. Compare air_time with arr_time - dep_time. What do you expect to see? What do you see? What do you need to do to fix it?

3. Compare dep_time, sched_dep_time, and dep_delay. How would you expect those three numbers to be related?

4. Find the 10 most delayed flights using a ranking function. How do you want to handle ties? Carefully read the documentation for min_rank().

5. What does 1:3 + 1:10 return? Why?

6. What trigonometric functions does R provide?

Grouped summaries with summarise()¶

The last key verb is summarise(). It collapses a data frame to a single row:

In [51]:

summarise(flights, delay = mean(dep_delay, na.rm = TRUE))

summarise(flights, delay = mean(dep_delay, na.rm = TRUE))

A tibble: 1 × 1

delay
<dbl>
12.63907

(We'll come back to what that na.rm = TRUE means very shortly.)

summarise() is not terribly useful unless we pair it with group_by(). This changes the unit of analysis from the complete dataset to individual groups. Then, when you use the dplyr verbs on a grouped data frame they'll be automatically applied "by group". For example, if we applied exactly the same code to a data frame grouped by month, we get the average delay per month:

In [52]:

by_month <- group_by(flights, month)
summarise(by_month, delay = mean(dep_delay, na.rm = TRUE))

by_month <- group_by(flights, month) summarise(by_month, delay = mean(dep_delay, na.rm = TRUE))

A tibble: 12 × 2

month	delay
<int>	<dbl>
1	10.036665
2	10.816843
3	13.227076
4	13.938038
5	12.986859
6	20.846332
7	21.727787
8	12.611040
9	6.722476
10	6.243988
11	5.435362
12	16.576688

Together group_by() and summarise() provide one of the tools that you'll use most commonly when working with dplyr: grouped summaries. But before we go any further with this, we need to introduce a powerful new idea: the pipe.

Combining multiple operations with the pipe¶

Imagine that we want to explore the relationship between the distance and average delay for each location. Using what you know about dplyr, you might write code like this:

In [53]:

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")

# It looks like delays increase with distance up to ~750 miles 
# and then decrease. Maybe as flights get longer there's more 
# ability to make up delays in the air?
ggplot(data = delay, mapping = aes(x = dist, y = delay)) +
  geom_point(aes(size = count), alpha = 1/3) +
  geom_smooth(se = FALSE)

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") # It looks like delays increase with distance up to ~750 miles # and then decrease. Maybe as flights get longer there's more # ability to make up delays in the air? ggplot(data = delay, 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'

There are three steps to prepare this data:

1. Group flights by destination.

2. Summarise to compute distance, average delay, and number of flights.

3. Filter to remove noisy points and Honolulu airport, which is almost twice as far away as the next closest airport.

This code is a little frustrating to write because we have to give each intermediate data frame a name, even though we don't care about them. Naming things is hard, so this slows down our analysis.

There's another way to tackle the same problem with the pipe, %>%:

In [54]:

sdelays <- 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")

sdelays <- 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")

This focuses on the transformations, not what's being transformed, which makes the code easier to read. You can read it as a series of imperative statements: group, then summarise, then filter. As suggested by this reading, a good way to pronounce %>% when reading code is "then".

Behind the scenes, x %>% f(y) turns into f(x, y), and x %>% f(y) %>% g(z) turns into g(f(x, y), z) and so on. You can use the pipe to rewrite multiple operations in a way that you can read left-to-right, top-to-bottom. We'll use piping frequently from now on because it considerably improves the readability of code, and we'll come back to it in more detail in Chapter pipes.

Working with the pipe is one of the key criteria for belonging to the tidyverse. The only exception is ggplot2: it was written before the pipe was discovered. Unfortunately, the next iteration of ggplot2, ggvis, which does use the pipe, isn't quite ready for prime time yet.

Missing values¶

You may have wondered about the na.rm argument we used above. What happens if we don't set it?

In [55]:

flights %>% 
  group_by(month) %>% 
  summarise(mean = mean(dep_delay))

flights %>% group_by(month) %>% summarise(mean = mean(dep_delay))

A tibble: 12 × 2

month	mean
<int>	<dbl>
1	NA
2	NA
3	NA
4	NA
5	NA
6	NA
7	NA
8	NA
9	NA
10	NA
11	NA
12	NA

We get a lot of missing values! That's because aggregation functions obey the usual rule of missing values: if there's any missing value in the input, the output will be a missing value. Fortunately, all aggregation functions have an na.rm argument which removes the missing values prior to computation:

In [56]:

flights %>% 
  group_by(month) %>% 
  summarise(mean = mean(dep_delay, na.rm = TRUE))

flights %>% group_by(month) %>% summarise(mean = mean(dep_delay, na.rm = TRUE))

A tibble: 12 × 2

month	mean
<int>	<dbl>
1	10.036665
2	10.816843
3	13.227076
4	13.938038
5	12.986859
6	20.846332
7	21.727787
8	12.611040
9	6.722476
10	6.243988
11	5.435362
12	16.576688

In this case, missing values represent cancelled flights, therefore we could also tackle the problem by first removing the cancelled flights. We'll save this dataset so we can reuse it in the next few examples.

In [57]:

not_cancelled <- flights %>% 
  filter(!is.na(dep_delay), !is.na(arr_delay))

not_cancelled <- flights %>% filter(!is.na(dep_delay), !is.na(arr_delay))

In [58]:

not_cancelled %>% 
  group_by(month) %>% 
  summarise(mean = mean(dep_delay))

not_cancelled %>% group_by(month) %>% summarise(mean = mean(dep_delay))

A tibble: 12 × 2

month	mean
<int>	<dbl>
1	9.985491
2	10.760239
3	13.164289
4	13.849187
5	12.891709
6	20.725614
7	21.522179
8	12.570524
9	6.630285
10	6.233175
11	5.420340
12	16.482161

Grouping by multiple variables¶

You can group a data frame by multiple variables as well. Note that the grouping information is printed on top of the output. The number in the square brackets indicates how many groups are created.

In [59]:

daily <- group_by(flights, year, month, day)
daily

daily <- group_by(flights, year, month, day) daily

A grouped_df: 336776 × 19

year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	1	517	515	2	830	819	11	UA	1545	N14228	EWR	IAH	227	1400	5	15	2013-01-01 05:00:00
2013	1	1	533	529	4	850	830	20	UA	1714	N24211	LGA	IAH	227	1416	5	29	2013-01-01 05:00:00
2013	1	1	542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	160	1089	5	40	2013-01-01 05:00:00
2013	1	1	544	545	-1	1004	1022	-18	B6	725	N804JB	JFK	BQN	183	1576	5	45	2013-01-01 05:00:00
2013	1	1	554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	116	762	6	0	2013-01-01 06:00:00
2013	1	1	554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	150	719	5	58	2013-01-01 05:00:00
2013	1	1	555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	158	1065	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	709	723	-14	EV	5708	N829AS	LGA	IAD	53	229	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	140	944	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	138	733	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	849	851	-2	B6	49	N793JB	JFK	PBI	149	1028	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	853	856	-3	B6	71	N657JB	JFK	TPA	158	1005	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	345	2475	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	361	2565	6	0	2013-01-01 06:00:00
2013	1	1	559	600	-1	941	910	31	AA	707	N3DUAA	LGA	DFW	257	1389	6	0	2013-01-01 06:00:00
2013	1	1	559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	44	187	5	59	2013-01-01 05:00:00
2013	1	1	559	600	-1	854	902	-8	UA	1187	N76515	EWR	LAS	337	2227	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	152	1076	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	134	762	6	0	2013-01-01 06:00:00
2013	1	1	601	600	1	844	850	-6	B6	343	N644JB	EWR	PBI	147	1023	6	0	2013-01-01 06:00:00
2013	1	1	602	610	-8	812	820	-8	DL	1919	N971DL	LGA	MSP	170	1020	6	10	2013-01-01 06:00:00
2013	1	1	602	605	-3	821	805	16	MQ	4401	N730MQ	LGA	DTW	105	502	6	5	2013-01-01 06:00:00
2013	1	1	606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	152	1085	6	10	2013-01-01 06:00:00
2013	1	1	606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	128	760	6	10	2013-01-01 06:00:00
2013	1	1	607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	157	1085	6	7	2013-01-01 06:00:00
2013	1	1	608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	139	719	6	0	2013-01-01 06:00:00
2013	1	1	611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	366	2586	6	0	2013-01-01 06:00:00
2013	1	1	613	610	3	925	921	4	B6	135	N635JB	JFK	RSW	175	1074	6	10	2013-01-01 06:00:00
2013	1	1	615	615	0	1039	1100	-21	B6	709	N794JB	JFK	SJU	182	1598	6	15	2013-01-01 06:00:00
2013	1	1	615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	120	746	6	15	2013-01-01 06:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	2123	2125	-2	2223	2247	-24	EV	5489	N712EV	LGA	CHO	45	305	21	25	2013-09-30 21:00:00
2013	9	30	2127	2129	-2	2314	2323	-9	EV	3833	N16546	EWR	CLT	72	529	21	29	2013-09-30 21:00:00
2013	9	30	2128	2130	-2	2328	2359	-31	B6	97	N807JB	JFK	DEN	213	1626	21	30	2013-09-30 21:00:00
2013	9	30	2129	2059	30	2230	2232	-2	EV	5048	N751EV	LGA	RIC	45	292	20	59	2013-09-30 20:00:00
2013	9	30	2131	2140	-9	2225	2255	-30	MQ	3621	N807MQ	JFK	DCA	36	213	21	40	2013-09-30 21:00:00
2013	9	30	2140	2140	0	10	40	-30	AA	185	N335AA	JFK	LAX	298	2475	21	40	2013-09-30 21:00:00
2013	9	30	2142	2129	13	2250	2239	11	EV	4509	N12957	EWR	PWM	47	284	21	29	2013-09-30 21:00:00
2013	9	30	2145	2145	0	115	140	-25	B6	1103	N633JB	JFK	SJU	192	1598	21	45	2013-09-30 21:00:00
2013	9	30	2147	2137	10	30	27	3	B6	1371	N627JB	LGA	FLL	139	1076	21	37	2013-09-30 21:00:00
2013	9	30	2149	2156	-7	2245	2308	-23	UA	523	N813UA	EWR	BOS	37	200	21	56	2013-09-30 21:00:00
2013	9	30	2150	2159	-9	2250	2306	-16	EV	3842	N10575	EWR	MHT	39	209	21	59	2013-09-30 21:00:00
2013	9	30	2159	1845	194	2344	2030	194	9E	3320	N906XJ	JFK	BUF	50	301	18	45	2013-09-30 18:00:00
2013	9	30	2203	2205	-2	2339	2331	8	EV	5311	N722EV	LGA	BGR	61	378	22	5	2013-09-30 22:00:00
2013	9	30	2207	2140	27	2257	2250	7	MQ	3660	N532MQ	LGA	BNA	97	764	21	40	2013-09-30 21:00:00
2013	9	30	2211	2059	72	2339	2242	57	EV	4672	N12145	EWR	STL	120	872	20	59	2013-09-30 20:00:00
2013	9	30	2231	2245	-14	2335	2356	-21	B6	108	N193JB	JFK	PWM	48	273	22	45	2013-09-30 22:00:00
2013	9	30	2233	2113	80	112	30	42	UA	471	N578UA	EWR	SFO	318	2565	21	13	2013-09-30 21:00:00
2013	9	30	2235	2001	154	59	2249	130	B6	1083	N804JB	JFK	MCO	123	944	20	1	2013-09-30 20:00:00
2013	9	30	2237	2245	-8	2345	2353	-8	B6	234	N318JB	JFK	BTV	43	266	22	45	2013-09-30 22:00:00
2013	9	30	2240	2245	-5	2334	2351	-17	B6	1816	N354JB	JFK	SYR	41	209	22	45	2013-09-30 22:00:00
2013	9	30	2240	2250	-10	2347	7	-20	B6	2002	N281JB	JFK	BUF	52	301	22	50	2013-09-30 22:00:00
2013	9	30	2241	2246	-5	2345	1	-16	B6	486	N346JB	JFK	ROC	47	264	22	46	2013-09-30 22:00:00
2013	9	30	2307	2255	12	2359	2358	1	B6	718	N565JB	JFK	BOS	33	187	22	55	2013-09-30 22:00:00
2013	9	30	2349	2359	-10	325	350	-25	B6	745	N516JB	JFK	PSE	196	1617	23	59	2013-09-30 23:00:00
2013	9	30	NA	1842	NA	NA	2019	NA	EV	5274	N740EV	LGA	BNA	NA	764	18	42	2013-09-30 18:00:00
2013	9	30	NA	1455	NA	NA	1634	NA	9E	3393	NA	JFK	DCA	NA	213	14	55	2013-09-30 14:00:00
2013	9	30	NA	2200	NA	NA	2312	NA	9E	3525	NA	LGA	SYR	NA	198	22	0	2013-09-30 22:00:00
2013	9	30	NA	1210	NA	NA	1330	NA	MQ	3461	N535MQ	LGA	BNA	NA	764	12	10	2013-09-30 12:00:00
2013	9	30	NA	1159	NA	NA	1344	NA	MQ	3572	N511MQ	LGA	CLE	NA	419	11	59	2013-09-30 11:00:00
2013	9	30	NA	840	NA	NA	1020	NA	MQ	3531	N839MQ	LGA	RDU	NA	431	8	40	2013-09-30 08:00:00

When you group by multiple variables, each summary peels off one level of the grouping by default, and a message is printed that tells you how you can change this behaviour.

In [60]:

summarise(daily, flights = n())

summarise(daily, flights = n())

`summarise()` has grouped output by 'year', 'month'. You can override using the
`.groups` argument.

A grouped_df: 365 × 4

year	month	day	flights
<int>	<int>	<int>	<int>
2013	1	1	842
2013	1	2	943
2013	1	3	914
2013	1	4	915
2013	1	5	720
2013	1	6	832
2013	1	7	933
2013	1	8	899
2013	1	9	902
2013	1	10	932
2013	1	11	930
2013	1	12	690
2013	1	13	828
2013	1	14	928
2013	1	15	894
2013	1	16	901
2013	1	17	927
2013	1	18	924
2013	1	19	674
2013	1	20	786
2013	1	21	912
2013	1	22	890
2013	1	23	897
2013	1	24	925
2013	1	25	922
2013	1	26	680
2013	1	27	823
2013	1	28	923
2013	1	29	890
2013	1	30	900
⋮	⋮	⋮	⋮
2013	12	2	1004
2013	12	3	973
2013	12	4	958
2013	12	5	969
2013	12	6	970
2013	12	7	691
2013	12	8	875
2013	12	9	962
2013	12	10	943
2013	12	11	954
2013	12	12	968
2013	12	13	970
2013	12	14	692
2013	12	15	880
2013	12	16	964
2013	12	17	949
2013	12	18	956
2013	12	19	974
2013	12	20	980
2013	12	21	811
2013	12	22	895
2013	12	23	985
2013	12	24	761
2013	12	25	719
2013	12	26	936
2013	12	27	963
2013	12	28	814
2013	12	29	888
2013	12	30	968
2013	12	31	776

If you're happy with this behaviour, you can also explicitly define it, in which case the message won't be printed out.

In [61]:

summarise(daily, flights = n(), .groups = "drop_last")

summarise(daily, flights = n(), .groups = "drop_last")

A grouped_df: 365 × 4

year	month	day	flights
<int>	<int>	<int>	<int>
2013	1	1	842
2013	1	2	943
2013	1	3	914
2013	1	4	915
2013	1	5	720
2013	1	6	832
2013	1	7	933
2013	1	8	899
2013	1	9	902
2013	1	10	932
2013	1	11	930
2013	1	12	690
2013	1	13	828
2013	1	14	928
2013	1	15	894
2013	1	16	901
2013	1	17	927
2013	1	18	924
2013	1	19	674
2013	1	20	786
2013	1	21	912
2013	1	22	890
2013	1	23	897
2013	1	24	925
2013	1	25	922
2013	1	26	680
2013	1	27	823
2013	1	28	923
2013	1	29	890
2013	1	30	900
⋮	⋮	⋮	⋮
2013	12	2	1004
2013	12	3	973
2013	12	4	958
2013	12	5	969
2013	12	6	970
2013	12	7	691
2013	12	8	875
2013	12	9	962
2013	12	10	943
2013	12	11	954
2013	12	12	968
2013	12	13	970
2013	12	14	692
2013	12	15	880
2013	12	16	964
2013	12	17	949
2013	12	18	956
2013	12	19	974
2013	12	20	980
2013	12	21	811
2013	12	22	895
2013	12	23	985
2013	12	24	761
2013	12	25	719
2013	12	26	936
2013	12	27	963
2013	12	28	814
2013	12	29	888
2013	12	30	968
2013	12	31	776

Or you can change the default behaviour by setting a different value, e.g. "drop" for dropping all levels of grouping or "keep" for same grouping structure as daily.

In [63]:

# Note the difference between the grouping structures
summarise(daily, flights = n(), .groups = "drop")

# Note the difference between the grouping structures summarise(daily, flights = n(), .groups = "drop")

A tibble: 365 × 4

year	month	day	flights
<int>	<int>	<int>	<int>
2013	1	1	842
2013	1	2	943
2013	1	3	914
2013	1	4	915
2013	1	5	720
2013	1	6	832
2013	1	7	933
2013	1	8	899
2013	1	9	902
2013	1	10	932
2013	1	11	930
2013	1	12	690
2013	1	13	828
2013	1	14	928
2013	1	15	894
2013	1	16	901
2013	1	17	927
2013	1	18	924
2013	1	19	674
2013	1	20	786
2013	1	21	912
2013	1	22	890
2013	1	23	897
2013	1	24	925
2013	1	25	922
2013	1	26	680
2013	1	27	823
2013	1	28	923
2013	1	29	890
2013	1	30	900
⋮	⋮	⋮	⋮
2013	12	2	1004
2013	12	3	973
2013	12	4	958
2013	12	5	969
2013	12	6	970
2013	12	7	691
2013	12	8	875
2013	12	9	962
2013	12	10	943
2013	12	11	954
2013	12	12	968
2013	12	13	970
2013	12	14	692
2013	12	15	880
2013	12	16	964
2013	12	17	949
2013	12	18	956
2013	12	19	974
2013	12	20	980
2013	12	21	811
2013	12	22	895
2013	12	23	985
2013	12	24	761
2013	12	25	719
2013	12	26	936
2013	12	27	963
2013	12	28	814
2013	12	29	888
2013	12	30	968
2013	12	31	776

In [64]:

summarise(daily, flights = n(), .groups = "keep")

summarise(daily, flights = n(), .groups = "keep")

A grouped_df: 365 × 4

year	month	day	flights
<int>	<int>	<int>	<int>
2013	1	1	842
2013	1	2	943
2013	1	3	914
2013	1	4	915
2013	1	5	720
2013	1	6	832
2013	1	7	933
2013	1	8	899
2013	1	9	902
2013	1	10	932
2013	1	11	930
2013	1	12	690
2013	1	13	828
2013	1	14	928
2013	1	15	894
2013	1	16	901
2013	1	17	927
2013	1	18	924
2013	1	19	674
2013	1	20	786
2013	1	21	912
2013	1	22	890
2013	1	23	897
2013	1	24	925
2013	1	25	922
2013	1	26	680
2013	1	27	823
2013	1	28	923
2013	1	29	890
2013	1	30	900
⋮	⋮	⋮	⋮
2013	12	2	1004
2013	12	3	973
2013	12	4	958
2013	12	5	969
2013	12	6	970
2013	12	7	691
2013	12	8	875
2013	12	9	962
2013	12	10	943
2013	12	11	954
2013	12	12	968
2013	12	13	970
2013	12	14	692
2013	12	15	880
2013	12	16	964
2013	12	17	949
2013	12	18	956
2013	12	19	974
2013	12	20	980
2013	12	21	811
2013	12	22	895
2013	12	23	985
2013	12	24	761
2013	12	25	719
2013	12	26	936
2013	12	27	963
2013	12	28	814
2013	12	29	888
2013	12	30	968
2013	12	31	776

The fact that each summary peels off one level of the grouping by default makes it easy to progressively roll up a dataset:

In [68]:

(per_day   <- summarise(daily, flights = n()))
(per_month <- summarise(per_day, flights = sum(flights)))
(per_year  <- summarise(per_month, flights = sum(flights)))

(per_day <- summarise(daily, flights = n())) (per_month <- summarise(per_day, flights = sum(flights))) (per_year <- summarise(per_month, flights = sum(flights)))

`summarise()` has grouped output by 'year', 'month'. You can override using the
`.groups` argument.

A grouped_df: 365 × 4

year	month	day	flights
<int>	<int>	<int>	<int>
2013	1	1	842
2013	1	2	943
2013	1	3	914
2013	1	4	915
2013	1	5	720
2013	1	6	832
2013	1	7	933
2013	1	8	899
2013	1	9	902
2013	1	10	932
2013	1	11	930
2013	1	12	690
2013	1	13	828
2013	1	14	928
2013	1	15	894
2013	1	16	901
2013	1	17	927
2013	1	18	924
2013	1	19	674
2013	1	20	786
2013	1	21	912
2013	1	22	890
2013	1	23	897
2013	1	24	925
2013	1	25	922
2013	1	26	680
2013	1	27	823
2013	1	28	923
2013	1	29	890
2013	1	30	900
⋮	⋮	⋮	⋮
2013	12	2	1004
2013	12	3	973
2013	12	4	958
2013	12	5	969
2013	12	6	970
2013	12	7	691
2013	12	8	875
2013	12	9	962
2013	12	10	943
2013	12	11	954
2013	12	12	968
2013	12	13	970
2013	12	14	692
2013	12	15	880
2013	12	16	964
2013	12	17	949
2013	12	18	956
2013	12	19	974
2013	12	20	980
2013	12	21	811
2013	12	22	895
2013	12	23	985
2013	12	24	761
2013	12	25	719
2013	12	26	936
2013	12	27	963
2013	12	28	814
2013	12	29	888
2013	12	30	968
2013	12	31	776

`summarise()` has grouped output by 'year'. You can override using the
`.groups` argument.

A grouped_df: 12 × 3

year	month	flights
<int>	<int>	<int>
2013	1	27004
2013	2	24951
2013	3	28834
2013	4	28330
2013	5	28796
2013	6	28243
2013	7	29425
2013	8	29327
2013	9	27574
2013	10	28889
2013	11	27268
2013	12	28135

A tibble: 1 × 2

year	flights
<int>	<int>
2013	336776

Be careful when progressively rolling up summaries: it's OK for sums and counts, but you need to think about weighting means and variances, and it's not possible to do it exactly for rank-based statistics like the median. In other words, the sum of groupwise sums is the overall sum, but the median of groupwise medians is not the overall median.

Ungrouping¶

You might also want to remove grouping outside of summarise(). You can do this and return to operations on ungrouped data using ungroup().

In [69]:

daily %>% 
  ungroup() %>%             # no longer grouped by date
  summarise(flights = n())  # all flights

daily %>% ungroup() %>% # no longer grouped by date summarise(flights = n()) # all flights

A tibble: 1 × 1

flights
<int>
336776

Counts¶

Whenever you do any aggregation, it's always a good idea to include either a count (n()), or a count of non-missing values (sum(!is.na(x))). That way you can check that you're not drawing conclusions based on very small amounts of data. For example, let's look at the planes (identified by their tail number) that have the highest average delays:

In [70]:

delays <- not_cancelled %>% 
  group_by(tailnum) %>% 
  summarise(
    delay = mean(arr_delay)
  )

ggplot(data = delays, mapping = aes(x = delay)) + 
  geom_freqpoly(binwidth = 10)

delays <- not_cancelled %>% group_by(tailnum) %>% summarise( delay = mean(arr_delay) ) ggplot(data = delays, mapping = aes(x = delay)) + geom_freqpoly(binwidth = 10)

Wow, there are some planes that have an average delay of 5 hours (300 minutes)!

The story is actually a little more nuanced. We can get more insight if we draw a scatterplot of number of flights vs. average delay:

In [71]:

delays <- not_cancelled %>% 
  group_by(tailnum) %>% 
  summarise(
    delay = mean(arr_delay),
    n = n()
  )

ggplot(data = delays, mapping = aes(x = n, y = delay)) + 
  geom_point(alpha = 1/10)

delays <- not_cancelled %>% group_by(tailnum) %>% summarise( delay = mean(arr_delay), n = n() ) ggplot(data = delays, mapping = aes(x = n, y = delay)) + geom_point(alpha = 1/10)

Not surprisingly, there is much greater variation in the average delay when there are few flights. The shape of this plot is very characteristic: whenever you plot a mean (or other summary) vs. group size, you'll see that the variation decreases as the sample size increases.

When looking at this sort of plot, it's often useful to filter out the groups with the smallest numbers of observations, so you can see more of the pattern and less of the extreme variation in the smallest groups. This is what the following code does, as well as showing you a handy pattern for integrating ggplot2 into dplyr flows. It's a bit painful that you have to switch from %>% to +, but once you get the hang of it, it's quite convenient.

In [72]:

delays %>% 
  filter(n > 25) %>% 
  ggplot(mapping = aes(x = n, y = delay)) + 
  geom_point(alpha = 1/10)

delays %>% filter(n > 25) %>% ggplot(mapping = aes(x = n, y = delay)) + geom_point(alpha = 1/10)

---

RStudio tip: a useful keyboard shortcut is Cmd/Ctrl + Shift + P. This resends the previously sent chunk from the editor to the console. This is very convenient when you're (e.g.) exploring the value of n in the example above. You send the whole block once with Cmd/Ctrl + Enter, then you modify the value of n and press Cmd/Ctrl + Shift + P to resend the complete block.

---

There's another common variation of this type of pattern. Let's look at how the average performance of batters in baseball is related to the number of times they're at bat. Here I use data from the Lahman package to compute the batting average (number of hits / number of attempts) of every major league baseball player.

When I plot the skill of the batter (measured by the batting average, ba) against the number of opportunities to hit the ball (measured by at bat, ab), you see two patterns:

1. As above, the variation in our aggregate decreases as we get more data points.

2. There's a positive correlation between skill (ba) and opportunities to hit the ball (ab). This is because teams control who gets to play, and obviously they'll pick their best players.

# Convert to a tibble so it prints nicely
batting <- as_tibble(Lahman::Batting)

batters <- batting %>% 
  group_by(playerID) %>% 
  summarise(
    ba = sum(H, na.rm = TRUE) / sum(AB, na.rm = TRUE),
    ab = sum(AB, na.rm = TRUE)
  )

batters %>% 
  filter(ab > 100) %>% 
  ggplot(mapping = aes(x = ab, y = ba)) +
    geom_point() + 
    geom_smooth(se = FALSE)

This also has important implications for ranking. If you naively sort on desc(ba), the people with the best batting averages are clearly lucky, not skilled:

batters %>% 
  arrange(desc(ba))
#> # A tibble: 19,689 x 3
#>   playerID     ba    ab
#>   <chr>     <dbl> <int>
#> 1 abramge01     1     1
#> 2 alanirj01     1     1
#> 3 alberan01     1     1
#> 4 banisje01     1     1
#> 5 bartocl01     1     1
#> 6 bassdo01      1     1
#> # … with 19,683 more rows

You can find a good explanation of this problem at http://varianceexplained.org/r/empirical_bayes_baseball/ and http://www.evanmiller.org/how-not-to-sort-by-average-rating.html.

Useful summary functions¶

Just using means, counts, and sum can get you a long way, but R provides many other useful summary functions:

• Measures of location: we've used mean(x), but median(x) is also useful. The mean is the sum divided by the length; the median is a value where 50% of x is above it, and 50% is below it.

In [74]:

not_cancelled %>%
  group_by(month) %>%
  summarise(
    med_arr_delay = median(arr_delay),
    med_dep_delay = median(dep_delay)
    )

not_cancelled %>% group_by(month) %>% summarise( med_arr_delay = median(arr_delay), med_dep_delay = median(dep_delay) )

A tibble: 12 × 3

month	med_arr_delay	med_dep_delay
<int>	<dbl>	<dbl>
1	-3	-2
2	-3	-2
3	-6	-1
4	-2	-2
5	-8	-1
6	-2	0
7	-2	0
8	-5	-1
9	-12	-3
10	-7	-3
11	-6	-3
12	2	0

It's sometimes useful to combine aggregation with logical subsetting. We haven't talked about this sort of subsetting yet, but you'll learn more about it in vectors (Subsetting).

In [75]:

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
  )

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 )

`summarise()` has grouped output by 'year', 'month'. You can override using the
`.groups` argument.

A grouped_df: 365 × 5

year	month	day	avg_delay1	avg_delay2
<int>	<int>	<int>	<dbl>	<dbl>
2013	1	1	12.6510229	32.48156
2013	1	2	12.6928879	32.02991
2013	1	3	5.7333333	27.66087
2013	1	4	-1.9328194	28.30976
2013	1	5	-1.5258020	22.55882
2013	1	6	4.2364294	24.37270
2013	1	7	-4.9473118	27.76132
2013	1	8	-3.2275785	20.78909
2013	1	9	-0.2642777	25.63415
2013	1	10	-5.8988159	27.34545
2013	1	11	-4.7622683	26.15984
2013	1	12	-13.0161527	23.47170
2013	1	13	14.9318463	52.54891
2013	1	14	3.6403034	22.44444
2013	1	15	0.4256527	19.71003
2013	1	16	34.2473623	46.08346
2013	1	17	6.4928962	25.60268
2013	1	18	1.8417582	25.05382
2013	1	19	-8.5260805	24.28906
2013	1	20	3.7400768	29.02913
2013	1	21	6.3159645	32.24359
2013	1	22	12.2768362	33.88323
2013	1	23	6.9177928	33.05181
2013	1	24	15.4273128	42.08824
2013	1	25	27.0989761	55.73694
2013	1	26	0.7511177	29.05833
2013	1	27	-1.3089330	36.02479
2013	1	28	9.3197200	38.70361
2013	1	29	-6.5558113	30.13333
2013	1	30	25.9108040	57.42889
⋮	⋮	⋮	⋮	⋮
2013	12	2	0.4509018	31.55488
2013	12	3	2.2227979	24.45926
2013	12	4	-0.2222222	28.40643
2013	12	5	51.6662546	80.83571
2013	12	6	21.1194690	37.51089
2013	12	7	5.0570571	27.18707
2013	12	8	36.9118012	56.16000
2013	12	9	42.5755556	53.07383
2013	12	10	44.5087957	52.19440
2013	12	11	10.9024390	24.90559
2013	12	12	5.3399374	23.46320
2013	12	13	0.1630322	22.14121
2013	12	14	46.3975045	59.97802
2013	12	15	15.8700980	38.98678
2013	12	16	7.3706806	27.81385
2013	12	17	55.8718563	60.46795
2013	12	18	11.0798319	26.59503
2013	12	19	9.0062112	31.04008
2013	12	20	13.8201663	35.24905
2013	12	21	12.4569288	36.85539
2013	12	22	23.8995485	45.64875
2013	12	23	32.2260417	50.03397
2013	12	24	-1.0438830	21.16961
2013	12	25	-1.8993007	30.09500
2013	12	26	7.1741935	34.54869
2013	12	27	-0.1488033	29.04683
2013	12	28	-3.2595326	25.60769
2013	12	29	18.7638249	47.25636
2013	12	30	10.0577125	31.24380
2013	12	31	6.2121212	24.45596

• Measures of spread: sd(x), IQR(x), mad(x). The root mean squared deviation, or standard deviation sd(x), is the standard measure of spread. The interquartile range IQR(x) and median absolute deviation mad(x) are robust equivalents that may be more useful if you have outliers.

In [76]:

# Why is distance to some destinations more variable than to others?
not_cancelled %>% 
  group_by(dest) %>% 
  summarise(distance_sd = sd(distance)) %>% 
  arrange(desc(distance_sd))

# Why is distance to some destinations more variable than to others? not_cancelled %>% group_by(dest) %>% summarise(distance_sd = sd(distance)) %>% arrange(desc(distance_sd))

A tibble: 104 × 2

dest	distance_sd
<chr>	<dbl>
EGE	10.542765
SAN	10.350094
SFO	10.216017
HNL	10.004197
SEA	9.977993
LAS	9.907786
PDX	9.873299
PHX	9.862546
LAX	9.657195
IND	9.458066
CVG	9.018212
SAT	9.005084
MEM	8.503128
HOU	8.467711
MCI	8.358227
AUS	8.319169
CLE	8.312362
DFW	8.298037
IAH	7.999074
DEN	7.981110
TYS	7.979272
STL	7.916233
BNA	7.811418
ORD	7.726294
PIT	7.724053
BUF	7.683876
DTW	7.543633
ROC	7.395674
SLC	7.357458
SDF	7.337980
⋮	⋮
PHL	2.74711414
BTV	2.50718881
BHM	0.06097108
ABQ	0.00000000
ACK	0.00000000
ALB	0.00000000
ANC	0.00000000
BDL	0.00000000
BGR	0.00000000
BUR	0.00000000
BZN	0.00000000
CAK	0.00000000
CHO	0.00000000
CRW	0.00000000
EYW	0.00000000
HDN	0.00000000
ILM	0.00000000
LGB	0.00000000
MTJ	0.00000000
MVY	0.00000000
OAK	0.00000000
OKC	0.00000000
PSE	0.00000000
PSP	0.00000000
PVD	0.00000000
SJC	0.00000000
SMF	0.00000000
SNA	0.00000000
TUL	0.00000000
LEX	NA

• Measures of rank: min(x), quantile(x, 0.25), max(x). Quantiles are a generalisation of the median. For example, quantile(x, 0.25) will find a value of x that is greater than 25% of the values, and less than the remaining 75%.

In [77]:

# 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)
  )

# 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) )

`summarise()` has grouped output by 'year', 'month'. You can override using the
`.groups` argument.

A grouped_df: 365 × 5

year	month	day	first	last
<int>	<int>	<int>	<int>	<int>
2013	1	1	517	2356
2013	1	2	42	2354
2013	1	3	32	2349
2013	1	4	25	2358
2013	1	5	14	2357
2013	1	6	16	2355
2013	1	7	49	2359
2013	1	8	454	2351
2013	1	9	2	2252
2013	1	10	3	2320
2013	1	11	11	2304
2013	1	12	30	2359
2013	1	13	1	2359
2013	1	14	453	2353
2013	1	15	453	2356
2013	1	16	2	2358
2013	1	17	453	2352
2013	1	18	455	2359
2013	1	19	456	2359
2013	1	20	525	2356
2013	1	21	455	2352
2013	1	22	5	2358
2013	1	23	158	2358
2013	1	24	37	2350
2013	1	25	15	2359
2013	1	26	107	2352
2013	1	27	523	2355
2013	1	28	10	2356
2013	1	29	448	2354
2013	1	30	3	2354
⋮	⋮	⋮	⋮	⋮
2013	12	2	12	2358
2013	12	3	457	2358
2013	12	4	457	2355
2013	12	5	16	2400
2013	12	6	6	2357
2013	12	7	3	2358
2013	12	8	16	2354
2013	12	9	11	2400
2013	12	10	7	2348
2013	12	11	459	2359
2013	12	12	2	2356
2013	12	13	15	2400
2013	12	14	3	2359
2013	12	15	5	2359
2013	12	16	1	2332
2013	12	17	4	2358
2013	12	18	500	2358
2013	12	19	17	2400
2013	12	20	1	2358
2013	12	21	2	2357
2013	12	22	7	2358
2013	12	23	11	2354
2013	12	24	9	2358
2013	12	25	456	2357
2013	12	26	1	2355
2013	12	27	2	2351
2013	12	28	7	2358
2013	12	29	3	2400
2013	12	30	1	2356
2013	12	31	13	2356

• Measures of position: first(x), nth(x, 2), last(x). These work similarly to x[1], x[2], and x[length(x)] but let you set a default value if that position does not exist (i.e. you're trying to get the 3rd element from a group that only has two elements). For example, we can find the first and last departure for each day:

In [78]:

not_cancelled %>% 
  group_by(year, month, day) %>% 
  summarise(
    first_dep = first(dep_time), 
    last_dep = last(dep_time)
  )

not_cancelled %>% group_by(year, month, day) %>% summarise( first_dep = first(dep_time), last_dep = last(dep_time) )

`summarise()` has grouped output by 'year', 'month'. You can override using the
`.groups` argument.

A grouped_df: 365 × 5

year	month	day	first_dep	last_dep
<int>	<int>	<int>	<int>	<int>
2013	1	1	517	2356
2013	1	2	42	2354
2013	1	3	32	2349
2013	1	4	25	2358
2013	1	5	14	2357
2013	1	6	16	2355
2013	1	7	49	2359
2013	1	8	454	2351
2013	1	9	2	2252
2013	1	10	3	2320
2013	1	11	11	2304
2013	1	12	30	2359
2013	1	13	1	2359
2013	1	14	453	2353
2013	1	15	453	2356
2013	1	16	2	2358
2013	1	17	453	2352
2013	1	18	455	2359
2013	1	19	456	2359
2013	1	20	525	2356
2013	1	21	455	2352
2013	1	22	5	2358
2013	1	23	158	2358
2013	1	24	37	2350
2013	1	25	15	2359
2013	1	26	107	2352
2013	1	27	523	2355
2013	1	28	10	2356
2013	1	29	448	2354
2013	1	30	3	2354
⋮	⋮	⋮	⋮	⋮
2013	12	2	12	2358
2013	12	3	457	2358
2013	12	4	457	2355
2013	12	5	16	2400
2013	12	6	6	2357
2013	12	7	3	2358
2013	12	8	16	2354
2013	12	9	11	2400
2013	12	10	7	2348
2013	12	11	459	2359
2013	12	12	2	2356
2013	12	13	15	2400
2013	12	14	3	2359
2013	12	15	5	2359
2013	12	16	1	2332
2013	12	17	4	2358
2013	12	18	500	2358
2013	12	19	17	2400
2013	12	20	1	2358
2013	12	21	2	2357
2013	12	22	7	2358
2013	12	23	11	2354
2013	12	24	9	2358
2013	12	25	456	2357
2013	12	26	1	2355
2013	12	27	2	2351
2013	12	28	7	2358
2013	12	29	3	2400
2013	12	30	1	2356
2013	12	31	13	2356

These functions are complementary to filtering on ranks. Filtering gives you all variables, with each observation in a separate row:

In [79]:

not_cancelled %>% 
  group_by(year, month, day) %>% 
  mutate(r = min_rank(desc(dep_time))) %>% 
  filter(r %in% range(r))

not_cancelled %>% group_by(year, month, day) %>% mutate(r = min_rank(desc(dep_time))) %>% filter(r %in% range(r))

A grouped_df: 770 × 20

year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour	r
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>	<int>
2013	1	1	517	515	2	830	819	11	UA	1545	N14228	EWR	IAH	227	1400	5	15	2013-01-01 05:00:00	831
2013	1	1	2356	2359	-3	425	437	-12	B6	727	N588JB	JFK	BQN	186	1576	23	59	2013-01-01 23:00:00	1
2013	1	2	42	2359	43	518	442	36	B6	707	N580JB	JFK	SJU	189	1598	23	59	2013-01-02 23:00:00	928
2013	1	2	2354	2359	-5	413	437	-24	B6	727	N789JB	JFK	BQN	180	1576	23	59	2013-01-02 23:00:00	1
2013	1	3	32	2359	33	504	442	22	B6	707	N763JB	JFK	SJU	193	1598	23	59	2013-01-03 23:00:00	900
2013	1	3	2349	2359	-10	434	445	-11	B6	739	N729JB	JFK	PSE	199	1617	23	59	2013-01-03 23:00:00	1
2013	1	4	25	2359	26	505	442	23	B6	707	N554JB	JFK	SJU	194	1598	23	59	2013-01-04 23:00:00	908
2013	1	4	2358	2359	-1	429	437	-8	B6	727	N599JB	JFK	BQN	189	1576	23	59	2013-01-04 23:00:00	1
2013	1	4	2358	2359	-1	436	445	-9	B6	739	N821JB	JFK	PSE	199	1617	23	59	2013-01-04 23:00:00	1
2013	1	5	14	2359	15	503	445	18	B6	739	N592JB	JFK	PSE	201	1617	23	59	2013-01-05 23:00:00	717
2013	1	5	2357	2359	-2	432	437	-5	B6	727	N649JB	JFK	BQN	195	1576	23	59	2013-01-05 23:00:00	1
2013	1	6	16	2359	17	451	442	9	B6	707	N606JB	JFK	SJU	197	1598	23	59	2013-01-06 23:00:00	829
2013	1	6	2355	2359	-4	433	437	-4	B6	727	N708JB	JFK	BQN	199	1576	23	59	2013-01-06 23:00:00	1
2013	1	7	49	2359	50	531	444	47	B6	739	N598JB	JFK	PSE	202	1617	23	59	2013-01-07 23:00:00	930
2013	1	7	2359	2359	0	506	437	29	B6	727	N805JB	JFK	BQN	196	1576	23	59	2013-01-07 23:00:00	1
2013	1	8	454	500	-6	625	648	-23	US	1117	N564UW	EWR	CLT	77	529	5	0	2013-01-08 05:00:00	892
2013	1	8	2351	2359	-8	414	437	-23	B6	727	N563JB	JFK	BQN	185	1576	23	59	2013-01-08 23:00:00	1
2013	1	8	2351	2359	-8	417	444	-27	B6	739	N592JB	JFK	PSE	190	1617	23	59	2013-01-08 23:00:00	1
2013	1	9	2	2359	3	432	444	-12	B6	739	N603JB	JFK	PSE	193	1617	23	59	2013-01-09 23:00:00	893
2013	1	9	2252	2249	3	12	2357	15	B6	22	N294JB	JFK	SYR	54	209	22	49	2013-01-09 22:00:00	1
2013	1	10	3	2359	4	426	437	-11	B6	727	N571JB	JFK	BQN	183	1576	23	59	2013-01-10 23:00:00	929
2013	1	10	2320	2250	30	16	2354	22	B6	1018	N612JB	JFK	BOS	35	187	22	50	2013-01-10 22:00:00	1
2013	1	11	11	2359	12	436	444	-8	B6	739	N779JB	JFK	PSE	190	1617	23	59	2013-01-11 23:00:00	917
2013	1	11	2304	2125	99	9	2234	95	UA	1066	N87531	EWR	BOS	45	200	21	25	2013-01-11 21:00:00	1
2013	1	12	30	2359	31	506	444	22	B6	739	N705JB	JFK	PSE	192	1617	23	59	2013-01-12 23:00:00	681
2013	1	12	2359	2359	0	429	437	-8	B6	727	N509JB	JFK	BQN	185	1576	23	59	2013-01-12 23:00:00	1
2013	1	13	1	2249	72	108	2357	71	B6	22	N206JB	JFK	SYR	41	209	22	49	2013-01-13 22:00:00	807
2013	1	13	2359	2130	149	435	218	137	B6	701	N337JB	JFK	SJU	189	1598	21	30	2013-01-13 21:00:00	1
2013	1	14	453	500	-7	640	648	-8	US	1117	N558UW	EWR	CLT	92	529	5	0	2013-01-14 05:00:00	923
2013	1	14	2353	2359	-6	429	444	-15	B6	739	N775JB	JFK	PSE	193	1617	23	59	2013-01-14 23:00:00	1
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	16	453	500	-7	631	648	-17	US	1877	N167US	EWR	CLT	82	529	5	0	2013-09-16 05:00:00	983
2013	9	16	2346	2359	-13	330	350	-20	B6	745	N508JB	JFK	PSE	203	1617	23	59	2013-09-16 23:00:00	1
2013	9	17	450	500	-10	624	648	-24	US	1877	N568UW	EWR	CLT	76	529	5	0	2013-09-17 05:00:00	958
2013	9	17	2352	2359	-7	403	350	13	B6	745	N796JB	JFK	PSE	202	1617	23	59	2013-09-17 23:00:00	1
2013	9	18	450	500	-10	620	648	-28	US	1877	N156UW	EWR	CLT	78	529	5	0	2013-09-18 05:00:00	969
2013	9	18	2349	2359	-10	332	350	-18	B6	745	N566JB	JFK	PSE	203	1617	23	59	2013-09-18 23:00:00	1
2013	9	19	10	2103	187	109	2226	163	EV	4119	N16918	EWR	RIC	43	277	21	3	2013-09-19 21:00:00	965
2013	9	19	2353	2359	-6	340	350	-10	B6	745	N760JB	JFK	PSE	201	1617	23	59	2013-09-19 23:00:00	1
2013	9	20	9	2250	79	123	7	76	B6	2002	N190JB	JFK	BUF	48	301	22	50	2013-09-20 22:00:00	980
2013	9	20	2349	2245	64	49	2356	53	B6	108	N328JB	JFK	PWM	44	273	22	45	2013-09-20 22:00:00	1
2013	9	21	8	2359	9	350	350	0	B6	745	N579JB	JFK	PSE	204	1617	23	59	2013-09-21 23:00:00	690
2013	9	21	2306	2255	11	8	2358	10	B6	718	N712JB	JFK	BOS	40	187	22	55	2013-09-21 22:00:00	1
2013	9	22	518	516	2	742	756	-14	UA	830	N529UA	EWR	IAH	185	1400	5	16	2013-09-22 05:00:00	896
2013	9	22	2350	2359	-9	348	350	-2	B6	745	N659JB	JFK	PSE	204	1617	23	59	2013-09-22 23:00:00	1
2013	9	23	455	500	-5	629	648	-19	US	1877	N172US	EWR	CLT	78	529	5	0	2013-09-23 05:00:00	987
2013	9	23	2342	2359	-17	331	350	-19	B6	745	N503JB	JFK	PSE	205	1617	23	59	2013-09-23 23:00:00	1
2013	9	24	455	500	-5	630	648	-18	US	1877	N153UW	EWR	CLT	75	529	5	0	2013-09-24 05:00:00	949
2013	9	24	2348	2359	-11	318	350	-32	B6	745	N570JB	JFK	PSE	190	1617	23	59	2013-09-24 23:00:00	1
2013	9	25	451	500	-9	636	648	-12	US	1877	N151UW	EWR	CLT	84	529	5	0	2013-09-25 05:00:00	964
2013	9	25	2358	2359	-1	357	350	7	B6	745	N729JB	JFK	PSE	216	1617	23	59	2013-09-25 23:00:00	1
2013	9	26	451	500	-9	619	648	-29	US	1877	N155UW	EWR	CLT	72	529	5	0	2013-09-26 05:00:00	992
2013	9	26	2349	2359	-10	332	350	-18	B6	745	N789JB	JFK	PSE	209	1617	23	59	2013-09-26 23:00:00	1
2013	9	27	453	500	-7	627	648	-21	US	1877	N161UW	EWR	CLT	76	529	5	0	2013-09-27 05:00:00	990
2013	9	27	2346	2359	-13	356	350	6	B6	745	N784JB	JFK	PSE	222	1617	23	59	2013-09-27 23:00:00	1
2013	9	28	451	500	-9	627	648	-21	US	1877	N181UW	EWR	CLT	77	529	5	0	2013-09-28 05:00:00	675
2013	9	28	2351	2359	-8	407	350	17	B6	745	N827JB	JFK	PSE	218	1617	23	59	2013-09-28 23:00:00	1
2013	9	29	521	520	1	739	800	-21	UA	322	N564UA	EWR	IAH	179	1400	5	20	2013-09-29 05:00:00	910
2013	9	29	2356	2359	-3	341	350	-9	B6	745	N613JB	JFK	PSE	204	1617	23	59	2013-09-29 23:00:00	1
2013	9	30	453	500	-7	621	648	-27	US	1877	N186US	EWR	CLT	76	529	5	0	2013-09-30 05:00:00	986
2013	9	30	2349	2359	-10	325	350	-25	B6	745	N516JB	JFK	PSE	196	1617	23	59	2013-09-30 23:00:00	1

• Counts: You've seen n(), which takes no arguments, and returns the size of the current group. To count the number of non-missing values, use sum(!is.na(x)). To count the number of distinct (unique) values, use n_distinct(x).

In [80]:

# Which destinations have the most carriers?
not_cancelled %>% 
  group_by(dest) %>% 
  summarise(carriers = n_distinct(carrier)) %>% 
  arrange(desc(carriers))

# Which destinations have the most carriers? not_cancelled %>% group_by(dest) %>% summarise(carriers = n_distinct(carrier)) %>% arrange(desc(carriers))

A tibble: 104 × 2

dest	carriers
<chr>	<int>
ATL	7
BOS	7
CLT	7
ORD	7
TPA	7
AUS	6
DCA	6
DTW	6
IAD	6
MSP	6
MSY	6
PIT	6
STL	6
BNA	5
CLE	5
DEN	5
IND	5
LAS	5
LAX	5
PBI	5
PHL	5
PHX	5
RDU	5
SEA	5
SFO	5
BUF	4
BWI	4
CHS	4
CVG	4
DFW	4
⋮	⋮
XNA	2
ABQ	1
ACK	1
ALB	1
ANC	1
BGR	1
BHM	1
BUR	1
BZN	1
CAK	1
CHO	1
CRW	1
EYW	1
HDN	1
ILM	1
LEX	1
LGB	1
MDW	1
MTJ	1
MYR	1
OAK	1
OKC	1
PSE	1
PSP	1
PVD	1
SAV	1
SBN	1
SMF	1
SNA	1
TUL	1

Counts are so useful that dplyr provides a simple helper if all you want is a count:

In [81]:

not_cancelled %>% 
  count(dest)

not_cancelled %>% count(dest)

A tibble: 104 × 2

dest	n
<chr>	<int>
ABQ	254
ACK	264
ALB	418
ANC	8
ATL	16837
AUS	2411
AVL	261
BDL	412
BGR	358
BHM	269
BNA	6084
BOS	15022
BQN	888
BTV	2510
BUF	4570
BUR	370
BWI	1687
BZN	35
CAE	106
CAK	842
CHO	46
CHS	2759
CLE	4394
CLT	13674
CMH	3326
CRW	134
CVG	3725
DAY	1399
DCA	9111
DEN	7169
⋮	⋮
PIT	2746
PSE	358
PSP	18
PVD	358
PWM	2288
RDU	7770
RIC	2346
ROC	2358
RSW	3502
SAN	2709
SAT	659
SAV	749
SBN	10
SDF	1104
SEA	3885
SFO	13173
SJC	328
SJU	5773
SLC	2451
SMF	282
SNA	812
SRQ	1201
STL	4142
STT	518
SYR	1707
TPA	7390
TUL	294
TVC	95
TYS	578
XNA	992

Just like with group_by(), you can also provide multiple variables to count().

In [82]:

not_cancelled %>% 
  count(carrier, dest)

not_cancelled %>% count(carrier, dest)

A tibble: 312 × 3

carrier	dest	n
<chr>	<chr>	<int>
9E	ATL	56
9E	AUS	2
9E	AVL	10
9E	BNA	452
9E	BOS	853
9E	BTV	2
9E	BUF	789
9E	BWI	815
9E	CAE	3
9E	CHS	331
9E	CLE	321
9E	CLT	271
9E	CMH	11
9E	CVG	1466
9E	DAY	342
9E	DCA	1011
9E	DFW	346
9E	DSM	84
9E	DTW	954
9E	GRR	38
9E	GSO	1
9E	GSP	96
9E	IAD	612
9E	IND	379
9E	JAX	373
9E	LEX	1
9E	MCI	334
9E	MEM	3
9E	MHT	10
9E	MKE	297
⋮	⋮	⋮
UA	SFO	6728
UA	SJU	685
UA	SNA	812
UA	STL	2
UA	STT	188
UA	TPA	1952
US	BOS	4002
US	CLT	8498
US	DCA	4468
US	PHL	623
US	PHX	2240
VX	LAS	364
VX	LAX	2554
VX	PSP	18
VX	SFO	2179
VX	SJC	1
WN	ATL	58
WN	AUS	293
WN	BNA	1273
WN	BWI	200
WN	DEN	1379
WN	HOU	1372
WN	MDW	4025
WN	MKE	1275
WN	MSY	296
WN	PHX	425
WN	STL	1448
YV	CLT	258
YV	IAD	278
YV	PHL	8

You can optionally provide a weight variable. For example, you could use this to "count" (sum) the total number of miles a plane flew:

In [83]:

not_cancelled %>% 
  count(tailnum, wt = distance)

not_cancelled %>% count(tailnum, wt = distance)

A tibble: 4037 × 2

tailnum	n
<chr>	<dbl>
D942DN	3418
N0EGMQ	239143
N10156	109664
N102UW	25722
N103US	24619
N104UW	24616
N10575	139903
N105UW	23618
N107US	21677
N108UW	32070
N109UW	25722
N110UW	21415
N11106	97433
N11107	97466
N11109	99634
N11113	88387
N11119	97933
N11121	104740
N11127	88104
N11137	77555
N11140	105964
N11150	100798
N11155	74745
N11164	94129
N11165	112918
N11176	96808
N11181	84183
N11184	95530
N11187	92231
N11189	93540
⋮	⋮
N985AT	16868
N985DL	57022
N986AT	15709
N986DL	67807
N987AT	16527
N987DL	50758
N988AT	22996
N988DL	45960
N989AT	43473
N989DL	80111
N990AT	51182
N990DL	48330
N991AT	16495
N991DL	83069
N992AT	23449
N992DL	51457
N993AT	28514
N993DL	46812
N994AT	21653
N994DL	54094
N995AT	11081
N995DL	50364
N996AT	17987
N996DL	91023
N997AT	29116
N997DL	53719
N998AT	14670
N998DL	64956
N999DN	54623
N9EAMQ	160862

• Counts and proportions of logical values: sum(x > 10), mean(y == 0). When used with numeric functions, TRUE is converted to 1 and FALSE to 0. This makes sum() and mean() very useful: sum(x) gives the number of TRUEs in x, and mean(x) gives the proportion.

In [86]:

# 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))

# 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))

`summarise()` has grouped output by 'year', 'month'. You can override using the
`.groups` argument.

A grouped_df: 365 × 4

year	month	day	n_early
<int>	<int>	<int>	<int>
2013	1	1	0
2013	1	2	3
2013	1	3	4
2013	1	4	3
2013	1	5	3
2013	1	6	2
2013	1	7	2
2013	1	8	1
2013	1	9	3
2013	1	10	3
2013	1	11	3
2013	1	12	1
2013	1	13	9
2013	1	14	1
2013	1	15	1
2013	1	16	3
2013	1	17	1
2013	1	18	1
2013	1	19	1
2013	1	20	0
2013	1	21	1
2013	1	22	2
2013	1	23	2
2013	1	24	3
2013	1	25	6
2013	1	26	2
2013	1	27	0
2013	1	28	2
2013	1	29	1
2013	1	30	2
⋮	⋮	⋮	⋮
2013	12	2	2
2013	12	3	1
2013	12	4	1
2013	12	5	9
2013	12	6	2
2013	12	7	3
2013	12	8	7
2013	12	9	5
2013	12	10	5
2013	12	11	1
2013	12	12	5
2013	12	13	3
2013	12	14	7
2013	12	15	2
2013	12	16	6
2013	12	17	15
2013	12	18	0
2013	12	19	3
2013	12	20	1
2013	12	21	5
2013	12	22	4
2013	12	23	9
2013	12	24	2
2013	12	25	1
2013	12	26	3
2013	12	27	7
2013	12	28	2
2013	12	29	3
2013	12	30	6
2013	12	31	4

In [88]:

# What proportion of flights are delayed by more than an hour?
not_cancelled %>% 
  group_by(year, month, day) %>% 
  summarise(hour_prop = mean(arr_delay > 60))

# What proportion of flights are delayed by more than an hour? not_cancelled %>% group_by(year, month, day) %>% summarise(hour_prop = mean(arr_delay > 60))

`summarise()` has grouped output by 'year', 'month'. You can override using the
`.groups` argument.

A grouped_df: 365 × 4

year	month	day	hour_prop
<int>	<int>	<int>	<dbl>
2013	1	1	0.07220217
2013	1	2	0.08512931
2013	1	3	0.05666667
2013	1	4	0.03964758
2013	1	5	0.03486750
2013	1	6	0.04704463
2013	1	7	0.03333333
2013	1	8	0.02130045
2013	1	9	0.02015677
2013	1	10	0.01829925
2013	1	11	0.02508179
2013	1	12	0.01027900
2013	1	13	0.14745973
2013	1	14	0.02708559
2013	1	15	0.02724177
2013	1	16	0.19109027
2013	1	17	0.05573770
2013	1	18	0.03516484
2013	1	19	0.02086438
2013	1	20	0.05889885
2013	1	21	0.06651885
2013	1	22	0.09491525
2013	1	23	0.06981982
2013	1	24	0.11453744
2013	1	25	0.17406143
2013	1	26	0.03576751
2013	1	27	0.05583127
2013	1	28	0.09218203
2013	1	29	0.03222094
2013	1	30	0.20351759
⋮	⋮	⋮	⋮
2013	12	2	0.05110220
2013	12	3	0.04041451
2013	12	4	0.03915344
2013	12	5	0.27688504
2013	12	6	0.11836283
2013	12	7	0.04204204
2013	12	8	0.24223602
2013	12	9	0.25777778
2013	12	10	0.26928281
2013	12	11	0.06044539
2013	12	12	0.04066736
2013	12	13	0.02699896
2013	12	14	0.25490196
2013	12	15	0.11274510
2013	12	16	0.05549738
2013	12	17	0.33892216
2013	12	18	0.05147059
2013	12	19	0.06728778
2013	12	20	0.09979210
2013	12	21	0.09612984
2013	12	22	0.16704289
2013	12	23	0.20000000
2013	12	24	0.02260638
2013	12	25	0.03776224
2013	12	26	0.07634409
2013	12	27	0.05306972
2013	12	28	0.03813038
2013	12	29	0.14861751
2013	12	30	0.07240294
2013	12	31	0.04347826

Exercises¶

1. Brainstorm at least 5 different ways to assess the typical delay characteristics of a group of flights. Consider the following scenarios:

   • A flight is 15 minutes early 50% of the time, and 15 minutes late 50% of the time.

   • A flight is always 10 minutes late.

   • A flight is 30 minutes early 50% of the time, and 30 minutes late 50% of the time.

   • 99% of the time a flight is on time. 1% of the time it's 2 hours late.

   Which is more important: arrival delay or departure delay?

2. Come up with another approach that will give you the same output as not_cancelled %>% count(dest) and not_cancelled %>% count(tailnum, wt = distance) (without using count()).

3. Our definition of cancelled flights (is.na(dep_delay) | is.na(arr_delay) ) is slightly suboptimal. Why? Which is the most important column?

4. Look at the number of cancelled flights per day. Is there a pattern? Is the proportion of cancelled flights related to the average delay?

5. Which carrier has the worst delays? Challenge: can you disentangle the effects of bad airports vs. bad carriers? Why/why not? (Hint: think about flights %>% group_by(carrier, dest) %>% summarise(n()))

6. What does the sort argument to count() do. When might you use it?

Grouped mutates and filters¶

Grouping is most useful in conjunction with summarise(), but you can also do convenient operations with mutate() and filter():

• Find the worst members of each group:

In [89]:

flights_sml %>% 
  group_by(year, month, day) %>%
  filter(rank(desc(arr_delay)) < 10)

flights_sml %>% group_by(year, month, day) %>% filter(rank(desc(arr_delay)) < 10)

A grouped_df: 3306 × 7

year	month	day	dep_delay	arr_delay	distance	air_time
<int>	<int>	<int>	<dbl>	<dbl>	<dbl>	<dbl>
2013	1	1	853	851	184	41
2013	1	1	290	338	1134	213
2013	1	1	260	263	266	46
2013	1	1	157	174	213	60
2013	1	1	216	222	708	121
2013	1	1	255	250	589	115
2013	1	1	285	246	1085	146
2013	1	1	192	191	199	44
2013	1	1	379	456	1092	222
2013	1	2	224	207	550	94
2013	1	2	268	288	1092	203
2013	1	2	334	323	937	150
2013	1	2	337	368	2586	346
2013	1	2	168	180	533	102
2013	1	2	180	176	213	49
2013	1	2	164	182	463	103
2013	1	2	379	359	1620	228
2013	1	2	175	252	1325	286
2013	1	3	185	172	301	58
2013	1	3	156	143	1576	186
2013	1	3	144	167	733	133
2013	1	3	174	175	760	140
2013	1	3	174	176	1008	152
2013	1	3	268	270	1069	158
2013	1	3	252	257	1035	151
2013	1	3	291	285	765	125
2013	1	3	158	150	301	61
2013	1	4	137	126	509	92
2013	1	4	177	161	445	69
2013	1	4	155	162	733	118
⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	27	203	187	812	111
2013	9	27	174	182	1092	149
2013	9	27	135	104	764	98
2013	9	28	95	89	509	77
2013	9	28	102	97	273	49
2013	9	28	223	190	488	74
2013	9	28	94	75	1080	141
2013	9	28	61	58	2133	287
2013	9	28	138	115	488	71
2013	9	28	75	60	2586	330
2013	9	28	91	101	589	93
2013	9	28	322	324	340	66
2013	9	29	129	134	200	33
2013	9	29	140	137	946	133
2013	9	29	190	208	502	92
2013	9	29	255	236	1047	137
2013	9	29	199	181	264	44
2013	9	29	131	110	1008	141
2013	9	29	173	141	2475	303
2013	9	29	144	124	431	61
2013	9	29	225	183	1076	129
2013	9	30	132	131	541	91
2013	9	30	145	129	541	76
2013	9	30	294	272	544	79
2013	9	30	129	137	725	110
2013	9	30	158	121	765	95
2013	9	30	182	174	708	95
2013	9	30	158	136	746	91
2013	9	30	194	194	301	50
2013	9	30	154	130	944	123

• Find all groups bigger than a threshold:

In [90]:

popular_dests <- flights %>% 
  group_by(dest) %>% 
  filter(n() > 365)
popular_dests

popular_dests <- flights %>% group_by(dest) %>% filter(n() > 365) popular_dests

A grouped_df: 332577 × 19

year	month	day	dep_time	sched_dep_time	dep_delay	arr_time	sched_arr_time	arr_delay	carrier	flight	tailnum	origin	dest	air_time	distance	hour	minute	time_hour
<int>	<int>	<int>	<int>	<int>	<dbl>	<int>	<int>	<dbl>	<chr>	<int>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2013	1	1	517	515	2	830	819	11	UA	1545	N14228	EWR	IAH	227	1400	5	15	2013-01-01 05:00:00
2013	1	1	533	529	4	850	830	20	UA	1714	N24211	LGA	IAH	227	1416	5	29	2013-01-01 05:00:00
2013	1	1	542	540	2	923	850	33	AA	1141	N619AA	JFK	MIA	160	1089	5	40	2013-01-01 05:00:00
2013	1	1	544	545	-1	1004	1022	-18	B6	725	N804JB	JFK	BQN	183	1576	5	45	2013-01-01 05:00:00
2013	1	1	554	600	-6	812	837	-25	DL	461	N668DN	LGA	ATL	116	762	6	0	2013-01-01 06:00:00
2013	1	1	554	558	-4	740	728	12	UA	1696	N39463	EWR	ORD	150	719	5	58	2013-01-01 05:00:00
2013	1	1	555	600	-5	913	854	19	B6	507	N516JB	EWR	FLL	158	1065	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	709	723	-14	EV	5708	N829AS	LGA	IAD	53	229	6	0	2013-01-01 06:00:00
2013	1	1	557	600	-3	838	846	-8	B6	79	N593JB	JFK	MCO	140	944	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	753	745	8	AA	301	N3ALAA	LGA	ORD	138	733	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	849	851	-2	B6	49	N793JB	JFK	PBI	149	1028	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	853	856	-3	B6	71	N657JB	JFK	TPA	158	1005	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	924	917	7	UA	194	N29129	JFK	LAX	345	2475	6	0	2013-01-01 06:00:00
2013	1	1	558	600	-2	923	937	-14	UA	1124	N53441	EWR	SFO	361	2565	6	0	2013-01-01 06:00:00
2013	1	1	559	600	-1	941	910	31	AA	707	N3DUAA	LGA	DFW	257	1389	6	0	2013-01-01 06:00:00
2013	1	1	559	559	0	702	706	-4	B6	1806	N708JB	JFK	BOS	44	187	5	59	2013-01-01 05:00:00
2013	1	1	559	600	-1	854	902	-8	UA	1187	N76515	EWR	LAS	337	2227	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	851	858	-7	B6	371	N595JB	LGA	FLL	152	1076	6	0	2013-01-01 06:00:00
2013	1	1	600	600	0	837	825	12	MQ	4650	N542MQ	LGA	ATL	134	762	6	0	2013-01-01 06:00:00
2013	1	1	601	600	1	844	850	-6	B6	343	N644JB	EWR	PBI	147	1023	6	0	2013-01-01 06:00:00
2013	1	1	602	610	-8	812	820	-8	DL	1919	N971DL	LGA	MSP	170	1020	6	10	2013-01-01 06:00:00
2013	1	1	602	605	-3	821	805	16	MQ	4401	N730MQ	LGA	DTW	105	502	6	5	2013-01-01 06:00:00
2013	1	1	606	610	-4	858	910	-12	AA	1895	N633AA	EWR	MIA	152	1085	6	10	2013-01-01 06:00:00
2013	1	1	606	610	-4	837	845	-8	DL	1743	N3739P	JFK	ATL	128	760	6	10	2013-01-01 06:00:00
2013	1	1	607	607	0	858	915	-17	UA	1077	N53442	EWR	MIA	157	1085	6	7	2013-01-01 06:00:00
2013	1	1	608	600	8	807	735	32	MQ	3768	N9EAMQ	EWR	ORD	139	719	6	0	2013-01-01 06:00:00
2013	1	1	611	600	11	945	931	14	UA	303	N532UA	JFK	SFO	366	2586	6	0	2013-01-01 06:00:00
2013	1	1	613	610	3	925	921	4	B6	135	N635JB	JFK	RSW	175	1074	6	10	2013-01-01 06:00:00
2013	1	1	615	615	0	1039	1100	-21	B6	709	N794JB	JFK	SJU	182	1598	6	15	2013-01-01 06:00:00
2013	1	1	615	615	0	833	842	-9	DL	575	N326NB	EWR	ATL	120	746	6	15	2013-01-01 06:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	2121	2100	21	2349	14	-25	DL	2363	N193DN	JFK	LAX	296	2475	21	0	2013-09-30 21:00:00
2013	9	30	2122	2127	-5	2226	2237	-11	EV	4372	N14902	EWR	DCA	35	199	21	27	2013-09-30 21:00:00
2013	9	30	2127	2129	-2	2314	2323	-9	EV	3833	N16546	EWR	CLT	72	529	21	29	2013-09-30 21:00:00
2013	9	30	2128	2130	-2	2328	2359	-31	B6	97	N807JB	JFK	DEN	213	1626	21	30	2013-09-30 21:00:00
2013	9	30	2129	2059	30	2230	2232	-2	EV	5048	N751EV	LGA	RIC	45	292	20	59	2013-09-30 20:00:00
2013	9	30	2131	2140	-9	2225	2255	-30	MQ	3621	N807MQ	JFK	DCA	36	213	21	40	2013-09-30 21:00:00
2013	9	30	2140	2140	0	10	40	-30	AA	185	N335AA	JFK	LAX	298	2475	21	40	2013-09-30 21:00:00
2013	9	30	2142	2129	13	2250	2239	11	EV	4509	N12957	EWR	PWM	47	284	21	29	2013-09-30 21:00:00
2013	9	30	2145	2145	0	115	140	-25	B6	1103	N633JB	JFK	SJU	192	1598	21	45	2013-09-30 21:00:00
2013	9	30	2147	2137	10	30	27	3	B6	1371	N627JB	LGA	FLL	139	1076	21	37	2013-09-30 21:00:00
2013	9	30	2149	2156	-7	2245	2308	-23	UA	523	N813UA	EWR	BOS	37	200	21	56	2013-09-30 21:00:00
2013	9	30	2150	2159	-9	2250	2306	-16	EV	3842	N10575	EWR	MHT	39	209	21	59	2013-09-30 21:00:00
2013	9	30	2159	1845	194	2344	2030	194	9E	3320	N906XJ	JFK	BUF	50	301	18	45	2013-09-30 18:00:00
2013	9	30	2203	2205	-2	2339	2331	8	EV	5311	N722EV	LGA	BGR	61	378	22	5	2013-09-30 22:00:00
2013	9	30	2207	2140	27	2257	2250	7	MQ	3660	N532MQ	LGA	BNA	97	764	21	40	2013-09-30 21:00:00
2013	9	30	2211	2059	72	2339	2242	57	EV	4672	N12145	EWR	STL	120	872	20	59	2013-09-30 20:00:00
2013	9	30	2231	2245	-14	2335	2356	-21	B6	108	N193JB	JFK	PWM	48	273	22	45	2013-09-30 22:00:00
2013	9	30	2233	2113	80	112	30	42	UA	471	N578UA	EWR	SFO	318	2565	21	13	2013-09-30 21:00:00
2013	9	30	2235	2001	154	59	2249	130	B6	1083	N804JB	JFK	MCO	123	944	20	1	2013-09-30 20:00:00
2013	9	30	2237	2245	-8	2345	2353	-8	B6	234	N318JB	JFK	BTV	43	266	22	45	2013-09-30 22:00:00
2013	9	30	2240	2245	-5	2334	2351	-17	B6	1816	N354JB	JFK	SYR	41	209	22	45	2013-09-30 22:00:00
2013	9	30	2240	2250	-10	2347	7	-20	B6	2002	N281JB	JFK	BUF	52	301	22	50	2013-09-30 22:00:00
2013	9	30	2241	2246	-5	2345	1	-16	B6	486	N346JB	JFK	ROC	47	264	22	46	2013-09-30 22:00:00
2013	9	30	2307	2255	12	2359	2358	1	B6	718	N565JB	JFK	BOS	33	187	22	55	2013-09-30 22:00:00
2013	9	30	NA	1842	NA	NA	2019	NA	EV	5274	N740EV	LGA	BNA	NA	764	18	42	2013-09-30 18:00:00
2013	9	30	NA	1455	NA	NA	1634	NA	9E	3393	NA	JFK	DCA	NA	213	14	55	2013-09-30 14:00:00
2013	9	30	NA	2200	NA	NA	2312	NA	9E	3525	NA	LGA	SYR	NA	198	22	0	2013-09-30 22:00:00
2013	9	30	NA	1210	NA	NA	1330	NA	MQ	3461	N535MQ	LGA	BNA	NA	764	12	10	2013-09-30 12:00:00
2013	9	30	NA	1159	NA	NA	1344	NA	MQ	3572	N511MQ	LGA	CLE	NA	419	11	59	2013-09-30 11:00:00
2013	9	30	NA	840	NA	NA	1020	NA	MQ	3531	N839MQ	LGA	RDU	NA	431	8	40	2013-09-30 08:00:00

• Standardise to compute per group metrics:

In [91]:

popular_dests %>% 
  filter(arr_delay > 0) %>% 
  mutate(prop_delay = arr_delay / sum(arr_delay)) %>% 
  select(year:day, dest, arr_delay, prop_delay)

popular_dests %>% filter(arr_delay > 0) %>% mutate(prop_delay = arr_delay / sum(arr_delay)) %>% select(year:day, dest, arr_delay, prop_delay)

A grouped_df: 131106 × 6

year	month	day	dest	arr_delay	prop_delay
<int>	<int>	<int>	<chr>	<dbl>	<dbl>
2013	1	1	IAH	11	1.106740e-04
2013	1	1	IAH	20	2.012255e-04
2013	1	1	MIA	33	2.350026e-04
2013	1	1	ORD	12	4.239594e-05
2013	1	1	FLL	19	9.377853e-05
2013	1	1	ORD	8	2.826396e-05
2013	1	1	LAX	7	3.444441e-05
2013	1	1	DFW	31	2.817951e-04
2013	1	1	ATL	12	3.996017e-05
2013	1	1	DTW	16	1.157257e-04
2013	1	1	ORD	32	1.130558e-04
2013	1	1	SFO	14	6.815770e-05
2013	1	1	RSW	4	9.441088e-05
2013	1	1	PHX	3	4.754057e-05
2013	1	1	MIA	5	3.560645e-05
2013	1	1	IAH	1	1.006127e-05
2013	1	1	MSP	29	2.486219e-04
2013	1	1	MSP	10	8.573168e-05
2013	1	1	LAX	29	1.426983e-04
2013	1	1	ORD	14	4.946192e-05
2013	1	1	IAD	12	1.051451e-04
2013	1	1	DFW	48	4.363279e-04
2013	1	1	BUF	5	6.537828e-05
2013	1	1	MSY	11	1.704924e-04
2013	1	1	ORD	4	1.413198e-05
2013	1	1	XNA	27	1.736669e-03
2013	1	1	ATL	5	1.665007e-05
2013	1	1	LAX	2	9.841260e-06
2013	1	1	FLL	1	4.935712e-06
2013	1	1	LAX	44	2.165077e-04
⋮	⋮	⋮	⋮	⋮	⋮
2013	9	30	PIT	54	1.174832e-03
2013	9	30	MKE	10	1.712036e-04
2013	9	30	CHS	10	1.897173e-04
2013	9	30	ROC	16	3.456545e-04
2013	9	30	MCO	1	4.851566e-06
2013	9	30	SFO	28	1.363154e-04
2013	9	30	CMH	105	1.741582e-03
2013	9	30	SAV	174	1.044042e-02
2013	9	30	FLL	10	4.935712e-05
2013	9	30	SAN	47	1.183730e-03
2013	9	30	SEA	40	7.919851e-04
2013	9	30	DCA	13	8.755624e-05
2013	9	30	LAX	40	1.968252e-04
2013	9	30	PHL	9	3.139717e-04
2013	9	30	CAK	3	1.395738e-04
2013	9	30	ATL	136	4.528820e-04
2013	9	30	BWI	2	5.896574e-05
2013	9	30	MDW	15	1.935384e-04
2013	9	30	PDX	52	2.246123e-03
2013	9	30	MSP	36	3.086340e-04
2013	9	30	MCI	58	1.286716e-03
2013	9	30	PWM	11	2.539067e-04
2013	9	30	FLL	3	1.480714e-05
2013	9	30	BUF	194	2.536677e-03
2013	9	30	BGR	8	1.152738e-03
2013	9	30	BNA	7	5.730565e-05
2013	9	30	STL	57	7.165125e-04
2013	9	30	SFO	42	2.044731e-04
2013	9	30	MCO	130	6.307036e-04
2013	9	30	BOS	1	5.257900e-06

A grouped filter is a grouped mutate followed by an ungrouped filter. I generally avoid them except for quick and dirty manipulations: otherwise it's hard to check that you've done the manipulation correctly.

Functions that work most naturally in grouped mutates and filters are known as window functions (vs. the summary functions used for summaries). You can learn more about useful window functions in the corresponding vignette: vignette("window-functions").

Exercises¶

1. Refer back to the lists of useful mutate and filtering functions. Describe how each operation changes when you combine it with grouping.

2. Which plane (tailnum) has the worst on-time record?

3. What time of day should you fly if you want to avoid delays as much as possible?

4. For each destination, compute the total minutes of delay. For each flight, compute the proportion of the total delay for its destination.

5. Delays are typically temporally correlated: even once the problem that caused the initial delay has been resolved, later flights are delayed to allow earlier flights to leave. Using lag(), explore how the delay of a flight is related to the delay of the immediately preceding flight.

6. Look at each destination. Can you find flights that are suspiciously fast? (i.e. flights that represent a potential data entry error). Compute the air time of a flight relative to the shortest flight to that destination. Which flights were most delayed in the air?

7. Find all destinations that are flown by at least two carriers. Use that information to rank the carriers.

8. For each plane, count the number of flights before the first delay of greater than 1 hour.