Final consumption expenditure of households

Data - OECD

Author

Info

source	dataset	.html	.RData
oecd	SNA_TABLE1	2024-09-11	2024-06-30
oecd	SNA_TABLE5	2024-07-01	2023-10-19

Data on housing

source	dataset	.html	.RData
bdf	RPP	2024-07-26	2024-07-01
bis	LONG_PP	2024-08-09	2024-05-10
bis	SELECTED_PP	2024-08-09	2024-05-10
ecb	RPP	2024-09-10	2024-09-10
eurostat	ei_hppi_q	2024-09-10	2024-09-10
eurostat	hbs_str_t223	2024-09-10	2024-09-10
eurostat	prc_hicp_midx	2024-09-10	2024-09-08
eurostat	prc_hpi_q	2024-09-10	2024-08-21
fred	housing	2024-09-10	2024-09-10
insee	IPLA-IPLNA-2015	2024-09-10	2024-09-10
oecd	housing	2024-09-11	2020-01-18
oecd	SNA_TABLE5	2024-07-01	2023-10-19

LAST_COMPILE

LAST_COMPILE
2024-09-11

Last

obsTime	Nobs
2022	8179
2021	10750

Layout - By country

OECD Website. html

United States

France

Nobs - TRANSACT, MEASURE

Code

SNA_TABLE5 %>%
  left_join(SNA_TABLE5_var$TRANSACT, by = "TRANSACT") %>%
  group_by(TRANSACT, Transact, MEASURE) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional()

TRANSACT

Code

SNA_TABLE5 %>%
  left_join(SNA_TABLE5_var$TRANSACT, by = "TRANSACT") %>%
  group_by(TRANSACT, Transact) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional()

MEASURE

Code

SNA_TABLE5 %>%
  left_join(SNA_TABLE5_var$MEASURE, by = "MEASURE") %>%
  group_by(MEASURE, Measure) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional()

MEASURE	Measure	Nobs
C	Current prices	81801
V	Constant prices, national base year	77900
VOB	Constant prices, OECD base year	75339
DOB	Deflator	72609
VP	Constant prices, previous year prices	60375
VPVOB	Constant prices, constant PPPs, OECD base year	28

obsTime

Code

SNA_TABLE5 %>%
  group_by(obsTime) %>%
  summarise(Nobs = n()) %>%
  arrange(desc(obsTime)) %>%
  print_table_conditional()

France, Germany, United Kingdom, United States

% of GDP

Code

SNA_TABLE5 %>%
  filter(LOCATION %in% c("FRA", "DEU", "USA", "GBR"),
         obsTime == "2017",
         MEASURE == "C") %>%
  left_join(SNA_TABLE5_var$TRANSACT, by = "TRANSACT") %>%
  left_join(SNA_TABLE5_var$LOCATION, by = "LOCATION") %>%
  select(Location, TRANSACT, Transact, obsValue) %>%
  mutate(Location = gsub(" ", "-", str_to_lower(gsub(" ", "-", Location))),
         Location = paste0('<img src="../../icon/flag/vsmall/', Location, '.png" alt="Flag">')) %>%
  spread(Location, obsValue) %>%
  mutate_at(vars(-1, -2), funs(round(100*./.[TRANSACT == "B1_GE"], 1))) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F, escape = F) else .}

% of Consumption

Code

SNA_TABLE5 %>%
  filter(LOCATION %in% c("FRA", "DEU", "USA", "GBR"),
         obsTime == "2017",
         MEASURE == "C") %>%
  left_join(SNA_TABLE5_var$TRANSACT, by = "TRANSACT") %>%
  left_join(SNA_TABLE5_var$LOCATION, by = "LOCATION") %>%
  select(Location, TRANSACT, Transact, obsValue) %>%
  mutate(Location = gsub(" ", "-", str_to_lower(gsub(" ", "-", Location))),
         Location = paste0('<img src="../../icon/flag/vsmall/', Location, '.png" alt="Flag">')) %>%
  spread(Location, obsValue) %>%
  mutate_at(vars(-1, -2), funs(round(100*./.[TRANSACT == "P31DC"], 1))) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F, escape = F) else .}

Housing

% of Consumption, % of GDP

Code

SNA_TABLE5 %>%
  filter(TRANSACT %in% c("P31DC", "P31CP040"),
         obsTime == "2017",
         MEASURE == "C") %>%
  arrange(LOCATION, TRANSACT) %>%
  select(LOCATION, TRANSACT, obsValue) %>%
  spread(TRANSACT, obsValue) %>%
  left_join(SNA_TABLE1 %>%
              filter(TRANSACT == "B1_GE",
                     MEASURE == "C",
                     obsTime == "2017") %>%
              select(LOCATION, B1_GE = obsValue), 
            by = c("LOCATION")) %>%
  left_join(SNA_TABLE5_var$LOCATION, by = "LOCATION") %>%
  mutate(Flag = gsub(" ", "-", str_to_lower(gsub(" ", "-", Location))),
         Flag = paste0('<img src="../../icon/flag/vsmall/', Flag, '.png" alt="Flag">')) %>%
  transmute(Flag,
            LOCATION,
            Location,
            `Housing (% of Consumption)` = (100*P31CP040 / P31DC) %>% round(digits = 1),
            `Housing (% of GDP)` = (100*P31CP040 / B1_GE) %>% round(digits = 1)) %>%
  arrange(-`Housing (% of Consumption)`) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F, escape = F) else .}

% of Consumption

Code

SNA_TABLE5 %>%
  filter(TRANSACT %in% c("P31DC", "P31CP040", "P31CP041", "P31CP042"),
         obsTime == "2018",
         MEASURE == "C") %>%
  arrange(LOCATION, TRANSACT) %>%
  select(LOCATION, TRANSACT, obsValue) %>%
  spread(TRANSACT, obsValue) %>%
  left_join(SNA_TABLE5_var$LOCATION, by = "LOCATION") %>%
  mutate(Flag = gsub(" ", "-", str_to_lower(gsub(" ", "-", Location))),
         Flag = paste0('<img src="../../icon/flag/vsmall/', Flag, '.png" alt="Flag">')) %>%
  transmute(Flag,
            LOCATION,
            Location,
            `Housing` = (100*P31CP040 / P31DC) %>% round(digits = 1),
            `Rents (Actual)` = (100*P31CP041 / P31DC) %>% round(digits = 1),
            `Rents (Implicit)` = (100*P31CP042 / P31DC) %>% round(digits = 1)) %>%
  arrange(-Housing) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F, escape = F) else .}

Food, Health, Housing, Restaurant

Code

SNA_TABLE5 %>%
  filter(TRANSACT %in% c("P31CP010", "P31DC", "P31CP040", "P31CP060", "P31CP110"),
         obsTime == "2017",
         MEASURE == "C") %>%
  arrange(LOCATION, TRANSACT) %>%
  left_join(SNA_TABLE5_var$LOCATION, by = "LOCATION") %>%
  mutate(Flag = gsub(" ", "-", str_to_lower(gsub(" ", "-", Location))),
         Flag = paste0('<img src="../../icon/flag/vsmall/', Flag, '.png" alt="Flag">')) %>%
  select(Flag, Location, TRANSACT, obsValue) %>%
  spread(TRANSACT, obsValue) %>%
  group_by(Flag, Location) %>%
  summarise(`Food` = (100*P31CP010 / P31DC) %>% round(digits = 1),
            `Housing` = (100*P31CP040 / P31DC) %>% round(digits = 1),
            `Health` = (100*P31CP060 / P31DC) %>% round(digits = 1),
            `Restaurant` = (100*P31CP110 / P31DC) %>% round(digits = 1)) %>%
  na.omit %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F, escape = F) else .}

Implicit / real Rents

Table 1

Code

library(gt)
library(gtExtras)
#devtools::install_github("rstudio/gt")
countrypops_new <- countrypops %>%
  select(LOCATION = country_code_3, LOCATION_2digit = country_code_2) %>%
  unique
table1 <- SNA_TABLE5 %>%
  filter(TRANSACT %in% c("P31CP042", "P31DC", "P31CP041"),
         MEASURE == "C",
         obsTime == "2019") %>%
  left_join(SNA_TABLE5_var$LOCATION, by = "LOCATION") %>%
  left_join(SNA_TABLE5_var$TRANSACT, by = "TRANSACT") %>%
  group_by(LOCATION) %>%
  mutate(values = 100*obsValue /obsValue[TRANSACT == "P31DC"]) %>%
  filter(TRANSACT != "P31DC") %>%
  mutate(Location = case_when(LOCATION == "EA19" ~ "Euro Area (19)",
                              LOCATION == "EA27" ~ "Europe (27)",
                              T ~ Location)) %>%
  left_join(countrypops_new, by = "LOCATION") %>%
  mutate(LOCATION_2digit = case_when(LOCATION == "EA19" ~ "EU",
                                     LOCATION == "EA27" ~ "EU",
                              T ~ LOCATION_2digit)) %>%
  ungroup %>%
  select(Location, LOCATION_2digit, Transact, values) %>%
  spread(Transact, values) %>%
  transmute(LOCATION_2digit, Location,
            `Actual rents` = `Actual rentals for housing`,
            `Imputed rents` = `Imputed rentals for housing`,
            `All rents` = `Actual rentals for housing` + `Imputed rentals for housing`) %>%
  gt() %>%
  fmt_number(columns = 3:5 , locale = "fr", decimals = 1, pattern = "{x}%") |>
  cols_align(align = "center", columns = 3:5) |> 
  fmt_flag(columns = LOCATION_2digit, height = "1.5em") %>%
  cols_width(3:5 ~ px(50)) |> 
  gt_theme_538()


gtsave(table1, filename = "SNA_TABLE5_files/figure-html/table1.png")
i_g("data/oecd/SNA_TABLE5_files/figure-html/table1.png")

France, Germany, US

Code

SNA_TABLE5 %>%
  filter(TRANSACT %in% c("P31CP042", "P31DC", "P31CP041"),
         MEASURE == "C",
         LOCATION %in% c("FRA", "DEU", "USA")) %>%
  left_join(SNA_TABLE5_var$LOCATION, by = "LOCATION") %>%
  left_join(SNA_TABLE5_var$TRANSACT, by = "TRANSACT") %>%
  year_to_date %>%
  group_by(date, LOCATION) %>%
  mutate(obsValue = obsValue /obsValue[TRANSACT == "P31DC"]) %>%
  filter(TRANSACT != "P31DC") %>%
  left_join(colors, by = c("Location" = "country")) %>%
  ggplot(.) + geom_line(aes(x = date, y = obsValue, color = color, linetype = Transact)) + 
  scale_color_identity() + add_6flags + theme_minimal() +
  scale_x_date(breaks = seq(1920, 2025, 10) %>% paste0("-01-01") %>% as.Date,
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-7, 26, 1),
                     labels = scales::percent_format(accuracy = 1)) +
  theme(legend.position = c(0.2, 0.9),
        legend.title = element_blank()) +
  ylab("Rentals (% of Consumption)") + xlab("")

Italy, Spain, Netherlands

Code

SNA_TABLE5 %>%
  filter(TRANSACT %in% c("P31CP042", "P31DC", "P31CP041"),
         MEASURE == "C",
         LOCATION %in% c("ITA", "ESP", "NLD")) %>%
  left_join(SNA_TABLE5_var$LOCATION, by = "LOCATION") %>%
  left_join(SNA_TABLE5_var$TRANSACT, by = "TRANSACT") %>%
  year_to_date %>%
  group_by(date, LOCATION) %>%
  mutate(obsValue = obsValue /obsValue[TRANSACT == "P31DC"]) %>%
  filter(TRANSACT != "P31DC") %>%
  left_join(colors, by = c("Location" = "country")) %>%
  ggplot(.) + geom_line(aes(x = date, y = obsValue, color = color, linetype = Transact)) + 
  scale_color_identity() + add_6flags + theme_minimal() +
  scale_x_date(breaks = seq(1920, 2025, 10) %>% paste0("-01-01") %>% as.Date,
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-7, 26, 1),
                     labels = scales::percent_format(accuracy = 1)) +
  theme(legend.position = c(0.2, 0.9),
        legend.title = element_blank()) +
  ylab("Rentals (% of Consumption)") + xlab("")