مین مواد پر جائیں

Chapter 6: Pandas DataFrames, Cleaning, Merging aur Grouping

MODULE 2 | WEEK 6​

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

NumPy numbers ke liye behtareen hai, magar asli duniya ka data aksar table ki shakal me hota hai: naam, shehar, umar, marks, qeemat. Isi table ko sambhalne ke liye Pandas hai. Data Scientist ka sab se zyada waqt Pandas me hi guzarta hai.

6.1 Pandas kya hai? Series aur DataFrame​

Pandas ek library hai jo table jaisa data (Excel sheet jaisa) sambhalne ke liye banai gayi hai. Iske do bunyadi data structures hain:

  • Series: ek column (ek qatar ke numbers, har ek ka label ke saath).

  • DataFrame: poori table (kai columns, yani kai Series ek sath).

Series banana​

import pandas as pd
marks = pd.Series([78, 85, 62], index=["Ali", "Sara", "Hina"])
print(marks)
print(marks["Sara"]) # label se value
print(marks.mean()) # NumPy jaise functions kaam karte hain
print(marks[marks > 70]) # filtering bhi
OUTPUT​
Ali 78
Sara 85
Hina 62
dtype: int64
85
75.0
Ali 78
Sara 85
dtype: int64

DataFrame banana​

Sab se aam tareeqa: dictionary se. Har key ek column ka naam, aur value us column ki list.

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

CSV file se data parhna aur save karna​

Asli data aksar CSV file me milta hai. pd.read_csv() us ko seedha DataFrame bana deta hai. Google Colab me file upload karne ke liye left side ke folder icon se upload karo.

df.to_csv("students.csv", index=False) # file me save (index=False: extra column na
bane)
df2 = pd.read_csv("students.csv") # file se parho
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 Data ko inspect karna (pehli nazar)​

Naya data milte hi pehle ye sawal poochte hain: kitni rows aur columns hain? Columns ke naam aur types kya hain? Kuch khali (missing) to nahi? Ye chand methods in sab ka jawab dete hain.

MethodKaam
df.head(n) / df.tail(n)Pehli / aakhri n rows (default 5)
df.shape(rows, columns) ki tadaad
df.columnsColumns ke naam
df.dtypesHar column ka data type
df.info()Poora khulasa: types, non-null count, memory
MethodKaam
df.describe()Numeric columns ka statistics (mean, std, min, max, quartiles)
df['col'].value_counts()Column me har value kitni bar aayi
df['col'].unique() / nunique()Alag alag values / unki tadaad
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​

Naye pandas versions me text columns ka type str dikhta hai, aur purane versions (aur kai Colab notebooks) me object . Dono ka matlab ek hi hai: 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 Data chunna: columns, rows, loc aur iloc​

Columns chunna​

print(df["name"]) # ek column (Series)
print(df[["name", "math"]]) # kai columns (double bracket, list ke saath)
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 aur iloc (rows chunne ke do tareeqay)

lociloc
Kis se dhoondta haiLabel / naamse**Position (number)**se
Slicing me endShamilhota haiShamilnahihota (Python jaisa)
Misaaldf.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: shart se rows chunna​

NumPy ki boolean masking ki tarah. Shartein jorne ke liye & (aur), | (ya), ~ (nahi) aur har shart bracket me.

print(df[df["math"] > 70]) # math 70 se zyada
print(df[(df["city"] == "Karachi") & (df["urdu"] >= 70)]) # dono shartein
print(df[df["city"].isin(["Quetta", "Lahore"])]) # in me se koi
print(df[df["math"].between(60, 80)]) # 60 se 80 ke beech
print(df[~(df["gender"] == "M")]) # M nahi
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: filtering ka aasan tareeqa (string me shart)
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)) # math ke hisaab se top 3
print(df.sort_values(["city", "math"], ascending=[True, False])) # pehle city, phir 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 Columns badalna aur naye columns banana​

df["total"] = df["math"] + df["urdu"] # naya 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 bhi yahan kaam aata hai
import numpy as np
df["grade"] = np.where(df["average"] >= 75, "A", "B")
# drop aur rename (naya DataFrame wapas deta hai)
small = df.drop(columns=["grade", "result"])
small = small.rename(columns={"average": "avg"})
print(small.columns.tolist())
# map: ek column ki values badalna
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) aur Date ke methods​

names = pd.Series([" Ali Khan ", "sara AHMED", "HINA Malik"])
print(names.str.strip().str.title()) # saaf aur Title Case
print(names.str.lower().str.contains("ali")) # lafz maujood hai?
print(names.str.strip().str.split(" ").str[0]) # pehla naam

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) # dino ka farq
OUTPUT​
[2026, 2026, 2026] [1, 2, 3]
['Thursday', 'Friday', 'Thursday']
49

6.5 Data Cleaning (data ko saaf karna)​

Asli data kabhi saaf nahi hota. Khali jagahein, duplicate rows, ghalat spelling, ghalat types, ajeeb values: ye sab pehle theek karni parti hain. Yaad karo: Garbage In, Garbage Out .

1) Missing values dhoondna​

print(raw.isnull().sum()) # har column me kitni khali
print(raw.isnull().sum().sum()) # kul khali jagahein
print((raw.isnull().mean() * 100).round(1)) # percent me

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) Missing values ka ilaaj​

  • dropna(): khali wali rows hata do. (Jab khali rows bohat kam hon.)

  • fillna(value): khali jagah kuch bhar do: numbers me mean/median , text me mode (sab se aam value).

  • ffill() / bfill(): pichli ya agli value se bharna (time series me).

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
AAM GALTIYAN​
  • Purane tareeqe df['col'].fillna(x, inplace=True) naye pandas me kaam nahi karta. Hamesha df['col'] = df['col'].fillna(x) likho.

  • inplace=True se bachna aur nateeje ko wapas variable me assign karna behtar hai.

3) Duplicate rows​

print(clean.duplicated().sum()) # kitni rows duplicate hain
clean = clean.drop_duplicates()
print(clean.shape)
OUTPUT​
1
(5, 4)

4) Ghalat text: spaces aur capital/small ka masla​

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()) # ab Lahore ek hi category me hai
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) Ghalat data types​

salary numbers hain magar text (str) ban kar aaye. Isay number banana parega, warna hisaab nahi hoga.

clean["salary"] = clean["salary"].astype(int)
# agar kuch values ghalat hon to: pd.to_numeric(col, errors="coerce") unhe NaN bana deta hai
print(clean["salary"].mean())
print(clean.dtypes)
OUTPUT​
57000.0
name str
age float64
city str
salary int64
dtype: object

6) Outliers (ajeeb values)​

Umar 150 saal? Ye outlier hai. Dhoondne ka mashhoor tareeqa IQR rule :

  • Q1 = 25% wali value, Q3 = 75% wali value, IQR = Q3 - Q1
• Jo value Q1 - 1.5 x IQR se neeche ya Q3 + 1.5 x IQR se upar ho, wo outlier hai.​
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)] # hata do
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: tables ko jorna​

concat: upar neeche ya side me jorna

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 neeche jorin
OUTPUT​
name marks
0 Ali 80
1 Sara 90
2 Hina 70
3 Usman 60

merge: kisi common column par jorna (SQL JOIN jaisa)

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=Kya rakhta haiVenn diagram
innerSirf wo rows jodonome common hainBeech ka hissa
leftLeft table kisabrows, right se jahan match mileLeft circle
rightRight table kisabrowsRight circle
outerDonoki sab rows (match na ho to NaN)Poora dono circles

6.7 Grouping: groupby aur pivot_table​

groupby ka kaam: data ko kisi column ke hisaab se group karna, phir har group par hisaab lagana. Ye Split, Apply, Combine ka tareeqa hai.

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: apne naam wale columns
summary = df.groupby("city").agg(
students=("name", "count"),
avg_math=("math", "mean"),
best_urdu=("urdu", "max"),
)
print(summary)
# kai columns par group
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 aur crosstab​

# pivot_table: Excel ke Pivot Table jaisa
print(pd.pivot_table(df, values="math", index="city", columns="gender", aggfunc="mean"))
# crosstab: do categories ki ginti
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
AAM GALTIYAN​
  • Filtering me and / or likhna. Pandas me & aur | aur har shart bracket me.

  • Ek column ke liye df['a'] , kai ke liye df[['a','b']] (double bracket).

  • loc me end shamil hota hai, iloc me nahi.

  • Cleaning ke baad nateeja wapas variable me assign karna bhool jana (drop_duplicates, str.strip waghera naya data wapas dete hain).

  • inplace=True par bharosa karna. Naye pandas me nateeja wapas assign karo.

  • Merge me ghalat how= chun lena aur data ghayab ho jana. Merge ke baad shape zaroor check karo.

6.8 Hands-on with Pandas​

Neeche wale sab exercises is ek DataFrame par hain. Pehle khud koshish karo, phir solution dekho.

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: Missing price ko median se bharo​

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

OUTPUT

0

Exercise 2: Har order ki total revenue (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: Kaunsa product sab se zyada revenue deta hai?​

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: Karachi ke sirf wo orders jin ka revenue 50,000 se zyada ho​

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: Har city me kitne orders aur average revenue​

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: Do tables jodo​

Ek chhoti table me har product ki category hai. Isay sales se jodo aur category ke hisaab se revenue nikalo.

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: sales me ek column size banao: revenue 50,000 se zyada ho to "Big", warna "Small". Phir pivot_table se dekho har city me kitne Big aur Small orders hain.

KEY TAKEAWAYS​
  • Series = ek column, DataFrame = poori table. read_csv() se file parhte hain, to_csv(index=False) se save.

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

  • iloc position se, loc label se. Filtering: &, |, ~ aur har shart bracket me.

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

  • concat tables ko upar neeche jorta hai, merge common column par jorta hai (inner, left, right, outer).

  • groupby = split, apply, combine. agg se kai hisaab, pivot_table Excel ke pivot jaisa.

  • Naye pandas me inplace=True ke bajaye nateeja wapas assign karo.