Pandas · Analyzing DataFrames and Cleaning Data

Before diving in, let's make sure these foundation ideas are clear.

What is a DataFrame?

A DataFrame is like a table in a spreadsheet. It has rows (going across) and columns (going down). Each column has a name (called a header), and each cell holds one piece of data.

   Name    Age   Score
0  Alice    22    88.5
1  Bob      19    NaN
2  Carol    21    74.0

• The numbers on the left (0, 1, 2) are the row index · the row's address.
• Name, Age, Score are the column names.
• NaN means "Not a Number" · it represents a missing value.

What is Pandas?

Pandas is a Python library (a collection of ready-made tools) that makes working with tables of data very easy. You import it at the start of every script:

import pandas as pd

The as pd part means "give pandas the nickname pd" so you can type pd.something instead of pandas.something.

What is a CSV file?

A CSV (Comma Separated Values) file is a plain text file that stores table data. Each row is a line of text, and the values are separated by commas:

Duration,Date,Pulse,Maxpulse,Calories
60,2020/12/01,110,130,409.1
45,2020/12/04,109,175,282.4

Pandas can read CSV files directly into a DataFrame with one line of code.

Why analyze before you compute?

Before doing any calculations · averages, totals, comparisons · you must understand your data. Otherwise you might calculate an average that includes missing values, or work with a column that has the wrong data type.

Think of it like checking your ingredients before you start cooking. You want to know: what do I have, how much, and is anything missing?

1.1 Loading a DataFrame from a CSV

import pandas as pd

df = pd.read_csv('data.csv')

• pd.read_csv('data.csv') · reads the file called data.csv from your current folder and turns it into a DataFrame.
• df · we store the DataFrame in a variable called df (short for "DataFrame").

Real-world use: In data science jobs, you almost always start by loading data from a CSV, Excel file, or database. This single line is the foundation of every data project.

1.2 head() · See the First Rows

What is it? A method that shows the top rows of your DataFrame.

Why use it? When a dataset has 10,000 rows, you can't read the whole thing. head() gives you a quick preview of the structure and content.

import pandas as pd

df = pd.read_csv('data.csv')

print(df.head(10))   # Show the first 10 rows

Expected Output (first 10 rows of the dataset):

   Duration          Date  Pulse  Maxpulse  Calories
0        60  '2020/12/01'    110       130     409.1
1        60  '2020/12/02'    117       145     479.0
2        60  '2020/12/03'    103       135     340.0
3        45  '2020/12/04'    109       175     282.4
4        45  '2020/12/05'    117       148     406.0
5        60  '2020/12/06'    102       127     300.0
6        60  '2020/12/07'    110       136     374.0
7       450  '2020/12/08'    104       134     253.3
8        30  '2020/12/09'    109       133     195.1
9        60  '2020/12/10'     98       124     269.0

What to notice: Already in row 7 you can see Duration is 450 · while every other value is between 30 and 60. That looks suspicious!

Default behaviour (no number given):

print(df.head())  # Shows first 5 rows by default

💡 Tip: If you call head() without a number, it always shows exactly 5 rows. This is a shortcut for a quick sanity check.

1.3 tail() · See the Last Rows

What is it? A method that shows the bottom rows of your DataFrame.

Why use it? The last rows of a dataset can reveal problems too · trailing blank rows, or data entered in a different format at the end.

print(df.tail())  # Show the last 5 rows

Expected Output:

    Duration          Date  Pulse  Maxpulse  Calories
27        60  '2020/12/27'     92       118     241.0
28        60  '2020/12/28'    103       132       NaN
29        60  '2020/12/29'    100       132     280.0
30        60  '2020/12/30'    102       129     380.3
31        60  '2020/12/31'     92       115     243.0

What to notice: Row 28 has NaN in Calories · a missing value.

🤔 Think about it: What would happen if you calculated the average calories without removing the NaN values first? Try to reason through it before reading on.

1.4 info() · Get a Full Data Report

What is it? A method that prints a structured summary of your entire DataFrame · column names, data types, and how many values are non-null (non-missing).

Why use it? It's the fastest way to spot missing values and check whether your columns have the right data type.

print(df.info())

Expected Output:

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 169 entries, 0 to 168
Data columns (total 4 columns):
 #   Column    Non-Null Count  Dtype  
---  ------    --------------  -----  
 0   Duration  169 non-null    int64  
 1   Pulse     169 non-null    int64  
 2   Maxpulse  169 non-null    int64  
 3   Calories  164 non-null    float64
dtypes: float64(1), int64(3)
memory usage: 5.4 KB
None

Breaking down every part of this output:

What does 164 non-null mean for Calories?

There are 169 rows total. Only 164 have a Calories value. So 5 rows are missing their Calories data. This is a problem you will need to fix before computing statistics.

What is int64? A whole number (integer). 64 refers to how the computer stores it internally · you don't need to worry about this detail as a beginner.

What is float64? A decimal number (floating point). Calories uses decimals like 409.1, so it's stored as float64.

💡 Tip: Always run df.info() immediately after loading a dataset. It tells you everything important at once.

1.5 What Are Null Values (NaN)?

NaN stands for "Not a Number." In data work, it means a cell is completely empty · no value was recorded for that row and column.

Think of it like a form where someone left a question blank. The field exists, but there's nothing in it.

Why are they a problem?

Most mathematical operations in Python cannot handle NaN gracefully. For example:

# Simple demonstration of NaN behavior
import math
print(5 + float('nan'))   # Output: nan
print(float('nan') > 10) # Output: False — but this is misleading!

When NaN appears in a calculation, the result becomes NaN too · which is wrong. Before doing any analysis, you must either remove or fill those missing values.

What is data cleaning?

Data cleaning (also called data wrangling or data munging) is the process of finding and fixing problems in a dataset before you analyze it.

Think of it like washing vegetables before cooking. You wouldn't skip that step · and you shouldn't skip data cleaning either.

The four main types of data problems

In our workout dataset, we have all four types:

We will handle each type one by one.

Our Dataset

Throughout the cleaning sections, we work with this dataset. Save it mentally · you'll see it again and again:

    Duration          Date  Pulse  Maxpulse  Calories
0         60  '2020/12/01'    110       130     409.1
1         60  '2020/12/02'    117       145     479.0
2         60  '2020/12/03'    103       135     340.0
3         45  '2020/12/04'    109       175     282.4
4         45  '2020/12/05'    117       148     406.0
5         60  '2020/12/06'    102       127     300.0
6         60  '2020/12/07'    110       136     374.0
7        450  '2020/12/08'    104       134     253.3   ← Wrong data
8         30  '2020/12/09'    109       133     195.1
9         60  '2020/12/10'     98       124     269.0
10        60  '2020/12/11'    103       147     329.3
11        60  '2020/12/12'    100       120     250.7   ← Duplicate
12        60  '2020/12/12'    100       120     250.7   ← Duplicate
13        60  '2020/12/13'    106       128     345.3
...
18        45  '2020/12/18'     90       112       NaN   ← Empty Calories
...
22        45           NaN    100       119     282.0   ← Empty Date
...
26        60      20201226    100       120     250.0   ← Wrong format
...
28        60  '2020/12/28'    103       132       NaN   ← Empty Calories

Why do empty cells matter?

Empty cells are like blank entries in a survey · they leave gaps in your data. If someone calculates the average calories burned per workout and 5 out of 32 entries are blank, the average will be computed on only 27 entries. This can skew your results or cause errors.

You have two strategies: remove the row, or fill the empty cell with a sensible value.

3.1 Strategy 1: Remove Rows with Empty Cells (dropna())

What is dropna()? A method that drops (removes) any row that has at least one NaN value.

Analogy: Imagine a quality inspector on a factory line. Any product with a missing component gets tossed out entirely.

Simple Example First

import pandas as pd

# Create a tiny DataFrame with a missing value
data = {'Name': ['Alice', 'Bob', 'Carol'],
        'Score': [88.5, None, 74.0]}

df = pd.DataFrame(data)
print("Before cleaning:")
print(df)

clean_df = df.dropna()
print("\nAfter dropna():")
print(clean_df)

Expected Output:

Before cleaning:
    Name  Score
0  Alice   88.5
1    Bob    NaN
2  Carol   74.0

After dropna():
    Name  Score
0  Alice   88.5
2  Carol   74.0

Bob's row was removed because his Score was missing.

Applying to the Workout Dataset

import pandas as pd

df = pd.read_csv('data.csv')

new_df = df.dropna()  # Returns a NEW clean DataFrame

print(new_df.to_string())

⚠️ Important: By default, dropna() does not change your original DataFrame. It creates and returns a brand new one. The original df is unchanged.

Making the Change Permanent with inplace=True

import pandas as pd

df = pd.read_csv('data.csv')

df.dropna(inplace=True)  # Modifies the ORIGINAL df directly

print(df.to_string())

What does inplace=True mean?

Think of it like editing a document:

• Without inplace=True: Pandas makes a photocopy and edits the copy. Your original is safe.
• With inplace=True: Pandas edits the original document directly.

🚫 Common Beginner Mistake: Writing df.dropna() and expecting df to change. It won't! You must either write df = df.dropna() or use inplace=True.

3.2 Strategy 2: Fill Empty Cells with a Value (fillna())

What is fillna()? A method that replaces all NaN values with a value you specify.

Why use this instead of dropping? Sometimes losing entire rows wastes too much data. For example, if only the Calories column is missing but Duration, Pulse, and Date are all valid · it might be better to estimate Calories than to throw the whole row away.

Simple Example

import pandas as pd

data = {'Name': ['Alice', 'Bob', 'Carol'],
        'Score': [88.5, None, 74.0]}

df = pd.DataFrame(data)

df.fillna(0, inplace=True)  # Fill ALL NaN values with 0

print(df)

Expected Output:

    Name  Score
0  Alice   88.5
1    Bob    0.0
2  Carol   74.0

Bob's missing Score is now 0.

🤔 Think about it: Is filling with 0 always a good idea? What if Score can never be 0 in real life? What would a better fill value be?

Fill a Specific Column Only

You almost never want to fill every column with the same value. More often, you target one specific column:

import pandas as pd

df = pd.read_csv('data.csv')

df.fillna({"Calories": 130}, inplace=True)
# Only the Calories column's NaN values become 130

Why 130? In this example it's just a placeholder. In real analysis, you'd choose a more meaningful value · like the average.

3.3 Strategy 3: Fill Empty Cells with a Calculated Value (Mean, Median, Mode)

Instead of guessing a fill value, you can calculate the average (mean), middle value (median), or most common value (mode) from the rest of the column and use that.

Analogy: If a student missed one exam, the school might substitute their average score from other exams instead of recording a zero. That's fairer.

Understanding Mean, Median, and Mode

Mean (Average): Add all values together, divide by how many there are.

Values: 10, 20, 30, 40, 50
Mean = (10+20+30+40+50) / 5 = 150 / 5 = 30

Median (Middle Value): Sort the values, then pick the one in the middle.

Values sorted: 10, 20, 30, 40, 50
Middle value = 30  (3 values on each side)

Mode (Most Common Value): The value that appears most often.

Values: 10, 20, 20, 20, 50
Mode = 20  (appears 3 times)

💡 When to use which:

• Mean works well when data is spread evenly without extreme outliers.
• Median works better when there are outliers (extreme values). Our duration value of 450 would inflate the mean, making median a safer choice.
• Mode works for categorical data or when one value is clearly the most typical.

Fill with Mean

import pandas as pd

df = pd.read_csv('data.csv')

x = df["Calories"].mean()   # Calculate mean of the Calories column
print(f"Mean calories: {x:.2f}")

df.fillna({"Calories": x}, inplace=True)
print(df.to_string())

What .mean() does: Adds up all non-NaN Calories values and divides by how many there are.

Expected intermediate output:

Mean calories: 304.68

All 5 missing Calories cells would be filled with 304.68.

Fill with Median

import pandas as pd

df = pd.read_csv('data.csv')

x = df["Calories"].median()   # The middle value of Calories

df.fillna({"Calories": x}, inplace=True)

Fill with Mode

import pandas as pd

df = pd.read_csv('data.csv')

x = df["Calories"].mode()[0]   # Most common Calories value
# Note the [0] — mode() returns a list, so we take the first item

df.fillna({"Calories": x}, inplace=True)

⚠️ Why [0] after mode()? The mode() method returns a Series (a list) because there can be more than one most-common value. Adding [0] picks the first one.

What is "wrong format"?

A column has wrong format when the same type of information is stored in different ways across different rows. For example, a "Date" column where some dates look like '2020/12/01' and one looks like 20201226 · a number instead of a formatted string.

Why is this a problem? Computers are very literal. If Python sees '2020/12/01' and 20201226 in the same column, it cannot sort them as dates, calculate the number of days between them, or group them by month · because one is a formatted date string and the other is just a plain number.

4.1 Converting to a Correct Date Format (pd.to_datetime())

Look at row 26 in our dataset · the Date value is 20201226 (a plain number), while every other date looks like '2020/12/26'.

We want to convert the entire Date column into proper Python date objects.

import pandas as pd

df = pd.read_csv('data.csv')

df['Date'] = pd.to_datetime(df['Date'], format='mixed')

print(df.to_string())

Breaking down the code:

• df['Date'] · we are selecting the "Date" column
• pd.to_datetime(...) · this is a Pandas function that converts values into proper date objects
• format='mixed' · tells Pandas that the dates are in different formats, so it should be flexible and figure each one out

Expected Result for key rows:

    Duration       Date  Pulse  Maxpulse  Calories
...
22        45        NaT    100       119     282.0   ← Empty date becomes NaT
...
26        60 2020-12-26    100       120     250.0   ← Fixed!
...

What is NaT? It stands for "Not a Time." Just like NaN is the missing value for numbers, NaT is the missing value for dates. Row 22 had no date at all, so after conversion it shows NaT.

4.2 Removing Rows with Wrong/Missing Dates After Conversion

After converting, any rows that had empty or unreadable dates now have NaT. We can remove those rows using dropna() targeted at the Date column:

import pandas as pd

df = pd.read_csv('data.csv')

df['Date'] = pd.to_datetime(df['Date'], format='mixed')

df.dropna(subset=['Date'], inplace=True)
# subset=['Date'] means: only drop rows where Date is NaN/NaT
# Rows with NaN in other columns are kept

What subset=['Date'] means: Instead of dropping any row with any missing value (which is the default), we only drop rows where the Date column specifically is empty. All other missing values are left alone.

🤔 Think about it: Why might we use subset here instead of just dropna()? What would happen to row 18 (which has a valid Date but missing Calories) if we called dropna() without subset?

What is "wrong data"?

Wrong data is a value that is technically present and in the right format, but is clearly incorrect or impossible given the context.

Example from our dataset: Row 7 has Duration = 450. All other Duration values are between 30 and 60 minutes. A 450-minute workout session (7.5 hours!) is extremely unlikely. It's almost certainly a typo · someone probably meant 45.

The tricky part: Pandas can't automatically detect this. The number 450 is perfectly valid as a number. You need domain knowledge (understanding what the data represents) to recognize it's wrong.

5.1 Replacing a Specific Wrong Value

Method 1 · Fix one specific cell:

import pandas as pd

df = pd.read_csv('data.csv')

df.loc[7, 'Duration'] = 45
# loc[7, 'Duration'] = row 7, column 'Duration'
# We set it to 45 (the corrected value)

print(df.loc[7])

Expected Output for row 7:

Duration             45
Date       '2020/12/08'
Pulse               104
Maxpulse            134
Calories          253.3
Name: 7, dtype: object

What is df.loc[]? It's how you access a specific cell or group of cells in a DataFrame.

• df.loc[7, 'Duration'] means: go to row 7, column 'Duration'.
• Assigning = 45 changes the value there.

5.2 Replacing Wrong Values Automatically with a Loop

For a small dataset you can fix values one by one. But imagine you have 10,000 rows · you can't check each one manually. Instead, you write a rule: "any Duration over 120 minutes is unrealistic · change it to 120."

import pandas as pd

df = pd.read_csv('data.csv')

for x in df.index:              # Loop through every row index
    if df.loc[x, "Duration"] > 120:   # Check if Duration is too large
        df.loc[x, "Duration"] = 120   # Cap it at 120

print(df.to_string())

Walking through the code line by line:

1. for x in df.index: · df.index is a list of all row numbers (0, 1, 2, ... 31). This loop visits each row one at a time, putting the row number in x.
2. if df.loc[x, "Duration"] > 120: · checks whether this row's Duration is greater than 120.
3. df.loc[x, "Duration"] = 120 · if it is too large, set it to exactly 120.

Expected change: Only row 7 (Duration = 450) is affected. It becomes 120. All other rows stay the same.

💡 Real-world use: In financial data, you might cap or flag transactions over a certain amount. In medical data, you might flag heart rates below 30 or above 220 as likely recording errors. This pattern is used everywhere.

5.3 Removing Rows with Wrong Data

Instead of correcting wrong values, you can simply delete those rows. This is a valid choice when you don't know what the correct value should be.

import pandas as pd

df = pd.read_csv('data.csv')

for x in df.index:
    if df.loc[x, "Duration"] > 120:
        df.drop(x, inplace=True)  # Delete the entire row

print(df.to_string())

What df.drop(x, inplace=True) does: Removes the row with index number x from the DataFrame permanently (because of inplace=True).

After this code: Row 7 is completely gone. The DataFrame goes from 32 rows to 31 rows.

⚠️ Correction vs Deletion · which should you choose?

• Correct if you're confident what the right value should be (e.g., 450 is clearly a typo for 45).
• Delete if the error is too severe to fix, or if you don't know what the right value should be.

What are duplicate rows?

A duplicate row is a row that has been entered more than once · all values in that row are identical to another row.

Analogy: Imagine a school roll call system accidentally recorded the same student twice. Any report generated from that data would count that student twice · doubling their influence on every statistic.

In our workout dataset, look at rows 11 and 12:

    Duration          Date  Pulse  Maxpulse  Calories
11        60  '2020/12/12'    100       120     250.7
12        60  '2020/12/12'    100       120     250.7

Every single value is identical. This workout session was accidentally recorded twice.

6.1 Discovering Duplicates with duplicated()

Before removing duplicates, it's good practice to first find them and see which rows they are.

import pandas as pd

df = pd.read_csv('data.csv')

print(df.duplicated())

Expected Output (abbreviated):

0     False
1     False
2     False
...
11    False
12     True   ← Row 12 is a duplicate of row 11
...
31    False
dtype: bool

What duplicated() returns: A Series (a column) of True or False values.

• False means "this row is unique · it hasn't appeared before."
• True means "this row is a duplicate · an identical row appeared earlier."

Notice that row 11 shows False (it's the first occurrence) and row 12 shows True (it's the repeat).

Second Simple Example

import pandas as pd

data = {'Name': ['Alice', 'Bob', 'Alice'],
        'Score': [88, 72, 88]}

df = pd.DataFrame(data)

print(df.duplicated())

Expected Output:

0    False
1    False
2     True    ← Second "Alice" with Score 88 is a duplicate
dtype: bool

6.2 Removing Duplicates with drop_duplicates()

Once you've confirmed duplicates exist, remove them:

import pandas as pd

df = pd.read_csv('data.csv')

df.drop_duplicates(inplace=True)

print(df.to_string())

What happens: Pandas scans all rows. When it finds two identical rows, it keeps the first one and removes the second. Our dataset drops from 32 rows to 31 rows (row 12 is removed, row 11 is kept).

The inplace=True rule applies here too: Without inplace=True, the result is returned as a new DataFrame. With inplace=True, the original DataFrame is modified.

💡 Real-world use: Duplicates are extremely common in datasets that come from form submissions, database merges, or automated logging systems. Always check for them!

Mistake 1: Forgetting inplace=True and wondering why nothing changed

Wrong:

df.dropna()         # Does nothing to df!
df.fillna(0)        # Does nothing to df!
df.drop_duplicates() # Does nothing to df!

Right (Option A · use inplace):

df.dropna(inplace=True)
df.fillna(0, inplace=True)
df.drop_duplicates(inplace=True)

Right (Option B · reassign):

df = df.dropna()
df = df.fillna(0)
df = df.drop_duplicates()

Mistake 2: Filling ALL columns with the same value

Wrong:

df.fillna(0, inplace=True)
# This fills missing dates with 0, missing names with 0 — nonsense!

Right:

df.fillna({'Calories': df['Calories'].mean()}, inplace=True)
# Only fill the specific column that makes sense

Mistake 3: Using dropna() without subset and accidentally removing too many rows

Wrong:

df.dropna(inplace=True)
# This removes a row if ANY column is missing
# A row with valid Date but missing Calories gets deleted too

Right:

df.dropna(subset=['Date'], inplace=True)
# Only remove rows where the Date is missing

Mistake 4: Not checking for wrong data · assuming numbers are always correct

Just because a cell has a number doesn't mean it's the right number. Always inspect your data domain and use describe() or info() to spot suspiciously large or small values:

print(df['Duration'].describe())
# Shows min, max, mean, etc. — helps you spot outliers

Mistake 5: Modifying a DataFrame inside a loop without resetting the index

After dropping rows, the index numbers may have gaps (e.g., 0, 1, 2, 4, 5 · missing 3). If you then loop using the old index, you might hit a KeyError. Reset after major operations if needed:

df.reset_index(drop=True, inplace=True)

Mistake 6: Forgetting [0] when using mode

Wrong:

df.fillna({'Calories': df['Calories'].mode()}, inplace=True)
# Tries to fill with an entire Series — will raise an error!

Right:

df.fillna({'Calories': df['Calories'].mode()[0]}, inplace=True)
# [0] selects the first (most common) value from the result