Structure of consumption expenditure by age of the reference person and COICOP consumption purpose

Data - Eurostat

Author

François Geerolf

Info

source dataset .html .RData
eurostat hbs_str_t225 2024-11-08 2024-11-21
insee bdf2017 2024-11-09 2023-11-21
insee if203 2024-11-09 2023-07-20

Data on housing

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

LAST_COMPILE

LAST_COMPILE
2024-11-22

Last

Code 
hbs_str_t225 %>%
  group_by(time) %>%
  summarise(Nobs = n()) %>%
  arrange(desc(time)) %>%
  head(2) %>%
  print_table_conditional()
time Nobs
2020 6634
2015 8072

coicop

All

Code 
hbs_str_t225 %>%
  left_join(coicop, by = "coicop") %>%
  group_by(coicop, Coicop) %>%
  summarise(Nobs = n()) %>%
  print_table_conditional()

2-digit

Code 
hbs_str_t225 %>%
  filter(nchar(coicop) == 4) %>%
  left_join(coicop, by = "coicop") %>%
  group_by(coicop, Coicop) %>%
  summarise(Nobs = n()) %>%
  print_table_conditional()
coicop Coicop Nobs
CP01 Food and non-alcoholic beverages 735
CP02 Alcoholic beverages, tobacco and narcotics 735
CP03 Clothing and footwear 735
CP04 Housing, water, electricity, gas and other fuels 735
CP05 Furnishings, household equipment and routine household maintenance 735
CP06 Health 735
CP07 Transport 735
CP08 Communications 735
CP09 Recreation and culture 735
CP10 Education 728
CP11 Restaurants and hotels 735
CP12 Miscellaneous goods and services 735

3-digit

Code 
hbs_str_t225 %>%
  filter(nchar(coicop) == 5) %>%
  left_join(coicop, by = "coicop") %>%
  group_by(coicop, Coicop) %>%
  summarise(Nobs = n()) %>%
  print_table_conditional()

4-digit

Code 
hbs_str_t225 %>%
  filter(nchar(coicop) == 6) %>%
  left_join(coicop, by = "coicop") %>%
  group_by(coicop, Coicop) %>%
  summarise(Nobs = n()) %>%
  print_table_conditional()

age

Code 
hbs_str_t225 %>%
  left_join(age, by = "age") %>%
  group_by(age, Age) %>%
  summarise(Nobs = n()) %>%
  print_table_conditional()
age Age Nobs
UNK Unknown 174
Y30-44 From 30 to 44 years 10571
Y45-59 From 45 to 59 years 10574
Y_GE60 60 years or over 10546
Y_LT30 Less than 30 years 10525

geo

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

unit

Code 
hbs_str_t225 %>%
  group_by(unit) %>%
  summarise(Nobs = n()) %>%
  print_table_conditional()
unit Nobs
PM 42390

time

Code 
hbs_str_t225 %>%
  group_by(time) %>%
  summarise(Nobs = n()) %>%
  print_table_conditional()
time Nobs
1988 2168
1994 3904
1999 3554
2005 6550
2010 11508
2015 8072
2020 6634

France - Compare

2020, HBS

Code 
hbs_str_t225 %>%
  filter(time == "2020",
         geo == "FR") %>%
  left_join(coicop, by = "coicop") %>%
  select_if(~ n_distinct(.) > 1) %>%
  spread(age, values) %>%
  select_if(~ n_distinct(.) > 1) %>%
  print_table_conditional

2015, HBS

All

Code 
hbs_str_t225 %>%
  filter(time == "2015",
         geo == "FR") %>%
  left_join(coicop, by = "coicop") %>%
  select_if(~ n_distinct(.) > 1) %>%
  spread(age, values) %>%
  select_if(~ n_distinct(.) > 1) %>%
  print_table_conditional

2-digit

Code 
hbs_str_t225 %>%
  filter(time == "2015",
         geo == "FR",
         nchar(coicop) == 4) %>%
  left_join(coicop, by = "coicop") %>%
  select_if(~ n_distinct(.) > 1) %>%
  spread(age, values) %>%
  select_if(~ n_distinct(.) > 1) %>%
  print_table_conditional
coicop Coicop Y_GE60 Y_LT30 Y30-44 Y45-59
CP01 Food and non-alcoholic beverages 165 98 127 146
CP02 Alcoholic beverages, tobacco and narcotics 24 26 24 27
CP03 Clothing and footwear 25 54 50 42
CP04 Housing, water, electricity, gas and other fuels 325 265 270 275
CP05 Furnishings, household equipment and routine household maintenance 55 40 45 45
CP06 Health 17 14 15 16
CP07 Transport 111 155 136 144
CP08 Communications 21 30 24 24
CP09 Recreation and culture 72 96 78 77
CP10 Education 1 10 5 11
CP11 Restaurants and hotels 30 72 71 61
CP12 Miscellaneous goods and services 154 142 156 133

All quintiles

Sums

2-digit

Code 
hbs_str_t225 %>%
  filter(time == "2020",
         substr(coicop, 1, 2) == "CP",
         nchar(coicop) == 4) %>%
  left_join(geo, by = "geo") %>%
  select_if(~ n_distinct(.) > 1) %>%
  group_by(age, geo, Geo) %>%
  summarise(values = sum(values)) %>%
  spread(age, values) %>%
  print_table_conditional
geo Geo UNK Y_GE60 Y_LT30 Y30-44 Y45-59
AT Austria NA 1000 999 999 998
BE Belgium NA 1001 1001 1001 1002
BG Bulgaria NA 1000 1001 1000 1000
CY Cyprus NA 998 1001 1000 1000
CZ Czechia NA 1000 1000 1001 999
DE Germany NA 1000 1000 1001 998
DK Denmark NA 1000 999 999 1001
EE Estonia NA 1000 998 1000 1000
EL Greece NA 1000 1001 1001 1000
ES Spain NA 999 1000 1000 1000
EU27_2020 European Union - 27 countries (from 2020) NA 1000 1002 1000 1001
FI Finland NA 999 1000 1000 1001
FR France NA 1000 1002 1001 1001
HR Croatia NA 1001 NA 1000 1000
HU Hungary NA 1001 1000 1000 1001
IE Ireland NA 998 1000 999 1001
IT Italy NA 1000 1001 1000 1000
LT Lithuania NA 999 999 1000 1002
LU Luxembourg NA 999 998 1002 1002
LV Latvia NA 1001 1000 1000 1001
ME Montenegro NA 999 NA 1000 1000
MT Malta NA 1000 1001 1001 1000
NL Netherlands NA 1000 999 999 1000
PL Poland NA 999 1001 1000 1000
PT Portugal NA 999 1000 1002 1001
RO Romania NA 1000 1000 1000 999
RS Serbia NA 999 1000 999 1000
SI Slovenia NA 1000 999 999 999
SK Slovakia NA 1000 999 998 1000
TR Türkiye NA 999 997 1002 1000

3-digit

Code 
hbs_str_t225 %>%
  filter(time == "2020",
         substr(coicop, 1, 2) == "CP",
         nchar(coicop) == 5) %>%
  left_join(geo, by = "geo") %>%
  select_if(~ n_distinct(.) > 1) %>%
  group_by(age, geo, Geo) %>%
  summarise(values = sum(values)) %>%
  spread(age, values) %>%
  print_table_conditional
geo Geo UNK Y_GE60 Y_LT30 Y30-44 Y45-59
AT Austria NA 1000 1001 999 1001
BE Belgium NA 998 1002 995 1001
BG Bulgaria NA 1000 1000 998 999
CY Cyprus NA 994 1001 998 996
CZ Czechia NA 1000 1003 998 998
DE Germany NA 996 977 990 986
DK Denmark NA 995 998 1000 1002
EE Estonia NA 983 979 984 989
EL Greece NA 1001 1001 1001 1001
ES Spain NA 1000 1000 1002 999
FI Finland NA 990 973 973 979
FR France NA 986 991 989 983
HR Croatia NA 1000 NA 1000 1003
HU Hungary NA 997 999 999 1004
IE Ireland NA 1002 1002 1003 1000
IT Italy NA 1001 1000 1000 997
LT Lithuania NA 1000 999 1001 999
LU Luxembourg NA 999 998 1000 998
LV Latvia NA 998 1000 996 1000
ME Montenegro NA 985 NA 983 985
MT Malta NA 1002 1001 1001 1000
NL Netherlands NA 1002 1002 1002 998
PL Poland NA 998 1002 996 999
RS Serbia NA 998 999 1001 998
SI Slovenia NA 1000 1001 1000 1000
SK Slovakia NA 1001 999 998 998
TR Türkiye NA 1001 1001 1000 998

CP041, CP042, CP041_042

2015

France

Code 
hbs_str_t225 %>%
  filter(coicop %in% c("CP041", "CP042"),
         time == "2015",
         geo %in% c("FR")) %>%
  spread(coicop, values) %>%
  mutate(CP041_042 = CP041 + CP042) %>%
  gather(coicop, values, CP041, CP042, CP041_042) %>%
  left_join(geo, by = "geo") %>%
  left_join(coicop, by = "coicop") %>%
  left_join(colors, by = c("Geo" = "country")) %>%
  mutate(Coicop = ifelse(coicop == "CP041_042", "Imputed rentals plus actual rentals", Coicop),
         age = ifelse(age == "Y_GE60", "Y60+", age),
         age = ifelse(age == "Y_LT30", "Y30-", age)) %>%
  ggplot + geom_line(aes(x = age, y = values/1000, color = Coicop, group = Coicop)) +
  theme_minimal() +
  xlab("") + ylab("Weight in CPI") +
  scale_y_continuous(breaks = 0.01*seq(-30, 50, 2),
                     labels = percent_format(accuracy = 1)) +
  theme(legend.position = c(0.2, 0.9),
        legend.title = element_blank())

France

Actual + Imputed

Code 
hbs_str_t225 %>%
  filter(coicop %in% c("CP041", "CP042"),
         time == "2015",
         geo %in% c("FR")) %>%
  mutate(Coicop = factor(coicop, levels = c("CP042", "CP041"), labels = c("Loyers imputés (propriétaires)", "Loyers réels (locataires)")),
         age = ifelse(age == "Y_GE60", "Y60+", age),
         age = ifelse(age == "Y_LT30", "Y30-", age)) %>%
  ggplot + geom_col(aes(x = age, y = values/1000, fill = Coicop)) +
  theme_minimal() +
  xlab("") + ylab("Poids dans l'Indice des Prix") +
  scale_y_continuous(breaks = 0.01*seq(-30, 50, 5),
                     labels = percent_format(accuracy = 1)) +
  theme(legend.position = "top",
        legend.direction = "horizontal",
        legend.title = element_blank())

Tous

Code 
load_data("eurostat/deg_urb_fr.RData")
data <- hbs_str_t225 %>%
  rename(category = age) %>%
  mutate(type = "age") %>%
  bind_rows(hbs_str_t223 %>%
              rename(category = quantile) %>%
              mutate(type = "quantile")) %>%
  bind_rows(hbs_str_t226 %>%
              rename(category = deg_urb) %>%
              mutate(type = "deg_urb")) %>%
  filter(coicop %in% c("CP041", "CP042"),
         time == "2020",
         geo == "FR") %>%
  select_if(~ n_distinct(.) > 1) %>%
  select(type, everything(.)) %>%
  arrange(coicop)

data %>%
  spread(coicop, values) %>%
  mutate(CP041_042 = CP041 + CP042) %>%
  print_table_conditional()
type category CP041 CP042 CP041_042
age Y_GE60 44 190 234
age Y_LT30 144 56 200
age Y30-44 73 132 205
age Y45-59 55 149 204
deg_urb DEG1 85 138 223
deg_urb DEG2 62 148 210
deg_urb DEG3 33 168 201
quantile QUINTILE1 175 69 244
quantile QUINTILE2 112 125 237
quantile QUINTILE3 71 154 225
quantile QUINTILE4 41 167 208
quantile QUINTILE5 25 169 194

Graph

Code 
data_1 <- data %>%
  mutate(Coicop = factor(coicop, levels = c("CP042", "CP041"), labels = c("Loyers imputés (propriétaires occupants)", "Loyers réels (locataires)")),
         Category = factor(category, levels = c("Y_LT30", "Y30-44", "Y45-59", "Y_GE60",
                                                "DEG1", "DEG2", "DEG3",
                                                "QUINTILE1", "QUINTILE2", "QUINTILE3", "QUINTILE4", "QUINTILE5"),
                           labels = c("- de 30 ans", "De 30 à 44 ans", "De 45 à 59 ans", "+ de 60 ans",
                                      "Villes", "Villes - peuplées\net banlieues", "Zones rurales",
                                      "1er\n(+ pauvres)", "2è", "3è", "4è", "5è\n(+ riches)")),
         Type = factor(type, levels = c("age", "deg_urb", "quantile"),
                       labels = c("Âge", "Commune de résidence", "Cinquième de niveau de vie"))) %>%
  arrange(Coicop) %>%
  mutate(values = values/1000) %>%
  select(type, Type, category, Category, coicop, Coicop, values)
write.csv(data_1, file = "~/Desktop/graphique5.csv")

data_1 %>%
  ggplot + geom_col(aes(x = Category, y = values, fill = Coicop)) +
  theme_minimal() +
  xlab("") + ylab("Poids budgétaire des loyers (%)") +
  scale_y_continuous(breaks = 0.01*seq(-30, 50, 2),
                     labels = percent_format(accuracy = 1)) +
  theme(legend.position = "bottom",
        legend.title = element_blank(),
        legend.margin=margin(t=-35),
        axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1)) +
  scale_fill_manual(values = c("#005DA4", "#F59C00")) +
  facet_wrap(~ Type, scales = "free")