Skip to main content

Chapter 6: Pandas DataFrames, Cleaning, Merging and Grouping

MODULE 2 | WEEK 6​

Chapter 6: Pandas DataFrames, Cleaning, Merging and Grouping​

NumPy is excellent for numbers, but real-world data is usually in the form of a table : name, city, age, marks, price. Pandas exists to handle exactly this kind of table. A Data Scientist spends most of their time in Pandas.

6.1 What is Pandas? Series and DataFrame​

Pandas is a library built to handle table-like data (like an Excel sheet). It has two basic data structures:

  • Series: a single column (a row of numbers, each with a label).

  • DataFrame: the whole table (many columns, that is, many Series together).

Creating a Series​

import pandas as pd
marks = pd.Series([78, 85, 62], index=["Ali", "Sara", "Hina"])
print(marks)
print(marks["Sara"]) # value by label
print(marks.mean()) # NumPy-like functions work
print(marks[marks > 70]) # filtering too
OUTPUT​
Ali 78
Sara 85
Hina 62
dtype: int64
85
75.0
Ali 78
Sara 85
dtype: int64

Creating a DataFrame​

The most common way: from a dictionary . Each key is a column name, and the value is the list for that column.

import pandas as pd
data = {
"name": ["Ali", "Sara", "Hina", "Usman", "Zara", "Bilal"],
"gender": ["M", "F", "F", "M", "F", "M"],
"city": ["Karachi", "Lahore", "Karachi", "Quetta", "Lahore", "Karachi"],
"math": [78, 85, 62, 90, 55, 70],
"urdu": [65, 90, 70, 60, 80, 75],
}
df = pd.DataFrame(data)
print(df)

OUTPUT

name gender city math urdu
0 Ali M Karachi 78 65
1 Sara F Lahore 85 90
2 Hina F Karachi 62 70
3 Usman M Quetta 90 60
4 Zara F Lahore 55 80
5 Bilal M Karachi 70 75

Reading data from a CSV file and saving it​

Real data is often found in a CSV file. pd.read_csv() turns it straight into a DataFrame. To upload a file in Google Colab, use the folder icon on the left side.

df.to_csv("students.csv", index=False) # save to file (index=False: no extra column)
df2 = pd.read_csv("students.csv") # read from file
print(df2.shape)
print(df2.head(2))

OUTPUT (6, 5) name gender city math urdu 0 Ali M Karachi 78 65 1 Sara F Lahore 85 90

6.2 Inspecting data (the first look)​

As soon as you get new data, you first ask these questions: how many rows and columns are there? What are the column names and types? Is anything empty (missing)? A handful of methods answer all of these.

MethodPurpose
df.head(n) / df.tail(n)First / last n rows (default 5)
df.shapeCount of (rows, columns)
df.columnsNames of the columns
df.dtypesData type of each column
df.info()Complete summary: types, non-null count, memory
df.describe()Statistics of numeric columns (mean, std, min, max, quartiles)
df['col'].value_counts()How many times each value appears in the column
df['col'].unique() / nunique()The distinct values / how many there are
print(df.shape)
print(df.columns.tolist())
df.info()
OUTPUT​
(6, 5)
['name', 'gender', 'city', 'math', 'urdu']
<class 'pandas.DataFrame'>
RangeIndex: 6 entries, 0 to 5
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 name 6 non-null str
1 gender 6 non-null str
2 city 6 non-null str
3 math 6 non-null int64
4 urdu 6 non-null int64
dtypes: int64(2), str(3)
memory usage: 372.0 bytes
VERSION NOTE​

In newer pandas versions, text columns show the type str , while in older versions (and many Colab notebooks) they show object . Both mean the same thing: text.

print(df.describe())
print(df["city"].value_counts())
print(df["city"].nunique())
OUTPUT​
math urdu
count 6.000000 6.000000
mean 73.333333 73.333333
std 13.500617 10.801234
min 55.000000 60.000000
25% 64.000000 66.250000
50% 74.000000 72.500000
75% 83.250000 78.750000
max 90.000000 90.000000
city
Karachi 3
Lahore 2
Quetta 1
Name: count, dtype: int64
3

6.3 Selecting data: columns, rows, loc and iloc​

Selecting columns​

print(df["name"]) # one column (Series)
print(df[["name", "math"]]) # several columns (double bracket, with a list)
OUTPUT​
0 Ali
1 Sara
2 Hina
3 Usman
4 Zara
5 Bilal
Name: name, dtype: str
name math
0 Ali 78
1 Sara 85
2 Hina 62
3 Usman 90
4 Zara 55
5 Bilal 70

loc and iloc (two ways to select rows)​

lociloc
Finds byLabel / namePosition (number)
End in slicingIncludedNotincluded (like Python)
Exampledf.loc[0:2, 'name']df.iloc[0:2, 0]
OUTPUT​
name Ali
gender M
city Karachi
math 78
urdu 65
Name: 0, dtype: object
name gender
1 Sara F
2 Hina F
name math
0 Ali 78
1 Sara 85
2 Hina 62
Lahore

Filtering: selecting rows by a condition​

Just like NumPy's boolean masking. To combine conditions use & (and), | (or), ~ (not), and put each condition in brackets.

print(df[df["math"] > 70]) # math above 70
print(df[(df["city"] == "Karachi") & (df["urdu"] >= 70)]) # both conditions
print(df[df["city"].isin(["Quetta", "Lahore"])]) # any of these
print(df[df["math"].between(60, 80)]) # between 60 and 80
print(df[~(df["gender"] == "M")]) # not M
OUTPUT​
name gender city math urdu
0 Ali M Karachi 78 65
1 Sara F Lahore 85 90
3 Usman M Quetta 90 60
name gender city math urdu
2 Hina F Karachi 62 70
5 Bilal M Karachi 70 75
name gender city math urdu
1 Sara F Lahore 85 90
3 Usman M Quetta 90 60
4 Zara F Lahore 55 80
name gender city math urdu
0 Ali M Karachi 78 65
2 Hina F Karachi 62 70
5 Bilal M Karachi 70 75
name gender city math urdu
1 Sara F Lahore 85 90
2 Hina F Karachi 62 70
4 Zara F Lahore 55 80
# query: an easy way of filtering (condition inside a string)
print(df.query("math > 70 and city == 'Karachi'"))
OUTPUT​
name gender city math urdu
0 Ali M Karachi 78 65

Sorting​

print(df.sort_values("math", ascending=False).head(3)) # top 3 by math
print(df.sort_values(["city", "math"], ascending=[True, False])) # first city, then math
OUTPUT​
name gender city math urdu
3 Usman M Quetta 90 60
1 Sara F Lahore 85 90
0 Ali M Karachi 78 65
name gender city math urdu
0 Ali M Karachi 78 65
5 Bilal M Karachi 70 75
2 Hina F Karachi 62 70
1 Sara F Lahore 85 90
4 Zara F Lahore 55 80
3 Usman M Quetta 90 60

6.4 Changing columns and creating new columns​

df["total"] = df["math"] + df["urdu"] # new column
df["average"] = df["total"] / 2
df["result"] = df["average"].apply(lambda x: "Pass" if x >= 60 else "Fail")
print(df[["name", "total", "average", "result"]])
OUTPUT​
name total average result
0 Ali 143 71.5 Pass
1 Sara 175 87.5 Pass
2 Hina 132 66.0 Pass
3 Usman 150 75.0 Pass
4 Zara 135 67.5 Pass
5 Bilal 145 72.5 Pass
# np.where works here too
import numpy as np
df["grade"] = np.where(df["average"] >= 75, "A", "B")
# drop and rename (they return a new DataFrame)
small = df.drop(columns=["grade", "result"])
small = small.rename(columns={"average": "avg"})
print(small.columns.tolist())
# map: replacing the values of a column
df["gender_full"] = df["gender"].map({"M": "Male", "F": "Female"})
print(df[["name", "gender_full"]].head(3))
OUTPUT​
['name', 'gender', 'city', 'math', 'urdu', 'total', 'avg']
name gender_full
0 Ali Male
1 Sara Female
2 Hina Female

Text (string) and Date methods​

names = pd.Series([" Ali Khan ", "sara AHMED", "HINA Malik"])
print(names.str.strip().str.title()) # clean and Title Case
print(names.str.lower().str.contains("ali")) # is the word present?
print(names.str.strip().str.split(" ").str[0]) # first name
OUTPUT​
0 Ali Khan
1 Sara Ahmed
2 Hina Malik
dtype: str
0 True
1 False
2 True
dtype: bool
0 Ali
1 sara
2 HINA
dtype: object
dates = pd.to_datetime(pd.Series(["2026-01-15", "2026-02-20", "2026-03-05"]))
print(dates.dt.year.tolist(), dates.dt.month.tolist())
print(dates.dt.day_name().tolist())
print((dates[2] - dates[0]).days) # difference in days
OUTPUT​
[2026, 2026, 2026] [1, 2, 3]
['Thursday', 'Friday', 'Thursday']
49

6.5 Data Cleaning (making data clean)​

Real data is never clean. Empty spots, duplicate rows, wrong spellings, wrong types, strange values: all of these must be fixed first. Remember: Garbage In, Garbage Out .

1) Finding missing values​

print(raw.isnull().sum()) # how many are empty in each column
print(raw.isnull().sum().sum()) # total empty spots
print((raw.isnull().mean() * 100).round(1)) # in percent

OUTPUT

name 0
age 1
city 1
salary 0
dtype: int64
2
name 0.0
age 16.7
city 16.7
salary 0.0
dtype: float64

2) Treating missing values​

  • dropna(): remove the rows that have empty values. (When there are very few such rows.)

  • fillna(value): put something in the empty spot: mean/median for numbers, mode (the most common value) for text.

  • ffill() / bfill(): fill from the previous or next value (in time series).

OUTPUT​
age city
0 20.0 Karachi
1 22.0 lahore
2 22.0 lahore
3 22.0 Karachi
4 21.0 Karachi
5 150.0 LAHORE

3) Duplicate rows​

print(clean.duplicated().sum()) # how many rows are duplicates
clean = clean.drop_duplicates()
print(clean.shape)
OUTPUT​
1
(5, 4)

4) Wrong text: the problem of spaces and capital/small letters​

clean["name"] = clean["name"].str.strip().str.title()
clean["city"] = clean["city"].str.strip().str.title()
print(clean[["name", "city"]])
print(clean["city"].value_counts()) # now Lahore is in a single category
OUTPUT​
name city
0 Ali Karachi
1 Sara Lahore
3 Hina Karachi
4 Usman Karachi
5 Zara Lahore
city
Karachi 3
Lahore 2
Name: count, dtype: int64

5) Wrong data types​

salary is made of numbers but arrived as text (str). It must be converted to numbers, otherwise calculations will not work.

clean["salary"] = clean["salary"].astype(int)
# if some values are wrong: pd.to_numeric(col, errors="coerce") turns them into NaN
print(clean["salary"].mean())
print(clean.dtypes)
OUTPUT​
57000.0
name str
age float64
city str
salary int64
dtype: object

6) Outliers (strange values)​

An age of 150 years? This is an outlier . A well-known way to find them is the IQR rule :

  • Q1 = the 25% value, Q3 = the 75% value, IQR = Q3 - Q1

  • A value below Q1 - 1.5 x IQR or above Q3 + 1.5 x IQR is an outlier.

q1 = clean["age"].quantile(0.25)
q3 = clean["age"].quantile(0.75)
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print(low, high)
print(clean[(clean["age"] < low) | (clean["age"] > high)]) # outlier rows
clean = clean[(clean["age"] >= low) & (clean["age"] <= high)] # remove them
print(clean)
OUTPUT​
19.5 23.5
name age city salary
5 Zara 150.0 Lahore 70000
name age city salary
0 Ali 20.0 Karachi 50000
1 Sara 22.0 Lahore 62000
3 Hina 22.0 Karachi 48000
4 Usman 21.0 Karachi 55000

6.6 Merging: joining tables​

concat: joining on top of each other or side by side

a = pd.DataFrame({"name": ["Ali", "Sara"], "marks": [80, 90]})
b = pd.DataFrame({"name": ["Hina", "Usman"], "marks": [70, 60]})
print(pd.concat([a, b], ignore_index=True)) # rows joined below
OUTPUT​
name marks
0 Ali 80
1 Sara 90
2 Hina 70
3 Usman 60

merge: joining on a common column (like SQL JOIN)

OUTPUT​
roll name marks
0 1 Ali 80
1 2 Sara 90
2 3 Hina 70
roll name marks
0 1 Ali 80.0
1 2 Sara 90.0
2 3 Hina 70.0
3 4 Usman NaN
roll name marks
0 1 Ali 80
1 2 Sara 90
2 3 Hina 70
3 5 NaN 60
roll name marks
0 1 Ali 80.0
1 2 Sara 90.0
2 3 Hina 70.0
3 4 Usman NaN
4 5 NaN 60.0
how=What it keepsVenn diagram
innerOnly the rows that are common tobothThe middle part
leftAllrows of the left table, with matches from the right where
found
Left circle
rightAllrows of the right tableRight circle
outerAll rows ofboth(NaN where there is no match)Both circles entirely

6.7 Grouping: groupby and pivot_table​

What groupby does: it groups the data by some column, then calculates on each group. This is the Split, Apply, Combine method.

OUTPUT​
city
Karachi 70.0
Lahore 70.0
Quetta 90.0
Name: math, dtype: float64
math urdu
city
Karachi 70.0 70.0
Lahore 70.0 85.0
Quetta 90.0 60.0
mean max count
gender
F 67.333333 85 3
M 79.333333 90 3
# Named aggregation: columns with names of your own
summary = df.groupby("city").agg(
students=("name", "count"),
avg_math=("math", "mean"),
best_urdu=("urdu", "max"),
)
print(summary)
# group by several columns
print(df.groupby(["city", "gender"])["math"].mean())
OUTPUT​
students avg_math best_urdu
city
Karachi 3 70.0 75
Lahore 2 70.0 90
Quetta 1 90.0 60
city gender
Karachi F 62.0
M 74.0
Lahore F 70.0
Quetta M 90.0
Name: math, dtype: float64

pivot_table and crosstab​

# pivot_table: like Excel's Pivot Table
print(pd.pivot_table(df, values="math", index="city", columns="gender", aggfunc="mean"))
# crosstab: counts of two categories
print(pd.crosstab(df["city"], df["gender"]))
OUTPUT​
gender F M
city
Karachi 62.0 74.0
Lahore 70.0 NaN
Quetta NaN 90.0
gender F M
city
Karachi 1 2
Lahore 2 0
Quetta 0 1

6.8 Hands-on with Pandas​

All the exercises below use this one DataFrame. First try it yourself, then look at the solution.

import pandas as pd import numpy as np sales = pd.DataFrame({ "order_id": [1, 2, 3, 4, 5, 6, 7, 8], "product": ["Laptop", "Mouse", "Phone", "Mouse", "Laptop", "Phone", "Mouse", "Phone"], "city": ["Karachi", "Lahore", "Karachi", "Quetta", "Lahore", "Karachi", "Lahore", "Quetta"], "price": [90000, 1500, 55000, 1500, 95000, 60000, 1600, np.nan], "quantity": [1, 3, 2, 1, 1, 1, 2, 2], }) print(sales) OUTPUT

order_id product city price quantity
0 1 Laptop Karachi 90000.0 1
1 2 Mouse Lahore 1500.0 3
2 3 Phone Karachi 55000.0 2
3 4 Mouse Quetta 1500.0 1
4 5 Laptop Lahore 95000.0 1
5 6 Phone Karachi 60000.0 1
6 7 Mouse Lahore 1600.0 2
7 8 Phone Quetta NaN 2

Exercise 1: Fill the missing price with the median​

sales["price"] = sales["price"].fillna(sales["price"].median())
print(sales["price"].isnull().sum())
OUTPUT​
0

Exercise 2: Total revenue of each order (price x quantity)​

sales["revenue"] = sales["price"] * sales["quantity"]
print(sales[["order_id", "product", "revenue"]])
OUTPUT​
order_id product revenue
0 1 Laptop 90000.0
1 2 Mouse 4500.0
2 3 Phone 110000.0
3 4 Mouse 1500.0
4 5 Laptop 95000.0
5 6 Phone 60000.0
6 7 Mouse 3200.0
7 8 Phone 110000.0

Exercise 3: Which product earns the most revenue?​

by_product = sales.groupby("product")["revenue"].sum().sort_values(ascending=False)
print(by_product)
print("Top product:", by_product.index[0])
OUTPUT​
product
Phone 280000.0
Laptop 185000.0
Mouse 9200.0
Name: revenue, dtype: float64
Top product: Phone

Exercise 4: Only the Karachi orders whose revenue is above 50,000​

print(sales[(sales["city"] == "Karachi") & (sales["revenue"] > 50000)])
OUTPUT​
order_id product city price quantity revenue
0 1 Laptop Karachi 90000.0 1 90000.0
2 3 Phone Karachi 55000.0 2 110000.0
5 6 Phone Karachi 60000.0 1 60000.0

Exercise 5: Number of orders and average revenue in each city​

print(sales.groupby("city").agg(orders=("order_id", "count"),
avg_revenue=("revenue", "mean")).round(0))
OUTPUT​
orders avg_revenue
city
Karachi 3 86667.0
Lahore 3 34233.0
Quetta 2 55750.0

Exercise 6: Join two tables​

A small table holds the category of each product. Join it with sales and find the revenue by category.

cat = pd.DataFrame({"product": ["Laptop", "Phone", "Mouse"],
"category": ["Computers", "Mobiles", "Accessories"]})
merged = pd.merge(sales, cat, on="product", how="left")
print(merged.groupby("category")["revenue"].sum())
OUTPUT​
category
Accessories 9200.0
Computers 185000.0
Mobiles 280000.0
Name: revenue, dtype: float64

Challenge: create a column size in sales: "Big" if revenue is above 50,000, otherwise "Small". Then use pivot_table to see how many Big and Small orders there are in each city.

KEY TAKEAWAYS​
  • Series = one column, DataFrame = the whole table. Read a file with read_csv(), save with to_csv(index=False).

  • Inspect with: head, shape, info, describe, value_counts.

  • iloc is by position, loc is by label. Filtering: &, |, ~ and each condition in brackets.

  • Cleaning steps: missing values (fillna/dropna), duplicates, wrong text (str.strip/title), types (astype), outliers (IQR).

  • concat joins tables on top of each other, merge joins on a common column (inner, left, right, outer).

  • groupby = split, apply, combine. agg gives several calculations, pivot_table works like an Excel pivot.

  • In newer pandas, assign the result back instead of using inplace=True.