Data Cleaning and Analysis Practice

Practise cleaning missing values, types, text and duplicates, then grouping, pivot tables and merging with pandas.

Intermediate8 problemsUpdated October 24, 2026

Review first: Missing Values, Data Types and Conversion, Cleaning Text Columns, Grouping and Aggregating, Pivot Tables and Crosstabs, Merging and Joining DataFrames

1.Fill missing ratings

Intermediate

Product ratings are category = ["food", "food", "toys", "toys", "toys"] and rating = [4.0, None, 3.0, None, 5.0]. Fill each missing rating with the average rating of its own category and print the result.

Show hint

groupby("category")["rating"].transform("mean"), then fillna with it.

Show solution

Python

import pandas as pd

df = pd.DataFrame({"category": ["food", "food", "toys", "toys", "toys"],
                   "rating": [4.0, None, 3.0, None, 5.0]})
df["rating"] = df["rating"].fillna(df.groupby("category")["rating"].transform("mean"))
print(df)

Output

  category  rating
0     food     4.0
1     food     4.0
2     toys     3.0
3     toys     4.0
4     toys     5.0

2.Clean a price column
Intermediate
Prices were exported as text: ["₹1,250", "₹980", "N/A", "₹2,300"]. Convert them to numbers (missing for "N/A"), and print the cleaned values and their mean.
Show hint
Remove the symbol and commas with str.replace, then pd.to_numeric(..., errors="coerce").
Show solution
Python
import pandas as pd raw = pd.Series(["₹1,250", "₹980", "N/A", "₹2,300"]) prices = pd.to_numeric(raw.str.replace("₹", "").str.replace(",", ""), errors="coerce") print(prices.tolist()) print(round(prices.mean(), 2))
Output
[1250.0, 980.0, nan, 2300.0] 1510.0
3.Standardise city names
Intermediate
Cities were typed as [" mumbai", "Mumbai ", "DELHI", "Bombay", "delhi"]. Strip spaces, use title case, map "Bombay" to "Mumbai", and print the count per city.
Show hint
str.strip().str.title(), then replace({"Bombay": "Mumbai"}).
Show solution
Python
import pandas as pd cities = pd.Series([" mumbai", "Mumbai ", "DELHI", "Bombay", "delhi"]) clean = cities.str.strip().str.title().replace({"Bombay": "Mumbai"}) print(clean.value_counts())
Output
Mumbai 3 Delhi 2 Name: count, dtype: int64

4.Latest record per customer

Intermediate

Customer updates are customer = ["A", "B", "A", "C", "B"], date = ["2026-01-05", "2026-01-07", "2026-02-01", "2026-01-20", "2026-01-02"], plan = ["basic", "pro", "pro", "basic", "basic"]. Keep only each customer's most recent row and print it sorted by customer.

Show hint

Convert the dates, sort by date, then drop_duplicates("customer", keep="last").

Show solution

Python

import pandas as pd

df = pd.DataFrame({
    "customer": ["A", "B", "A", "C", "B"],
    "date": pd.to_datetime(["2026-01-05", "2026-01-07", "2026-02-01", "2026-01-20", "2026-01-02"]),
    "plan": ["basic", "pro", "pro", "basic", "basic"],
})
latest = df.sort_values("date").drop_duplicates("customer", keep="last")
print(latest.sort_values("customer"))

Output

  customer       date   plan
2        A 2026-02-01    pro
1        B 2026-01-07    pro
3        C 2026-01-20  basic

5.Summary per department

Intermediate

Employees have dept = ["IT", "IT", "Sales", "Sales", "HR"] and salary = [80000, 72000, 55000, 61000, 48000]. Print, for each department, the headcount, average salary and highest salary, sorted by average salary from highest to lowest.

Show hint

Named aggregation, for example agg(people=("salary", "count"), ...).

Show solution

Python

import pandas as pd

df = pd.DataFrame({"dept": ["IT", "IT", "Sales", "Sales", "HR"],
                   "salary": [80000, 72000, 55000, 61000, 48000]})
summary = df.groupby("dept").agg(people=("salary", "count"),
                                 avg_salary=("salary", "mean"),
                                 top_salary=("salary", "max"))
print(summary.sort_values("avg_salary", ascending=False))

Output

       people  avg_salary  top_salary
dept
IT          2     76000.0       80000
Sales       2     58000.0       61000
HR          1     48000.0       48000

6.Pivot table with totals

Intermediate

Sales rows have region = ["N", "N", "S", "S", "N", "S"], quarter = ["Q1", "Q2", "Q1", "Q2", "Q1", "Q1"] and revenue = [100, 140, 90, 120, 60, 30]. Print a pivot table of total revenue with regions as rows, quarters as columns, and totals.

Show hint

pd.pivot_table(..., aggfunc="sum", margins=True, margins_name="Total").

Show solution

Python

import pandas as pd

df = pd.DataFrame({"region": ["N", "N", "S", "S", "N", "S"],
                   "quarter": ["Q1", "Q2", "Q1", "Q2", "Q1", "Q1"],
                   "revenue": [100, 140, 90, 120, 60, 30]})
print(pd.pivot_table(df, index="region", columns="quarter", values="revenue",
                     aggfunc="sum", margins=True, margins_name="Total"))

Output

quarter   Q1   Q2  Total
region
N        160  140    300
S        120  120    240
Total    280  260    540

7.Customers without orders

Intermediate

Customers have ids 1 to 4 (Asha, Ravi, Meera, John); orders have customer_id = [1, 2, 2, 5]. Use a merge with indicator=True to print the names of customers with no orders and the customer ids in orders that match no customer.

Show hint

An outer merge with indicator=True labels rows left_only, right_only or both.

Show solution

Python

import pandas as pd

customers = pd.DataFrame({"customer_id": [1, 2, 3, 4], "name": ["Asha", "Ravi", "Meera", "John"]})
orders = pd.DataFrame({"customer_id": [1, 2, 2, 5]})
check = pd.merge(customers, orders, on="customer_id", how="outer", indicator=True)
print(check.loc[check["_merge"] == "left_only", "name"].tolist())
print(check.loc[check["_merge"] == "right_only", "customer_id"].tolist())

Output

['Meera', 'John']
[5]

8.Share of the group total

Intermediate

For sales with team = ["A", "A", "B", "B", "B"] and amount = [300, 100, 200, 200, 600], add a column with each sale's share of its team's total as a percentage (1 decimal) and print the table.

Show hint

Divide by groupby("team")["amount"].transform("sum").

Show solution

Python

import pandas as pd

df = pd.DataFrame({"team": ["A", "A", "B", "B", "B"], "amount": [300, 100, 200, 200, 600]})
df["share_pct"] = (df["amount"] / df.groupby("team")["amount"].transform("sum") * 100).round(1)
print(df)

Output

  team  amount  share_pct
0    A     300       75.0
1    A     100       25.0
2    B     200       20.0
3    B     200       20.0
4    B     600       60.0

← All practice sets Learn Python for Data Analysis course →