eurostat : data from 2 tables
Get data from two tables: Expenditure on social protection Code: tps00098 and Expenditure on pensions tps00103.
Pick a country and:
- Graph the expenditure on social protection and pensions (one graph, two variables)
- Calculate the diffrence between these two variables for the country you choose and Germany. Graph the two variables.
library(eurostat)
library(dplyr)
library(tidyr)
library(ggplot2)
tps00098 <- get_eurostat("tps00098")
tps00103 <- get_eurostat("tps00103")
cnt_098 <- tps00098 %>% filter(geo == 'EL') %>%
select(time, values) %>%
rename(sp_exp = values)
cnt_103 <-tps00103 %>% filter(geo == 'EL') %>% filter(geo == 'EL') %>%
select(time, values) %>%
rename(pn_exp = values)
cnt_DF <- inner_join(cnt_098, cnt_103, by = "time") %>%
mutate(diff = sp_exp - pn_exp)
ggplot(cnt_DAT) +
geom_line(aes(x = time, y = sp_exp), color = "red") +
geom_line(aes(x = time, y = pn_exp), color = "blue")
cnt_DAT %>%
gather(expenditure, values, c("sp_exp", "pn_exp")) %>%
ggplot(aes(x = time, y = values, colour = expenditure)) +
geom_line(size = 1.5) +
theme_bw()
# reference country or EU average
# here EU 18 countries with Euro
ref_098 <- tps00098 %>% filter(geo == 'EA18') %>%
select(time, values) %>%
rename(sp_exp = values)
ref_103 <-tps00103 %>% filter(geo == 'EA18') %>%
select(time, values) %>%
rename(pn_exp = values)
ref_DF <- inner_join(ref_098, ref_103, by = "time") %>%
mutate(diff = sp_exp - pn_exp)
x1 <- cnt_DF %>%
mutate(geo = "EL") %>%
select(time, geo, diff)
x2 <- ref_DF %>%
mutate(geo = "EA18") %>%
select(time, geo, diff)
bind_rows(x1, x2) %>%
ggplot(aes(x = time, y = diff, colour = geo)) +
geom_line(size = 1.2) +
theme_bw()
bind_rows(x1, x2) %>%
ggplot(aes(x = time, y=diff, fill = geo)) +
geom_bar(stat="identity", position = "dodge") +
theme_bw()