How do I pull earnings from the QLFS and put them on a bar chart?
In this blog post, we show a minimal example: mean monthly earnings by highest education attained, for the latest complete year, inflated to the latest headline CPI month.
We use the Quarterly Labour Force Survey (QLFS) and the public CPI Analytical Series. Please note: the QLFS is only available to EconData subscribers. On the other hand, CPI_ANL_SERIES is publicly available to anyone who has registered an EconData account at https://www.econdata.co.za .
The earnings series in our QLFS dataflow are gross monthly wage rates from PALMS (and LMDSA), not from StatsSA’s QLFS Excel tables. They are stored as inflation adjusted rands. In the steps below we take an employment-weighted mean across the four quarters of the latest complete year, then inflate those real values to the latest CPI month automatically.
# 1. Load R packages (run install.packages() if you don't have one of these installed already)
library(tidyverse)
library(econdatar) # library(remotes) then install_github("coderaanalytics/econdatar@4.0.4")
The QLFS contains more than 10 000 series, so always subset with series_key rather than downloading the whole dataflow. The series key has four dimensions, in order LABSTAT.CATEGORY.AREA.SEX. Use + to select several codes in one dimension, and . to separate dimensions. Here WRML is mean gross monthly real earnings, EMPL is employment (our weights), the E* codes are education groups, SA is national, and A is both sexes.
The read_dataset() options below are the ones you need for a tidy long table: tidy = TRUE, wide = FALSE, combine = TRUE, plus series_key.
# 2. Extract QLFS mean real earnings (WRML) and employment (EMPL) by education
# LABSTAT.CATEGORY.AREA.SEX - use + within a dimension, . between dimensions
qlfs_series_key <- "WRML+EMPL.ENONE+ELTPS+EPRIM+ESNOT+ESECD+ETERT.SA.A"
qlfs <- read_dataset("QLFS",
tidy = TRUE,
wide = FALSE,
combine = TRUE,
series_key = qlfs_series_key
) %>% as_tibble()
Axis labels come from EconData’s grouping_category metadata. A regular expression strips the Education: prefix. Attainment order is taken from the education codes in the same series key used to download the data.
# 3. Split the series key into dimension codes; labels from grouping_category
qlfs <- qlfs %>%
separate(series_key, into = c("labstat", "category", "area", "sex"),
sep = "\\.", remove = FALSE) %>%
mutate(education = str_remove(as.character(grouping_category),
"^Education:\\s*"))
edu_code_levels <- str_split(
str_extract(qlfs_series_key, "(?<=\\.)[^.]+(?=\\.)"),
"\\+"
)[[1]]
edu_label_levels <- qlfs %>%
distinct(category, education) %>%
slice(match(edu_code_levels, category)) %>%
pull(education)
Each quarter’s WRML is already a survey-weighted mean of individual earnings. To get one figure for the year, we take the latest calendar year in which every education group has four non-missing earnings quarters, then weight those quarterly means by that quarter’s employment (EMPL). Quarters with more workers therefore count more.
# 4. Latest calendar year with four non-missing earnings quarters for every education group
n_edu <- n_distinct(qlfs$category)
latest_year <- qlfs %>%
filter(labstat == "WRML", !is.na(obs_value)) %>%
mutate(year = year(time_period)) %>%
distinct(year, category, time_period) %>%
count(year, category, name = "n_quarters") %>%
group_by(year) %>%
filter(n() == n_edu, all(n_quarters == 4)) %>%
ungroup() %>%
summarise(year = max(year), .groups = "drop") %>%
pull(year)
# 5. Employment-weighted mean of the quarterly means, for that year
earnings_edu <- qlfs %>%
mutate(year = year(time_period)) %>%
filter(year == latest_year) %>%
select(time_period, labstat, category, education, obs_value) %>%
pivot_wider(names_from = labstat, values_from = obs_value) %>%
group_by(category, education) %>%
summarise(
mean_earnings = weighted.mean(WRML, w = EMPL, na.rm = TRUE),
.groups = "drop"
) %>%
mutate(education = factor(education, levels = edu_label_levels))
Headline CPI lives in CPI_ANL_SERIES, series CPS00000. That dataflow is public: you only need a registered EconData account, not a paid subscription. The QLFS earnings series carry a base_period (currently Dec 2024). We read that from the metadata, look up the matching CPI observation, and multiply by the ratio of the latest CPI month to that base. Nothing in this step is hard-coded to a particular month.
# 6. Headline CPI (public), and inflate from the QLFS earnings base period
# to the latest CPI month available
cpi <- read_dataset("CPI_ANL_SERIES",
tidy = TRUE,
wide = FALSE,
combine = TRUE,
series_key = "CPS00000"
) %>% as_tibble()
earnings_base <- qlfs %>%
filter(labstat == "WRML") %>%
distinct(base_period) %>%
pull(base_period) %>%
as.character() %>%
unique()
cpi_base_date <- dmy(paste("01", earnings_base[[1]]))
cpi_latest_row <- cpi %>% filter(time_period == max(time_period))
cpi_base_row <- cpi %>% filter(time_period == cpi_base_date)
inflate <- cpi_latest_row$obs_value / cpi_base_row$obs_value
cpi_month_label <- format(cpi_latest_row$time_period, "%B %Y")
earnings_edu <- earnings_edu %>%
mutate(mean_earnings_latest = mean_earnings * inflate)
Now we can create a visual chart.
# 7. Build the ggplot chart
ggplot_earnings_edu <- ggplot(earnings_edu,
aes(x = education, y = mean_earnings_latest / 1000)) +
theme_minimal(base_size = 15) +
geom_col(fill = "#273b8d", width = 0.7) +
scale_y_continuous(
expand = expansion(mult = c(0, 0.05)),
labels = scales::label_comma(big.mark = " ")
) +
coord_flip(ylim = c(0, NA), clip = "off") +
theme(
plot.title.position = "plot",
plot.caption.position = "plot",
plot.caption = element_text(hjust = 0, margin = margin(b = 3, t = 9)),
panel.grid.major.y = element_blank(),
axis.title.y = element_blank(),
plot.margin = margin(t = 3, r = 8, b = 3, l = 3, unit = "mm")
) +
labs(
y = "Thousands of rands per month",
title = "Mean monthly earnings by education",
caption = paste0(
"Source: StatsSA QLFS (PALMS), StatsSA CPI; EconData\n",
latest_year, " employment-weighted mean, inflated to ",
cpi_month_label, " prices"
)
)
# codera_logo(xpos = 0.82, ypos = 0.18, wdth = 6)
# 8. Export the ggplot object to a file
ggsave(
filename = "statssa/qlfs/qlfs_mean_earnings_education.jpg",
plot = ggplot_earnings_edu,
width = 17.5,
height = 11.5,
units = "cm"
)
Here is the output of that code:
