Turns the raw EUKLEMS file into the dataset the analysis actually uses:
output/clean/pkp_clean.rdsRun 00_setup.Rmd first. Everything
after this document loads pkp_clean.rds rather than the raw
file.
load("output/clean/00_setup.RData")
suppressPackageStartupMessages({ library(dplyr) })
load("Data/Luiss_datasets.RData")
annual$year <- as.integer(annual$year)
dim(klems)## [1] 61128 236Two filters. Industries are matched exactly against
the lists from 00_setup.Rmd - never by pattern, because the
industry column mixes four levels of one hierarchy. Countries are the
usable comparators plus EU12, which is kept because it is
the only aggregate carrying growth accounting.
Two industry levels are kept, following the mentors’ request for division-level detail:
keep_geo <- c(peer_countries, ga_benchmark)
keep_nace <- c(focus_sections, division_codes)
dat <- klems %>%
filter(nace_r2_code %in% keep_nace,
geo_code %in% keep_geo)
nrow(dat)## [1] 17010table(dat$geo_code)##
## AT BG CZ DE DK ES EU11 FI FR IT JP LT LU LV NL RO
## 810 810 810 810 810 810 810 810 810 810 810 810 810 810 810 810
## SE SI SK UK US
## 810 810 810 810 810Critical. The dataset now holds two levels of the
same hierarchy: section C contains division
C26. Adding them together would double-count - the same
error that overstates the Slovenian economy by 5.4 times.
Every row is therefore labelled section or
division, and every division records its parent.
Always filter on level before
aggregating.
dat <- dat %>%
mutate(
level = if_else(nace_r2_code %in% focus_sections, "section", "division"),
parent = if_else(level == "section",
nace_r2_code,
unname(division_parent[nace_r2_code]))
)
table(dat$level)##
## division section
## 13608 3402Out of 236 columns. The selection covers every variable the team
highlighted in Variable-List-Highlighted.xlsx, plus the
growth-accounting block and the identifiers. intersect()
guards against a mistyped name silently becoming a missing column
later.
Note on duplicated names. Eight names appear twice
in the source variable list - once in the statistical module and once in
the intangibles analytical module: VA_CP,
VA_Q, I_Soft_DB, I_RD,
I_OIPP, K_Soft_DB, K_RD,
K_OIPP. The merged file carries only one column for each,
so one version won. Which one is not recoverable from the file, and it
is worth confirming with the mentors before these variables carry weight
in the analysis.
wanted <- c(
# identifiers
"nace_r2_code", "nace_r2_name", "geo_code", "year", "level", "parent",
# output and labour input
"VA_CP", "VA_Q", "VAadj", "VAadj_q", "EMP", "H_EMP",
# investment, national accounts basis
"I_GFCF", "I_IT", "I_CT", "I_Soft_DB", "I_RD", "I_OIPP",
# investment, CHS basis
"I_Tang", "I_Intang", "I_NatAcc", "I_NonNatAcc",
"I_Innovprop", "I_EconComp",
"I_OrgCap", "I_Brand", "I_Train", "I_Design",
# investment in chained volumes (2015) - needed for anything over time
"Iq_GFCF", "Iq_Tang", "Iq_Intang", "Iq_NatAcc", "Iq_NonNatAcc",
# capital stocks
"K_GFCF", "K_RD", "K_Soft_DB", "K_OIPP", "K_Tang",
"K_NatAcc", "K_NonNatAcc", "K_Innovprop", "K_EconComp",
"K_OrgCap", "K_Brand", "K_Train",
# K_Intang is kept for reference only - it is unusable as delivered for
# Slovenia (one year out of 27). Use K_Intang_rebuilt instead.
"K_Intang",
# growth accounting
"VA_G", "VAConIntang", "VAConTangICT", "VAConTangNICT",
"VAConLC", "VAConTFP",
"CAPIntang_QI", "CAPTang_QI", "LAB_QI"
)
wanted <- intersect(wanted, names(dat))
dat <- dat %>%
select(all_of(wanted)) %>%
rename(nace = nace_r2_code,
geo = geo_code)
length(wanted)## [1] 55All monetary variables are in millions of national currency, so levels cannot be compared across countries. Every indicator below is a ratio, which is unit-free.
Two accounting bases exist and must not be mixed:
I_GFCF over
VA_CPI_Intang and
I_Tang over VAadjGross fixed capital formation can be negative in national accounts, when disposals of assets exceed acquisitions in a period. This is real data, not an error, and it appears in small or volatile industries - for example Slovak coke and refined petroleum, Danish chemicals, Swedish air transport. It affects 40 of roughly 10,000 division-level observations, and none in Slovenia.
It matters because a share is only meaningful when the part and the whole are both non-negative: a negative numerator produces shares like -1342 percent. Two helpers handle this explicitly rather than letting nonsense through.
# An INTENSITY (flow relative to output) may legitimately be negative - it means
# net disinvestment. Only the denominator has to be positive.
safe_intensity <- function(num, den) {
if_else(!is.na(den) & den > 0, 100 * num / den, NA_real_)
}
# A SHARE (part of a whole) must lie between 0 and 100 by definition. It can
# fall outside that range only when some component of the whole is negative -
# for example intangible investment of 100 against tangible investment of -90
# gives a total of 10 and a "share" of 1000 percent. Such a figure is not
# meaningful, so it is set to NA and counted, never silently kept or clamped
# to a boundary.
safe_share <- function(part, whole) {
x <- if_else(!is.na(whole) & whole != 0, 100 * part / whole, NA_real_)
if_else(!is.na(x) & x >= 0 & x <= 100, x, NA_real_)
}dat <- dat %>%
mutate(
# -- investment intensities (percent of value added) --------------------
inv_intensity = safe_intensity(I_GFCF, VA_CP), # national accounts basis
intang_intensity = safe_intensity(I_Intang, VAadj), # CHS basis
tang_intensity = safe_intensity(I_Tang, VAadj),
# -- the headline structural indicator ----------------------------------
intang_share = safe_share(I_Intang, I_Intang + I_Tang),
# -- CHS composition (shares of total intangible investment) ------------
# The three Corrado-Hulten-Sichel categories.
sh_compinfo = safe_share(I_Soft_DB, I_Intang), # computerised information
sh_innovprop = safe_share(I_Innovprop, I_Intang), # innovative property
sh_econcomp = safe_share(I_EconComp, I_Intang), # economic competencies
# -- the measurement gap -------------------------------------------------
# Share of intangible investment that national accounts do NOT capitalise.
sh_nonNA = safe_share(I_NonNatAcc, I_Intang),
# -- individual assets, as shares of intangible investment ---------------
sh_rd = safe_share(I_RD, I_Intang),
sh_orgcap = safe_share(I_OrgCap, I_Intang),
sh_brand = safe_share(I_Brand, I_Intang),
sh_train = safe_share(I_Train, I_Intang),
sh_design = safe_share(I_Design, I_Intang),
# -- tangible composition: ICT vs non-ICT (national accounts basis) ------
ict_share = safe_share(I_IT + I_CT + I_Soft_DB, I_GFCF),
# -- labour productivity, CHS basis, real ---------------------------------
# Per HOUR is the primary measure: it is what EUKLEMS itself uses, and it
# does not confound productivity with differences in working time. Per
# PERSON is reported alongside as a robustness check, since it is the more
# familiar measure and the two diverge where part-time work is common.
# UNITS. VAadj_q is millions of euro; H_EMP is thousands of hours; EMP is
# thousands of persons. So:
# VAadj_q / H_EMP = thousand EUR per hour -> x1000 gives EUR per hour
# VAadj_q / EMP = thousand EUR per person -> left as is
# H_EMP / EMP = hours per person -> already correct
lp_hour = 1000 * VAadj_q / H_EMP, # EUR per hour, 2015 prices
lp_person = VAadj_q / EMP, # thousand EUR per person
hours_per_person = H_EMP / EMP, # hours per person per year
# -- reconstructed intangible capital stock -------------------------------
# K_Intang is unusable as delivered (one year for Slovenia). Its components
# are complete from 2000, and their sum reproduces the published figure
# exactly in the year it exists, so it is rebuilt here.
K_Intang_rebuilt = K_NatAcc + K_NonNatAcc,
# -- flag the known anomaly ----------------------------------------------
# Slovenian stock variables spike implausibly in 2020 (K_OrgCap roughly
# doubles and falls back). The anomaly is in the source LUISS module, not
# introduced here. Flow variables are unaffected.
stock_suspect = (geo == "SI" & year == 2020)
)# Sections get the short labels from 00_setup; divisions keep their full name
# from the source data.
dat <- dat %>%
mutate(
industry = if_else(level == "section",
unname(industry_labels[nace]),
nace_r2_name),
group = unname(industry_groups[parent]),
is_focus_country = geo %in% peer_countries
) %>%
select(-nace_r2_name) %>%
relocate(nace, industry, level, parent, group, geo, year)
head(dat[dat$level == "division", 1:7])## # A tibble: 6 × 7
## nace industry level parent group geo year
## <chr> <chr> <chr> <chr> <chr> <chr> <dbl>
## 1 C10-C12 Manufacture of food products; beverage… divi… C Manu… AT 1995
## 2 C10-C12 Manufacture of food products; beverage… divi… C Manu… AT 1996
## 3 C10-C12 Manufacture of food products; beverage… divi… C Manu… AT 1997
## 4 C10-C12 Manufacture of food products; beverage… divi… C Manu… AT 1998
## 5 C10-C12 Manufacture of food products; beverage… divi… C Manu… AT 1999
## 6 C10-C12 Manufacture of food products; beverage… divi… C Manu… AT 2000If any of these fail, stop and investigate before using the dataset.
# 1. Does industry + country + year still uniquely identify a row?
stopifnot(nrow(dat) == nrow(distinct(dat, nace, geo, year)))
# 1b. Is every division assigned to a parent section?
stopifnot(!any(is.na(dat$parent)))
# 2. Does the CHS identity hold? (computerised info + innovative property +
# economic competencies should equal total intangibles)
chs_gap <- dat %>%
filter(!is.na(I_Intang), I_Intang > 0) %>%
mutate(d = abs(I_Soft_DB + I_Innovprop + I_EconComp - I_Intang) / I_Intang) %>%
summarise(max_deviation_pc = 100 * max(d, na.rm = TRUE)) %>%
pull(max_deviation_pc)
# 3. Does the national-accounts split hold?
na_gap <- dat %>%
filter(!is.na(I_Intang), I_Intang > 0) %>%
mutate(d = abs(I_NatAcc + I_NonNatAcc - I_Intang) / I_Intang) %>%
summarise(max_deviation_pc = 100 * max(d, na.rm = TRUE)) %>%
pull(max_deviation_pc)
# 4. Are the shares in a sensible range? They must be, by construction -
# safe_share() returns NA rather than a nonsensical value.
share_range <- range(dat$intang_share, na.rm = TRUE)
# 4b. How many observations were set aside because a component was negative?
n_suppressed <- sum(!is.na(dat$I_Intang) & !is.na(dat$I_Tang) &
is.na(dat$intang_share))
cat("unique key .................. OK\n")## unique key .................. OKcat("CHS identity, max deviation :", round(chs_gap, 6), "percent\n")## CHS identity, max deviation : 0 percentcat("NA split, max deviation :", round(na_gap, 6), "percent\n")## NA split, max deviation : 0 percentcat("intangible share range ......", round(share_range[1], 1), "to",
round(share_range[2], 1), "percent\n")## intangible share range ...... 0.3 to 100 percentcat("shares set to NA (negative component):", n_suppressed, "\n")## shares set to NA (negative component): 60stopifnot(chs_gap < 0.01, na_gap < 0.01,
share_range[1] >= 0, share_range[2] <= 100)saveRDS(dat, "output/clean/pkp_clean.rds")
cat("01_prepare_data complete\n")## 01_prepare_data completecat(" rows :", nrow(dat), "\n")## rows : 17010cat(" columns :", ncol(dat), "\n")## columns : 76cat(" sections :", sum(dat$level == "section") / (length(unique(dat$geo)) * 27), "\n")## sections : 6cat(" divisions :", sum(dat$level == "division") / (length(unique(dat$geo)) * 27), "\n")## divisions : 24cat(" countries :", length(unique(dat$geo)), "\n")## countries : 21cat(" years :", min(dat$year), "-", max(dat$year), "\n")## years : 1995 - 2021cat(" saved :", round(file.size("output/clean/pkp_clean.rds") / 1024, 1),
"KB to output/clean/pkp_clean.rds\n")## saved : 5352 KB to output/clean/pkp_clean.rdsLater documents begin with:
load("output/clean/00_setup.RData")
dat <- readRDS("output/clean/pkp_clean.rds")And then, always, one of these before aggregating:
sections <- dat %>% filter(level == "section") # the main analysis
divisions <- dat %>% filter(level == "division") # economic competencies onlyMixing the two levels in one total double-counts.