Joining Data with dplyr

Joinning tables

Inner join

The inner_join is the key to bring tables together. To use it, you need to provide the two tables that must be joined and the columns on which they should be joined.

library(dplyr)

Registered S3 method overwritten by 'dplyr':
  method           from
  print.rowwise_df     

Attaching package: ‘dplyr’

The following objects are masked from ‘package:stats’:

    filter, lag

The following objects are masked from ‘package:base’:

    intersect, setdiff, setequal, union

library(readr)
library(tidyr)

parts <- read.csv("parts.csv", stringsAsFactors = FALSE)

EOF within quoted string

part_categories <- read.csv("part_categories.csv", stringsAsFactors = FALSE)
inventories <- read.csv("inventories.csv", stringsAsFactors = FALSE)
inventory_parts <- read.csv("inventory_parts.csv", stringsAsFactors = FALSE)
colors <- read.csv("colors.csv", stringsAsFactors = FALSE)
sets <- read.csv("sets.csv", stringsAsFactors = FALSE)
themes <- read.csv("themes.csv", stringsAsFactors = FALSE)

parts %>% 
    inner_join(part_categories, by = c("part_cat_id" = "id"), suffix = c("_part", "_category"))

We can now see the part category associated with each part, and you used the suffix argument to make sure your table’s fields are clear.

Joining parts and inventories

The LEGO data has many tables that can be joined together. Often times, some of the things you care about may be a few tables away. For now, we know that parts is a list of all LEGO parts, and a new table, inventory_parts, has some additional information about those parts, such as the color_id of each part you would find in a specific LEGO kit.

Let’s join these two tables together to observe how joining parts with inventory_parts increases the size of your table because of the one-to-many relationship that exists between these two tables.

# Combine the parts and inventory_parts tables
parts %>%
  inner_join(inventory_parts, by = "part_num")

This is an example of a one-to-many relationship. Notice that the table increased in the number of rows after the join.

Joining in either direction

An inner_join works the same way with either table in either position. The table that is specified first is arbitrary, since you will end up with the same information in the resulting table either way.

# Combine the parts and inventory_parts tables
inventory_parts %>%
  inner_join(parts, by = "part_num")

This is the same join as before, but the order of the tables is reversed. For an inner_join, either direction will yield a table that contains the same information! Note that the columns will appear in a different order depending on which table comes first.

Joining three tables

You can string together multiple joins with inner_join and the pipe (%>%).

We’ll now connect sets, a table that tells us about each LEGO kit, with inventories, a table that tells us the specific version of a given set, and finally to inventory_parts, a table which tells us how many of each part is available in each LEGO kit.

So, sets would tell you the name of the set, inventories would give you IDs for each of the versions of the set, and inventory_parts would tell you how many of each part would be in each version.

sets %>%
    # Add inventories using an inner join 
    inner_join(inventories, by="set_num") %>%
    # Add inventory_parts using an inner join 
    inner_join(inventory_parts, by= c("id" = "inventory_id"))

What’s the most common color?

Now let’s join an additional table, colors, which will tell us the color of each part in each set, so that we can answer the question, “what is the most common color of a LEGO piece?”

# Count the number of colors and sort
sets %>%
    inner_join(inventories, by = "set_num") %>%
    inner_join(inventory_parts, by = c("id" = "inventory_id")) %>%
    inner_join(colors, by = c("color_id" = "id"), suffix = c("_set", "_color")) %>%
  count(name_color, sort = TRUE)  

Black and White are the two most prominent colors.

Left and Right Joins

Left joining two sets by part and color

We’ll practice your ability to do this looking at two new sets: the Millennium Falcon and Star Destroyer sets.

inventory_parts_joined <- inventories %>%
  inner_join(inventory_parts, by = c("id" = "inventory_id")) %>%
  select(-id, -version) %>%
  arrange(desc(quantity))

millennium_falcon <- inventory_parts_joined %>%
  filter(set_num == "7965-1")

star_destroyer <- inventory_parts_joined %>%
  filter(set_num == "75159-1")

# Combine the star_destroyer and millennium_falcon tables
millennium_falcon %>%
  left_join(star_destroyer, by= c("part_num", "color_id"), suffix = c("_falcon", "_star_destroyer"))

Using this table, and the part_num or color_id columns, you can determine the frequency that a specific part or piece appears in the Millennium Falcon and Star Destroyer sets.

Left joining two sets by color

What if we join the datasets by color alone?

# Aggregate Millennium Falcon for the total quantity in each part
millennium_falcon_colors <- millennium_falcon %>%
  group_by(color_id) %>%
  summarize(total_quantity = sum(quantity))

# Aggregate Star Destroyer for the total quantity in each part
star_destroyer_colors <- star_destroyer %>%
  group_by(color_id) %>%
  summarize(total_quantity = sum(quantity))

# Left join the Millennium Falcon colors to the Star Destroyer colors
millennium_falcon_colors %>%
  left_join(star_destroyer_colors, by = "color_id", suffix = c("_falcon", "_star_destroyer"))

Sometimes, the level on which you’re comparing two sets- by piece or just by color- will require some thought and some pre-processing.

Finding an observation that doesn’t have a match

Left joins are really great for testing your assumptions about a data set and ensuring your data has integrity.

For example, the inventories table has a version column, for when a LEGO kit gets some kind of change or upgrade. It would be fair to assume that all sets (which joins well with inventories) would have at least a version 1.

inventory_version_1 <- inventories %>%
  filter(version == 1)

# Join versions to sets
sets %>%
  left_join(inventory_version_1, by = "set_num") %>%
  # Filter for where version is na
  filter(is.na(version))

It looks like there are cases where a set does not have an original version.

Counting part colors

Sometimes you’ll want to do some processing before you do a join, and prioritize keeping the the second (right) table’s rows instead.

parts %>%
    # Count the part_cat_id
    count(part_cat_id) %>%
    # Right join part_categories
    right_join(part_categories, by = c("part_cat_id" = "id")) %>% 
  # Filter for NA
    filter(is.na(n))

Cleaning up your count

In both left and right joins, there is the opportunity for there to be NA values in the resulting table. Fortunately, the replace_na function can turn those NAs into meaningful values.

parts %>%
    count(part_cat_id) %>%
    right_join(part_categories, by = c("part_cat_id" = "id")) %>%
    # Use replace_na to replace missing values in the n column
    replace_na(list(n = 0))

Joining tables to themselves

Tables can be joined to themselves!

In the themes table, which is available for you to inspect in the console, you’ll notice there is both an id column and a parent_id column. Keeping that in mind, you can join the themes table to itself to determine the parent-child relationships that exist for different themes.

themes %>% 
    # Inner join the themes table
    inner_join(themes, by = c("id" = "parent_id"), suffix = c("_parent", "_child")) %>%
    # Filter for the "Harry Potter" parent name 
    filter(name_parent == "Harry Potter")

It looks like the Harry Potter parent theme has a few children, which are named after the books and movies from the series.

Joining themes to their grandchildren

We can go a step further than looking at themes and their children. Some themes actually have grandchildren: their children’s children.

Here, we can inner join themes to a filtered version of itself again to establish a between our last join’s children and their children.

# Join themes to itself again to find the grandchild relationships
themes %>% 
  inner_join(themes, by = c("id" = "parent_id"), suffix = c("_parent", "_child")) %>%
  inner_join(themes, by = c("id_child" = "parent_id"), suffix = c("_parent", "_grandchild"))

Left-joining a table to itself

Some themes might not have any children at all, which means they won’t be included in the inner join.

themes %>% 
  # Left join the themes table to its own children
  left_join(themes, by = c("id" = "parent_id"), suffix = c("_parent", "_child")) %>%
  # Filter for themes that have no child themes
  filter(is.na(id_child))

Full, Semi, and Anti Joins

Differences between Batman and Star Wars

We’ll compare two themes, each of which is made up of many sets.

inventory_parts_joined <- inventories %>%
  inner_join(inventory_parts, by = c("id" = "inventory_id")) %>%
  arrange(desc(quantity)) %>%
  select(-id, -version)

inventory_parts_joined %>%
  # Combine the sets table with inventory_parts_joined 
  inner_join(sets, by = c("set_num")) %>%
  # Combine the themes table with your first join 
  inner_join(themes, by = c("theme_id" = "id"), suffix = c("_set", "_theme"))

Since each theme is made up of many sets, combining these tables is the first step towards being able to compare different themes.

Aggregating each theme

Previously, you combined tables to compare themes. Before doing this comparison, you’ll want to aggregate the data to learn more about the pieces that are a part of each theme, as well as the colors of those pieces.

inventory_sets_themes <- inventory_parts_joined %>%
  inner_join(sets, by = "set_num") %>%
  inner_join(themes, by = c("theme_id" = "id"), suffix = c("_set", "_theme"))

batman <- inventory_sets_themes %>%
  filter(name_theme == "Batman")

star_wars <- inventory_sets_themes %>%
  filter(name_theme == "Star Wars")

# Count the part number and color id, weight by quantity
batman %>%
  count(part_num, color_id, wt = quantity)

star_wars %>%
  count(part_num, color_id, wt = quantity)

Full-joining Batman and Star Wars LEGO parts

Now that we’ve got separate tables for the pieces in the batman and star_wars themes, we’ll want to be able to combine them to see any similarities or differences between the two themes.

batman_parts <- batman %>%
  count(part_num, color_id, wt = quantity)

star_wars_parts <- star_wars %>%
  count(part_num, color_id, wt = quantity)

batman_parts %>%
  # Combine the star_wars_parts table 
  full_join(star_wars_parts, by = c("part_num", "color_id"), suffix = c("_batman", "_star_wars")) %>%
  # Replace NAs with 0s in the n_batman and n_star_wars columns 
  replace_na(list(n_batman = 0, n_star_wars = 0))

Now, we’ve created a comprehensive table that includes the part number, color id, and quantity of each piece in the Batman and Star Wars themes!

Comparing Batman and Star Wars LEGO parts

The table you created in the last exercise includes the part number of each piece, the color id, and the number of each piece in the Star Wars and Batman themes. However, we have more information about each of these parts that we can gain by combining this table with some of the information we have in other tables.

parts_joined <- batman_parts %>%
  full_join(star_wars_parts, by = c("part_num", "color_id"), suffix = c("_batman", "_star_wars")) %>%
  replace_na(list(n_batman = 0, n_star_wars = 0))

parts_joined %>%
  # Sort the number of star wars pieces in descending order 
  arrange(desc(n_star_wars)) %>%
  # Join the colors table to the parts_joined table
  inner_join(colors, by = c("color_id" = "id")) %>% 
  # Join the parts table to the previous join 
  inner_join(parts, by = "part_num", suffix = c("_color", "_part")) 

We can now see the pieces side by side from each of these themes. Since the pieces are sorted by number of Star Wars pieces in descending order, we can see that the most common Star Wars piece is Black and has the part number 2780. While there are 392 pieces of this part in the Star Wars theme, we can also see from the table that there are 104 pieces of the same part in the Batman theme.

Something within one set but not another

We will determine which parts are in both the batwing and batmobile sets, and which sets are in one, but not the other. While answering these questions, we’ll also be determining whether or not the parts we’re looking at in both sets also have the same color in common.

batmobile <- inventory_parts_joined %>%
  filter(set_num == "7784-1") %>%
  select(-set_num)

batwing <- inventory_parts_joined %>%
  filter(set_num == "70916-1") %>%
  select(-set_num)

# Filter the batwing set for parts that are also in the batmobile set
batwing %>%
  semi_join(batmobile, by = c("part_num"))

# Filter the batwing set for parts that aren't in the batmobile set
batwing %>%
  anti_join(batmobile, by = c("part_num"))

Based on these joins, we now know that there are 126 parts in the batwing set that are also in the batmobile set, and 183 parts that are in the batwing set that aren’t in the batmobile set.

What colors are included in at least one set?

Besides comparing two sets directly, you could also use a filtering join like semi_join to find out which colors ever appear in any inventory part. Some of the colors could be optional, meaning they aren’t included in any sets.

# Use inventory_parts to find colors included in at least one set
colors %>%
  semi_join(inventory_parts, by = c("id" = "color_id"))

Which set is missing version 1?

Each set included in the LEGO data has an associated version number. We want to understand the version we are looking at to learn more about the parts that are included. Before doing that, we should confirm that there aren’t any sets that are missing a particular version.

# Use filter() to extract version 1 
version_1_inventories <- inventories %>%
  filter(version == 1)

# Use anti_join() to find which set is missing a version 1
sets %>%
  anti_join(version_1_inventories, by = c("set_num"))

Aggregating sets to look at their differences

To compare two individual sets, and the kinds of LEGO pieces that comprise them, we’ll need to aggregate the data into separate themes. Additionally, we’ll want to add a column so that we can understand the percentages of specific pieces that are part of each set, rather than looking at the numbers of pieces alone.

inventory_parts_themes <- inventories %>%
  inner_join(inventory_parts, by = c("id" = "inventory_id")) %>%
  arrange(desc(quantity)) %>%
  select(-id, -version) %>%
  inner_join(sets, by = "set_num") %>%
  inner_join(themes, by = c("theme_id" = "id"), suffix = c("_set", "_theme")) %>% 
  replace_na(list(quantity = 0))

batman_colors <- inventory_parts_themes %>%
  # Filter the inventory_parts_themes table for the Batman theme
  filter(name_theme == "Batman") %>%
  group_by(color_id) %>%
  summarize(total = sum(quantity)) %>%
  # Add a percent column of the total divided by the sum of the total 
  mutate(percent = total / sum(total))

# Filter and aggregate the Star Wars set data; add a percent column
star_wars_colors <- inventory_parts_themes %>%
  filter(name_theme == "Star Wars") %>%
  group_by(color_id) %>%
  summarize(total = sum(quantity)) %>%
  mutate(percent = total/ sum(total))

batman_colors %>%
  full_join(star_wars_colors, by = "color_id", suffix = c("_batman", "_star_wars")) %>%
  replace_na(list(total_batman = 0, total_star_wars = 0, percent_batman = 0, percent_star_wars = 0)) %>%
  inner_join(colors, by = c("color_id" = "id")) %>%
  # Create the difference and total columns
  mutate(difference = percent_batman - percent_star_wars,
         total = total_batman + total_star_wars) %>%
  # Filter for totals greater than 200
  filter(total>200)

With this combined table that contains all the information from the batman_colors and star_wars_colors tables, we can now create an informative visualization to compare the colors of these sets.

Visualizing the difference: Batman and Star Wars

We created colors_joined. Now we’ll create a bar plot with one bar for each color (name), showing the difference in percentages.

library(forcats)
colors_joined <- batman_colors %>%
  full_join(star_wars_colors, by = "color_id", suffix = c("_batman", "_star_wars")) %>%
  replace_na(list(total_batman = 0, total_star_wars = 0,  percent_batman = 0, percent_star_wars = 0)) %>%
  inner_join(colors, by = c("color_id" = "id")) %>%
  mutate(difference = percent_batman - percent_star_wars,
         total = total_batman + total_star_wars) %>%
  filter(total >= 200) %>%
  mutate(name = fct_reorder(name, difference)) 

# we need to add # to create our palette
colors_joined$rgb <- paste("#", colors_joined$rgb, sep="")
color_palette <- setNames(colors_joined$rgb, colors_joined$name)

library(ggplot2)
# Create a bar plot using colors_joined and the name and difference columns
ggplot(colors_joined, aes(name, difference, fill = name)) +
  geom_col() +
  coord_flip() +
  scale_fill_manual(values = color_palette, guide = FALSE) +
  labs(y = "Difference: Batman - Star Wars")

As you can see from the plot, the Batman set has more black, yellow, and red, while the Star Wars set has more light bluish gray, white, and tan.