The usual two navigation tricks, and they earn their keep in this deck in particular. The three stacked horizontal lines in the bottom-left corner open a table of contents, and pressing the letter o gives you an overview of every slide at once. This lecture is really two separate topics bolted together: reshaping one dataset between long and wide, and merging two datasets into one. When you come back to it during an assignment you will usually want one or the other rather than both, so being able to jump straight to the pivot slides or straight to the join slides is the point.
Click the three stacked horizontal lines in the bottom-left corner of the slide to open the table of contents, then jump to whichever section you want
Press the “o” key to see an overview of every slide at once
The code boxes here are live. The box with the pale blue background is an editor running R in your browser, Run Code runs everything in it, and highlighting part of it and pressing Command and Enter on a Mac, or Control and Enter on Windows, runs just that part. The icon of two stacked sheets of paper copies the code out to your own machine, and the reload button beside it restores the original after you have experimented. One more, at the right end of the toolbar: the eye icon hides that cell’s output and a second click brings it back. That one matters here, because reshaping prints whole tables and a wide result can push the next thing you want to read off the bottom of the slide. Something specific to this deck as well: many of these cells run themselves as the slide loads, so you will often see a result before you touch anything. That is deliberate. With reshaping and merging the lesson is what the table looks like afterwards, and comparing the before with the after is easier when the after is already on screen.
Two words you will hear constantly, so let us pin them down with an example rather than a definition. On the left is the same data in long format: one column called yield holding every yield value, and a year column saying which year each belongs to. On the right is wide format: the years have become column names, and the yields sit underneath them. Same information, two shapes. Now read the note under the wide version, because it is the real cost of that shape: there is nothing in the wide dataset telling you what those numbers are. The column is called 2019, not yield in 2019. The code on the left builds the example from the ground up. Repeating each of the four state names twice gives one row for each state-year combination. Repeating 2019 and 2020 four times pairs those two years with every state, and the eight yield numbers supply one measurement for each row. The assignment is wrapped in parentheses so the object is both saved as yield data long and printed, and as tibble changes only how that small table is displayed. On the right, pivot wider starts from that saved object, takes its new headers from year, and takes the cell contents from yield. That conversion is why you can compare the two sides as the same observations reorganized, rather than two different datasets.
Long format
A single column representing a single variable
Wide format
Multiple columns representing a single variable
Note: there is nothing in the dataset that tells you what the data values represent in the wide format.
Here is the question people get stuck on: which shape is correct? The answer is that it depends entirely on what you are about to do. Look at the model on the slide. It has separate coefficients for rainfall in each month, which means the estimation needs each month’s rainfall in its own column. So for this analysis, the dataset shown is the long one, even though it has months spread across columns, because long and wide are relative to the variable you are thinking about. That is why the word long on this slide is in red: it is long with respect to yield, while being wide with respect to rainfall. The code makes that mixed shape deliberately. It first creates the same eight state-year yield rows as before. The square-bracket step repeats every one of those rows five times so there is room for May through September. Mutate then adds the five month names repeatedly and generates forty rainfall values between zero and twenty with run if. Finally, pivot wider takes the month values into column names, takes rainfall into the cells, and adds the prefix R underscore so the new names match the rainfall terms in the equation, such as R underscore May. The random values exist only to give the reshaping operation something concrete to display. The structure, not the particular rainfall numbers, is what you should study.
Suppose you are interested in estimating the following statistical model:
\text{corn yield} = \beta_0 + \beta_1 R_{May} + \beta_2 R_{June} + \beta_3 R_{July} + \beta_4 R_{August} + \beta_5 R_{September} + v
where R refers to rainfall.
Then the following dataset is in a long format:
And here is the same data in a shape that is too long for that analysis. Every rainfall observation is on its own row, with a month column saying which month it is. Nothing is wrong with this data; it is perfectly tidy. It simply does not match the model we want to estimate, which needs one column per month. Read the callout, because it is the point of this whole slide: whether a dataset is wide or long is not a property of the data, it is a judgment about what you are doing with it. There is no universally correct shape. The construction mirrors the previous tab up to the last step. Four state names repeated twice and the two years repeated four times create eight state-year yield observations. The square-bracket expression then repeats each observation five times, mutate labels those copies May through September and assigns a random rainfall value between zero and twenty, and head fifteen prints only the first fifteen rows so the slide stays readable. There is no pivot wider at the end, so month remains a column and rainfall remains one measurement column. Keeping that last step off is exactly what lets you see why this otherwise valid long table is too long for the displayed model.
This is too long for your analysis.
Point
Whether a dataset is wide or long is determined based on what you are doing with the dataset.
Let us actually do the conversion, starting from long and going to wide. First we need something to work with, so this tab just creates the dataset: four states, two years each, with a yield for every combination. Eight rows, three columns. Run it and look at it, and keep the shape in mind, because in a moment we are going to turn those eight rows into four. In the code, each state is repeated twice because it has one observation in 2019 and one in 2020. The two-year sequence is repeated once for each of the four states, and the eight yield values line up with those eight state-year rows. The left arrow saves the result as yield data long, while the surrounding parentheses print it immediately so you can verify the object before reshaping it. Move to the next tab with those roles in mind: state identifies the row, year will supply new headers, and yield will supply their values.
Create the following dataset in long format:
The function is pivot_wider from the tidyr package, and it takes two arguments beyond the data itself. names_from says which column supplies the new column names, and values_from says which column supplies the values that go underneath them. So here, the values in the year column become the new column headers, and the values in the yield column fill the cells. Read the two bullets under each side; they say exactly that. If you can hold onto names_from meaning where the headers come from and values_from meaning where the numbers come from, you have understood this function.
How
To convert a long-formatted data.frame into a wide-formatted data.frame, you can use tidyr::pivot_wider() function from the tidyr package.
variable_1 becomes the name of the new variablesvariable_2 becomes the value of the new variablesExample
year becomes the name of the new variablesyield becomes the value of the new variablesA small practical problem with what we just did: the new columns are called 2019 and 2020, which are numbers. Column names that begin with a digit are awkward in R; you end up needing backticks around them every time you refer to one. The fix is names_prefix, which sticks a string on the front of every new name, so you get yield_2019 instead. It also makes the columns self-documenting, which addresses the complaint from the very first slide that a wide dataset does not say what its numbers are. Read the pipeline from top to bottom. Yield data long enters pivot wider, names prefix supplies the literal text yield underscore, names from still says that year supplies the changing part of each header, and values from still says that yield fills the cells. Quoted column names work here just like the unquoted names in the preceding example. The prefix does not change any observations; it changes only the labels created during the pivot.
You can append a character string to the new variable names. The previous example had 2019 and 2020 as the name of the new variables.
One more capability before we go the other way. So far we have widened a single value column, but datasets usually have several. These two tabs show that values_from accepts a vector of column names, so you can widen yield and rainfall in the same call. Look carefully at the resulting column names, because this is the part that matters later: you get yield_2019, yield_2020, rainfall_2019, rainfall_2020. The new names combine the original variable with the year, joined by an underscore. Remember that structure, because reversing it is the trickiest thing in this lecture. The preparation tab recreates the eight state-year rows and adds one rainfall observation beside each yield observation, so yield and rainfall are two value columns measured for the same keys. In the demonstration, year is still the names from column, but the values from argument now receives a combine vector containing both yield and rainfall. Pivot wider therefore makes one pair of year columns for each value variable. No explicit names prefix is needed because tidyr uses the original value-column name as part of each new header when more than one value column is widened. That automatic combination is useful now, and it is also why the reverse pivot must later separate a header such as rainfall underscore 2020 into two meanings.
Create the following data in long format:
You can simply supply multiple variables to be made wide like this:
Now the other direction, wide to long, using pivot_longer. It takes three things. cols is which columns to gather up. names_to is the name of the new column that will hold what those column headers were. And values_to is the name of the new column that will hold the values. In the example, we pivot everything except state, using a minus sign to say all but this one, and we declare that the old headers represent years and the old values represent yields. Notice that pivot_longer is where the information a wide dataset was missing gets put back in, because you are telling it what those columns meant.
How
To convert a wide-formatted data.frame into a long-formatted data.frame, you can use tidyr::pivot_longer() function from the tidyr package.
x: list of the name of the columns to pivot into longer formaty: what the name of x representsz: what the values stored in x representsExample
x: all the variables except statey: “year”z: “yield”If you widened with a prefix, you will want to strip it off when you go back. names_prefix on pivot_longer does exactly that, removing the given string from the front of each name before storing it. Otherwise your year column would contain yield_2019 rather than 2019. And note the sentence at the bottom, which catches people out: the resulting year column is character, not numeric, because column names are always text. If you intend to use it as a number, in a model or on an axis, convert it with as.numeric. The minus state selection leaves state in place as the identifier and pivots every other column. Names to calls the new header-label column year, values to calls the stacked measurement column yield, and names prefix removes yield underscore when that text is present. The currently saved object has bare 2019 and 2020 headers, so in this run there is simply no prefix to strip. The result still has the intended three roles: state identifies the unit, year identifies the occasion, and yield holds the measurement. Removing a prefix cleans the text, but it does not perform the numeric conversion mentioned below the code.
You do not want yield_ in front of the year numbers in the new year variable? You can use the names_prefix option as follows:
Notice year is character. Convert it to numeric using as.numeric() if you use it as a numeric variable.
This is the hardest thing in this lecture, so take it slowly through the four tabs. The situation is that we widened two variables at once, so every column name now encodes two pieces of information, the variable and the year, glued together with an underscore. A single advanced pivot_longer can do this with .value, but these tabs use an explicit three-step route so you can see each part. The second tab shows you why an ordinary pivot_longer falls short. The trick taught here is to pivot everything into one long column first, then split the combined name into its two parts with separate, and then widen just the variable part back out again. Three steps, and the last tab puts them together. Start in Prep and compare the two objects. The long table has state and year as identifiers, with yield and rainfall beside them. Pivot wider takes names from year and values from both measurement columns, producing headers such as yield underscore 2019 and rainfall underscore 2020. The red objective is to recover exactly those original roles. The one-step tab shows the failure mode. Pivoting every column except state into a column called year and a column called yield does make the table longer, but those names are misleading. The new year column actually contains combined labels such as yield underscore 2019, while the new yield column mixes yield values and rainfall values. One ordinary pivot cannot tell which part of a combined header names the measurement and which part names the year. The separate tab makes those meanings explicit. First, pivot longer stores every combined header in type year and every number in value. The generic syntax shows the four jobs performed by separate: provide the data, name the character column to split, give the names of the new columns, and state the separator. In the example, separate splits type year at the underscore into type and year. Compare the before and after outputs on screen: value is unchanged, while one ambiguous label column becomes two useful columns. The three-step tab then pivots only type back across columns. Names from type creates separate yield and rainfall columns, while values from value supplies their contents. State and year are the remaining identifiers, so the result returns to one row per state-year combination. This route exists not merely to change the number of rows, but to recover the semantic structure that was packed into the wide column names.
Long
Create the following dataset in the long format:
Wide
Convert the long dataset into the wide format:
Objective: We would like to convert the wide data back to the original long data.
You can revert it in one pivot_longer() call using the .value sentinel, although this deck demonstrates a more explicit three-step method.
However, you can take advantage of the tidyr::separate() function, which separates a variable of type character by a user-specified separator into two variables in the dataset.
Before separation:
After separation:
After separating type_year to type and year, all you have to do is to apply tidyr::pivot_wider() to have the desired long-formatted data.
Time to practise on real data rather than eight made-up rows. We are back to the flights dataset from nycflights13, which you met in the previous lecture. Run it and remind yourself what is in it. The three exercises that follow take you round a full circle: summarize it into a long dataset, widen that, and then narrow it back. If you can do that round trip, you have the reshaping half of this lecture.
We will use flights data from the nycflights13 package.
The first exercise is really a revision of last lecture: count the number of flights for each carrier and month. That is group_by on two variables and then summarize with n. The result is in long format, one row per carrier-month combination, and that is the input for the next exercise, so name it as instructed. There is a folded answer, and there is also a chunk showing the output you should be getting, so you can check yourself without reading the code.
Using flights data, calculate the total number of flights by carrier-month, which is in the long format. Name the resulting object num_flights.
Work here
Answer
Here is the output you are supposed to get if done correctly:
Now widen it. You want months across the top and carriers down the side, which means names_from is month and values_from is your count column. Use the names_prefix argument as well, so that your columns come out as month_1 rather than a bare 1, for the reason we discussed earlier. Look at the shape of the result: instead of a long list of carrier-month rows, you get a compact table with one row per carrier. This is the shape you would want for a printed table in a paper. The work area is where you should write that pipeline yourself. In the folded answer, num flights enters pivot wider, month supplies the new headers, month underscore is attached to each header, and num obs supplies the counts. The output chunk also assigns the result to num flights wide and wraps that assignment in parentheses so the saved object prints for checking. That assignment matters because Exercise 3 starts from the wide object you create here.
Reshape the num_flights data into a wide format with the number of flights per month as columns, and assign the result to an R object named num_flights_wide.
Work here
Answer
Here is the output you are supposed to get if done correctly:
And now reverse it, to complete the circle. pivot_longer, gathering all the month columns, which you can select neatly with starts_with of month underscore rather than listing twelve of them. Strip the prefix off with names_prefix, and name the two new columns. Compare your answer with the object you created in exercise one. It should hold the same information, though the row ordering and the column types may differ, which is itself a useful thing to notice about round trips. In the answer, starts with month underscore selects only the twelve columns created by the previous pivot, so carrier stays in place as the identifier. Names to creates a column called month from those old headers, names prefix removes month underscore before the labels are stored, and values to stacks the counts in a column called num flights. The output should again have one row per carrier-month combination. Month is now character because it came from column names, whereas the original month in num flights was numeric, so matching information does not necessarily mean identical column types.
Reshape the data (num_flights_wide) back into the long format so that a single column has all the flight number values.
Work here
Answer
Here is the output you are supposed to get if done correctly:
The second half of this lecture is merging, and the motivation is completely practical. Data almost never arrives in one file. If you want to know how crop prices affect supply, you need price and production in the same dataset, and they will have come from different sources in different files. Combining them is not a minor preliminary; it is usually where the real errors creep into an analysis, which is why we are spending time on doing it deliberately rather than hopefully.
It is very common that you have data stored in separate files, and you need to combine them before you conduct any statistical analysis.
For example, if you are interested in how crop price affects the supply of crops, you want to have price and production data in a single dataset. However, it may be that price and production data are stored in two separate files.
Let us build up to why merging works, starting from a deliberately impossible case. Here are two datasets: one column of prices, one column of yields. The question in the callout is whether you can merge them. Look at them and think about it before I answer. The answer is no, and the reason is worth stating precisely: nothing in either dataset says which price goes with which yield. They are just two lists of numbers. Whatever order they happen to be in is not information you can rely on.
Now suppose, you have collected price and production data for Lancaster and Douglas County from 2015 to 2016.
Here is what the datasets look like (these are made-up numbers).
Question
Can you merge the two?
Now we add a column to each: county. Can you merge them now? Better, but still no, and this is the case that catches people out. We now know which county each price and each yield belongs to, but every county appears twice, once for each year. So for Lancaster there are two prices and two yields, and nothing says which price goes with which yield. Knowing who is not enough when you have several observations for the same who.
Let’s display one more variable from each of the datasets.
Okay, great. At least we know which price and production belong to which county! In other words, we know which price and production belong to who (or where).
Question
Can you merge the two?
And now year as well. This time the answer is yes. Every row is uniquely identified by the combination of county and year, so each price has exactly one yield it can correspond to. That combination is what the important callout names: the keys. The keys are the variables that, taken together, uniquely identify a row and let you line two datasets up. Note that here it takes two variables together; neither county nor year alone would do it. Identifying your keys correctly is the single most important step in any merge.
Let’s display one more variable from each of the datasets.
Question
Can you merge the two now?
Key
The function is left_join from dplyr. There is a whole family of join functions, and they are listed here, but I want to give you permission to ignore most of them for now. left_join covers the overwhelming majority of real cases. The advice in the last bullet is genuine: learn the others when you hit a situation left_join cannot handle, and not before. Trying to memorize the differences between six joins in the abstract, before you have a problem that needs them, is a waste of your time and it does not stick.
You can use the left_join() function from the dplyr package to merge two datasets.
There are different types of join functions:
right_join() (you never need to use this one)inner_join()full_join()semi_join()nest_join()But, most of the time, left_join() is sufficient.
Try to learn other functions when you encounter a case where left_join() is not sufficient. Do not waste your time until then.
dplyr::left_join()The syntax is two datasets and the keys, but the three rules underneath are what you actually need to know. Rule one: you get every row from the left dataset, and columns from both. Rule two: where a left-hand row has no match on the right, the new columns are filled with NA rather than the row being dropped. Rule three: where there are multiple matches, you get every combination, which means the result can have more rows than you started with. And the note is the practical upshot of rule one: the order of the two datasets matters, because the left one determines which rows survive.
Syntax
Rules to be aware of
data_x, and all non-key columns from data_y by defaultdata_x with no match in data_y will have NA values in the new columnsdata_x and data_y, all combinations of the matches are returnedNote
The order of datasets matters.
These tabs work through the simplest case, where each row on the left has exactly one match on the right. Start with the data prep tab and answer the question there yourself before moving on: what are the keys? Then the first demo joins them, and does it in both orders. Notice that here the order makes no difference, and the callout explains why: every row in each dataset has exactly one partner in the other, so nothing gets dropped or duplicated whichever way round you go. Then the later tabs deliberately break that symmetry by adding a county to one dataset and not the other, so you can see rules one and two actually operating. In Data prep, N and T are both two, so N times T gives four observations in each starting table. Set seed fixes the random draws so everyone sees the same made-up prices and yields. Run if generates prices between two and six and yields between one hundred eighty and two hundred eighty. The county and year vectors arrange those values so every county-year combination appears exactly once in each table. Printing the two tables side by side lets you identify county and year as the joint keys before seeing the answer. Demo 1 passes yield data as the left table, price data as the right table, and the combine vector county and year to by. Switching the tables reverses which measurement starts on the left, but it does not change the matched observations or the row count because both sides contain the same four keys exactly once. This is the special one-to-one situation described by the callout. Data prep 2 creates a six-row yield table by adding Chase County in both years. The surrounding parentheses save and display it. In Demo 2, putting price data on the left keeps its four Lancaster and Douglas rows, so the unmatched Chase rows that exist only on the right never enter the result. Putting yield data with Chase on the left keeps all six yield rows, and the two Chase rows receive N A for price because no right-hand key matches them. Those two outputs make the order rule visible rather than abstract. The keeping all rows tab addresses the case where dropping unmatched rows from either side is not acceptable. Full join uses the same county-year keys but retains the union of keys from both tables, so the Chase observations survive even when price data is written first. Move to the next demonstration after checking which function matches your actual intention: left join when the left table defines the population you must keep, and full join when unmatched rows from both tables matter.
We use price_data and yield_data for demonstrations.
Question
What are the keys?
The keys are county and year, so
Switching the two?
Note
In this instance, which comes first does not matter because all the individual rows in yield_data (left data) have exactly one match in price_data (right data) without fail, and vice versa.
Let’s expand the yield_data as follows:
yield_data_with_chase on the right:
yield_data_with_chase on the left:
Remember?
left_join() returns all rows and columns from data_x, and all non-key columns from data_y by defaultdata_x with no match in data_y will have NA values in the new columnsWe saw in the previous slide having price_data (as data_x) and yield_data_with_chase as (data_y), left_join() discarded rows in yield_data_with_chase (data_y).
If you would like to keep unmatched rows in data_y, you can use full_join().
Now a more realistic case, one to many. The weather data here has several observations per county and year, one for each month, whereas the yield data has just one row per county-year. So each yield row has several weather rows it matches. Look carefully at what the join produces: the yield value gets repeated across every matching weather row. That is rule three in action, and it is correct behaviour, but it means your dataset has grown and any given yield figure now appears more than once. If you were to average yields on that result without thinking, you would be weighting by the number of weather observations. The preparation code makes twelve weather rows. Run if supplies a rainfall value between zero and twenty for each row. Repeating each county four times provides four observations per county, the year pattern assigns two months to each county-year, and the alternating four and five values label those months. Chase is present in weather but not in the current yield table. In the Demo, county and year are the only join keys, so month is intentionally not used to match. Each Lancaster or Douglas yield row therefore matches two monthly weather rows and appears twice, while the right-side Chase rows are omitted because yield data is on the left. Read the output row count as a diagnostic of both rule one and rule three.
And now many to many, which is where you should be genuinely careful. The yield data now has multiple crops per county-year, and the weather data has multiple months per county-year, so both sides have several rows per key. The join produces every combination of the two, and the result grows quickly. Notice the commented-out line in the code, which passes relationship equals many-to-many. Recent versions of dplyr warn you when a join is many-to-many, precisely because it is so often a mistake, and that argument is how you confirm you meant it. If you see that warning and you were not expecting it, stop and check your keys. The new yield table contains eight rows. The first four random values are corn yields between one hundred eighty and two hundred eighty, the next four are soybean yields between forty and eighty, and the repeated county-year pattern gives both crops one row for every Lancaster and Douglas year. Weather still contributes two months for each of those county-year keys. Because crop and month are not join keys, each key has two crop rows on the left and two month rows on the right, producing four combinations. Across the four matching county-years, the join returns sixteen rows. That multiplication is the behavior you are acknowledging with relationship many-to-many, not a request for dplyr to change how the matches are formed.
Two exercises to finish, and they are deliberately built as a pair. Both merge temperature onto the January flights, but one uses daily temperature and the other hourly. Before you write any code, answer the question each exercise asks: is this one-to-one, one-to-many, or many-to-many? Work it out by thinking about how many rows each dataset has per key, not by running it and seeing. That habit of predicting the relationship before joining is what stops you silently multiplying your dataset, and it is the real lesson of this whole section. The Preparation tab creates the three objects you need. Flights enters filter month equals one, then select keeps month, day, departure delay, and origin. Daily temperature starts from weather, keeps January, groups by origin, month, and day, and summarizes all hourly readings into one mean temperature for each airport-day. Hourly temperature instead keeps origin, month, day, hour, and temperature, so it still has many rows for a given airport-day. The difference between summarizing and selecting is what creates the two different join relationships. In Exercise 1, many flights can share an origin, month, and day, but daily temp January has only one row for that key. From the flight table’s point of view this is many-to-one, or one-to-many if you describe the same relationship from the daily table’s side. The left join uses origin, month, and day as its combine vector of keys, keeps every January flight, and attaches that day’s mean temperature. The output chunk and folded answer show the same call. In Exercise 2, those same keys are repeated on both sides: there are many flights and many hourly weather observations per airport-day. Because hour is not included as a key, every flight matches every hourly temperature row for its day, making this many-to-many. The relationship argument set to many-to-many records that this multiplication is intentional and suppresses the diagnostic warning; it does not choose a single hour for each flight. That limitation is exactly why the next section introduces joins that can match observations in time more intelligently.
Create the following datasets and take a look at them to understand what’s in them:
Flights in January:
daily temperature in January:
hourly temperature in January:
You are interested in learning the impact of daily temperature on departure delay for the flights in January. To do so, you need to have the variables in a single dataset.
Is this going to be a 1-to-1 matching, 1-to-m, or m-to-m matching?
Merge daily_temp_Jan to flights_Jan using left_join()
Work here
Here is the output you are supposed to get if done correctly:
Answer
You are interested in learning the impact of hourly temperature on departure delay for the flights in January. To do so, you need to have them in a single dataset.
Is this going to be a 1-to-1 matching, 1-to-m, or m-to-m matching?
Merge hourly_temp_Jan to flights_Jan using left_join()
Work here
Here is the output you are supposed to get if done correctly:
Answer
Let me start with a new exact-hour equality join. In the exercises a moment ago you matched January flights to every hourly temperature row at their origin airport on the same day. Here you match each flight to the temperature at its origin airport for its scheduled departure hour, and it looks like it works. Every flight came back, no warning, no error. But count the missing temperatures and fifty-two flights have none, because the weather station did not report for three particular hours. Nobody spots that in twenty-seven thousand rows. This is what equality joins do the moment your two datasets are on slightly different schedules, and it is why the rest of this section exists. What you actually wanted was the nearest observation in time, not the exactly matching one. Data prep filters both source tables to January and keeps only the variables needed for the example. In the flight table, select renames time hour to scheduled departure hour and retains origin and departure delay. In the weather table, it renames the same timestamp variable to observation hour and retains origin and temperature. The different names let the later join state clearly which event time comes from which table. In A join that quietly fails, join by requires the same origin and exact equality between scheduled departure hour and observation hour. Because this is a left join, n row confirms that all 27,004 flights survived. Sum of is N A on the temperature column asks a different and essential question: how many left rows failed to acquire a weather value? The answer is fifty-two, which is why the important callout says row retention alone does not prove a successful match. The next tab filters those missing temperatures and counts them by origin and scheduled departure hour, revealing three unreported airport-hours rather than a random code failure. Rule two filled N A exactly as designed. The smaller example exists because you can inspect four irrigation events more easily than twenty-seven thousand flights. Readings stores field, a reading date converted from date text, and soil moisture. Irrigation stores field, its own independently timed irrigation date, and inches applied. The goal is not an equal date. It is to attach the most recent earlier reading to each irrigation event. No date occurs in both tables, while joining on field alone deliberately ignores time. With relationship many-to-many acknowledging the repeated fields, that field-only left join expands four irrigation events to ten combinations. The problem callout identifies the missing capability: we need a condition that can compare dates and then choose the closest qualifying row. Move to join by to write that condition.
January flights, and the hourly weather at each origin airport:
Both timestamps come from time_hour, renamed here to dep_time and obs_time so the two are easy to tell apart. This looks like an ordinary equality join on airport and hour.
This tab introduces a new exact-hour equality join: match each flight to the temperature at its origin for its scheduled departure hour.
No error, no warning
Every flight survived the join, so nothing looks wrong. Yet 52 flights came out with no temperature at all.
Where did they go?
The weather station simply did not report for three particular hours. Those flights had nothing to match, so left_join() did exactly what Rule 2 says and filled NA.
The real question
There was a perfectly good observation an hour earlier. Equality could not reach it, because equality only accepts an exact match.
27,000 rows is too many to watch a join happen. So we switch to something you can see all of.
Soil moisture is measured on irregular dates, and irrigation happens on its own dates:
Goal: for each irrigation event, attach the most recent soil moisture reading taken before it.
Joining on date is hopeless, because not a single date appears in both datasets. Joining on field alone gives every combination:
The problem
Four irrigation events became ten rows. Equality is the wrong tool: we do not want rows where the dates match, we want the row where the date is closest.
To match on anything other than equality you need a different way of writing the keys, and that is join_by. Notice the two lines in the first block do exactly the same thing: by equals a character vector of names, and join_by with the names unquoted. So join_by is just a more expressive way of saying what you already know how to say. The payoff is the second block, where instead of naming a column you write a comparison: field, and irrigation date greater than or equal to reading date. That is no longer a key at all, it is a condition, and only join_by can express it. In both equality examples, county and year have the same names in x and y, so join by can match them without quotes. For the inequality form, read each expression from the left table to the right table: field matches the common field, the left-side irrigation date must be greater than or equal to the right-side reading date, and that admits readings taken on or before irrigation. The read-it-out-loud callout gives you the safest way to check the inequality direction. This is progress, but it is not yet a single-match solution. Field A on May twenty-second qualifies for all three of its earlier readings, so the inequality join returns all three. The Rolling join tab adds the instruction that chooses just the nearest qualifying date.
These two are identical:
join_by() is not just a style preference. Because it takes expressions rather than names, it can say things by = cannot.
Inside join_by(), the left-hand side refers to data_x and the right-hand side to data_y. So you can write a comparison instead of a key:
Read it out loud
“Match rows in the same field where the irrigation date is at or after the reading date.”
This is an inequality join. It is progress, but look at field A on 2024-05-22: it matched all three of its earlier readings.
The inequality join gave us every earlier reading, but we only wanted one, the most recent. Wrapping the comparison in closest says exactly that: of all the rows satisfying the inequality, keep only the one where the two dates are nearest. Now every irrigation event usually gets one reading, although duplicate rows on the nearest date can return multiple matches. This is what is normally called a rolling join, because the value rolls forward in time until a newer one replaces it. Look at the last row though. Field B irrigated on the second of May, with no reading at or before that date, so it is NA; closest cannot invent a match. Then the last tab takes this straight back to the flights, where fifty-two missing temperatures become zero. In the first tab, field remains an equality condition and closest wraps only irrigation date greater than or equal to reading date. Four left-side irrigation events therefore produce four rows, with the latest earlier moisture value carried forward when one exists. Direction matters shows why the comparison sign is part of your research question. Greater than or equal searches at or before irrigation, while less than or equal searches at or after irrigation. Rolling backward cannot match field B on May second because no earlier reading exists. Rolling forward cannot match field A on May twenty-second because no later reading exists. In each case, the unmatched left row stays and the right-side columns become N A. Back to the flights, origin still has to match exactly, while closest chooses the latest observation time at or before each flight’s scheduled departure hour. The result is saved as rolled. N row checks that all 27,004 flights remain and that no duplication occurred, while the missing-temperature count falls from fifty-two to zero. The comparison table is important because it checks both failure modes at once: a useful rolling join fixed the missing values without changing the number of observational units.
Wrap the inequality in closest() to keep only the nearest qualifying match:
This is a rolling join
Four irrigation events in, four rows out. Each irrigation carries its most recent preceding reading when one exists; otherwise it receives NA. The value “rolls” forward in time until a newer reading replaces it.
Flip the inequality and you get the next reading instead of the previous one:
Look at the NAs
Each direction fails on a different row.
closest() never invents a match. Unmatched rows behave exactly like Rule 2: NA in the new columns.
Now fix the join we started with. Same data, same question, one word different:
52 to 0
| rows | flights with no temperature | |
|---|---|---|
dep_time == obs_time |
27,004 | 52 |
closest(dep_time >= obs_time) |
27,004 | 0 |
The row count did not change, so nothing was duplicated. The 52 flights that fell in a reporting gap now carry the most recent observation at or before each flight’s scheduled departure hour, which is the number you wanted all along.
A rolling join is deliberately one-directional, and sometimes that is wrong. If you just want the closest reading in time, whichever side it falls on, you have to look both ways. There is no single helper for this, so we build it: roll backward, roll forward, then compare the two gaps and keep the smaller one. Look at the first row of the intermediate table. Rolling backward found a reading seven days earlier, rolling forward found one just two days later. The rolling join gave you the seven-day-old reading; the nearest join gives you the two-day-old one. Same data, different answer, and which is correct depends entirely on your question. The Code tab implements those three numbered steps in one pipeline. Irrigation first joins to readings with the backward condition. Rename immediately preserves that result as date before and moisture before, which prevents the second join from leaving two indistinguishable reading columns. The next left join uses the forward condition and its rename creates date after and moisture after. The first mutate subtracts the dates in the appropriate order and converts each date difference to a plain numeric number of days, giving gap before and gap after. The second mutate creates the final moisture choice with if else. Choose the earlier measurement when there is no later gap, or when an earlier gap exists and is less than or equal to the later gap. Otherwise choose the later measurement. The N A checks matter because a comparison with a missing gap cannot by itself produce a usable true or false choice. Less than or equal also gives the earlier measurement priority if the two numeric gaps are equal. Printing nearest shows the full intermediate result so you can audit the decision rather than seeing only the selected value. The Compare tab uses select to display field, irrigation date, the two gaps, and the chosen moisture. For field A on May eighth, the seven-day earlier gap loses to the two-day later gap, so moisture twenty-two is selected instead of thirty. The important callout is not saying that nearest is more correct. It is showing that rolling and nearest answer different questions from the same rows. Use the final tab to choose deliberately. A rolling join is appropriate when direction carries meaning, especially when an analysis may use only information available before an event. Taking a later measurement there would leak future information. A two-sided nearest join is appropriate only when you want the best measurement of an underlying condition and before versus after has no substantive meaning.
A rolling join only ever looks one way. If you want the closest reading in either direction, do both and keep the better one:
No built-in helper
closest() always needs a direction, so there is no one-liner for a two-sided nearest match in dplyr. You build it from the pieces you just learned.
The intermediate columns show the choice being made:
It is a different answer
For field A on 05-08, the previous reading is 7 days old but the next one is only 2 days away.
moisture = 30)moisture = 22)Neither is wrong. Choose the one your question needs.
Use a rolling join when the direction is part of the question, and especially when only the past may be used: “what was the soil moisture going into this irrigation?” Using a later reading there would leak information you could not have had at the time.
Use a nearest join when you simply want the best available measurement of an underlying condition, and time direction carries no meaning.
Three things to watch, and the first is a distinction worth getting straight. A reading five days before and a reading five days after are not a tie, because the inequality has already thrown one side away before closest ever looks at distance. A real tie means two rows sharing the same nearest date, which in practice means a duplicate in your lookup table, and then you do get both rows back and your dataset quietly grows. Second, unmatched rows are easy to miss at scale, so count your NAs rather than assuming. Third, if you have used data.table, you already know all of this as the roll argument. Same concepts, different spelling. The equidistant tab constructs one irrigation on May tenth and two readings exactly five days away on opposite sides. The first join uses greater than or equal and returns only May fifth; the second uses less than or equal and returns only May fifteenth. Each produces one row because the inequality filters a side before closest compares distances, exactly as the two-filters callout says. The real-tie tab instead creates two reading rows on the same May fifth date with different moisture values. Both are on the allowed side and both have the same nearest distance, so one irrigation row becomes two result rows. The warning callout tells you to compare n row before and after because closest selects a distance, not a unique record. Duplicate lookup keys can therefore reactivate rule three even in a rolling join. The unmatched example saves a backward rolling result and sums is N A on read date. That explicitly counts left-side events for which no right-side date satisfied the inequality. In a large dataset, make this check part of the join rather than relying on visual inspection. If you know data table, read on as the mapping between differently named keys: field matches field, and reading date in the lookup is aligned with irrigation date in the event table. Roll true requests the most recent value at or before the event, while roll nearest requests the closest value in either direction. These examples are marked not run because they are a syntax reference, but the final callout gives you the conceptual translation to the dplyr operations used in this section.
An irrigation on 05-10 sits exactly 5 days from a reading on 05-05 and 5 days from one on 05-15. That is not a tie, because the inequality already chose a side:
Two filters, in order
closest() never compares across the inequality. First the inequality discards one side, then closest() finds the nearest of what survives.
A genuine tie needs two rows at the same distance on the same side, which in practice means a duplicated date in your lookup table:
Check your row count
Rule 3 has not gone away. closest() narrows you to the nearest distance, not to a single row. If several rows sit at that distance you get all of them, so compare nrow() before and after, every time.
closest() cannot invent a match. When nothing satisfies the inequality you get NA, exactly as in Rule 2:
Count them
In a four-row example you can see the NA. In a four-million-row dataset you cannot. Count them deliberately.
The same two operations exist in data.table, spelled differently:
Same idea, different spelling
roll = TRUE is closest(x >= y). roll = "nearest" is the two-sided version we had to build by hand in dplyr.
Two exercises, and the second one matters more than the first. The first is deliberately the opposite direction from the demonstration: you want the first rain on or after planting, so work out which way the inequality points before you write it. Field A had rain two days before planting, which should not be selected, and field C has no rain after planting at all. The second asks for the nearest rain in either direction, and then asks whether that is actually the right answer. It is not. For fields A and C the nearest rain fell before the seed was even in the ground, and field C’s honest NA becomes a date that means nothing. Nearest is not an upgrade. It answers a different question. Preparation gives planting one date for each of fields A, B, and C. Rain has its own field, date, and inches columns, with multiple events for A and B but only an earlier event for C. As date converts every displayed date string into a value R can compare and subtract. Inspect the two printed tables before joining so you can predict the matches from the calendar rather than treating the output as a surprise. For Exercise 1, planting is the left table, field must match, and closest wraps planting date less than or equal to rain date. Read that condition aloud as rain occurring on or after planting. Closest then selects the first qualifying rain. Field A’s April eighteenth rain is rejected and April twenty-seventh is selected. Field B receives May sixth. Field C remains in the left-join result but gets N A because no rain date satisfies the condition. The output chunk and folded answer show the same call so you can check your work without opening the solution first. Exercise 2 builds both directions but stops at the comparison table so you make the final decision yourself. The first join finds rain at or before planting and renames its columns date before and inches before. The second finds rain at or after planting and renames those columns date after and inches after. Mutate converts the two date differences into numeric gaps, and select keeps only the field, planting date, candidate dates, and gaps needed to compare them. The pipeline does not create a final nearest column on screen; you choose the smaller available gap by reading the result. The discussion supplies that interpretation. Nearest selects April eighteenth for A, May sixth for B, and May tenth for C. Only B agrees with the forward rolling question. For A and C, the smaller gap points to rain before planting, which cannot answer whether the crop received water after it entered the ground. The important callout explains why replacing C’s honest N A with a plausible-looking but irrelevant date is dangerous. Keep the final habit on screen: state the relationship and direction your question requires first, then verify the join against those predictions.
For each field, find the first rain event on or after the planting date.
Before running anything, predict two things:
Work here
Here is the output you are supposed to get if done correctly:
Answer
Now the other tool. For each field, find the nearest rain event in either direction, following the three steps from the Nearest join tab.
Work here
Here is the output you are supposed to get if done correctly:
Answer
planting %>%
left_join(rain, join_by(field, closest(plant_date >= rain_date))) %>%
rename(date_before = rain_date, inches_before = inches) %>%
left_join(rain, join_by(field, closest(plant_date <= rain_date))) %>%
rename(date_after = rain_date, inches_after = inches) %>%
mutate(
gap_before = as.numeric(plant_date - date_before),
gap_after = as.numeric(date_after - plant_date)
) %>%
select(field, plant_date, date_before, gap_before, date_after, gap_after)Now decide
Read off which rain event the nearest rule would pick for each field. Is it the same one Exercise 1 gave you? Should it be?
Exercise 1 behaved itself:
plant_date <= rain_date only admits rain at or after planting.NA. There is no rain on or after 05-25, and closest() will not reach backward to invent one.Exercise 2 is where it gets interesting. The nearest rule picks:
| field | nearest rain | but that is… |
|---|---|---|
| A | 04-18 (2 days) | 2 days before planting |
| B | 05-06 (4 days) | after planting, same as Ex. 1 |
| C | 05-10 (15 days) | 15 days before planting |
Nearest is not the better tool here
For “did the crop get water after it went in the ground?”, rain that fell before planting is no answer at all. Worse, field C’s honest NA gets replaced by a real-looking date that is simply irrelevant.
A nearest join is not an upgrade on a rolling join. It answers a different question, and when direction carries meaning, it will quietly answer the wrong one.
The habit to keep
Same as the rest of this chapter: decide what the relationship should be before you join, then check that the result matches your prediction.