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.
| Method | Purpose |
|---|---|
| df.head(n) / df.tail(n) | First / last n rows (default 5) |
| df.shape | Count of (rows, columns) |
| df.columns | Names of the columns |
| df.dtypes | Data 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)
| loc | iloc | |
|---|---|---|
| Finds by | Label / name | Position (number) |
| End in slicing | Included | Notincluded (like Python) |
| Example | df.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 keeps | Venn diagram |
|---|---|---|
| inner | Only the rows that are common toboth | The middle part |
| left | Allrows of the left table, with matches from the right where found | Left circle |
| right | Allrows of the right table | Right circle |
| outer | All 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.