Scania Trucks: Data Cleaning with Pandas
This project cleans vehicle data from Scania's Air Pressure System (APS) dataset. The aim is to practise a standard data analyst workflow: inspect the files, check missing values and duplicates, tidy the data, and save cleaned CSVs.
The source has two files: training (60,000 records) and test (16,000 records). Those names come from the original prediction challenge: training data is normally used to build a model, and test data checks how it performs on separate examples. Here we simply clean both files.
pos means an APS component failure; neg means a failure in another component. The measurement names are anonymised, so we keep the original column names.
Source: UCI Machine Learning Repository. The original CSVs are included in data/raw; the cleaned copies are in data/processed.
1. Load the data
We use pandas throughout. The original CSVs have 20 lines of notes above the header, so skiprows=20 skips those notes. na_values="na" tells pandas how missing values are recorded in these files. Run the notebook with its working directory set to notebooks.
import pandas as pd
pd.set_option("display.max_columns", 8)
train = pd.read_csv("../data/raw/aps_failure_training_set.csv", skiprows=20, na_values="na")
test = pd.read_csv("../data/raw/aps_failure_test_set.csv", skiprows=20, na_values="na")
train_shape_before = train.shape
test_shape_before = test.shape
train_missing_before = train.isna().sum().sum()
test_missing_before = test.isna().sum().sum()
train.head()| class | aa_000 | ab_000 | ac_000 | ... | ee_008 | ee_009 | ef_000 | eg_000 | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | neg | 76698 | NaN | 2.130706e+09 | ... | 73224.0 | 0.0 | 0.0 | 0.0 |
| 1 | neg | 33058 | NaN | 0.000000e+00 | ... | 97576.0 | 1500.0 | 0.0 | 0.0 |
| 2 | neg | 41040 | NaN | 2.280000e+02 | ... | 95128.0 | 514.0 | 0.0 | 0.0 |
| 3 | neg | 12 | 0.0 | 7.000000e+01 | ... | 0.0 | 0.0 | 4.0 | 32.0 |
| 4 | neg | 60874 | NaN | 1.368000e+03 | ... | 433954.0 | 1218.0 | 0.0 | 0.0 |
5 rows × 171 columns
2. Inspect the data
Start with the number of rows and columns, then check the data types and a few summary statistics. The files have one class label and 170 numerical measurements.
pd.DataFrame({
"Dataset": ["Training", "Test"],
"Rows": [train.shape[0], test.shape[0]],
"Columns": [train.shape[1], test.shape[1]]
})| Dataset | Rows | Columns | |
|---|---|---|---|
| 0 | Training | 60000 | 171 |
| 1 | Test | 16000 | 171 |
train.dtypes.value_counts()float64 169 object 1 int64 1 Name: count, dtype: int64
train.describe().T[["count", "mean", "min", "50%", "max"]].head(10).round(2)| count | mean | min | 50% | max | |
|---|---|---|---|---|---|
| aa_000 | 60000.0 | 5.933650e+04 | 0.0 | 30776.0 | 2.746564e+06 |
| ab_000 | 13671.0 | 7.100000e-01 | 0.0 | 0.0 | 2.040000e+02 |
| ac_000 | 56665.0 | 3.560143e+08 | 0.0 | 152.0 | 2.130707e+09 |
| ad_000 | 45139.0 | 1.906206e+05 | 0.0 | 126.0 | 8.584298e+09 |
| ae_000 | 57500.0 | 6.820000e+00 | 0.0 | 0.0 | 2.105000e+04 |
| af_000 | 57500.0 | 1.101000e+01 | 0.0 | 0.0 | 2.007000e+04 |
| ag_000 | 59329.0 | 2.216400e+02 | 0.0 | 0.0 | 3.376892e+06 |
| ag_001 | 59329.0 | 9.757200e+02 | 0.0 | 0.0 | 4.109372e+06 |
| ag_002 | 59329.0 | 8.606010e+03 | 0.0 | 0.0 | 1.055286e+07 |
| ag_003 | 59329.0 | 8.859128e+04 | 0.0 | 0.0 | 6.340207e+07 |
The measurement columns have loaded as numbers, so they do not need manual type conversion. Large counter values are not automatically errors; without units or valid operating limits, we leave them unchanged.
3. Check missing values
Sort the columns by missing percentage to see where the biggest gaps are.
missing_values = pd.DataFrame({
"Missing count": train.isna().sum(),
"Missing percent": train.isna().mean() * 100
})
missing_values.sort_values("Missing percent", ascending=False).head(10).round(2)| Missing count | Missing percent | |
|---|---|---|
| br_000 | 49264 | 82.11 |
| bq_000 | 48722 | 81.20 |
| bp_000 | 47740 | 79.57 |
| bo_000 | 46333 | 77.22 |
| ab_000 | 46329 | 77.22 |
| cr_000 | 46329 | 77.22 |
| bn_000 | 44009 | 73.35 |
| bm_000 | 39549 | 65.92 |
| bl_000 | 27277 | 45.46 |
| bk_000 | 23034 | 38.39 |
For this project, we remove columns with more than 50% missing values in the training data. This is a simple project rule, rather than proof that those columns are useless. We remove the same columns from the test file so both files still match.
missing_percent = train.drop(columns="class").isna().mean()
columns_to_drop = missing_percent[missing_percent > 0.50].index
train = train.drop(columns=columns_to_drop)
test = test.drop(columns=columns_to_drop)
columns_to_drop.tolist()['ab_000', 'bm_000', 'bn_000', 'bo_000', 'bp_000', 'bq_000', 'br_000', 'cr_000']
4. Check duplicates and labels
There are no exact duplicate rows in either original file. We check this explicitly and include the usual duplicate-removal step; it does not remove any rows here.
pd.Series({
"Training duplicate rows": train.duplicated().sum(),
"Test duplicate rows": test.duplicated().sum()
})Training duplicate rows 0 Test duplicate rows 0 dtype: int64
train = train.drop_duplicates().copy()
test = test.drop_duplicates().copy()
train["class"] = train["class"].str.strip().str.lower()
test["class"] = test["class"].str.strip().str.lower()
train["class"].value_counts()class neg 59000 pos 1000 Name: count, dtype: int64
5. Remove a constant column
cd_000 contains the same value wherever it is recorded. We remove it because it does not help compare observed measurement values between records in this project.
unique_values = train.drop(columns="class").nunique()
constant_columns = unique_values[unique_values <= 1].index
train = train.drop(columns=constant_columns)
test = test.drop(columns=constant_columns)
constant_columns.tolist()['cd_000']
6. Fill the remaining missing values
Dropping every incomplete row would leave only 591 of the original 60,000 training rows. Instead, we fill the remaining gaps with each column's median. The median is less affected by very large values than the mean.
We calculate medians from training only and use those same values for the test file. These are replacement values, not actual sensor readings; this is a simple cleaning choice for the project.
medians = train.select_dtypes(include="number").median()
train = train.fillna(medians)
test = test.fillna(medians)
pd.Series({
"Training missing values": train.isna().sum().sum(),
"Test missing values": test.isna().sum().sum()
})Training missing values 0 Test missing values 0 dtype: int64
7. Check the cleaned data
Compare the starting and finishing sizes, then check missing values, duplicate rows and labels again. Both files should have the same columns in the same order.
summary = pd.DataFrame({
"Dataset": ["Training", "Test"],
"Rows before": [train_shape_before[0], test_shape_before[0]],
"Rows after": [len(train), len(test)],
"Columns before": [train_shape_before[1], test_shape_before[1]],
"Columns after": [train.shape[1], test.shape[1]],
"Missing before": [train_missing_before, test_missing_before],
"Missing after": [train.isna().sum().sum(), test.isna().sum().sum()]
})
summary| Dataset | Rows before | Rows after | Columns before | Columns after | Missing before | Missing after | |
|---|---|---|---|---|---|---|---|
| 0 | Training | 60000 | 60000 | 171 | 162 | 850015 | 0 |
| 1 | Test | 16000 | 16000 | 171 | 162 | 228680 | 0 |
pd.Series({
"Matching columns": train.columns.equals(test.columns),
"Training duplicate rows": train.duplicated().sum(),
"Test duplicate rows": test.duplicated().sum(),
"Training labels valid": train["class"].isin(["neg", "pos"]).all(),
"Test labels valid": test["class"].isin(["neg", "pos"]).all()
})Matching columns True Training duplicate rows 1 Test duplicate rows 0 Training labels valid True Test labels valid True dtype: object
Removing columns makes one pair of training records identical. They were different in the original file, so we keep both records rather than treating them as a source duplicate.
8. Save the cleaned files
Save the results without a pandas row index. The original CSVs remain unchanged.
train.to_csv("../data/processed/train_clean.csv", index=False)
test.to_csv("../data/processed/test_clean.csv", index=False)
print("Cleaned training and test files saved in data/processed.")Cleaned training and test files saved in data/processed.
Summary
- Loaded and inspected 60,000 training records and 16,000 test records.
- Removed eight columns with more than 50% missing values and one constant column.
- Checked duplicates, data types and class labels.
- Filled the remaining missing measurements with training-column medians.
- Saved two cleaned files, each containing 161 measurement columns plus the class label.
Both files retain their original row counts and have no missing cells. This prepares the data for further analysis; it does not tell us which measurements cause failures.