Skip to content
Tutorials Center
My learning

Topics

Lesson 61 of 64 ยท Projects

Project: Analysing Used Car Selling Prices

Clean a messy used-car listings file with pandas, explore prices with groupby and correlation, chart the drivers of price and estimate depreciation per year of age.

What makes a used car cheap or expensive? Age and mileage, obviously. But by how much per year? Does a BMW keep its value better than a Ford? Is an electric car really worth a premium? Questions like these are what a dealer, an insurer or a buyer asks, and answering them from a listings file is a classic first real data project.

In this project you take a deliberately messy export of used-car listings, clean it, explore it and measure which factors drive the price. The data is synthetic: we generate it ourselves with a fixed random seed, so it has realistic problems (prices stored as $12,300, mileage as 45,000 km, inconsistent capitals, duplicates, gaps and typos) but the numbers are made up and every run gives identical results. When you have a real file, replace one line with pd.read_csv("listings.csv") and carry on from step 2.

You will practise cleaning text into numbers, handling duplicates, missing values and outliers, groupby summaries, correlation, charts with Matplotlib and a simple estimate of depreciation. If any of these feel rusty, see Data Cleaning in Practice, groupby Basics and Correlation Analysis.

flowchart LR
    A["1. Look<br/>shape, dtypes"] --> B["2. Clean<br/>text to numbers,<br/>duplicates, outliers"]
    B --> C["3. Explore<br/>groupby, correlation"]
    C --> D["4. Chart<br/>drivers of price"]
    D --> E["5. Estimate<br/>depreciation"]
    E --> F["6. Report"]

The usual order: never explore before you have cleaned.

Step 1: Look at the raw file

The first block builds the raw CSV text (the setup part at the top; you can skim it) and then reads it like any CSV file. Always start by looking: the shape, the first rows, the data types and the categories.

Python Step 1: the raw listings

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The audit already shows the problems:

  • price and mileage are text (str), because of the $, the commas and the km.
  • brand contains Toyota, toyota and Toyota (with spaces) as if they were three brands; fuel has Petrol and PETROL.
  • mileage has missing values (fewer non-null entries than rows).
  • There are duplicate rows: the same listing exported twice.

Step 2: Clean it

Work through the problems one by one, and check the effect after each fix. This is the part of the work that matters most: every conclusion afterwards depends on it.

Python Step 2: cleaning, one fix at a time

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The summary shows a maximum price of 1,200,000, while three quarters of the cars cost under about 15,000. Six listings cost 1.2 million: a 2022 Skoda or a 2014 BMW with 133,000 km is not worth that, so they are typing errors (an extra zero or two). Do not delete data you do not understand, but here the evidence is clear. We drop prices above 100,000 and write down the decision.

Two more decisions: we compute the age from the year (a more useful number than the year itself) and fill the missing mileage with the median mileage of cars of the same age, because mileage grows with age. Filling with the overall median would make a new car look as if it had driven 100,000 km.

The whole cleaning job fits into one function, so that you can rerun it on next month's file. This is the setup used by the remaining steps:

Python The cleaning function, with checks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

assert lines are cheap insurance: if next month's file breaks an assumption, the script stops instead of quietly producing a wrong report. See Testing DataFrames for more.

Step 3: Explore the prices

Start with simple group summaries, then correlations. groupby with agg gives count, median and mean per group; the median is the better "typical price" because expensive cars pull the mean up.

Python Step 3: groupby summaries and correlation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

What do the numbers say?

  • Brand: BMW is far above the others, which sit close together.
  • Fuel: electric and diesel have the highest medians. Careful: this is not yet an explanation. The mix of ages may differ by fuel type, and that is why we need step 5.
  • Correlation: price correlates strongly negatively with age (about -0.8) and with mileage (about -0.7), and age and mileage correlate with each other (0.85). Two predictors that move together cannot be separated by looking at correlations alone.
  • Depreciation: the median price of a one-year-old car is about 19,700, and by age 8 it is about 10,150. Averaged over those years, that is a loss of roughly 9 percent per year (the median is noisy; the regression in step 5 gives a cleaner estimate). A percentage loss per year (not a fixed amount) is typical for cars.

Step 4: Chart the drivers

Four charts tell the story better than any table. We use Matplotlib directly (see Charts for Analysis and the Matplotlib track).

Python Step 4: four charts of price drivers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the bottom-right chart. On a normal axis the price curve bends (fast loss at first, slower later); on a logarithmic axis it is close to a straight line. That is the signature of a constant percentage loss per year, and it tells us how to model the data: take the logarithm of the price.

Step 5: Estimate the effect of each factor

Group tables mix the effects up (electric cars are newer, so are they expensive because they are electric or because they are young?). A regression separates them: it estimates the effect of each factor while holding the others constant. We fit it with plain NumPy least squares, no extra library.

The model is log(price) = constant + brand + fuel + age + mileage. With a log price, a coefficient b means that the price changes by a factor exp(b), which is a percentage. Brand and fuel are text, so get_dummies turns them into 0/1 columns; we drop Toyota and Petrol so that all the effects are measured relative to a Toyota petrol car.

Python Step 5: price drivers with least squares

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the table as follows (exact numbers come from the output above):

  • age: each extra year lowers the price by about 7.5 percent, with brand, fuel and mileage fixed.
  • fuel: a comparable electric car is worth about 45 percent more than a petrol car, a hybrid about 22 percent more and a diesel about 6 percent more.
  • brands: compared to a Toyota, a Honda is about 6 percent cheaper, a Skoda 14 percent and a Ford 18 percent cheaper, while a BMW is about 76 percent more expensive.
  • mileage: once age is in the model, an extra 10,000 km costs only about 2.3 percent. Age and mileage overlap, so most of the "mileage effect" you saw earlier was really an age effect.
  • R squared near 0.95 means the model explains most of the variation in log price.

Be careful with conclusions. This data is synthetic, so the effects reflect the rules we used to generate it, not the real market. With real data, remember that a regression shows association, not cause; that important factors (condition, trim level, accident history, region) are missing here; and that the model is a simple straight-line fit in the log price.

Step 6: Report

A good report is short: the question, the cleaning decisions, the main findings with numbers, the limits, and one chart. For this project:

  1. Data: 412 raw rows became 394 listings after removing 12 duplicates and 6 price typos; 20 missing mileages were filled by the median of the same age.
  2. Main driver: age. A car loses about 7.5 percent of its price for each extra year, so a 10-year-old car is worth roughly 60 percent of the same car at 3 years.
  3. Brand and fuel: BMW carries a large premium; electric and hybrid cars sell above petrol cars of the same age.
  4. Mileage: matters, but much less than age once age is accounted for.
  5. Limits: synthetic data, no condition or trim information, a simple model.

Extension ideas

  • Replace the fuel and brand columns with an interaction: does a BMW lose value faster than a Ford? Fit the model separately per brand with groupby.
  • Add a price_per_km column and look for listings that are suspiciously cheap for their mileage (possible bargains or errors).
  • Split the data into a training and a test part (df.sample(frac=0.8, random_state=0)) and measure the prediction error on the cars the model has not seen.
  • Export the cleaned table with to_parquet or to_csv and the chart with fig.savefig (see Writing and Formatting Data).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is the median usually a better 'typical price' than the mean here?

    The median ignores how extreme the largest values are, so a handful of luxury cars or typing errors do not distort it.
  2. 2. What does str.replace(r'[$,]', '', regex=True) do to '$12,300'?

    The pattern [$,] matches a dollar sign or a comma. Replacing them with nothing leaves digits, which astype(float) or to_numeric can then convert.
  3. 3. Why fill missing mileage with the median of cars of the same age instead of the overall median?

    Filling from similar rows keeps the relationship between age and mileage intact. The overall median would make new cars look heavily used.
  4. 4. A scatter plot of price against age bends on a normal axis but is nearly straight on a log axis. What does that suggest?

    A straight line on a log scale means exponential change: the same percentage lost every year. That is why we model the logarithm of the price.
  5. 5. In the regression, why does the effect of mileage look small even though mileage correlates strongly with price?

    When two predictors move together, a regression splits their shared effect between them. Here age takes most of it, leaving a small extra effect for mileage.

Summary

  • Always audit first: shape, head, info, value_counts, duplicated. Text with units ($12,300, 45,000 km) must be converted with str.replace and to_numeric before any maths.
  • Clean one problem at a time and check each fix; put the final steps in a function and add assert sanity checks.
  • Treat impossible values as errors only with evidence; fill missing values from similar rows (here, the same age).
  • groupby(...).agg(["count", "median", "mean"]) and corr() give the first picture; charts, including a log axis, show the shape.
  • A regression on the log price turns coefficients into percentage effects and separates overlapping factors such as age and mileage.
  • Report the data decisions, the numbers, the chart and the limits; do not claim causes.

Next: Exercises to practise on your own data.