Skip to content

README.md

Latest commit

 

History

History
112 lines (87 loc) · 4.18 KB

README.md

File metadata and controls

112 lines (87 loc) · 4.18 KB
Raw

active-lives

Active Lives SPSS to CSVS

Walkthrough including code: https://docs.google.com/document/d/1k9r3TWJMUNgx0z5Zg_t7FR1HXuF32SPQ/

The script is a data processing pipeline for:

  • Importing and cleaning SPSS data on sports participation.
  • Extracting metadata and respondent demographics.
  • Integrating external population data.
  • Computing detailed participation metrics and demographic breakdowns.
  • Aggregating data by geography (national, regional, local).
  • Generating multiple output files for reporting and further analysis.

More specifically:

  1. Input and Metadata Handling
  • Reads an SPSS data file named "Nov 20-21 Full-Year Master.sav".
  • Extracts both the dataset and metadata (variable names, labels, coded values).
  • Creates an Excel file with multiple sheets containing:
    • Info: Summary and notes.
    • Contents: Overview.
    • Columns: Variable details.
    • Labels: Value labels (mapping codes to descriptions).
  1. Respondents Data
  • Generates a filename for respondents data by replacing "Master" with "Respondents".
  • Filters relevant demographic/behavioral columns.
  • Saves this filtered subset as a new SPSS file.
  1. Sports Participation Data
  • Extracts columns related to sports participation (prefix "MONTHS_12_").
  • Cleans column names and saves the list of sports participation variables to Sports.csv.
  1. Categorization
  • Extracts variables by category prefixes.
  • Saves cleaned category variable names to Categories.csv.
  1. Population Estimates
  • Loads population data from an Excel file (ukpopestimatesmid2020on2021geography.xls).
  • Breaks down population by youth (5-16 years) and adults (16+ years).
  • Aggregates population by:
    • Regions
    • Local Authorities
    • National level (England)
  1. Local Authorities and Regions Data
  • Extracts local authority and region labels from metadata.
  • Merges these with population estimates.
  • Saves results to Local Authorities.csv and Regions.csv.
  1. England-Level Summary
  • Extracts and saves overall England population summary to England.csv.
  1. Sports and Activity Codes Definition
  • Defines a dictionary mapping sports names to SPSS variable codes.
  • Generates activity-based column names dynamically.
  1. Load and Filter Data
  • Loads the original SPSS file again.
  • Filters required columns: demographics + sports participation variables.
  1. Data Cleaning and Aggregation
  • Initializes zero-value columns.
  • Sums participation metrics over 12 months, frequency, minutes played, etc.
  1. Calculations on Participation
  • Creates metrics like:
    • Participated vs Not Participated
    • Regular participation
    • Activity levels (Inactive, Fairly Active, Active)
  • Assigns weighted values (wt_final) depending on participation and demographics.
  1. Demographic Breakdown
  • Breaks down participation by:
    • Gender
    • Age groups
    • Ethnicities
  1. Label Mapping
  • Replaces numeric codes with descriptive labels for regions, local authorities, age, gender, ethnicity.
  1. Population Data Integration
  • Merges participation data with population estimates (England, regions, authorities).
  1. Output Generation for Sports
  • Saves processed sports participation data to SPSS and CSV files per sport.
  1. Aggregate Data by Geography
  • Aggregates participation metrics for:
    • England (national)
    • Regions
    • Local Authorities
  1. Sorting and Cleaning Aggregated Data
  • Sorts by area name.
  • Cleans up column names and removes unnecessary columns.
  1. Calculate Participation Percentages and Estimates
  • Calculates percentage participation metrics.
  • Calculates population estimates for participation categories by multiplying percentages with population counts.
  • Breaks down participation by demographics for detailed analysis.
  • Calculates latent demand (potential for increased participation).
  1. Final Output
  • Rounds and sorts columns.
  • Saves the final aggregated and processed data to CSV files named by sport or group.