---
title: "Joining Data with data.table in R"
author: "Joschka Schwarz"
---

```{r}
#| include: false
source(here::here("R/setup-ggplot2-tie.R"))
options(dplyr.summarise.inform = FALSE)
```

In the real world, data sets typically come split across many tables while most data analysis functions in R are designed to work with single tables of data. In this course, you'll learn how to effectively combine data sets into single tables using data.table. You'll learn how to add columns from one table to another table,\nhow to filter a table based on observations in another table, and how to identify records across multiple tables matching complex criteria. Along the way, you'll learn how to troubleshoot failed join operations and best practices for working with complex data sets. After completing this course you'll be well on your way to be a data.table master!\n

# 1. Joining Multiple data.tables 

This chapter will show you how to perform simple joins that will enable you to combine information spread across multiple tables.

## Welcome to the course

Theory. Coming soon ...


**1. Welcome to the course**

Hi, my name is Scott Ritchie. I'll be your instructor for this course on joining data in R with data table. Welcome, and I look forward to seeing you in the course.

**2. Joining data.tables**

A join describes the action of combining information from two different data tables into a single data table. This is a fundamental skill when working with multiple data sources. The majority of R's functions for analyzing and visualizing data are designed to work on a single data frame or data table. But, you'll often find data you want to analyze is spread across multiple datasets, that may come from different sources. For example, you might be working with two data tables in your customer database. One containing their demographic information, shown in blue, and another containing their shipping address, shown in orange. The question is: how do you build a single data table containing all the information about each customer? Joins are an efficient and reliable way of solving this type of problem.

**3. Course overview**

In chapter one of the course, you'll learn how to use the merge function to perform four types of joins that you can find in any data-driven language. In chapter two you'll learn how to incorporate joins directly into your data table workflows. In chapter three, you'll learn how to diagnose and avoid common join errors. Finally, in chapter four you'll learn how to concatenate data tables that have the same columns, and how to transform them between wide and long formats.

**4. Table keys**

The first skill you need to learn is to identify the join key columns. These are the columns you need in each data table to match the rows between them for a join. No matter what type of join you want to do, you will always need to know which columns to use as join keys. Returning to the customer database example, to match the rows between the two data tables you would need to use the values stored in the name column, as you can see from their highlighted matching values.

**5. Inspecting `data.tables` in your R session**

To identify join keys, you will need to learn about the contents of the data tables you are working with. There are a few different ways you can do this. The first way is using the tables() function. It will show you all data tables in your R session, along with their number of rows, their number and names of their columns, and how much space they occupy in terms of memory. It will also tell you any columns you have set as their keys, which you'll learn in the next chapter.

**6. Inspecting `data.tables` in your R session**

Another way is using the str() function. This is a general purpose function that will show you the type of data stored in any R object, in this case, a data table along with the types and first few entries of each column.

**7. Inspecting `data.tables` in your R session**

Finally, typing in the variable name and hitting enter in the console will show you the values stored in a data table. If it has more than 100 rows, only the first and last five rows are displayed by default.

**8. Let's practice!**

Now lets explore some of the data tables you will be using in this course. 

## Exploring data.tables

In this exercise, you will explore two `data.table`s: 

* `netflix`: contains information about some of the <a href="https://www.netflix.com">Netflix</a> 
original series released in 2017
* `imdb`: contains ratings for some TV shows and movies obtained from <a href="https://www.imdb.com">IMDB</a>

*This course touches on a lot of concepts you may have forgotten, so if you ever need a quick refresher, download the <a href="https://datacamp-community-prod.s3.amazonaws.com/6fdf799f-76ba-45b1-b8d8-39c4d4211c31">data.table Cheat Sheet</a> and keep it handy!*

**Steps**

1. Use the `tables()` function to show all the `data.tables`s in your R session (along with their number of rows, columns and other info). 
2. Use the `head()` function to print the first 6 rows of `netflix` and `imdb`. 
3. Print the `str()`ucture of `netflix` and `imdb`.

```{r}
# Load package
library(data.table)

# Load data
netflix <- fread("data/netflix_2017.csv")
imdb    <- fread("data/imdb_ratings.csv")

# What data.tables are in my R session?
tables()

# View the first six rows 
head(netflix)
head(imdb)

# Print the structure
str(netflix)
str(imdb)
```

Nice work! Let's take a closer look at these `data.table`s!

## Identifying join keys

The `netflix` and `imdb` `data.table`s from the previous exercise have been loaded into your workspace. 

> *Question*
> ---
> Which columns can be used to match the rows across these two `data.table`s?<br>
> <br>
> ⬜ The `"release_date"` column in `netflix` and the `"title"` column in `imdb`.<br>
> ⬜ The `"episodes"` column in `netflix` and the `"rating"` column in `imdb`.<br>
> ✅ The `"title"` column in both `netflix` and `imdb`.<br>

Well done! Remember that columns that link observations across `data.table`s are known as "join keys".

## Multiple data.tables, multiple keys

Three new `data.table`s are available in your workspace: `area`, `capitals`, and `population`, containing basic statistics about the geography and demographics of Australia.

> *Question*
> ---
> Which columns can be used to match rows across these three `data.table`s?<br>
> <br>
> ⬜ None, because no column is present in all three `data.table`s.<br>
> ⬜ The `"state"` column in `area` and `capitals`. But no key can uniquely identify rows in `population` and `capitals`.<br>
> ✅ The `"state"` column for joining `area` and `capitals`, and the `"city"` column for `capitals` and `population`.<br>

Excellent! A `data.table` may have different join key columns depending on the other `data.table` it is joined to.

## The merge function

Theory. Coming soon ...

**1. The merge function**

Now that you can successfully identify join keys, it's time to learn how to perform joins using the merge() function.

**2. Joins**

In this chapter, you will learn four different joins. The inner join, the full join, the left join, and the right join. Each of these joins give a different result, based on what observations are present in one data table but not in the other.All four are standard joins that originally come from database query languages, such as SQL. So the concepts and skills you will learn in this chapter are widely applicable, not just for the data table package in R. In this chapter, you will learn how to do these joins using the merge() function. This function comes from base R but is extended in the data table package to work efficiently with data tables.

**3. Inner join**

An inner join combines the columns of two data tables, keeping only the observations present in both data tables, that is, rows whose value in the join key column can be found in both data tables.Returning to our example customer database, an inner join of the demographics data table, shown in blue, to the shipping addresses data table, shown in orange, creates a new data table containing all the columns from both data tables, containing just the people with entries in both; those highlighted in pink, yellow, and green. An inner join is the default behavior of the merge() function.  It takes two data tables as inputs, one to its x and one to its y arguments, along with the name of the key column in each data table to the by.x and by.y arguments respectively.

**4. The by argument**

When the key columns have the same name in both data tables, you can use the by argument instead to avoid typing the column name twice.

**5. Full join**

If you want to keep all observations that are present in either data table you can supply an additional argument to the merge() function; setting the all argument to be equal to TRUE. This is known as a full join or a full outer join. Observations which were present in only one data table will have missing values in the columns from the other data table, as shown in the white cells in the result.

**6. Let's practice!**

In the next lesson, you will learn about left joins and right joins. But now, it's time for you to practice inner joins and full joins.

## Inner join

Two `data.table`s are available in your workspace:  

* `netflix`: contains a selection of original series released by <a href="https://www.netflix.com">Netflix</a> in 2017
* `imdb`: contains average user ratings for a selection of TV shows and movies from <a href="https://www.imdb.com">IMDB</a>. 
Your goal is use the <a href="https://www.rdocumentation.org/packages/data.table/topics/merge">`merge()`</a> function to perform an inner join to build a new `data.table` containing all series from `netflix` that have a rating in `imdb`.

**Steps**

1. Remind yourself of the contents of both `data.table`s by printing them.

```{r}
# Print the data.tables in your R session
netflix
imdb
```

2. Use the correct column to inner join `netflix` and `imdb` using the `merge()` function.

```{r}
# Inner join netflix and imdb
merge(netflix, imdb, by = "title")
```

Great job! Inner joins are useful when you want to build a complete set of information for observations that are present in both `data.table`s.

## Full join

Using the same `data.table`s as the previous exercise, this time use `merge()` to perform a full join to build a new `data.table` containing all rows present in either `netflix` or `imdb`.

**Steps**

1. Use the `merge()` function to full join the `netflix` and `imdb` `data.table`s.

```{r}
# Full join netflix and imdb
merge(netflix, imdb, by = "title", all = TRUE)
```

2. Try swapping the order of the `netflix` and `imdb` `data.table`s in the merge function to see what changes!

```{r}
# Full join imdb and netflix
merge(imdb, netflix, by = "title", all = TRUE)
```

Nice work! The new `data.table` contains all TV shows and movies listed in either `netflix` or `imdb`.

## Left and right joins

Theory. Coming soon ...

**1. Left and right joins**

There are two more joins you can perform using the merge() function, left joins and the right joins.

**2. Left joins**

A left join keeps only the observations that are present in the data table on the left side of the join. In other words, a left join will add information from the data table on the right to the data table on the left. This is useful when you have two data tables from different sources, but you're really only interested in the observations from one.The data table on the left side of the join is the data table given to the x argument of the merge() function. To perform a left join with the merge() function, you set the argument all.x to be equal to TRUE.

**3. Right joins**

Conversely, a right join keeps only the observations that are present in the data table on the right side of the join. The data table on the right side of the join is the data table given to the y argument of the merge() function. To perform a right join with the merge() function you set the argument all.y to be equal to TRUE.

**4. Right joins - Left joins**

The outcome is the same as swapping the order of the data tables in the merge() function and performing a left join. Most people find one or the other to fit more naturally when thinking about data and stick to that. Its only in rare cases when joining multiple data tables in a sequence of joins where you might need to use both left and right joins.

**5. Default values**

Any arguments you don't specify in a function call in R take on their default values. For example, the arguments all, all.x, and all.y for the merge() function have a default value of FALSE. You can look up the default values for any function's arguments by using the help() function.

**6. Exercise instructions**

In the code exercises throughout the rest of the course, you will be instructed to join one data table to another using the wording you see on the slide. Regardless of the type of join, the data table that you see after the word "to" should always be placed on the left side of the join in your code.So if we ask you to join the shipping data.table to the demographics data table, the demographics data table should always be on the left side of the join: the first argument to the merge function.

**7. Let's practice!**

Go ahead and code some left and right joins.

## Left join

In this exercise, you will practice left joins! In a left join, rows from only the left (or the first) `data.table` will be retained. 

Both `netflix` and `imdb` are available in your workspace.

**Steps**

1. Use the `merge()` function to left join `imdb` *to* `netflix`.

```{r}
# Left join imdb to netflix
merge(netflix, imdb, by = "title", all.x = TRUE)
```

Fantastic! Did you notice that missing information for observations that are not present in the right table of the join contain `NA`s for the right `data.table`'s columns?

## Right join

In this exercise, you will right join `imdb` *to* `netflix`. The resulting `data.table` should contain all entries from `imdb` with the release date and the number of episodes added for titles that are also listed in the `netflix` `data.table`.

**Steps**

1. Use the `merge()` function to right join `imdb` *to* `netflix`.

```{r}
# Right join imdb to netflix
merge(netflix, imdb, by = "title", all.y = TRUE)
```

2. Compare the right join you performed in the previous step to the left join of `netflix` *to* `imdb`.

```{r}
# Compare to a left join of netflix to imdb
merge(imdb, netflix, by = "title", all.x = TRUE)
```

Nicely done! Right joins and left joins are essentially the same, with the order of input `data.table`s swapped in the join.

## Mastering simple joins

Three new `data.tables` have been loaded into your R session containing information about the geography and population of Australia: `area`, `capitals`, and `population`. Your goal is to perform a sequence of joins to build a single `data.table` containing information about the geographical area of each Australian state and the population of its capital city, storing the final result in a new variable: `australia_stats`. There are three `data.tables` so you will need to perform two joins. First, you will join `capitals` and `population`, and then you will join the resulting `data.table` to the `area`.

**Steps**

1. Identify the key column so you can join `capitals` and `population`. Assign the column name as a string to `capitals_population_key`. 
2. Using this key, left join `population` to `capitals`.

```{r}
# Load data
capitals   <- fread("data/australia_population/australia_capitals.csv")
population <- fread("data/australia_population/australia_cities_top20.csv", drop = "percentage") 
area       <- fread("data/australia_population/australia_area.csv",         drop = "precentage")

# Identify the key for joining capitals and population
capitals_population_key <- "city"

# Left join population to capitals
capital_pop <- merge(capitals, population, by = capitals_population_key, all.x = TRUE)
capital_pop
```

3. Identify the key column so you can join `capital_pop` and `area`. Assign the column name as a string to `capital_pop_area_key`. 
4. Using this key, inner join `area` to `capital_pop`.

```{r}
# Identify the key for joining capital_pop and area
capital_pop_area_key <- "state"

# Inner join area to capital pop
australia_stats <- merge(capital_pop, area, by = capital_pop_area_key)

# Print the final result
australia_stats
```

Well done! Mastering these joins enables you to combine information in many different ways to build the right `data.table` to ask your data science questions of interest.

# 2. Joins Using data.table Syntax

In this chapter you will perform joins using the data.table syntax, set and view data.table keys, and perform anti-joins.

## Joins using data.table syntax

Theory. Coming soon ...

**1. data.table syntax**

Congratulations on completing the first chapter and welcome back. In this lesson, you will perform joins using the data table syntax.

**2. Recap of the data.table syntax**

The general form of a data table is shown here. It can be read out loud as "Take DT, filter its rows in i, then compute on columns j grouped by values in columns by.

**3. Joins**

To perform a join using this syntax, you pass another data table to the i argument. This can be read out loud as "Join DT to i using the column in on as the join key".You can think of it as just any other filter operation in i, except here you're filtering the rows in DT based on those that have matches to rows in i.

**4. Right joins**

This naturally leads to the definition of a right join. Like any other subset operation in R, values that have no match return missing values. So here, rows from the data table you are subsetting on in the i argument are kept in the result, with missing values where there was no match in the main data table.

**5. The on argument**

Join key columns are supplied to the on argument. This follows similar rules to the j and by arguments in the data table syntax. If you use the list function or its alias you can enter the join key column name as if it was a variable.You can also use a character vector to specify the join key. This also allows you to store the join key in a variable that you pass to the on argument. Note that here we haven't used the list function or its alias, so the data table syntax looks for the join underscore key variable, not for a column with the same name.

**6. Left joins**

To perform a left join you can swap the order of the data tables, so that the data table on the left side of the join is passed to the i argument. To left join the demographics data table shown in blue to the shipping data table shown in orange we swap the order of data tables from the previous slide and perform a right join on the demographics data table.

**7. Inner joins**

To perform an inner join you supply an additional argument, nomatch equals zero. This tells the data table syntax to ignore rows that cannot be matched between the two data tables, giving you an inner join.

**8. Full joins**

It is not possible to perform a full join with the data table syntax, so you will need to rely on the merge function you learned in chapter 1. Remember to set all equal to TRUE to perform a full join!

**9. Anti-joins**

Finally, another type of join you can perform using the data table syntax is an anti-join. This filters the main data table on the left so that it contains only rows that have no match in the data table on the right.Anti joins are performed by adding an exclamation mark, known as the "not" operator, before the data table you want to filter on in the i argument.

**10. Let's practice!**

Later on in this chapter you will learn how to use the other functionality of the data table syntax while joining data tables. For now, its your turn to try out joins using the data table syntax.

## Right join with the data.table syntax

`capitals` and `population`, the two `data.table`s containing information about the geography and population of Australia are available in your workspace. Your goal is to add the state of each city to the `population` by performing a right join using the `data.table` syntax.

**Steps**

1. Right join `population` to `capitals` using the `data.table` syntax.

```{r}
# Right join population to capitals using data.table syntax
capitals[population, on = .(city)]
```

2. To compare the output, right join `population` to `capitals` using the `merge()` function.

```{r}
# Right join using merge
merge(capitals, population, by = "city", all.y = TRUE)
```

Nice work! Did you notice the difference in row ordering in the result? This is because joins using the `data.table` syntax treat the `i` argument like a subset operation, so it returns rows in the order they appear in the `data.table` given to the `i` argument, while the `merge()` function sorts rows based on the values in the key column.

## Inner join with the data.table syntax

In the last exercise, you right joined `population` to `capitals`. The result contained all rows from `population` and had some `NA`s. In this exercise, you will perform an inner join instead, retaining rows only from both the tables.

**Steps**

1. Inner join `population` to `capitals` using the `data.table` syntax.

```{r}
# Inner join with the data.table syntax
capitals[population, on = .(city), nomatch = 0]
```

Great job! If you are performing many inner joins, you can change the default behaviour of `nomatch` by setting `options(datatable.nomatch = 0)` in your R session.

## Anti-joins

Anti-joins are useful when you want to filter rows in one table not found in the other. 
Your goal is to filter the `population` so that it does not contain the state capitals, and filter the `area` so that it only contains administrative areas, not the major Australian states.

**Steps**

1. Filter `population` so the result doesn't contain any rows from `capitals`.

```{r}
# Anti-join capitals to population
population[!capitals, on = .(city)]
```

2. Filter `area` so the result doesn't contain any rows from `capitals`.

```{r}
# Anti-join capitals to area
area[!capitals, on = .(state)]
```

Nicely done! Anti-joins are a useful way of identifying rows that have no match in another `data.table`.

## Setting and viewing data.table keys

Theory. Coming soon ...

**1. Setting and viewing data.table keys**

In this lesson, you will learn how to set and view the keys of a data table.

**2. Setting `data.table` keys**

In the previous lesson you learned how to perform joins using the data table syntax. In each case, you had to use the on argument to specify how to match rows between the two data tables. However, it is possible to tell R which columns are keys for each data table in advance of a join, removing the need for the on argument. This is useful if you find yourself performing several different joins with a single data table.Setting a key will also sort a data table by that column in memory, which makes joining and filtering operations on that columns much faster for large data tables.With that in mind, its useful to know you can set multiple key columns for a single data table. You'll learn more about joins that require multiple keys in the next chapter.

**3. The `setkey()` function**

The setkey() function is used for this purpose.It takes a single data table as its first argument, then any number of key column names as its remaining arguments.These can be entered as if they were variables, or can be wrapped inside quotes, either will work.If you don't provide any column names to the setkey() function, it will use all columns of the data table as its keys!

**4. The `setkey()` function**

When keys are set for two data tables, you can use the data table syntax without the on argument for performing joins.

**5. Setting keys programmatically**

You can also use the setkeyv() function to set the keys of a data table by passing in a character vector of the key column names. This is useful if you want to set the keys of a data table programmatically, where your key column names are stored in another variable.

**6. Getting keys**

You can check whether a data table has any key columns set by using the haskey() function, and get the key you've set by using the key() function.

**7. Getting keys**

If you haven't set the key for a data table then the haskey() function will return FALSE and the key() function will return NULL.

**8. Viewing all `data.tables` and their keys**

The tables() function you learned about in the very first lesson will also show you the keys you have set for any data tables in your R session.

**9. Let's practice!**

Now it's your turn to play with keys.

## Setting keys

The `netflix` and `imdb` `data.table`s are available in your workspace. Your goal is to set the keys for both `data.table`s so that you can perform a series of joins without needing to specify the join keys each time.

**Steps**

1. Set `title` as the key of both `netflix` and `imdb` using `setkey()`. 
2. Inner join `imdb` to `netflix` without specifying the `on` argument.

```{r}
# Set the keys
setkey(netflix, "title")
setkey(imdb, "title")

# Inner join
netflix[imdb, nomatch = 0]
```

Good job! Using `setkey()` will also sort the rows of a `data.table` by the key columns. This makes joins and filter operations on the keys more efficient, and can be much faster for large `data.tables`.

## Getting keys

You can also get and set the keys programmatically. In this exercise, only one of `netflix` and `imdb` has their key set. Your job is to find out which `data.table` has had their key set, then use that to set the key for the other `data.table`.

**Steps**

1. Check whether `netflix` and `imdb` have had any key set. 
2. Assign the key of the `data.table` which has a key to `the_key`. 
3. Set the key of the other `data.table` using `the_key`.

```{r}
# Check for keys
haskey(netflix)
haskey(imdb)

# Find the key
the_key <- key(netflix)

# Set the key for the other data.table
setkeyv(imdb, the_key)
```

Fantastic! The `key()` function is a useful way of reducing typing errors that can happen when manually typing long keys or multiple keys over and over again. This can be used together with the `haskey()` and `setkeyv()` functions for programmatic key checking and setting.

## Incorporating joins into your data.table workflow

Theory. Coming soon ...


**1. Incorporating joins into your data.table workflow**

The real power of using the data table syntax for performing joins is that it allows you to incorporate joins into your other data table workflows. This enables you to perform rapid data analysis when your data is spread across multiple data tables.

**2. Chaining data.table expressions**

The most flexible way of incorporating joins into your data table workflows is by chaining data table expressions.In the first expression, you perform your join operation. Then the second data table expression allows you to work on the result with any of the other data table expression functionality you've learned in the previous course.The general form on the slide can be read as "Join DT1 to DT2 using the column in on as the join key. Then in the result, filter by rows in i, compute on columns j grouped by values in columns by."

**3. Join then compute**

Here's an example. We have two data tables, one filled with information about the business's customers,

**4. Join then compute**

and a second with details of the purchase history.

**5. Join then compute**

To get the average spent per sale for return customers grouped by gender, we chain two data table expressions. In the first, we join customers to purchases, then in the second, we filter to customers with more than 1 sale, calculate the average spent per sale in j, grouped by the gender column in by.

**6. Computation with joins**

You can also use the j argument to compute on columns in the same data table expression as a join. This is a memory efficient way to perform calculations on the result of a join because the data table expression will create only the join result for the columns used in j in memory. This can save a lot of time and memory for large data tables!

**7. Joining and column creation**

Column creation can also be performed in the same data table expression as a join. The new column will be added to the main data table on the left side of the join. In the example on the slide, after joining purchases to customers, we are adding a new column, return_customers to the customers data table, which is computed based on the sales column from the purchases data table in the result of the join.

**8. Grouping by matches**

The by argument gains a special symbol, .EACHI, when used in a join expression. This lets you group computation in j by each row in the data table on the right side of the join.This can be read as "Join DT1 to DT2, then for each row in DT2 group calculations in j by the matching rows in DT1."

**9. Grouping by matches**

In this example, we used by = .EACHI to count the number of shipping addresses associated with each person in the customers data table.

**10. Grouping by columns with joins**

The by argument also works slightly differently than normal when used in a join expression.It can only be provided columns from the main data table on the left side of the join, and you can only perform computation on columns from the main data table.

**11. Grouping by columns with joins**

For example, you could left join shipping to customers to calculate the average age, grouped by gender, of customers with shipping addresses, because both the age and gender columns are in the customers data table.

**12. Let's practice!**

Now its time for you to join data tables while doing some computations!

## Exploring the Australian population

Your goal is to calculate the total percentage of people living in major cities of Australia (listed in `capitals`). For this exercise, `population` has a new column (`"percentage"`) containing the percentage of people living in each of its listed cities.

**Steps**

1. First, inner join `capitals` to `population`.

```{r}
# read data
population <- fread("data/australia_population/australia_cities_top20.csv")

# Inner join capitals to population
population[capitals, on = .(city), nomatch = 0]
```

2. In the same `data.table` expression, <a href="https://www.rdocumentation.org/packages/base/topics/sum">`sum()`</a> the `"percentage"` column to calculate the total percentage of people living in major Australian cities.


```{r}
# Join and sum
population[capitals, on = .(city), nomatch = 0,
           j = sum(percentage)]
```

Well done! Using the `j` argument to compute information from the result of the join will allow you to quickly ask and answer questions about your data. Here in a single command you've learnt roughly 65% of Australians live in one of its major cities.

## Finding multiple matches

Two new `data.table`s have been loaded in your workspace:  

* `life_exp`: contains the life expectancy of each country in 2010 sourced from the <a href="https://www.gapminder.org">Gapminder foundation</a> 
* `continents`: contains a mapping between each country and the continent(s) they are part of built from information provided by <a href="https://www.countries-ofthe-world.com/">Countries-ofthe-World.com</a>. 
In this exercise, you will find the countries that are listed in more than one continent

**Steps**

1. Complete the code to build a `data.table` containing the *number of matches* in `continents` for each row in `life_exp`.

```{r}
# Load data
life_exp   <- fread("data/life_expectancy/gapminder_life_expectancy_2010.csv")
continents <- fread("data/life_expectancy/continents.csv") 

# How many continents is each country listed in?
continents[life_exp, on = .(country), .N, 
           by = .EACHI]
```

2. Chain a second `data.table[]` expression to the first to filter the result of the previous step to contain just countries with more than one match between `life_exp` and `continents`.

```{r}
# What countries are listed in multiple continents?
continents[life_exp, on = .(country), .N, 
           by = .EACHI][N > 1]
```

## Exploring world life expectancy

The two `data.table`s from the previous exercise have been loaded into your workspace:

* `life_exp`: contains the life expectancy of each country in 2010 sourced from the <a href="https://www.gapminder.org">Gapminder foundation</a> 
* `continents`: contains a mapping between each country and the continent(s) they are part of built from information provided by <a href="https://www.countries-ofthe-world.com/">Countries-ofthe-World.com</a>. 
Your goal this time is to calculate the average life expectancy across countries within each continent.

**Steps**

1. Inner join `life_exp` to `continents` and calculate the <a href="https://www.rdocumentation.org/packages/base/topics/mean">`mean()`</a> `"years"` of life expectancy grouped by the `"continent"` column.


```{r}
# Change names
setnames(life_exp, old = "life_expectancy", new = "years")

# Calculate average life expectancy per continent:
avg_life_expectancy <- continents[life_exp, on = .(country), 
                                  nomatch = 0][, j = mean(years), 
                                               by = .(continent)]
avg_life_expectancy
```

Excellent work! Using `by = .EACHI` is a useful way of checking your join has the number of matches you expect, and finding rows that may cause problems in downstream analysis. Remember if you want to use `by=` to group computations in `j` by another column you will need to chain a second `data.table[]` expression.

# 3. Diagnosing and Fixing Common Join Problems

This chapter will discuss common problems and errors encountered when performing data.table joins and show you how to troubleshoot and avoid them.

## Complex keys

Theory. Coming soon ...

**1. Complex keys**

Welcome back. In this lesson, you'll learn how to diagnose misspecified joins, and how to use more complex join keys.

**2. Misspecified joins**

A misspecified join is one where you don't use the correct join keys. This can lead to a variety of errors and malformed data tables.All of these are good indicators you need to go back and double-check your join keys.

**3. Column type mismatch**

If you see an error message with the word "bmerge" and "typeof" it means that the join key columns you've specified have different types, so they can't be matched.You might be using the wrong columns,

**4. Column type mismatch**

Or the columns may have been loaded in as different types and need converting so that both have the same type.

**5. Malformed full joins - no common key values**

If the columns have the same type, then the output and difficulty of diagnosing the problem will depend on the type of join, and whether the columns coincidentally have any values in common. If the columns have no values in common then a full join will lead to the two data tables being stacked, and filled with missing values.

**6. Malformed right and left joins - no common key values**

A right or left join will just return the rows from one of the data tables, with columns from the other filled entirely with missing values.

**7. Malformed inner joins - no common key values**

And an inner join will have zero rows.

**8. Malformed joins - coincidental common key values**

If you use the wrong key, but the columns coincidentally share some common values, the problem can be much harder to pick up. So its important to check that the result of your join makes sense.

**9. Avoiding misspecified joins**

The best way to avoid misspecified joins is to take the time to learn about the information each column contains so that you can critically reason about how to match rows before attempting a join.There are several factors that can make identifying join keys more difficult than the examples you've encountered in previous lessons.

**10. Keys with different column names**

Sometimes the join keys might have different column names in each data table.In these cases, you use the by dot x and by dot y arguments in the merge() function,and in the data table join syntax you use an equals operator in the on argument, or in the character vector supplied to the on argument.

**11. Multi-column keys**

You may also need multiple columns to match rows between data tables. This can be because the information that uniquely identifies rows is split across multiple columns,

**12. Multi-column keys**

or because there are multiple entries for each entity.

**13. Specifying multiple keys with merge()**

In the merge() function, multiple key columns are supplied as a character vector to the by argument.If one or more of these keys has different column names in both data tables, you will need to supply a character vector to both the by dot x and by dot y arguments. Join key column names are matched in the order they appear in each character vector.

**14. Specifying multiple keys with the data.table syntax**

When using the data table syntax, multiple keys can be supplied separated by commas.Columns that have different names are specified using an equals sign.

**15. Final Slide**

Now, its your turn to practice with complex keys.

## Keys with different names

You have been contracted by a local school to perform some analysis using their internal databases. Two of those datasets are available in your R session: 

* `students`: contains information about the students attending the school 
* `guardians`: contains information about the adult who is the primary point of contact for the school for each student. 
Your goal is to build a new `data.table` containing all the information about the guardian for each student.

**Steps**

1. Use the `merge()` function to full join `guardians` to `students` using the `"name"` column as the join key. Do the results look correct?

```{r}
# Load data
students  <- fread("data/school_db/school_db_students.tsv")
guardians <- fread("data/school_db/school_db_guardians.tsv")

# Full join
merge(students, guardians, by = "name", all = TRUE)
```

2. Now see what happens when you left join `guardians` to `students` with the `merge()` function using `"name"` as the join key.

```{r}
# Left join
merge(students, guardians, by = "name", all.x = TRUE)
```

3. Perform an inner join using the `data.table` syntax.

```{r}
# Inner join
students[guardians, on = .(name), nomatch = 0]
```

4. Explore the `data.table`s in your console to find the column in each of `students` and `guardians` that matches rows between them, then modify the code to use these column names to perform an inner join.


```{r}
# What are the correct join key columns?
students[guardians, on = .(guardian = name), nomatch = 0]
```

Nice work! Knowing what to expect when a join goes wrong will help you quickly catch and diagnose problems in the future. Remember, join keys can have different column names in each `data.table`.

## Multi-column keys

Two new datasets are available in your R session: 

* `subjects`: contains the subject each student is taking each semester this year 
* `locations`: contains the room allocations for each subject
Your goal is to build a new `data.table` with the locations of each subject for each student.

**Steps**

1. Right join `locations` to `subjects` using the `"subject"` and `"semester"` columns in both `data.tables` as two separate join key columns. You should get an error.

```{r, error=TRUE}
# Load data
subjects  <- fread("data/school_db/school_db_subjects.tsv")
setnames(subjects, old = "class", new = "subject")

locations <- fread("data/school_db/school_db_locations.tsv")
setnames(locations, old = "class", new = "subject")
locations[, semester := as.character(semester)]

# Right join
subjects[locations, on = .(subject, semester)]
```

2. Print the `str()`ucture of `subjects` and `locations`. 
3. Is the class of the `semester` column same in both? If yes, assign `TRUE` to `same_class`, else assign `FALSE`.

```{r}
# Structure 
str(subjects)
str(locations)

# Does semester have the same class? 
same_class <- FALSE
```

4. Use the `as.integer()` function to change the class of the `"semester"` column in `locations`.
5. Right join `locations` to `subjects` again, using the `data.table` syntax.

```{r}
# Fix the column class
locations[, semester := as.integer(semester)]

# Right join
subjects[locations, on = .(subject, semester)]
```

Great job! Sometimes you will need to make the column types consistent to perform a successful join.

## Multi-key, single-key

In addition to `locations`, you will work with the `teachers` dataset in this exercise. It contains information about the teacher for each subject. The teacher of each subject does not change between semesters. Your goal is to build a new `data.table` containing the room allocation for each subject, using the `teachers` and `locations` `data.table`s.

**Steps**

1. Identify the key column(s) for joining `teachers` and `locations` and assign it as a character vector in the form `c("teachers_join_key" = "locations_join_key")` to the `join_key` variable. 
2. Using the `data.table` syntax, right join `locations` to `teachers` using the `join_key` variable.

```{r}
# Load data
teachers <- fread("data/school_db/school_db_teachers.tsv")
setnames(teachers, old = "subject", new = "topic")

# Identify and set the keys
join_key <- c("topic" = "subject")

# Right join
teachers[locations, on = join_key]
```

Fantastic job! Even though the previous exercise required the `\\"subject\\"` and `\\"semester\\"` columns from `locations` as the join key, here only the `\\"subject\\"` column was required to match its rows to `teachers`. Remember the join keys depend on the two `data.tables` in the join!

## Tricky columns

Theory. Coming soon ...

**1. Problem columns**

In the last lesson you learned how to work with complex join keys, and how to diagnose and fix problems arising from misspecified joins. In this lesson you will learn how to diagnose common problems you may encounter with data table columns when joining.

**2. Common column names**

When joining two data tables you may sometimes find that they share column names which are not the join keys.An example is shown on the slide. Both data tables have "name", "gender", and "age" columns, and you can see that to join these two data tables, you would need to match the name and parent columns for the join keys.Common column names like this pose two challenges: they make it harder to correctly identify the join keys and they make it harder to interpret the result of the join.

**3. Common column names**

So what happens to these columns in a join?With the data table syntax, columns from the data table passed to the i argument will have "i dot" added to the start of their column names.

**4. Common column names with merge()**

When using the merge() function, these common column names will instead have "dot x" or "dot y" added to the end, denoting which of the input data tables they came from.

**5. Adding context with your own suffixes**

The suffixes argument to the merge() function allows you to use different suffixes than just "dot x" and "dot y". This is a useful way of providing helpful context to the result of the join.

**6. Renaming columns**

Another solution is to rename these columns, either before or after the join. This can be done with the setnames() function.You can either give it a single character vector to give new names to all the columns in a data table, or two character vectors to rename a subset of columns.

**7. Joining with `data.frames`**

Another problem you may run into is mixing of data tables and data frames in your R session. The merge() function also works for data frames, and can also be used to join a data frame with a data table.Sometimes, data frames store the unique identifying information about their rows in the rownames. You will need to extract these if you want to use them as the join keys or want to keep that information in the result of the join.The simplest way to do this is to convert it to a data table using the as dot data dot table function along with the keep dot rownames argument to convert this into a column with the column name of your choice.

**8. Let's practice!**

It's time to join data tables with tricky columns.

## Column name suffixes

The three `data.table`s containing information about the geography and population of Australia have been loaded into your R session: `capitals`, `area`, and `population`. Both `area` and `population` now have a column named `"percentage"`. The `"percentage"` column in `area` contains the percentage of total land mass each state occupies. The `"percentage"` column in `population` contains the percentage of the total population living in each `city`.

**Steps**

1. Use the `merge()` function to inner join `population` to `capitals` and save the result to `capital_pop`. 
2. Use the `merge()` function to left join `area` to `capital_pop` and view the result.

```{r}
# Inner join
capital_pop <- merge(capitals, population, by = "city")

# Left join
merge(capital_pop, area, by = "state", all.x = TRUE)
```

3. This time, left join `area` to `capital_pop` changing the suffixes of the two `"percentage"` columns to `".pop"` and `".area"`.

```{r}
# Inner join from step 1
capital_pop <- merge(capitals, population, by = "city")

# Left join with suffixes
merge(capital_pop, area, by = "state", all.x = TRUE, suffixes = c(".pop", ".area"))
```

Good job! Changing the suffixes is good practice because it makes it easier to know what each column represents when you come back to the joined `data.table` at a later time.

## Joining a data.frame

The `netflix` and `imdb` datasets have been loaded into your R session. Your goal is to add IMBD ratings to all series in the `netflix` dataset, but this time `netflix` has been loaded in as a `data.frame` instead of a `data.table`.

**Steps**

1. Convert `netflix` to a `data.table` using the `as.data.table()` function so that its rownames become a column named `"series"`. 
2. Right join the new `data.table` you've just created,`netflix_dt`, to `imdb` using the `data.table` join syntax.

```{r}
# load data
netflix <- netflix |> tibble::column_to_rownames("title")

# Convert netflix to a data.table
netflix_dt <- as.data.table(netflix, keep.rownames = "series")

# Right join
imdb[netflix_dt, on = .(title = series)]
```

Nice one! You can use the `merge()` function to join a `data.frame`, but if you want to use the row names you will need to convert it into a column first.

## Duplicate matches

Theory. Coming soon ...

**1. Duplicate matches**

The last set of join problems you will commonly encounter arise from duplicate matches in the join key values.

**2. Join key duplicates**

Sometimes you will want to join two data tables based on columns with duplicate entries. In the example shown on the slide, we are joining two data tables containing data we've measured on the microbial content of soil at two different farm sites. We've used multiple methods to try to quantify whether a bacterial genus is present, and if so, by how much? We then might want to join these two data tables to get a list of bacteria that could be found at both sites using any method, so we join the two data tables using the genus column as the join key.This leads to multiplicative matches. Each row in the site2_ecology data table will match two rows in the site1 ecology_data table, leading to 12 matches all up, six for each bacteria shown.

**3. Error from multiplicative matches**

If you try to run this code, you will get a long error as shown here warning you that the result will have more rows than both data tables combined.This error is shown to protect you from making unexpectedly large data tables by accident because multiplicative matches are a common problem in misspecified joins, which you learnt about in the first lesson of this chapter.

**4. Allowing multiplicative matches**

When you do a join like this intentionally, you can set allow dot cartesian equals TRUE to let the join proceed.This argument is found in both the data table syntax and the merge() function.

**5. Allowing multiplicative matches**

With data table syntax, you will get the following data table as a result

**6. Missing values**

If you have missing values in your join key column, then these can also lead to multiplicative matches, because they will match all other missing values in the join.

**7. Filtering missing values**

To remove these matches, which are likely meaningless, you can filter out rows with missing values in the join key columns using the is dot na function along with not operator.

**8. Keeping only the first match**

In some cases, you may want to join two data tables where each value in the join key column has multiple matches, but you only want to keep the first match. For example, we might have multiple collections at site 1, but when matching to the site2_ecology data table we only want to match the most recent collection.To do this you can set mult equals first to keep only the first matching row in the main data table on the left side of the join.

**9. Keeping only the last match**

Conversely, you can set mult equals last to keep only the last matching row.

**10. Identifying and removing duplicates**

You can also use the duplicated() and unique() functions to identify and remove duplicates.

**11. The duplicated() function**

The duplicated() function from the the data table package will tell you which rows are duplicates. It returns a vector of TRUE and FALSE values, where each entry corresponds to a row in the data table. A value of TRUE means that row has the same values as one of the previous rows in the data table.An additional argument, "by", can be used to restrict those checks to only a subset of columns, for example looking for duplicates in just the join key column.

**12. The unique() function**

The unique() function takes the same arguments, but instead returns the data table without the rows that were determined to be duplicates.

**13. Changing the search order**

You can also change the direction the duplicated() and unique() functions search for and remove duplicates by specifying fromLast equals TRUE.

**14. Let's practice!**

Now it's your turn to practice handling duplicate matches.

## Joining with missing values

Two new `data.tables` have been loaded into your R session: `heart` and `cardio`. Each one contains a set of <a href="https://en.wikipedia.org/wiki/DNA_microarray">microarray probes</a> you have found to be associated with heart disease in two separate studies*. Each probe measures the <a href="https://en.wikipedia.org/wiki/Gene_expression">expression levels</a> of a gene. Each gene can be measured by one or more probes, and some probes do not have any known gene annotation in the <a href="https://www.ncbi.nlm.nih.gov/refseq/">human genome reference sequence</a>. The two studies have used different microarray platforms that use different probes to measure each gene. Your goal is to find which genes had reproducible associations with heart disease in both studies.

* Note: associations are randomly generated, not representative of any true biological finding or real dataset.

**Steps**

1. Using the `merge()` function, inner join `cardio` to `heart` with the appropriate argument to override any errors that you encounter. 
2. Remove the probes from both `data.tables` with no gene annotation (i.e., remove rows with missing values in the `gene` column). 
3. Repeat the inner join with the new `data.tables` to get a `data.table` of reproducible associations between genes and heart disease.

```{r}
# Load data
heart  <- fread("data/heart_data/illumina_chd_genes.csv",  na.strings  = "")
cardio <- fread("data/heart_data/affymetrix_chd_genes.csv", na.strings = "") 

# Try an inner join
merge(heart, cardio, by = "gene", allow.cartesian = TRUE)

# Filter missing values
heart_2  <- heart[!is.na(gene)]
cardio_2 <- cardio[!is.na(gene)]

# Inner join the filtered data.tables
merge(heart_2, cardio_2, by = "gene")
```

Nice work! If the only columns that have the same name across two data.tables are the join keys, you can drop the `by` argument from `merge()` - it will find and use these columns as the join keys automatically!

## Filtering duplicates

The `heart_2` and `cardio_2` data tables you filtered for missing values are available in your workspace. Your goal is to select one representative probe per gene in each `data.table` so that each gene has only a single entry in the join result. You want to select the probe with the weakest association to get a conservative estimate of reproducibility. The `"change"` column contains the fold change in expression levels for each probe between the healthy subjects and those with heart disease*. The `"pvalue"` column contains the p-value for the association strength. Rows are ordered by decreasing order of association strength (by increasing P-value).

* Note: associations are randomly generated, not representative of any true biological finding or real dataset.

**Steps**

1. Use the `unique()` function to remove duplicate entries in the `"gene"` column in both `heart_2` and `cardio_2`. Keep only the *last* row for each gene.  
2. Inner join `cardio_3` to `heart_3`  using the `merge()` function. Append `".heart"` and `".cardio"` as suffixes to the `"change"` and `"pvalue"` columns.

```{r}
# Keep only the last probe for each gene
heart_3  <- unique(heart_2,  by = "gene", fromLast = TRUE)
cardio_3 <- unique(cardio_2, by = "gene", fromLast = TRUE)

# Inner join
reproducible <- merge(heart_3, cardio_3, by = "gene", suffixes = c(".heart", ".cardio"))
reproducible
```

Great job! You could also have filtered duplicates using the `duplicated()` function: `heart <- heart[!duplicated(heart, by=\\"gene\\", fromLast = TRUE)]`.

## Joining and filtering duplicates

Another `data.table` has been loaded into your R session, `framingham`, containing a set of 35 genes and their associations with heart disease from an open access study <a href="https://www.ncbi.nlm.nih.gov/pmc/articles/PMC3684247/">by Joehanes R. *et al.* published in 2013</a>. Your goal is to compare the results of this study to the results from each of your studies. The `cardio_2` and `heart_2` `data.table`s filtered for missing values, but still containing multiple probes for each gene, have been loaded into your R session. The `data.table` of reproducible associations you created in the last exercise, `reproducible`, has also been loaded into your R session.

**Steps**

1. Using the `data.table` syntax, right join `framingham` to `heart_2`, taking the first probe for each gene in `heart_2`. 
2. Anti-join `framingham` to `reproducible` to see whether you found any genes that have not previously been seen to have an association with heart disease.

```{r}
# Load data
framingham <- fread("data/heart_data/framingham_chd_genes.csv", na.strings = "")

# Right join taking the first match
heart_2[framingham, on = .(gene), mult = "first"]

# Anti-join
reproducible[!framingham, on = .(gene)]
```

Excellent work! You could also select the last probe per gene by setting `mult = \\"last\\"`.

# 4. Concatenating and Reshaping data.tables

In the last chapter of this course you'll learn how to concatenate observations from multiple tables together, how to identify observations present in one table but not another, and how to reshape tables between long and wide formats.

## Concatenating two or more data.tables

Theory. Coming soon ...

**1. Concatenating data.tables**

Welcome back! In this lesson you will learn how to work with datasets whose rows are spread across multiple data tables by concatenating them into a single data table.

**2. Same columns, different data.tables**

Sometimes datasets you want to work with are split across multiple data tables, usually because they've been read in from multiple files. You may want to concatenate these rows into a single data table.

**3. Concatenation functions**

There are two functions you can use to concatenate data tables. The rbind() function, and the rbindlist() function.

**4. The rbind() function**

The rbind() function takes any number of data tables as inputs, and concatenates their rows into a single data table.

**5. Adding an identifier column**

Each of these variables can be given names using the equals operator, and an extra argument "idcol" will tell rbind() to use these names to create an extra column in the result that indicates each row's data table of origin.

**6. Adding an identifier column**

If you use the idcol argument without naming the variables the data tables will be numbered instead.

**7. Adding an identifier column**

And if you don't supply a column name to idcol and just set it to true, the column will be named "dot id".

**8. Handling missing columns**

When the input data tables each have a different number of columns, you will need to set the fill argument to TRUE. This will fill the missing columns with NAs in the result.

**9. Handling missing columns**

If you don't do this, the code will result in an error that looks like this, telling you that the column numbers are inconsistent.

**10. The rbindlist() function**

You can use the rbindlist() function if your data tables are stored as elements of a single list.An example of where you might find this useful is when you have several files you want to import into a single data table. Rather than reading each file into separate variables, you could load these into a list by lapply()-ing the fread() function over a vector of file names, and then use the rbindlist() function to concatenate their rows into a single data table.

**11. Adding an identifier column**

When using the rbindlist() function, the idcol argument uses the names of the list elements when creating the additional identifier column.

**12. Handling different column orders**

Finally, the use dot names argument in both rbind() and rbindlist() functions matches columns by their names when concatenating data tables.

**13. `data.tables` with different column names**

Setting use dot names to false allows you to concatenate data tables whose columns have different names.

**14. Pitfalls of `use.names = FALSE`**

But, you need to be careful because it will mean columns are always concatenated in the order they are found.

**15. Differing defaults**

The use dot names argument has different defaults in the rbind() and rbindlist() functions.By default it is true in the rbind() function, but in the rbindlist() function its default is FALSE, and changes to TRUE if you set fill equal to TRUE.

**16. Let's practice!**

Now it's your turn to see how these functions work in practice.

## Concatenating data.table variables

Three `data.table`s containing Ebola case numbers <a href="http://apps.who.int/gho/data/node.ebola-sitrep">reported in Guinea to the World Health Organisation</a> for three weeks at the height of the 2014 Ebola outbreak have been loaded into your R session: `ebola_W50`, `ebola_W51`, and `ebola_W52`. Your goal is to create a single `data.table` containing the case numbers in each region of Guinea across all three weeks.

**Steps**

```{r}
# Load data
ebola_W50 <- fread("data/ebola_cases/ebola_2014_W50.csv")
ebola_W51 <- fread("data/ebola_cases/ebola_2014_W51.csv")
ebola_W52 <- fread("data/ebola_cases/ebola_2014_W52.csv")
```

1. Concatenate `ebola_W50` and `ebola_W51` (in that order) using the <a href="https://www.rdocumentation.org/packages/data.table/topics/rbindlist">`rbind()`</a> function.

```{r}
# Concatenate case numbers from weeks 50 and 51
rbind(ebola_W50, ebola_W51)
```

2. Modify your call to `rbind()` to also concatenate `ebola_W52`. You should get an error.

```{r, error=TRUE}
# Concatenate case numbers from all three weeks
rbind(ebola_W50, ebola_W51, ebola_W52)
```

3. Fix your call to `rbind()` so that the three `data.tables` are successfully concatenated.

```{r}
# Modify the code
rbind(ebola_W50, ebola_W51, ebola_W52, fill = TRUE)
```

Good job! Datasets with regular updates may come in separate files. The `rbind()` function enables you to reliably and efficiently concatenate these into a single `data.table` once they have been loaded into your R session with `fread()`.

## Concatenating a list of data.tables

A list of `data.table`s has been loaded into your R session: `gdp`. Its elements contain a `data.table` for each continent, each `data.table` containing the gross domestic product (gdp) in the year 2000 for the countries in each continent (data sourced from the <a href="https://www.gapminder.org">Gapminder foundation</a>). Your goal is to build a new `data.table` containing the observations from all `data.table`s in the `gdp` `list`

**Steps**

1. Concatenate all `data.table`s in the `gdp` `list`, saving the result to `gdp_all_1`.

*Column 2 ['country'] of item 3 appears in position 1 in item 2. Set use.names=TRUE to match by column name, or use.names=FALSE to ignore column names. use.names='check' (default from v1.12.2) emits this message and proceeds as if use.names=FALSE for  backwards compatibility. See news item 5 in v1.12.2 for options to control this message.*

```{r}
# Load packages
library(stringr)
library(purrr)

# Load data
gdp <- list.files("data/gdp/", full.names = T, pattern = ".*_2000.csv") %>%
            .[!(. |> str_detect("middle_east"))] %>%
            map(fread) |> 
            set_names(c("africa","asia","europe","north_america","oceania","south_america"))

# Concatenate its data.tables
gdp_all_1 <- rbindlist(gdp, use.names = FALSE)
```

2. Modify the code so that a new column, `"continent"` is created in the result stored in `gdp_all_2`, which contains the continent(s) each country is located in.
3. Inspect the result in your console.

```{r}
# Concatenate its data.tables
gdp_all_2 <- rbindlist(gdp, idcol = "continent", use.names = FALSE)
```

4. In your console run the code to check the result of your previous call to `rbindlist()`. Can you see any problems?
5. Modify your call to `rbindlist()` to fix any problems you have found, saving the result in `gdp_all_3`.

```{r}
# Run this code to inspect gdp_all_2
gdp_all_2 <- rbindlist(gdp, idcol = "continent")
str(gdp_all_2)
gdp_all_2[95:105]

# Fix the problem
gdp_all_3 <- rbindlist(gdp, idcol = "continent", use.names = TRUE)
gdp_all_3
```

Fantastic! Remember to check the data tables created by `rbind()` or `rbindlist()` for correctness: these functions do not throw an error if the wrong columns are concatenated!

## Set operations

Theory. Coming soon ...

**1. Set operations**

In this lesson, you will learn how to use set operations to compare and combine the rows of two data tables that have the same columns.

**2. Set operation functions**

This group of three functions will enable you to identify rows that are duplicated across two data tables, those unique to either data table, and concatenate two data tables keeping only the unique rows in both.

**3. Set operations: `fintersect()`**

The fintersect() function takes two data tables that have the same columns as its inputs and returns a new data table containing the set of rows that can be found in both data tables. You can see in the example running fintersect() on these two data tables will return just the highlighted rows.

**4. `fintersect()` and duplicate rows**

By default only one copy of each row is returned, even if there are multiple copies in each data table.

**5. `fintersect()` and duplicate rows**

You can set all equals TRUE to keep all pairs of matching duplicates. In the example, two copies of the yellow lion row can be found in both dt1 and dt2, so there are two copies in the result. The extra copy in dt1 is ignored, because it doesn't have another copy it can match to in dt2.

**6. Set operations: `fsetdiff()`**

The fsetdiff() function takes two data tables that have the same columns as its inputs, and returns a new data table containing the set of rows that are only found in the data table supplied as its first argument.In the example here, you can see fsetdiff() returns the rows from dt1 highlighted in blue. These are the rows that are unique to dt1.

**7. `fsetdiff()` and duplicates**

When there are duplicate rows, only one copy of each row is returned in the result by default.

**8. `fsetdiff()` and duplicates**

When you set all equals TRUE, any copies unique to the first data table are also returned.Here, not only do we keep both copies of the "antelope" row highlighted in purple, but one of the "lion" rows highlighted in yellow is also included in the result. This is because there are three copies of this row in dt1, and only two in dt2, so the extra copy in dt1 is returned because it does not have a matching pair in dt2.

**9. Set operations: `funion()`**

The funion() function takes two data tables that have the same columns as its inputs and returns a new data table containing the set of all unique rows found in either data table.

**10. `funion()` and duplicates**

By default, duplicate rows are ignored. The result will only contain one copy of each unique row.

**11. `funion()` and duplicates**

Setting all equals TRUE will keep all copies of each row. The result is equivalent to using the rbind function to concatenate the two data tables.

**12. Removing duplicates when combining many `data.tables`**

The funion() function is a useful way to concatenate two data tables while removing duplicate rows.When working with more than two data tables, you can concatenate them all using the rbind() or rbindlist() functions, and then use the duplicated() and unique() functions to identify or remove the duplicate rows.

**13. Let's practice!**

Now, its your turn to practice set operations.

## Identifying observations shared by multiple tables

The list of data tables, `gdp`, is available in your workspace. Your goal is to find the countries that are located in more than one continent, *i.e.*, rows that are present in more than one `data.table` in `gdp`.

**Steps**

1. Use the `fintersect()` function to build a new `data.table` containing countries located in both Europe and Asia.
2. Concatenate all `data.table`s in `gdp` and assign it to `gdp_all`.   
3. Use the `duplicated()` function to filter `gdp_all` to rows that are duplicates to find all countries located in more than one continent.

```{r}
# Load proper data
# Load data
gdp <- list.files("data/gdp/", full.names = T, pattern = ".*_2000.csv") %>%
            .[!(. |> str_detect("middle_east"))] %>%
            str_replace("gdp_europe_2000", "gdp_europe_2000_original") %>%
            purrr::map(fread) |> 
            set_names(c("africa","asia","europe","north_america","oceania","south_america"))

# Obtain countries in both Asia and Europe
fintersect(gdp$asia, gdp$europe)

# Concatenate all data tables
gdp_all <- rbindlist(gdp)

# Find all countries that span multiple continents
gdp_all[duplicated(gdp_all)]
```

Nicely done! Did you know base R provides set operation functions that work on vectors? For example the `intersect()` function will find the common elements of two vectors.

## Removing duplicates while combining tables

The same list of data tables as the last exercise, `gdp`, is available in your workspace. Your goal this time is to build a `data.table` in which each country appears only once, even if it is located in more than one continent.

**Steps**

1. Use the `funion()` function to build a new `data.table` containing all countries in either Europe or Asia, with each country appearing only once in the result. 
2. Concatenate all `data.table`s in `gdp` and assign it to `gdp_all`.   
3. Use the `unique()` function to remove duplicate rows from`gdp_all` so that each country occurs only once in the result.

```{r}
# Get all countries in either Asia or Europe
funion(gdp$asia, gdp$europe)

# Concatenate all data tables
gdp_all <- rbindlist(gdp)

# Print all unique countries
unique(gdp_all)
```

Great job! Functions in R can be chained together in the same statement, for example `gdp_all <- unique(rbindlist(gdp))`.

## Identifying observations unique to a table

An additional `data.table` has been loaded into your R session, `middle_east`, along with the `gdp` list of data tables from the previous exercise. The `middle_east` data table contains the set of countries in the <a href="https://en.wikipedia.org/wiki/Middle_East">Middle East</a>, a geopolitical region which spans parts of Europe, Africa, and Asia. All countries in the `middle_east` data table appear in one or more data tables in the `gdp` list. Your goal is to print data tables containing all countries in Africa, Europe, and Asia, that are not found in the `middle_east`.

**Steps**

1. Print a new `data.table` containing rows from `gdp$africa` that are not present in `middle_east`. 
2. Print a new `data.table` containing rows from `gdp$asia` that are not present in `middle_east`. 
3. Print a new `data.table` containing rows from `gdp$europe` that are not present in `middle_east`.

```{r}
# Load data
middle_east <- fread("data/gdp/gdp_middle_east_2000.csv")

# Which countries are in Africa but not considered part of the middle east?
fsetdiff(gdp$africa, middle_east)

# Which countries are in Asia but not considered part of the middle east?
fsetdiff(gdp$asia, middle_east)

# Which countries are in Europe but not considered part of the middle east?
fsetdiff(gdp$europe, middle_east)
```

Nice work! You can do this in one line of code: `lapply(gdp, fsetdiff, middle_east)`.

## Melting data.tables

Theory. Coming soon ...

**1. Melting data.tables**

In this lesson, you will learn how to melt data tables from wide to long formats.

**2. Melting a wide data.table**

A wide format data table is one where a single variable is spread across multiple columns corresponding to some grouping, such as in the example data table on the left.This type of layout is common when working with excel spreadsheets because it is a human friendly way of organising data with regular groupings. However, when working with datasets in R, you will often want to reshape this data into a long format, shown on the right. In the long format these values occupy a single column, with another column indicating group membership. This format allows you to take full advantage of the functionality in the data table syntax like grouping of calculations.

**3. The `melt()` function**

You can use the melt() function to reshape a wide data table to a long data table.There are a few different ways you can use this function.First, you can provide a vector of column names to the measure dot vars argument. The melt() function will take these columns and stack them on top of each other to create two new columns: "variable" and "value". The "value" column will contain the values of these stacked columns while the "variable" column labels each row with the column name from the original wide data table.

**4. The `melt()` function**

You can give these new columns any name you like using the variable dot name and value dot name arguments.

**5. The `melt()` function**

You can also use the id dot vars argument instead of the measure dot vars argument to tell the melt() function which columns you want to keep aside as row identifiers in the new data table.The melt() function will then stack all other columns into the new "value" column.

**6. The `melt()` function**

You can use both the id dot vars and measure dot vars arguments at the same time. If you use both arguments, any columns you don't provide to either argument will be dropped in the result.

**7. Let's practice!**

Now it's your turn to see how melt() works in practice.

## Melting a wide table

A data table named `gdp_per_capita` containing the gross domestic product (GDP) per capita for countries in Oceania every five years from 1990 to 2010 sourced from the <a href="https://www.gapminder.org">Gapminder foundation</a> is available in your workspace. Your goal is to reshape this data table to the long format so that there is a single column containing all GDP per capita values for each country and year.

**Steps**

1. Print `gdp_per_capita` to learn about its contents. 
2. Use the `melt()` function to create a long format `data.table` stacking the values for each country's column.

```{r}
# Load & prepare data
gdp_per_capita <- fread("data/gdp/gdp_per_capita_oceania.csv", header = T)
gdp_per_capita <- data.table::transpose(gdp_per_capita, keep.names = "year", make.names = "country")

# Print gdp_per_capita
gdp_per_capita

# Reshape gdp_per_capita to the long format
melt(gdp_per_capita, id.vars = "year")
```

3. Within your call to `melt()`, name the new `"variable"` column as `"country"` and the new `"value"` column as `"gdp_pc"`.


```{r}
# Rename the new columns
melt(gdp_per_capita, id.vars = "year", 
     variable.name = "country", value.name = "gdp_pc")
```

Excellent! By default the `\\"variable\\"` column in the result will be a `factor`. You can change this by setting `variable.factor = FALSE`.

## More melts

A data table containing confirmed Ebola case numbers from Guinea in weeks 50 and 51 of the outbreak has been loaded into your R session. Your goal is to melt `ebola_wide` into a long format data table containing a single column for the case numbers variable.

**Steps**

1. Use the `measure.vars` argument to stack the columns `"Week_50"` and `"Week_51"`. The new column containing its values should be called `"cases"`, and the new column of variable labels should be called `"period"`.


```{r}
# Load package
library(dplyr)

# Prepare data
ebola_wide <- 

ebola_W50 |> 
  select(-c(period_code, Probable)) |> 
  rename("Week_50" = "Confirmed") |> 
  mutate(Week_51 = NA) |> 
  
  bind_rows(

ebola_W51 |> 
  select(-c(period_code, Probable)) |> 
  rename("Week_51" = "Confirmed") |> 
  mutate(Week_50 = NA)

  ) |> 
  arrange(Location)

# Print ebola_wide
ebola_wide

# Stack Week_50 and Week_51
melt(ebola_wide, measure.vars = c("Week_50", "Week_51"), 
     variable.name = "period", value.name = "cases")
```

2. Modify the call to `melt()` to also ask to keep only the `"Location"` column as row identifiers, dropping the `"period_start"` and `"period_end"` columns from the result.

```{r}
# Modify the code
melt(ebola_wide, measure.vars = c("Week_50", "Week_51"), 
     variable.name = "period", value.name = "cases", 
     id.vars = "Location")
```

Great job! Using `id.vars` together with `measure.vars` keeps only those columns in the result.

## Casting data.tables

Theory. Coming soon ...

## Casting a long table

`gdp_oceania` contains the population and GDP for every country in Oceania for every five years from 1990 to 2010 and is available in your workspace. Your goal is to create wide format `data.table`s containing the population and total GDP for each country with each year having its own column.

**Steps**

1. Use <a href="https://www.rdocumentation.org/packages/data.table/topics/dcast.data.table">`dcast()`</a> to build a wide `data.table` containing the population of each country, with each country as a row and each year as a column.

```{r}
# Load data
gdp_oceania <- fread("data/gdp/gdp_and_pop_oceania.csv")

# Split the population column by year
dcast(gdp_oceania, formula = country ~ year, value.var = "population")
```

2. Create another wide format `data.table` containing the GDP of each country with each year as a row and each country as a column.

```{r}
# Split the gdp column by country
dcast(gdp_oceania, formula = year ~ country, value.var = "gdp")
```

Nice work! One way to remember the dcast formula is that the column you give to the left hand side will always be the left most column of row identifiers in the result.

## Casting multiple columns

The data table from the previous exercise, `gdp_oceania`, is available in your workspace. Your goal is to create a wide format data table, splitting both the `"gdp"` and `"population"` columns by the `"year"` column.

**Steps**

1. Convert `gdp_oceania` to wide format with each country as a row, and splitting both the `"gdp"` and `"population"` columns into columns for each `"year"`.

```{r}
# Modify data
gdp_oceania[,continent := "Oceania"]

# Split the gdp and population columns by year
dcast(gdp_oceania, formula = country ~ year, value.var = c("gdp", "population"))
```

2. Convert `wide` to a matrix using the `"country"` column as the matrix rownames.

```{r}
# Reshape from wide to long format
wide <- dcast(gdp_oceania, formula = country ~ year, value.var = c("gdp", "population"))

# convert to a matrix
as.matrix(wide, rownames = "country")
```

3. Modify the call to `dcast()` so that the `"continent"` column is also kept aside as row identifiers.

```{r}
# Modify your previous code
dcast(gdp_oceania, formula = country + continent ~ year, value.var = c("gdp", "population"))
```

Nicely done! If you don't want to keep any columns as row identifiers in the result, you can use the special symbol `.` in the formula.

## Splitting by multiple groups

`gdp_by_industry_oceania` contains the GDP generated by the agriculture and tourism industries in both 1995 and 2010. Your goal is to create a wide format `data.table` containing columns of GDP for each year and industry.

**Steps**

1. Print `gdp_by_industry_oceania`. 
2. Convert `gdp_by_industry_oceania` to a wide format with each country as a row, splitting the `"gdp"` column so that the result has columns containing the gdp for each industry and each year.

```{r}
# Load data
gdp_by_industry_oceania <- fread("data/gdp/gdp_by_industry_oceania.tsv")

# Split gdp by industry and year
dcast(gdp_by_industry_oceania, country ~ industry + year, value.var = "gdp")
```

Awesome job! For advanced use cases take a look at the `fill` argument which controls how `dcast` handles group combinations that do not occur, and the `fun.aggregate` argument which controls how dcast handles cases where there are multiple values for different group combinations.