Auto MPG: Cleaning Numeric Text Fields and the Efficiency Shift
A lesson-style dashboard built from the cleaned Auto MPG CSV, focused on numeric parsing traps, preserved nulls, and how lighter, smaller-engine cars pushed fuel efficiency higher from 1970 to 1982.
Cleaned coverage
398 cars
13 model years
The cleaned file spans 1970 through 1982 with typed numeric columns ready for analysis.
Fuel-efficiency shift
+14.0 MPG
1970 → 1982 average
Average MPG rises from 17.7 to 31.7 across the period.
Average engine-size change
-152.5 cu in
Average displacement
The typical engine shrinks from about 281 to 129 cubic inches by the end of the series.
Preserved horsepower nulls
6 rows
1.5% of records
Those rows stay missing after cleaning instead of being silently replaced with zero.
Cleaning lesson
What the raw text columns were trying to hide
- Importing the raw file as strings first matters because numeric-looking fields contain punctuation and placeholders that are easy to miss.
- horsepower includes question marks in the raw file; replacing them with nulls before numeric conversion exposes 6 genuinely missing values.
- weight is numeric in meaning but arrives with a trailing period, so a small text cleanup step is required before type casting.
- model_year and origin behave like coded numerics: they sort well as numbers, but the dashboard converts them into readable calendar years and region labels for display.
- car_name remains text because punctuation in names is descriptive metadata, not a parsing error.
What the cleaned data says
Efficiency rose because the fleet got lighter and smaller
The cleaned dataset shows a clear structural shift: average MPG improves from 17.7 in 1970 to 31.7 in 1982, while average displacement falls from 281 to 129 cubic inches and average weight drops by about 919 pounds.
The vehicle mix changes just as sharply. Large engines above 250 cubic inches make up roughly 62.1% of the 1970 sample but only 3.2% by 1982, while small engines under 140 cubic inches dominate the later years.
Regional differences reinforce the same pattern: lighter Japan models post the highest average fuel economy at 29.0 MPG, followed by Europe at 28.4, while heavier USA vehicles trail at 20.7.
Best yearly average MPG: 1980 (33.7 MPG)
Large-engine share: 62.1% → 3.2%
Small-engine share in 1982: 67.7%
Strongest MPG relationship: weight (r = -0.832)
Cleaning traps and type decisions
| Raw symptom | Field | Cleaning move | Why it matters for analysis |
|---|---|---|---|
| Load raw columns as strings first | All imported fields | Inspect the raw tokens before type inference, then cast intentionally. | This prevents placeholders and punctuation from being silently misread as numbers or dropped too early. |
| Question-mark placeholder in horsepower | horsepower | Replace '?' with null and then use numeric conversion with coercion. | After cleaning, 6 rows (1.51%) remain missing instead of being falsely turned into 0. |
| Trailing punctuation in numeric text | weight | Strip the terminal period from values like 3504. before numeric casting. | The raw field is still numeric in meaning, but punctuation would make strict parsers fail or force an object dtype. |
| Two-digit calendar codes | model_year | Keep the stored field numeric, but map 70–82 to 1970–1982 for time-series labels. | This preserves chronological sorting and makes the charts readable as a real timeline. |
| Compact numeric category codes | origin | Preserve the numeric code for storage, then map 1/2/3 to USA/Europe/Japan in the dashboard. | The values are categorical labels, not continuous measurements, so readable names matter more than the raw code. |
| Free text with punctuation | car_name | Keep as text and use only in labels or hover details. | Names include apostrophes, spaces, and parentheses, so they should never be coerced into numeric fields. |
Average MPG by model year
Weight versus MPG across individual cars
Fleet mix by displacement band over time
Yearly averages: smaller engines, lighter cars, higher MPG
MPG distribution shifts by era
Horsepower versus weight density
Selected checkpoints in the efficiency transition
| Year | Avg MPG | Avg displacement | Avg weight | Why it stands out |
|---|---|---|---|---|
| 1970 | 17.7 | 281 cu in | 3,373 lb | Heavy, large-displacement baseline with low average MPG. |
| 1973 | 17.1 | 257 cu in | 3,419 lb | Lowest yearly average MPG in the series. |
| 1980 | 33.7 | 116 cu in | 2,437 lb | Peak average MPG as the fleet becomes much lighter and smaller. |
| 1982 | 31.7 | 129 cu in | 2,454 lb | Efficiency stays high with large engines nearly gone from the sample. |