from pathlib import Path
import csv
import math
import re
import sys
import pandas as pd
from IPython.display import display, Markdown
# Show full text in table cells for chapter outputs.
pd.set_option("display.max_colwidth", None)
try:
from sentence_transformers import SentenceTransformer
HAS_SENTENCE_TRANSFORMERS = True
except Exception:
HAS_SENTENCE_TRANSFORMERS = False
class CSVSemanticRetriever:
"""Semantic variable retriever from a metadata CSV."""
def __init__(
self,
csv_path,
variable_col="Variable",
description_col="Description",
model_name="all-MiniLM-L6-v2",
):
self.csv_path = Path(csv_path)
self.variable_col = variable_col
self.description_col = description_col
self.model_name = model_name
self.records = self._load_metadata()
self.model = None
if HAS_SENTENCE_TRANSFORMERS:
self.model = SentenceTransformer(self.model_name)
self.backend = "sentence-transformers" if self.model is not None else "lexical-fallback"
self.embeddings = None
if self.model is not None:
self.embeddings = self._build_embeddings([r["Description"] for r in self.records], is_query=False)
# Lexical fallback index for environments where sentence-transformers is unavailable.
self.doc_tokens = [set(self._tokenize(r["Description"])) for r in self.records]
token_df = {}
for tokens in self.doc_tokens:
for tok in tokens:
token_df[tok] = token_df.get(tok, 0) + 1
n_docs = max(len(self.doc_tokens), 1)
self.idf = {tok: math.log((1 + n_docs) / (1 + doc_freq)) + 1.0 for tok, doc_freq in token_df.items()}
def _load_metadata(self):
if not self.csv_path.exists():
raise FileNotFoundError(f"Metadata file not found: {self.csv_path}")
with self.csv_path.open("r", encoding="utf-8-sig", newline="") as f:
reader = csv.DictReader(f)
if reader.fieldnames is None:
raise ValueError(f"Could not read header from {self.csv_path}.")
normalized = {name.strip(): name for name in reader.fieldnames}
var_key = normalized.get(self.variable_col)
desc_key = normalized.get(self.description_col)
if var_key is None or desc_key is None:
raise ValueError(
f"Expected columns '{self.variable_col}' and '{self.description_col}' in {self.csv_path}, got {reader.fieldnames}."
)
records = []
for row in reader:
var = str(row.get(var_key, "")).strip()
desc = str(row.get(desc_key, "")).strip()
if var and desc:
records.append({"Variable": var, "Description": desc})
if not records:
raise ValueError(
f"No valid rows found in {self.csv_path}. Expected columns '{self.variable_col}' and '{self.description_col}'."
)
return records
@staticmethod
def _tokenize(text):
tokens = re.findall(r"[a-z0-9]+", text.lower())
stopwords = {
"a", "an", "and", "are", "as", "at", "be", "by", "for", "from", "how", "in", "is",
"it", "of", "on", "or", "that", "the", "this", "to", "was", "were", "what", "when",
"where", "which", "who", "with", "you", "your",
}
return [tok for tok in tokens if len(tok) > 2 and tok not in stopwords]
def _build_embeddings(self, texts, is_query=False):
# E5 models are trained with explicit query/passage prefixes.
if "e5" in self.model_name.lower():
prefix = "query: " if is_query else "passage: "
texts = [prefix + t for t in texts]
return self.model.encode(texts, normalize_embeddings=True)
@staticmethod
def _cosine(a, b):
if hasattr(a, "tolist"):
a = a.tolist()
if hasattr(b, "tolist"):
b = b.tolist()
return sum(x * y for x, y in zip(a, b))
def retrieve(self, query, top_k=5):
use_semantic = self.model is not None and self.embeddings is not None
q_emb = self._build_embeddings([query], is_query=True)[0] if use_semantic else None
q_tokens = None if use_semantic else set(self._tokenize(query))
scored = []
for i, rec in enumerate(self.records):
if use_semantic:
score = self._cosine(q_emb, self.embeddings[i])
else:
overlap = q_tokens.intersection(self.doc_tokens[i])
if not overlap:
score = 0.0
else:
q_union_d = q_tokens.union(self.doc_tokens[i])
inter_weight = sum(self.idf.get(tok, 1.0) for tok in overlap)
union_weight = sum(self.idf.get(tok, 1.0) for tok in q_union_d)
score = inter_weight / max(union_weight, 1e-12)
scored.append({
"Variable": rec["Variable"],
"Description": rec["Description"],
"Similarity": float(score),
})
scored.sort(key=lambda x: x["Similarity"], reverse=True)
return scored[:top_k]
def results_to_dataframe(rows):
df = pd.DataFrame(rows)
if not df.empty:
df["Similarity"] = df["Similarity"].round(3)
df = df[["Variable", "Similarity", "Description"]]
return df1 Data Preparation for Descriptive Analysis
1.1 The Necessary Groundwork
Data preparation remains foundational in any empirical workflow. Before moving toward advanced descriptive methods, we still need to inspect variable classes, detect implausible values, document coding conventions, handle missingness, and keep a reproducible audit trail of preprocessing decisions. These steps are not optional in advanced work, they are what makes advanced work interpretable.
Many readers will already be familiar with this groundwork. We therefore keep it concise in this chapter. For missing-data handling, for example, simple baselines such as median or mean imputation can be useful as first passes, while multiple imputation approaches are often more appropriate when uncertainty propagation is important (Rubin 1987; van Buuren and Groothuis-Oudshoorn 2011). Similarly, metadata dictionaries, type checks, and transformation logs remain good practice regardless of the downstream method.
The focus here is different: we concentrate on an advanced preparation step that becomes critical when variable spaces are large, namely semantic variable selection from metadata descriptions.
1.2 The Variable Selection Problem in Large Surveys
In modern applied settings, analysts frequently work with hundreds or thousands of documented variables. For example, the ESS Round 11 integrated file includes more than 600 variables (European Social Survey European Research Infrastructure (ESS ERIC) 2025, 2024). In parallel survey infrastructures, such as Eurostat documentation workflows, combined dictionaries can exceed 1,700 variables across several surveys.
At this scale, manual browsing of variable lists is slow and error-prone. The challenge is not only volume, it is semantic mismatch: research questions are expressed in natural language, while variable names are often terse technical codes (e.g., HY040G, PL031, DA1000). A researcher interested in “rental income” or “health attitudes” may not know where to start from code names alone.
This motivates a preparation technique that maps natural-language queries to variable descriptions automatically, before any statistical modeling begins.
1.3 From Keywords to Meanings: Sentence Embeddings
Sentence embeddings represent text as dense vectors in a high-dimensional semantic space. Texts with similar meaning tend to be close in that space, even when they do not share the exact same keywords. Transformer-based models such as BERT (Devlin et al. 2019) and Sentence-BERT (Reimers and Gurevych 2019) make this practical for retrieval tasks.
In our setting, each variable description is encoded as a vector. A user query is encoded in the same space. We then rank variables using cosine similarity, following the vector-space retrieval logic developed in information retrieval (Salton, Wong, and Yang 1975). The highest-ranked variables form a short list of candidates for subsequent analysis.
For didactic purposes, we use the lightweight model all-MiniLM-L6-v2 from the sentence-transformers ecosystem. This is smaller than multilingual production models, but well-suited for a chapter example.
1.4 A Semantic Retrieval Pipeline in Python
The following implementation illustrates a compact retriever class for metadata CSV files with two columns, Variable and Description.
This class follows the same conceptual structure used in larger retrieval pipelines: load metadata, embed descriptions, embed query, rank by cosine similarity, return top results.
1.4.1 Note for R Users (Conceptual Workflow)
The same logic can be implemented in R without changing the analytical objective. Conceptually, the workflow remains identical: (1) read the metadata dictionary from CSV, (2) compute text embeddings for variable descriptions with an available embedding interface, (3) encode user queries in the same vector space, (4) compute cosine similarity between query and description vectors, and (5) return the top-k ranked variables for review. In practice, this can be done with native R text tooling or by calling Python embedding libraries through an R bridge when model coverage is needed.
1.5 Applying the Pipeline to ESS Round 11
We now apply the retriever to the ESS metadata file (available in the data folder of the GitHub repository). It acts as a variable dictionary with one row per variable and two key fields, Variable and Description, which are exactly the inputs required for semantic retrieval.
Before running retrieval, it is helpful to inspect the first lines of the metadata file to verify its expected structure.
metadata_preview = pd.read_csv("data/ESS11_variable_description.csv", encoding="utf-8-sig").head(5)
display(metadata_preview)| Variable | Description | |
|---|---|---|
| 0 | nwspol | News about politics and current affairs: watching: reading or listening: in minutes |
| 1 | netusoft | Internet use: how often |
| 2 | netustm | Internet use: how much time on typical day: in minutes |
| 3 | ppltrst | Most people can be trusted or you can't be too careful |
| 4 | pplfair | Most people try to take advantage of you: or try to be fair |
retriever = CSVSemanticRetriever("data/ESS11_variable_description.csv")
print(f"Number of variable descriptions loaded: {len(retriever.records)}")
print(f"Sentence-transformers available: {HAS_SENTENCE_TRANSFORMERS}")
print(f"Retrieval backend: {retriever.backend}")
print(f"Python executable: {sys.executable}")
queries = [
"attitudes towards health and well-being",
"household income and financial situation",
]
for q in queries:
display(Markdown(f"**Query:** {q}"))
top = retriever.retrieve(q, top_k=5)
display(results_to_dataframe(top))Warning: You are sending unauthenticated requests to the HF Hub. Please set a HF_TOKEN to enable higher rate limits and faster downloads.BertModel LOAD REPORT from: sentence-transformers/all-MiniLM-L6-v2
Key | Status | |
------------------------+------------+--+-
embeddings.position_ids | UNEXPECTED | |
Notes:
- UNEXPECTED: can be ignored when loading from different task/architecture; not ok if you expect identical arch.
Number of variable descriptions loaded: 468
Sentence-transformers available: True
Retrieval backend: sentence-transformers
Python executable: /Users/antoinesoetewey/Documents/GitHub/Advanced-descriptive-analysis-of-tabular-data/.venv/bin/pythonQuery: attitudes towards health and well-being
| Variable | Similarity | Description | |
|---|---|---|---|
| 0 | health | 0.565 | Subjective general health |
| 1 | iphlppla | 0.538 | Important to help people and care for others well-being |
| 2 | stflife | 0.506 | How satisfied with life as a whole |
| 3 | ipeqopta | 0.386 | Important that people are treated equally and have equal opportunities |
| 4 | hlthhmp | 0.382 | Hampered in daily activities by illness/disability/infirmity/mental problem |
Query: household income and financial situation
| Variable | Similarity | Description | |
|---|---|---|---|
| 0 | hincfel | 0.746 | Feeling about household's income nowadays |
| 1 | hincsrca | 0.741 | Main source of household income |
| 2 | hinctnta | 0.681 | Household's total net income: all sources |
| 3 | fnsdfml | 0.645 | Severe financial difficulties in family when growing up: how often |
| 4 | hhmmb | 0.566 | Number of people living regularly as member of household |
For the first query, we typically expect variables related to perceived health, life satisfaction, and subjective well-being. For the second query, we expect variables concerning household income levels, financial strain, and material conditions. In practice, this first-pass shortlist already reduces the manual search space substantially.
1.6 Interpreting Similarity Scores
Cosine scores are relative rather than absolute quality guarantees. A score of 0.72, for instance, can be highly relevant in one dictionary and less informative in another. It is often more useful to inspect rank order, score gaps, and thematic coherence of the top results than to rely on one universal threshold.
Three practical cautions are worth noting:
- Polysemy can create ambiguous matches when the same word has several meanings.
- Domain shift can reduce quality when metadata vocabulary differs from the model’s training distribution.
- Very short descriptions can limit semantic context and produce unstable ranking.
Even with these limits, embedding-based retrieval is generally more robust than exact keyword search when wording varies across documentation files.
1.7 Placing This Step in the Broader Pipeline
This chapter covers step 1 of a broader descriptive workflow: semantic variable selection from metadata. Its output is a focused set of candidate variables aligned with the research question.
Subsequent steps belong to later chapters: richer exploratory summaries in Chapter 3, type-aware association measurement in Chapter 4, and network-based structure mapping in Chapter 6. Interactive communication layers are then developed further in Chapter 8.
The key point is conceptual: semantic retrieval is a preparation operation on metadata, not a statistical analysis of the observed sample itself.
1.8 Summary and Key Takeaways
- Groundwork still matters: Missing-data handling, plausibility checks, encoding decisions, and reproducible pipelines remain essential before advanced analysis.
- Scale changes the preparation problem: With hundreds or thousands of variables, manual selection from codebooks becomes inefficient and inconsistent.
- Embeddings bridge language and variable codes: Sentence embeddings map free-text research questions and variable descriptions into a common semantic space.
- Cosine ranking yields practical shortlists: Top-k retrieval provides an operational way to identify candidate variables before statistical analysis.
- This is preparation, not modeling: The output is a curated variable subset that feeds downstream descriptive and association methods.
- Interpretation remains necessary: Similarity scores support ranking, but domain review is still needed to validate final variable choices.
1.9 Looking Ahead
With a semantically curated variable shortlist in hand, we can now examine variable types more formally. The next chapter focuses on data types in tabular data, which is a necessary step before selecting appropriate descriptive and association measures.