Pensions - spr_exp_pens

Data - Eurostat

Author

François Geerolf

Info

LAST_DOWNLOAD

Code 
tibble(source = c("oecd", "oecd", "oecd", "oecd", "eurostat"),
       dataset = c("PAG", "PNN_NEW", "PNNI_NEW", "SOCX_AGG", "spr_exp_pens")) %>%
  mutate(Title = read_lines(paste0("~/Library/Mobile\ Documents/com~apple~CloudDocs/website/data/", source, "/",dataset, ".qmd"), skip = 1, n_max = 1) %>% gsub("title: ", "", .) %>% gsub("\"", "", .)) %>%
  mutate(Download = as.Date(file.info(paste0("~/Library/Mobile\ Documents/com~apple~CloudDocs/website/data/", source, "/", dataset, ".RData"))$mtime),
         Compile = as.Date(file.info(paste0("~/Library/Mobile\ Documents/com~apple~CloudDocs/website/data/", source, "/", dataset, ".html"))$mtime)) %>%
  mutate(Compile = paste0("[", Compile, "](https://fgeerolf.com/data/", source, "/", dataset, '.html)')) %>%
  print_table_conditional()
source dataset Title Download Compile
oecd PAG Pensions at A Glance - PAG 2023-09-09 [2024-09-15]
oecd PNN_NEW Funded Pension Statistics - PNN_NEW 2019-12-24 [2024-09-15]
oecd PNNI_NEW Funded Pensions Indicators - PNNI_NEW 2019-12-27 [2024-09-15]
oecd SOCX_AGG Social Expenditure - Aggregated data - SOCX_AGG 2024-06-30 [2024-09-15]
eurostat spr_exp_pens Pensions - spr_exp_pens 2024-10-08 [2024-11-01]

LAST_COMPILE

LAST_COMPILE
2024-11-05

Last

Code 
spr_exp_pens %>%
  group_by(time) %>%
  summarise(Nobs = n()) %>%
  arrange(desc(time)) %>%
  head(1) %>%
  print_table_conditional()
time Nobs
2021 5532

spdepb

Code 
spr_exp_pens %>%
  left_join(spdepb, by = "spdepb") %>%
  group_by(spdepb, Spdepb) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional
spdepb Spdepb Nobs
SCPANTPEN Anticipated old age pension 18219
SCPDISPEN Disability pension 18219
SCPEARLYMARK Early retirement benefit for labour market reasons 18219
SCPOLDPEN Old age pension 18219
SCPSURVPEN Survivors pension 18219
TOTAL Total 18219
SCPEARLYRED Early retirement benefit due to reduced capacity to work 18213
SCPPARPEN Partial pension 18213

spdepm

Code 
spr_exp_pens %>%
  left_join(spdepm, by = "spdepm") %>%
  group_by(spdepm, Spdepm) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional
spdepm Spdepm Nobs
NON_MEANS Non means-tested 48586
TOTAL Total 48586
MEANS Means-tested 48568

unit

Code 
spr_exp_pens %>%
  left_join(unit, by = "unit") %>%
  group_by(unit, Unit) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional
unit Unit Nobs
MIO_EUR Million euro 26688
PC_GDP Percentage of gross domestic product (GDP) 25104
MIO_PPS Million purchasing power standards (PPS) 23892
PPS_HAB Purchasing power standard (PPS) per inhabitant 23820
EUR_HAB_KP10 Euro per inhabitant (at constant 2010 prices) 23472
MIO_NAC Million units of national currency 22764

geo

Code 
spr_exp_pens %>%
  left_join(geo, by = "geo") %>%
  group_by(geo, Geo) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  mutate(Flag = gsub(" ", "-", str_to_lower(Geo)),
         Flag = paste0('<img src="../../icon/flag/vsmall/', Flag, '.png" alt="Flag">')) %>%
  select(Flag, everything()) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F, escape = F) else .}

List of Countries: 2000-2019

Code 
spr_exp_pens %>%
  filter(# SCPOLDPEN: Old age pension
         spdepb == "TOTAL",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "PC_GDP",
         time %in% c("1997", "2017")) %>%
  select(geo, time, values) %>%
  left_join(geo, by = "geo") %>%
  spread(time, values) %>%
  mutate(`Change` = round(`2017` - `1997`, 1)) %>%
  na.omit %>%
  arrange(Change) %>%
  mutate(Flag = gsub(" ", "-", str_to_lower(Geo)),
         Flag = paste0('<img src="../../icon/flag/vsmall/', Flag, '.png" alt="Flag">')) %>%
  select(Flag, everything()) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F, escape = F) else .}

France, Germany, Italy

% of GDP - Total

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT"),
         # SCPOLDPEN: Old age pension
         spdepb == "TOTAL",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "PC_GDP") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  ggplot + geom_line(aes(x = date, y = values/100, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#009246")) +
  theme_minimal()  +
  geom_image(data = . %>%
               filter(date == as.Date("2015-01-01")) %>%
               mutate(image = paste0("../../icon/flag/", str_to_lower(gsub(" ", "-", Geo)), ".png")),
             aes(x = date, y = values/100, image = image), asp = 1.5) +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = "none") +
  xlab("") + ylab("Total Spending on Pensions (% of GDP)") +
  scale_y_continuous(breaks = 0.01*seq(0, 60, 1),
                     labels = scales::percent_format(accuracy = 1))

% of GDP - Old age pension

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT"),
         # SCPOLDPEN: Old age pension
         spdepb == "SCPOLDPEN",
         # TOTAL: Total
         spdepm == "TOTAL",
         # PC_GDP: Million euro
         unit == "PC_GDP") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  ggplot + geom_line(aes(x = date, y = values/100, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#009246")) +
  theme_minimal()  +
  geom_image(data = . %>%
               filter(date == as.Date("2015-01-01")) %>%
               mutate(image = paste0("../../icon/flag/", str_to_lower(gsub(" ", "-", Geo)), ".png")),
             aes(x = date, y = values/100, image = image), asp = 1.5) +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = c(0.3, 0.85),
        legend.title = element_blank()) +
  xlab("") + ylab("Old Age Pension (% of GDP)") +
  scale_y_continuous(breaks = 0.01*seq(0, 60, 1),
                     labels = scales::percent_format(accuracy = 1))

Amounts (Bn€) - Total

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT"),
         # SCPOLDPEN: Old age pension
         spdepb == "TOTAL",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "MIO_EUR") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  ggplot + geom_line(aes(x = date, y = values/1000, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#009246")) +
  theme_minimal()  +
  geom_image(data = . %>%
               filter(date == as.Date("2015-01-01")) %>%
               mutate(image = paste0("../../icon/flag/", str_to_lower(gsub(" ", "-", Geo)), ".png")),
             aes(x = date, y = values/1000, image = image), asp = 1.5) +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = "none") +
  xlab("") + ylab("") +
  scale_y_log10(breaks = seq(0, 1000, 50),
                labels = dollar_format(suffix = " Bn€", prefix = "", accuracy = 1))

Amounts (Bn€) - Old age pension

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT"),
         # SCPOLDPEN: Old age pension
         spdepb == "SCPOLDPEN",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "MIO_EUR") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  mutate(values = values/1000) %>%
  ggplot + geom_line(aes(x = date, y = values, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#009246")) +
  theme_minimal()  + add_3flags +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = "none") +
  xlab("") + ylab("") +
  scale_y_log10(breaks = seq(0, 1000, 50),
                labels = dollar_format(suffix = " Bn€", prefix = "", accuracy = 1))

Austria, Denmark, Netherlands, Sweden

% of GDP - Total

Code 
spr_exp_pens %>%
  filter(geo %in% c("AT", "DK", "NL", "SE"),
         # SCPOLDPEN: Old age pension
         spdepb == "TOTAL",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "PC_GDP") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  left_join(colors, by = c("Geo" = "country")) %>%
  mutate(values = values/100) %>%
  ggplot + geom_line(aes(x = date, y = values, color = color)) +
  scale_color_identity() + theme_minimal()  + add_4flags +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = c(0.1, 0.85),
        legend.title = element_blank()) +
  xlab("") + ylab("Total Spending on Pensions (% of GDP)") +
  scale_y_continuous(breaks = 0.01*seq(0, 60, 1),
                     labels = scales::percent_format(accuracy = 1))

France, Germany, Italy, Sweden

Total

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT", "SE"),
         # SCPOLDPEN: Old age pension
         spdepb == "TOTAL",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "PC_GDP") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  ggplot + geom_line(aes(x = date, y = values/100, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#009246", "#FECC00")) +
  theme_minimal()  +
  geom_image(data = . %>%
               filter(date == as.Date("2015-01-01")) %>%
               mutate(image = paste0("../../icon/flag/", str_to_lower(gsub(" ", "-", Geo)), ".png")),
             aes(x = date, y = values/100, image = image), asp = 1.5) +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = "none") +
  xlab("") + ylab("Total Spending on Pensions (% of GDP)") +
  scale_y_continuous(breaks = 0.01*seq(0, 60, 1),
                     labels = scales::percent_format(accuracy = 1))

France, Germany, Italy, Sweden, Greece

Total

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT", "SE", "EL"),
         # SCPOLDPEN: Old age pension
         spdepb == "TOTAL",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "PC_GDP") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  ggplot + geom_line(aes(x = date, y = values/100, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#0D5EAF", "#009246", "#FECC00")) +
  theme_minimal()  +
  geom_image(data = . %>%
               filter(date == as.Date("2016-01-01")) %>%
               mutate(image = paste0("../../icon/flag/", str_to_lower(gsub(" ", "-", Geo)), ".png")),
             aes(x = date, y = values/100, image = image), asp = 1.5) +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = "none") +
  xlab("") + ylab("Total Spending on Pensions (% of GDP)") +
  scale_y_continuous(breaks = 0.01*seq(0, 60, 1),
                     limits = c(0.09, 0.18),
                     labels = scales::percent_format(accuracy = 1))

France, Germany, Italy, Greece, Portugal

Total

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT", "EL", "PT"),
         # SCPOLDPEN: Old age pension
         spdepb == "TOTAL",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "PC_GDP") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  ggplot + geom_line(aes(x = date, y = values/100, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#0D5EAF", "#009246", "#FF0000")) +
  theme_minimal()  +
  geom_image(data = . %>%
               filter(date == as.Date("2017-01-01")) %>%
               mutate(image = paste0("../../icon/flag/", str_to_lower(gsub(" ", "-", Geo)), ".png")),
             aes(x = date, y = values/100, image = image), asp = 1.5) +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = "none") +
  xlab("") + ylab("Total Spending on Pensions (% of GDP)") +
  scale_y_continuous(breaks = 0.01*seq(0, 60, 1),
                     labels = scales::percent_format(accuracy = 1))

Old age pension

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT", "EL", "PT"),
         # SCPOLDPEN: Old age pension
         spdepb == "SCPOLDPEN",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "PC_GDP") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  ggplot + geom_line(aes(x = date, y = values/100, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#0D5EAF", "#009246", "#FF0000")) +
  theme_minimal()  +
  geom_image(data = . %>%
               filter(date == as.Date("2017-01-01")) %>%
               mutate(image = paste0("../../icon/flag/", str_to_lower(gsub(" ", "-", Geo)), ".png")),
             aes(x = date, y = values/100, image = image), asp = 1.5) +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = "none") +
  xlab("") + ylab("Old age pension (% of GDP)") +
  scale_y_continuous(breaks = 0.01*seq(0, 60, 1),
                     labels = scales::percent_format(accuracy = 1))

Disability pension

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT", "EL", "PT"),
         # SCPOLDPEN: Old age pension
         spdepb == "SCPDISPEN",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "PC_GDP") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  ggplot + geom_line(aes(x = date, y = values/100, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#0D5EAF", "#009246", "#FF0000")) +
  theme_minimal()  +
  geom_image(data = . %>%
               filter(date == as.Date("2012-01-01")) %>%
               mutate(image = paste0("../../icon/flag/", str_to_lower(gsub(" ", "-", Geo)), ".png")),
             aes(x = date, y = values/100, image = image), asp = 1.5) +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = "none") +
  xlab("") + ylab("Disability pension (% of GDP)") +
  scale_y_continuous(breaks = 0.01*seq(0, 60, 0.2),
                     labels = scales::percent_format(accuracy = .1))

Survivors pension

Code 
spr_exp_pens %>%
  filter(geo %in% c("FR", "DE", "IT", "EL", "PT"),
         # SCPOLDPEN: Old age pension
         spdepb == "SCPSURVPEN",
         # TOTAL: Total
         spdepm == "TOTAL",
         # MIO_EUR: Million euro
         unit == "PC_GDP") %>%
  year_to_date %>%
  left_join(geo, by = "geo") %>%
  ggplot + geom_line(aes(x = date, y = values/100, color = Geo)) +
  scale_color_manual(values = c("#002395", "#000000", "#0D5EAF", "#009246", "#FF0000")) +
  theme_minimal()  +
  geom_image(data = . %>%
               filter(date == as.Date("2009-01-01")) %>%
               mutate(image = paste0("../../icon/flag/", str_to_lower(gsub(" ", "-", Geo)), ".png")),
             aes(x = date, y = values/100, image = image), asp = 1.5) +
  scale_x_date(breaks = as.Date(paste0(seq(1960, 2020, 2), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = "none") +
  xlab("") + ylab("Survivors pension (% of GDP)") +
  scale_y_continuous(breaks = 0.01*seq(0, 60, 0.2),
                     labels = scales::percent_format(accuracy = .1))