Joining Data with dplyr
Learn to combine related tables in R with dplyr’s inner, left, right, full, semi and anti joins, practiced on LEGO and Stack Overflow data.
Data about one subject often sits in several related tables, and answering a question means matching rows across them on the columns they share. In R, the dplyr package handles this with a family of join verbs. You combine tables of sets, parts, themes and colors in a LEGO dataset published on the Rebrickable website and decide which join each question calls for. Then you apply the same methods to Stack Overflow questions, answers and tags.
What You Will Achieve
- Combine three or more related tables in R by choosing the key columns they have in common
- Choose between an inner, left, right or full join based on which unmatched rows you need to keep
- Keep or drop the rows of one table depending on whether they appear in another, using semi and anti joins
- Join a table to itself to trace children and grandchildren in a hierarchy of records
Program Overview
Inner, Left and Right Joins on LEGO Data
Starts with inner_join on sets, parts and inventories, then adds the mirrored left and right joins and self-joins for the theme hierarchy.
- inner_join and one-to-many relationships
- Chaining three or more tables
- left_join, right_join and observations without a match
- Self-joins for child and grandchild themes
Full, Semi and Anti Joins
Compares the parts and colors of Batman and Star Wars sets with full_join, then uses semi_join and anti_join to find what sets share or lack.
- full_join and aggregating each theme
- semi_join and anti_join for set differences
- Finding a set missing version 1
- Visualizing differences between sets
Stack Overflow Case Study
Applies every join type to R-tagged Stack Overflow questions, answers and tags, and adds bind_rows for stacking tables.
- Comparing scores across tags
- Gaps between questions and answers
- bind_rows for stacking posts
- Charting question and answer trends for tags such as dplyr and ggplot2
Certificate Awarded (Paid)
Job Outlook
Combining tables is everyday work wherever data is stored across several linked tables, including analysis and reporting in R, where questions about one subject often need records from more than one source.
Good for: R learners who need to answer questions spanning several linked tables and want many short, guided exercises on two real datasets.
Keep in mind: Three of the four chapters work on the same LEGO dataset and the last on Stack Overflow posts, so all practice stays within those two prepared datasets.
Bottom line: A tightly scoped course on dplyr’s six join verbs plus bind_rows, drilled through one LEGO dataset before a Stack Overflow case study.
Frequently Asked Questions
What is the difference between mutating and filtering joins?
Mutating joins (inner, left, right and full) bring columns from a second table into the first. Filtering joins (semi and anti) only decide which rows of the first table stay, based on whether a match exists in the other table, so no new columns are added.
What does the Stack Overflow case study involve?
The last chapter works with Stack Overflow questions tagged with R, along with their answers, the dates they were asked and their scores. You join questions to tags and answers, compare scores across tags, look for gaps between questions and answers, and chart trends for tags such as dplyr and ggplot2.
Why would you join a table to itself?
The LEGO themes in the data form a hierarchy, so joining the themes table to itself links each theme to its children, and a further join reaches the grandchildren. One lesson repeats the self-join as a left join of the table to itself.
Which dplyr verb stacks tables instead of joining them?
The final chapter adds bind_rows, which stacks one table on top of another rather than matching rows on a key. The lessons use it to combine question and answer posts before counting and visualizing them by tag.


There are no reviews yet.