Cleaning, restructuring, and exploratory analysis of 1,000 customer records — pure-Python preprocessing (CSV → nested JSON) plus a pandas/seaborn EDA with 12 figures and summary tables.
Tech stack & skills: Python (standard library: csv, json, math) · pandas · Matplotlib · seaborn — covering data loading & parsing, data cleaning, missing-value handling, CSV→nested-JSON restructuring, feature encoding, and exploratory data analysis with summary statistics and visualizations.
Preprocess and analyze customer records (including credit-card details) using pure Python for the cleaning stage and pandas / seaborn for the analysis stage. The pipeline loads raw CSV data, restructures it into nested JSON, corrects errors, handles missing values, and produces a full set of summary tables and visualizations to prepare the data for further modeling.
Customer-Data-Preprocessing-and-Analysis/
├── README.md
├── LICENSE
├── requirements.txt
├── customer_data_preprocessing.ipynb # main notebook
├── Data/
│ └── acw_user_data.csv # input dataset
├── Figures/ # generated plots (embedded below)
├── Tables/ # generated summary tables (CSV + Markdown)
└── Results/ # generated JSON (created on run)
Stage 1 — Preprocessing (standard library only)
- Reads the raw CSV with the
csvmodule. - Restructures flat rows into a nested record format (vehicle, credit card, and address grouped into sub-objects).
- Flags problematic rows (e.g. missing dependants).
- Writes
processed.json, and splits records intoretired.jsonandemployed.json. - Flags credit cards whose validity span exceeds 10 years into
removed_card.json. - Computes a salary-per-commute-mile metric and writes a sorted
commute.json.
Stage 2 — Analysis (pandas / seaborn)
- Summary statistics, univariate and multivariate plots.
- An extended EDA section generating the tables and figures shown below.
All generated files are written to Results/, Figures/, and Tables/.
- Python 3.9+
- Packages listed in
requirements.txt(pandas,seaborn,matplotlib,notebook)
# 1. (optional) create a virtual environment
python -m venv .venv
source .venv/bin/activate # on Windows: .venv\Scripts\activate
# 2. install dependencies
pip install -r requirements.txt
# 3. launch Jupyter
jupyter notebookThen open customer_data_preprocessing.ipynb and run the cells top to bottom.
- Upload
customer_data_preprocessing.ipynbto Google Colab. - Upload
acw_user_data.csvwhen prompted (or mount Google Drive) into aData/folder. - Run each cell in order.
| count | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|
| Age (Years) | 1000 | 54.3 | 21.32 | 18 | 36 | 54 | 72 | 91 |
| Yearly Salary (GBP) | 1000 | 57814.1 | 23270.4 | 17046 | 39089.8 | 57518 | 77211.5 | 98915 |
| Yearly Pension (GBP) | 1000 | 5875.41 | 10986.5 | 0 | 0 | 0 | 0 | 46117 |
| Distance Commuted to Work (miles) | 1000 | 10.46 | 6.95 | 0 | 5.64 | 12.17 | 16.26 | 20.34 |
| Dependants | 1000 | 2.13 | 0.97 | 1 | 1 | 2 | 3 | 5 |
| Vehicle Year | 1000 | 2006.93 | 8.21 | 1992 | 2000 | 2008 | 2014 | 2020 |
| missing | pct | |
|---|---|---|
| Employer Company | 246 | 24.6 |
| Dependants | 19 | 1.9 |
| Retired | count | mean_age | mean_salary | mean_pension |
|---|---|---|---|---|
| False | 754 | 45.3 | 57431.9 | 0 |
| True | 246 | 81.8 | 58985.6 | 23883.8 |
| Marital Status | count | mean_salary | median_salary | mean_age |
|---|---|---|---|---|
| single | 330 | 59446.2 | 59975.5 | 55.1 |
| divorced | 71 | 57865.6 | 58250 | 56 |
| married or civil partner | 541 | 57147.2 | 56477 | 54.1 |
| widowed | 58 | 54684.7 | 53060 | 50.1 |
| Sex | count | mean_salary | median_salary | mean_commute |
|---|---|---|---|---|
| Female | 499 | 57721.3 | 57387 | 10.4 |
| Male | 501 | 57906.5 | 57690 | 10.5 |
| Dependants | count | mean_salary | mean_age |
|---|---|---|---|
| 1 | 255 | 58096.8 | 54.7 |
| 2 | 479 | 57889 | 53.3 |
| 3 | 178 | 57927.8 | 54.4 |
| 4 | 54 | 55155.5 | 59.2 |
| 5 | 34 | 58264.8 | 57.8 |
| Employer Company | count |
|---|---|
| Evans Inc | 3 |
| Wright LLC | 3 |
| Smith PLC | 3 |
| Smith Ltd | 3 |
| Hawkins Group | 2 |
| Brown Ltd | 2 |
| Johnston PLC | 2 |
| Foster PLC | 2 |
| Williams PLC | 2 |
| Fletcher LLC | 2 |
| Age (Years) | Yearly Salary (GBP) | Yearly Pension (GBP) | Distance Commuted to Work (miles) | Dependants | Vehicle Year | |
|---|---|---|---|---|---|---|
| Age (Years) | 1 | 0.05 | 0.69 | -0.62 | 0.04 | 0.03 |
| Yearly Salary (GBP) | 0.05 | 1 | 0.03 | 0.42 | -0.01 | 0.08 |
| Yearly Pension (GBP) | 0.69 | 0.03 | 1 | -0.81 | 0 | 0.02 |
| Distance Commuted to Work (miles) | -0.62 | 0.42 | -0.81 | 1 | -0.01 | 0.01 |
| Dependants | 0.04 | -0.01 | 0 | -0.01 | 1 | -0.05 |
| Vehicle Year | 0.03 | 0.08 | 0.02 | 0.01 | -0.05 | 1 |
- Pension income rises strongly with age (r ≈ 0.69), while salary is essentially flat across age.
- Commute distance is strongly negatively correlated with pension (r ≈ -0.81) — retired customers no longer commute.
- Retired customers (~25% of the sample) skew much older (mean age 82 vs 45) yet earn comparable salaries.
- Salary shows no meaningful gap by sex, and only minor variation by marital status or dependants.
This project began as a university assignment and was later reworked into a standalone portfolio project. The dataset (acw_user_data.csv) is derived from publicly available open-government data.
Released under the MIT License.