Data Sources & Methodology

Data Sources

VSECAT draws on the latest available year from three major Eurostat surveys. The codebook links provide researchers with full documentation of the original datasets, including variable definitions and coding schemas.

Methodology

This section describes how VSECAT transforms three official Eurostat sources—AES 2022, LFS 2023 (yearly), and SES 2018—into VSECAT. The goal is to ensure that variables, categories, and keys are consistent across surveys, enabling reliable cross-country and cross-sector analysis of the data.
The methodology has two stages:

• Preprocessing: Cleaning, harmonizing, and enriching each source dataset individually.
• Merging: Joining the harmonized outputs into the VSECAT database.

In the next figure, a high-level view of the whole methodology used to create VSECAT is depicted. In the following sections, all the process will be detailed.

Preprocessing

Objectives and Common Principles

The preprocessing stage ensures that AES, LFS, and SES share:

• Common variable names and definitions (e.g., COUNTRY, SEX, AGE_GROUPS, ISCO-08, FTPT, LOCSIZEFIRM).
• Consistent category labels (e.g., Male/Female, Full-time/Part-time, firm size bands).
• Standardized classifications:
  • Occupations: ISCO-08 codes normalized to the same digit level and linked to official titles. The maximum size of digit level is 2-digit.
  • Education: ISCED-11 aligned levels and harmonized fields of study.
• Derived indicators for comparability:
  • AGE_GROUPS: 14–19, 20–29, 30–39, 40–49, 50–59, 60+.
  • ORIGIN: National / Foreigner (where parental country data exists).
• Special code handling: “Not applicable” and “Not available” standardized to readable labels.
• SES-specific adjustments: Currency normalization and aggregation to a merge-ready grain.

These steps create a harmonized structure that allows the next stage, merging, to combine the datasets without ad-hoc fixes.

AES 2022

LFS 2023

SES 2018

Harmonization Outcome

After preprocessing:

• Shared keys across all datasets: COUNTRY, SEX, AGE_GROUPS, HATLEVEL, ISCO-08, ISCO-08_Title, FTPT
• Shared keys across AES-LFS: CITIZENSHIP, HATFIELD, MAINSTAT, JOBSTAT, LOCSIZEFIRM, ORIGIN.
• Consistent category labels: Same wording for categorical variables such as sex, working time, firm size, education domains, etc.
• SES earnings normalized and aggregated: Ready to match AES/LFS slices.
• Special codes harmonized: “Not applicable” and “Not available” standardized; placeholders replaced by actual country names.

This alignment is the foundation for the next step, Merging, where we combine the three harmonized datasets into VSECAT.

Merging

Objectives and inputs

After preprocessing, the three sources (AES 2022, LFS 2023‑Y, SES 2018) share a common set of variables, labels, and classifications. The merging stage:

• Preserves maximum coverage across countries and breakdowns by using outer joins.
• Aligns at two grains:
  • A rich population grain for AES×LFS (demographics, labour status, education, firm size, etc.).
  • A reduced analytical grain for linking SES earnings (country, sex, occupation, age group, working time, plus occupation title).
• Resolves duplicate groups across sources with a clear priority order, producing a single entry per group.

Pre-join quality filters

Before any join, each input table is filtered to remove rows that would undermine comparability:

1. Occupation title must be informative: rows whose ISCO‑08_Title equals “Not available” or “Not applicable” (case‑insensitive) are discarded in all three inputs. This ensures that every record entering the merge has a valid occupational attribution.
2. Free‑text “No answer”: any row that contains the literal string “No answer” in any variable is removed (the check is done row‑wise and case‑insensitive). This avoids mixing explicit item non‑response with valid categories.

Join keys and sequence

1. Merge AES and LFS (rich population grain). AES and LFS are merged with an outer join on the following key set: COUNTRY, SEX, ISCO‑08, AGE_GROUPS, ISCO‑08_Title, HATLEVEL, FTPT, CITIZENSHIP, HATFIELD, MAINSTAT, JOBSTAT, LOCSIZEFIRM, COUNTRY_MOTHER, COUNTRY_FATHER, ORIGIN. This retains the demographic, education, labour‑status and firm‑size detail that characterize the population tables.
2. Merge the AES×LFS result with SES (earnings grain). The intermediate table is then outer‑joined with SES on a reduced key set tailored to SES earnings coverage:
    COUNTRY, SEX, ISCO‑08, AGE_GROUPS, ISCO‑08_Title, FTPT.

Because SES is linked on fewer keys than AES×LFS, earnings indicators are conditioned only by: country, sex, occupation (2‑digit ISCO‑08), age group, and full/part‑time. They are not conditioned by other AES/LFS dimensions such as HATLEVEL, HATFIELD, MAINSTAT, JOBSTAT, CITIZENSHIP, ORIGIN, or LOCSIZEFIRM. In practice, this means the same SES earnings for a given (COUNTRY, SEX, ISCO‑08, AGE_GROUPS, FTPT) combination will align with all corresponding AES/LFS segments that differ only along those additional dimensions. This reflects the lower number of shared variables of the merge keys in SES.

Both joins are outer, so combinations present in any source are retained for downstream analysis; missing elements from other sources remain empty instead of removed.

Coalescing rule

After joining, some variables may be present in multiple source‑suffixed versions (e.g., VARIABLE_aes, VARIABLE_lfs, VARIABLE_ses) or also as an unsuffixed column carried by the merge. The pipeline coalesces these into one column per variable, following a strict priority order:

1. AES value if available (_aes)
2. otherwise LFS value (_lfs)
3. otherwise the unsuffixed value (if created by the merge)
4. otherwise SES value (_ses)

Empty strings are treated as missing before applying this precedence. After coalescing, the redundant suffixed columns are dropped. Join keys are excluded from coalescing (they are already harmonized).

This rule ensures a deterministic resolution of overlaps while reflecting a design choice to prioritize AES, then LFS, and only then SES for any non‑key variable that appears in more than one source.

Exact de-duplication with counts

The merged table is scanned for fully identical rows (same values in all columns). Identical rows are collapsed into a single record and an auxiliary counter (N_instances) is added to indicate how many duplicates existed. This provides a traceable audit of record multiplicity without inflating statistics.

Final categorical alignment

To keep categories stable and efficient for analysis:

• Low‑cardinality columns are converted to categorical data type (only when it’s safe to do so)
• Category sets are unified and ordered consistently across the table.

This step avoids subtle inconsistencies when grouping or pivoting later.

What VSECAT represents

• The final table is a wide analytical base where population descriptors (from AES/LFS) and earnings indicators (from SES) are aligned at the (COUNTRY, SEX, ISCO‑08, AGE_GROUPS, FTPT) grain. Additional AES/LFS dimensions (education level/field, labour status, citizenship/origin, firm size) remain available to segment populations, but earnings are not further broken down by those dimensions at merge time.
• Because both joins are outer, the merged dataset may contain cells without earnings (where SES has no observation for a given key) or population cells without complements in the other surveys. These are expected and signal true coverage differences across sources.

Practical notes for users of VSECAT

• Occupation filtering: records without a usable occupation code are dropped before the merge—analyses should assume valid ISCO‑08 code and title in every final row.
• “No answer”: any row containing this literal answer anywhere is removed; users will not encounter this residual category.
• Coalesced variables: where the same concept appears from multiple surveys, VSECAT provides one unified column following the precedence described above. Users do not need to manage suffixed variants.