---
title: "Join several tables into one"
output: html_notebook
---
 
# Difference between join and column binding in data frames
```{r}
library(tidyverse)
library(gapminder)
```

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


```{r message=FALSE}
geo <- read_csv("https://raw.githubusercontent.com/open-numbers/ddf--gapminder--fasttrack/master/ddf--entities--geo--country.csv")
head(geo)
```
```{r}
geo_europe <- geo %>% filter(world_4region == "europe") %>% 
  select(main_religion_2008, name, world_4region, income_groups)
glimpse(geo_europe) 
```

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



```{r}
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.

```{r}
geo_europe %>% filter(name == "Bosnia and Herzegovina")
```

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



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


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

```{r}
anti_join(x = gapminder_europe, y = geo_europe,
                 by = c("country" = "name"))
```
```{r}
anti_join( x = geo_europe, y = gapminder_europe,
                 by = c( "name" = "country"))
```


```{r}
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")
```
```{r}
anti_join(girls, boys, by = "names")
```

```{r}
semi_join(boys, girls, by = "names")
```
```{r}
semi_join( girls, boys, by = "names")
```

```{r}
full_join(x = boys, y = girls, by = "names")
```

```{r}
full_join(boys, girls, by = "names", suffix = c("_boys", "_girls"))
```

```{r}
full_join(boys, girls, by = c("names", "laps"))
```


# POLLS Exercises
SDÍLEJ OBRAZOVKU!!!!
```{r}
boys
```
```{r}
girls
```



Solutions - demonstrations
```{r}
girls %>% filter(names == "Pepa") %>% inner_join(boys, by = "names")
```



```{r}
full_join(boys, girls, by = "laps")
```