01_scania_aps_data_cleaning.ipynb

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.

IN [1] · PYTHON
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()
SAVED OUTPUT [1]
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.

IN [2] · PYTHON
pd.DataFrame({
    "Dataset": ["Training", "Test"],
    "Rows": [train.shape[0], test.shape[0]],
    "Columns": [train.shape[1], test.shape[1]]
})
SAVED OUTPUT [2]
Dataset Rows Columns
0 Training 60000 171
1 Test 16000 171
IN [3] · PYTHON
train.dtypes.value_counts()
SAVED OUTPUT [3]
float64    169
object       1
int64        1
Name: count, dtype: int64
IN [4] · PYTHON
train.describe().T[["count", "mean", "min", "50%", "max"]].head(10).round(2)
SAVED OUTPUT [4]
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.

IN [5] · PYTHON
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)
SAVED OUTPUT [5]
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.

IN [6] · PYTHON
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()
SAVED OUTPUT [6]
['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.

IN [7] · PYTHON
pd.Series({
    "Training duplicate rows": train.duplicated().sum(),
    "Test duplicate rows": test.duplicated().sum()
})
SAVED OUTPUT [7]
Training duplicate rows    0
Test duplicate rows        0
dtype: int64
IN [8] · PYTHON
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()
SAVED OUTPUT [8]
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.

IN [9] · PYTHON
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()
SAVED OUTPUT [9]
['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.

IN [10] · PYTHON
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()
})
SAVED OUTPUT [10]
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.

IN [11] · PYTHON
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
SAVED OUTPUT [11]
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
IN [12] · PYTHON
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()
})
SAVED OUTPUT [12]
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.

IN [13] · PYTHON
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.")
SAVED OUTPUT [13]
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.