Join several tables into one

Difference between join and column binding in data frames

library(tidyverse)
library(gapminder)

gapminder_europe <- gapminder::gapminder %>% 
  filter(continent == "Europe", year == 2007)
glimpse(gapminder_europe)

Rows: 30
Columns: 6
$ country   <fct> Albania, Austria, Belgium, Bosnia and Herzegovina, Bulgari…
$ continent <fct> Europe, Europe, Europe, Europe, Europe, Europe, Europe, Eu…
$ year      <int> 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007…
$ lifeExp   <dbl> 76.423, 79.829, 79.441, 74.852, 73.005, 75.748, 76.486, 78…
$ pop       <int> 3600523, 8199783, 10392226, 4552198, 7322858, 4493312, 102…
$ gdpPercap <dbl> 5937.030, 36126.493, 33692.605, 7446.299, 10680.793, 14619…

geo <- read_csv("https://raw.githubusercontent.com/open-numbers/ddf--gapminder--fasttrack/master/ddf--entities--geo--country.csv")
head(geo)

geo_europe <- geo %>% filter(world_4region == "europe") %>% 
  select(main_religion_2008, name, world_4region, income_groups)
glimpse(geo_europe) 

Rows: 73
Columns: 4
$ main_religion_2008 <chr> NA, NA, NA, "muslim", "christian", "christian", NA, "christian", "muslim",…
$ name               <chr> "Abkhazia", "Akrotiri and Dhekelia", "Åland", "Albania", "Andorra", "Armen…
$ world_4region      <chr> "europe", "europe", "europe", "europe", "europe", "europe", "europe", "eur…
$ income_groups      <chr> NA, NA, NA, "upper_middle_income", "high_income", "upper_middle_income", N…

We would like to add the religion and income_groups column to the gapminder_europe data frame.

The resulting data grame is going to inherit (all and only) rows from gapminder_europe, if we pursue the “inner join” operation

dplyr::inner_join(x = gapminder_europe, y = geo_europe, 
                  by = c("country" = "name")) #mind the equal sign!

We have an NA in religion in Bosnia and Herzegovina - it should already be so in the geo_europe data frame; i.e. not generated here.

geo_europe %>% filter(name == "Bosnia and Herzegovina")

dplyr::left_join(x = gapminder_europe, y = geo_europe,
                 by = c("country" = "name")) #mind the equal sign!

dplyr::right_join(x = gapminder_europe, y = geo_europe,
                 by = c("country" = "name")) #mind the equal sign!

dplyr::full_join(x = gapminder_europe, y = geo_europe,
                 by = c("country" = "name"))

anti_join(x = gapminder_europe, y = geo_europe,
                 by = c("country" = "name"))

anti_join( x = geo_europe, y = gapminder_europe,
                 by = c( "name" = "country"))

boys <- data.frame(names = c("Pepa", "Honza", "Kuba"), laps = c(10, 3, 8))
girls <- data.frame(names = c("Hanka", "Pavla", "Pepa"), laps = c(9, 5, 5))

anti_join(boys, girls, by = "names")

full_join(boys, girls, by = "names", suffix = c("_boys", "_girls"))

POLLS Exercises

SDÍLEJ OBRAZOVKU!!!!

Solutions - demonstrations

girls %>% filter(names == "Pepa") %>% inner_join(boys, by = "names")

full_join(boys, girls, by = "laps")