Data Cleaning Lab towards a Silver Data Lake


UN Gender, Rights & Literacy

Context. This lab uses three UNdata exports related to abortion policies, reproductive health for women aged 15–49, and adult literacy by sex. Students will practice building a metadata catalogue, cleaning, reshaping and documenting datasets to make them ready for analysis and visualisation in Python (Colab).

Learning Objectives

  • Produce a dataset-level and column-level metadata catalogue (data dictionary) for the UN datasets.
  • Identify and remove non-data rows (e.g., footnotes, metadata) from tabular exports.
  • Fix data types for key fields (e.g., Year, numeric indicators).
  • Reshape tables by pivoting the Subgroup column into meaningful variables.
  • Create derived indicators (e.g., number of legal abortion grounds, literacy gender gap).
  • Export and document clean datasets for reuse in BI tools such as Tableau.

Material

Exercise 0 – Metadata Catalogue in Python / Colab

In this preparatory exercise, you will generate a metadata catalogue that describes the content of each UN dataset and each column. This is your starting point for data documentation and for understanding the structure and completeness of the data before cleaning.

Tasks

  1. Open the Colab notebook and run the section “1bis. Building a metadata catalogue for the three datasets”. Make sure the three CSV files (abortion policies, reproductive indicator F 15–49, adult literacy by sex) are correctly loaded at the beginning of the notebook.
  2. Inspect the dataset-level metadata table (dataset_catalog). For each dataset, note the number of rows and columns, the number of countries, the year range, and the sample of subgroups, units and sources. Write 3–5 lines describing what each dataset contains and which dimensions it covers.
  3. Inspect the column-level metadata table (column_catalog). Focus on the inferred roles (time, geography, measure, coded_measure, unit, provenance, dimension, annotation) and the percentage of missing values. Identify at least two columns per dataset that may require special attention during cleaning (e.g., many missing values, ambiguous role).
  4. Export the metadata tables as CSV files (UNdata_metadata_datasets.csv and UNdata_metadata_columns.csv). Open them in a spreadsheet or text editor and think about how they could be used as a data dictionary in a project or shared with collaborators.

Exercise 1 – Python / Colab Cleaning Notebook

You will work in the same Jupyter notebook (Google Colab compatible) to clean and reshape the three UNdata exports, building on the understanding provided by the metadata catalogue.

Datasets

  • Abortion policies by legal ground.
  • Reproductive health indicator, Female 15–49.
  • Adult literacy rate by sex (Female 15+ yr, Male 15+ yr).

Tasks

  • Upload the three CSV files to the Colab notebook and inspect their structure using head() and info(). List at least three data quality issues you observe (e.g., strange rows, non-integer years, missing units, mixed content in Subgroup).
  • Implement or reuse the cleaning function that removes rows where Unit is missing and where Year is missing, then converts Year to an integer. Apply it to all three datasets and report how many rows were removed in each case.
  • For the abortion policies dataset, pivot Subgroup so that each legal ground becomes a separate column (e.g., abortion_econ_social, abortion_foetal_impairment, etc.). Create a new column n_grounds_allowed that sums the legal ground columns for each Country–Year pair. Identify a few countries with very restrictive and very permissive abortion policies and briefly comment.
  • For the reproductive health indicator dataset (Female 15–49), create a minimal table with columns Country or Area, Year and repro_indicator_f1549 (renamed from Value). Check for duplicate (Country, Year) combinations and discuss how you would handle them if they exist.
  • For the adult literacy dataset, pivot Subgroup so that you obtain two separate columns literacy_f15plus and literacy_m15plus. Then compute literacy_gender_gap = literacy_m15plus − literacy_f15plus. Identify three countries with the largest positive literacy_gender_gap and explain what this gap means in substantive terms.
  • (Optional) Merge the reproductive health table with the literacy table on Country or Area and Year. Inspect how many observations remain. Comment on potential issues arising from different survey years and missing values when combining indicators.
  • Export the cleaned tables as CSV files: abortion_policies_clean.csv, repro_indicator_clean.csv, adult_literacy_clean.csv, and (optionally) repro_literacy_merged.csv. In a short paragraph, document the main cleaning and transformation steps you applied to each file.

To Do

  • Inspired by this exercise, proceed to process the data collection for your project. 
  • Deliver clean data collections with their associated metadata
  • Create a GitHub repository and store them there
  • Create a Kaggle data collection with your datasets and manual metadata.