Eurostat Bulk Download Facility

Continuous Integration framework
for Data Scientist (v0.2)

1 Retrieve data series from Eurostat

Three steps:

1. Search in Eurostat’s TOC to retrieve time series codes.
2. Fetch data-sets or time-series.
3. Retrieve related information from dictionaries.

Methods used from library eurostat.

1.1 Search

Available time series related to public debt are:

#Call the library:
  #library (eruostat)

#Description of available time series:
    kable ( 
      search_eurostat (
                      "debt", 
                      type = "dataset", 
                      fixed = TRUE 
                      )[c("code", "title")]
      )

code	title
gov_10dd_edpt1	Government deficit/surplus, debt and associated data
gov_10dd_edpt3	Transition from the deficit/surplus to the change in debt
gov_10dd_cgd	Central government debt
gov_10dd_ggd	General government debt
gov_10dd_slgd	State government debt
gov_10dd_logd	Local government debt
gov_10dd_ssfd	Social security funds debt
gov_10dd_acd	Apparent cost of general government gross debt
gov_10dd_mggd	Market value of general government gross debt
gov_10dd_rmd	Average remaining maturity of general government gross debt
gov_10q_ggdebt	Quarterly government debt
ilc_mded05	Financial burden of the repayment of debts from hire purchases or loans - EU-SILC survey

1.2 Fetch selected data

We decide to use the data set “Government deficit/surplus, debt and associated data” which code is “gov_10dd_edpt1”.

Table below shows the first six rows of the data set.

# Retrieve the data:
      # As I do not know the amount of memory needed
      # First time I will allow for cache use.
      # length 142149 bytes (138 KB) downloaded 138 KB
      # No need for cache usage.
    eu_gdme <- get_eurostat ("gov_10dd_edpt1"
                             , cache=F
                             , time_format = "raw"
                             )
      
# Have a look: it looks better
        kable ( 
          head (eu_gdme),
          caption = "Government deficit/surplus, 
          debt and associated data"
        )

Government deficit/surplus, debt and associated data

unit	sector	na_item	geo	time	values
MIO_EUR	S1	B1GQ	AT	2019	398521.9
MIO_EUR	S1	B1GQ	BE	2019	473638.9
MIO_EUR	S1	B1GQ	BG	2019	60675.3
MIO_EUR	S1	B1GQ	CY	2019	21943.6
MIO_EUR	S1	B1GQ	CZ	2019	220200.7
MIO_EUR	S1	B1GQ	DE	2019	3435760.0

1.3 Retrieve related information from dictionaries

#Dictionaries:
  #label_eurostat method substitutes 
  # the coded variables by their 
  # description in the dictionary
  eu_gdlab <- label_eurostat(eu_gdme)
  
  # This can be used to have in the same data
  # sets both codes and descriptions

  # Check that the order is the same (must be 0)
  # If yes, we can add dic information to data.
  if (sum (eu_gdme$values-eu_gdlab$values)==0){
    print ("Labels check: All is good. Variables in the data sets are properly labelled. Keep going.")
    names (eu_gdlab)[1:4] <- paste0 (
      "lab_",names (eu_gdlab)[1:4])
  }else{ 
    print ("Labels check: Something is wrong. Please check the code.")
  }

## [1] "Labels check: All is good. Variables in the data sets are properly labelled. Keep going."

#The resulting data set is eu_debt:
    eu_debt <- cbind (eu_gdme, eu_gdlab[1:4])

# We can delete the rest:
rm (list=c("eu_gdlab", "eu_gdme"))

2 Present relevant summary information.

At this point we are interested on the time window and geography that we can cover.

Therefore we want to know, per each EU country, what period is covered.

# Produce a table with useful summary information on the data sets.
  # "ctry" is a vector of country names passed
  # to an object in the setup chunk.
  # Allows to quickly drop country groups:
  sel <- which (eu_debt$geo%in%ctry$ctry)

  #Number of observations per data set:
  num.obs <- aggregate (time ~ geo + na_item, 
                        data = eu_debt[sel,],
                        length)
  # The interest is to know which time series
  # have les than n (20) observations.
    n <- num.obs %>%
      filter (time < 20)%>%
      group_by(na_item, geo)%>%
      spread(na_item, time)

  
  # First period of available data.
  # Minimum start period (2002):
  first.year <- aggregate (time~geo+na_item,
           data = eu_debt[sel,], 
           min)
  f <- first.year %>%
      filter (time>2002)%>%
      group_by(na_item, geo)%>%
      spread(na_item, time)
  
  # Series that cannot be used due to
  # lack of data, narrow time window.
  kable (
    rbind (
    naitemdic[which(naitemdic$code_name%in%names (n)[-1]),],
    naitemdic[which(naitemdic$code_name%in%names (f)[-1]),]
    ),
    caption = "Time series with not enougth data."
  )

Time series with not enougth data.

code_name	full_name
B5GQ	Gross national income at market prices
AF_81L	Trade credits and advances - liabilities
IGL_F4_EA19	Intergovernmental lending within EA19 (from 2015)
IGL_F4_EA18	Intergovernmental lending within EA18 (2014)
IGL_F4_EA17	Intergovernmental lending within EA17 (2011-2013)
IGL_F4_EU27_2020	Intergovernmental lending within EU27 (from 2020)
IGL_F4_EU28	Intergovernmental lending within EU28 (2013-2020)
IGL_F4_EU27_2007	Intergovernmental lending within EU27 (2007-2013)
IGL_F4_EU25	Intergovernmental lending within EU25 (2004-2006)
B5GQ	Gross national income at market prices
GD_F2	Government consolidated gross debt at face value - Currency and deposits
AF_81L	Trade credits and advances - liabilities

  #Discarded data series
  nouse <- rbind ( 
    naitemdic [which (
      naitemdic$code_name%in%names(n)[-1])
    ,"code_name"],
    naitemdic [which (
      naitemdic$code_name%in%names(f)[-1])
      , "code_name"])$code_name
  
  #Resulting data
  eu_debt_bk <- eu_debt
  eu_debt <- eu_debt%>%
    # Drop time series with non enougth observations.
    filter (!na_item%in%nouse)%>%
    # We are interested in easily drop
    # group of countries UA/EA.
    mutate (ctry = ifelse (geo%in%ctry$ctry,1,0))

3 Simple plots

# unique (eu_debt[, c("na_item", "lab_na_item", "sector")])

# Basic line plot by na_items all countries.
theme_set(theme_minimal())

dplot <- eu_debt %>%
  filter (ctry==1) %>%
  filter (unit == "PC_GDP")%>%
  # filter (na_item %in% c("GD_F4", "GD_F42"))%>%
  filter (values>0)

p_gd   <- ggplot(
    data = eu_debt %>%
      filter (ctry==1)%>%
      filter (na_item %in% c("GD_F4", "GD_F42"))%>%
      filter (unit == "PC_GDP"),
    aes(
      x = time, 
      y = values, 
      group = geo, 
      color = geo
      ))+
    geom_line()+
    facet_wrap(~lab_na_item)+
  theme(legend.position="bottom", legend.box = "horizontal")+ 
  guides(colour = guide_legend(nrow = 3))+ 
  theme (axis.text.x = element_text (
    angle = 45, vjust = 0.5, hjust=1),
    axis.title.x=element_blank())+
  labs(title = "Government consolidated gross debt",
       subtitle = "(at facet value)",
       y = "% GDC", x = "")
p_gd

# Basic line plot by countries GD_F4.
theme_set(theme_minimal())

p_gd4   <- ggplot(
    data = eu_debt %>%
      filter (ctry==1)%>%
      filter (na_item %in% c("GD_F4"))%>%
      filter (unit == "PC_GDP"),
    aes(
      x = time, 
      y = values, 
      group = geo, 
      color = geo
      ))+
    geom_line()+
    facet_wrap(~lab_geo)+
  theme(legend.position="none")+ 
  theme (
    axis.title.x=element_blank(),
    axis.text.x=element_blank(),
    axis.ticks.x=element_blank())+
  labs(title = "Government consolidated gross debt",
       subtitle = "(at facet value, all loans. 1995-2019)",
       y = "% GDC", x = "")
p_gd4

#Gross fixed capital formation 
P51G <- ggplot(
    data = eu_debt %>%
      filter (ctry==1)%>%
      filter (na_item %in% c("P51G"))%>%
      filter (unit == "PC_GDP"),
    aes(
      x = time, 
      y = values, 
      group = geo, 
      color = geo
      ))+
    geom_line()+
    facet_wrap(~lab_geo)+
  theme(legend.position="none")+ 
  theme (
    axis.title.x=element_blank(),
    axis.text.x=element_blank(),
    axis.ticks.x=element_blank())+
  labs(title = "Gross fixed capital formation",
       subtitle = "(1995-2019; P51G )",
       y = "% GDC", x = "")
P51G

# Net lending (+) /net borrowing (-)
B9 <- ggplot(
    data = eu_debt %>%
      filter (ctry==1)%>%
      filter (na_item %in% c("B9"))%>%
      filter (unit == "MIO_EUR"),
    aes(
      x = time, 
      y = values, 
      group = geo, 
      color = geo
      ))+
    geom_line()+
    facet_wrap(~lab_geo)+
  theme(legend.position="none")+ 
  theme (
    axis.title.x=element_blank(),
    axis.text.x=element_blank(),
    axis.ticks.x=element_blank())+
  labs(title = "Net lending (+) /net borrowing (-)",
       subtitle = "(1995-2019; B9)",
       y = "Million euro", x = "")
B9

4 Annex

Time series considered:

Available time series

	na_item	lab_na_item	sector
1	B1GQ	Gross domestic product at market prices	S1
36	B9	Net lending (+) /net borrowing (-)	S13
71	D41PAY	Interest, payable	S13
101	GD	Government consolidated gross debt	S13
136	GD_F3	Government consolidated gross debt at face value - Debt securities	S13
171	GD_F31	Government consolidated gross debt at face value - Short-term debt securities	S13
206	GD_F32	Government consolidated gross debt at face value - Long-term debt securities	S13
241	GD_F4	Government consolidated gross debt at face value - Loans	S13
276	GD_F41	Government consolidated gross debt at face value - Short-term loans	S13
311	GD_F42	Government consolidated gross debt at face value - Long-term loans	S13
346	P51G	Gross fixed capital formation	S13
376	B9	Net lending (+) /net borrowing (-)	S1311
406	GD	Government consolidated gross debt	S1311
436	B9	Net lending (+) /net borrowing (-)	S1312
447	GD	Government consolidated gross debt	S1312
458	B9	Net lending (+) /net borrowing (-)	S1313
488	GD	Government consolidated gross debt	S1313
518	B9	Net lending (+) /net borrowing (-)	S1314
545	GD	Government consolidated gross debt	S1314