Lesson 22

Exploring a Dataset

Put the module together: load a small dataset, check its shape and gaps, fix the types, then answer real questions with filters and groups.

Intermediate25 min

Exploring a Dataset hero Module 24 Lesson 22

Exploring a dataset is a routine, not a flash of insight. Look at the shape, look at the types, count the gaps, fix what is broken, and only then ask questions. This lesson runs that routine end to end on one small table.

The five steps of a first pass over a new dataset

Step 1: load it and look

Never start with a question. Start with shape, head, and info, because they tell you how much data you have, what the columns are called, and which ones pandas failed to read as numbers.

python

import io

import pandas as pd

raw = '''name,region,plan,months,spend
Avery,North,pro,12,480
Jordan,South,basic,6,90
Casey,North,pro,,660
Riley,East,basic,3,45
Morgan,South,pro,9,405
Quinn,East,free,2,0
Avery,North,pro,12,480
Devi,West,basic,7,unknown
'''
members = pd.read_csv(io.StringIO(raw))
print(members.shape)
print(members.head(3))
print(members.dtypes)

Output:

text

(8, 5)
     name region   plan  months spend
0   Avery  North    pro    12.0   480
1  Jordan  South  basic     6.0    90
2   Casey  North    pro     NaN   660
name       object
region     object
plan       object
months    float64
spend      object
dtype: object

Step 2: find the problems

Three things go wrong in almost every file: a repeated row, a gap, and a number stored as text. Count each one before you change anything, so you can tell afterwards whether the cleaning worked.

python

import io

import pandas as pd

raw = '''name,region,plan,months,spend
Avery,North,pro,12,480
Jordan,South,basic,6,90
Casey,North,pro,,660
Riley,East,basic,3,45
Morgan,South,pro,9,405
Quinn,East,free,2,0
Avery,North,pro,12,480
Devi,West,basic,7,unknown
'''
members = pd.read_csv(io.StringIO(raw))
print(members.duplicated().sum())
print(members.isna().sum())
print(members["spend"].dtype)

Output:

text

1
name      0
region    0
plan      0
months    1
spend     0
dtype: int64
object

spend came in as text because one row holds the word unknown, so nothing numeric will work on it yet. months fared better: an empty field is one of the spellings pandas already reads as missing.

Step 3: clean it

Drop the repeat, coerce the text column to numbers, and decide what each gap deserves. A missing length of membership is better filled with the middle value than with zero, which would claim the member just joined. A missing spend gets no filler at all: 0 would be a claim, and every summary skips NaN anyway.

python

import io

import pandas as pd

raw = '''name,region,plan,months,spend
Avery,North,pro,12,480
Jordan,South,basic,6,90
Casey,North,pro,,660
Riley,East,basic,3,45
Morgan,South,pro,9,405
Quinn,East,free,2,0
Avery,North,pro,12,480
Devi,West,basic,7,unknown
'''
members = pd.read_csv(io.StringIO(raw)).drop_duplicates()
members["spend"] = pd.to_numeric(members["spend"], errors="coerce")
members["months"] = members["months"].fillna(members["months"].median())
members["plan"] = members["plan"].astype("category")
print(members.isna().sum())
print(members.dtypes)
print(members)

Output:

text

name      0
region    0
plan      0
months    0
spend     1
dtype: int64
name        object
region      object
plan      category
months     float64
spend      float64
dtype: object
     name region   plan  months  spend
0   Avery  North    pro    12.0  480.0
1  Jordan  South  basic     6.0   90.0
2   Casey  North    pro     6.5  660.0
3   Riley   East  basic     3.0   45.0
4  Morgan  South    pro     9.0  405.0
5   Quinn   East   free     2.0    0.0
7    Devi   West  basic     7.0    NaN

One gap is left on purpose, and it is in a column whose average we are about to report. Say so in the result: this is the average of six recorded spends, not of seven members.

Step 4: what does normal look like

describe gives the middle and the spread of the numbers, and value_counts shows how the labels are distributed. Together they tell you whether an answer later on is surprising or ordinary.

python

import io

import pandas as pd

raw = '''name,region,plan,months,spend
Avery,North,pro,12,480
Jordan,South,basic,6,90
Casey,North,pro,7,660
Riley,East,basic,3,45
Morgan,South,pro,9,405
Quinn,East,free,2,0
Devi,West,basic,7,
'''
members = pd.read_csv(io.StringIO(raw))
print(members[["months", "spend"]].describe().round(2))
print(members["plan"].value_counts())
print(members["region"].nunique())

Output:

text

       months   spend
count    7.00    6.00
mean     6.57  280.00
std      3.41  271.94
min      2.00    0.00
25%      4.50   56.25
50%      7.00  247.50
75%      8.00  461.25
max     12.00  660.00
plan
pro      3
basic    3
free     1
Name: count, dtype: int64
4

Step 5: answer the questions

Now the work is short, because every tool is one you have already met. A derived column, a filter, a group, and a sort answer four different questions in a handful of lines.

python

import io

import pandas as pd

raw = '''name,region,plan,months,spend
Avery,North,pro,12,480
Jordan,South,basic,6,90
Casey,North,pro,7,660
Riley,East,basic,3,45
Morgan,South,pro,9,405
Quinn,East,free,2,0
Devi,West,basic,7,
'''
members = pd.read_csv(io.StringIO(raw))
members["per_month"] = (members["spend"] / members["months"]).round(2)

print(members.groupby("plan", observed=True)["spend"].agg(["size", "count", "mean"]))
print(members.loc[members["per_month"] > 50, ["name", "plan", "per_month"]])
print(members.nlargest(3, "spend")[["name", "region", "spend"]])
print(members.groupby("region")["spend"].sum(min_count=1).sort_values(ascending=False))

Output:

text

       size  count   mean
plan                     
basic     3      2   67.5
free      1      1    0.0
pro       3      3  515.0
    name plan  per_month
2  Casey  pro      94.29
     name region  spend
2   Casey  North  660.0
0   Avery  North  480.0
4  Morgan  South  405.0
region
North    1140.0
South     495.0
East       45.0
West        NaN
Name: spend, dtype: float64

Two details keep this honest. size and count differ on the basic plan, which is the row whose spend nobody recorded. And min_count=1 makes a total of nothing come out as NaN rather than as a confident zero. Finish by writing the cleaned table out with to_csv(path, index=False), so the next piece of work starts from good data instead of repeating all of this.

Key takeaways

Look at shape, head, and info before you ask any question
Count the duplicates, the gaps, and the wrong dtypes first
Choose each fill value deliberately; zero is rarely the honest answer
describe and value_counts tell you what normal looks like
Next: What Is Matplotlib
Previous: Reading and Writing Files

Loading playground…

Check your understanding

Exploring a Dataset Quiz

What is the first thing to check on a new dataset?

1 / 10

← Reading and Writing Files