Cleaning and Summarising Data

Introduction

Real data is messy, and raw rows rarely answer your question. You usually clean it, add useful columns, and then summarise. We continue with the same df.

Add a calculated column

df["revenue"] = df["price"] * df["units"]
print(df)

    product    category  price  units  revenue
0  Notebook  Stationery    3.5     10     35.0
1   Pen Set  Stationery    5.0      4     20.0
2  Backpack        Bags   29.9      2     59.8
3    Bottle     Kitchen   12.0      6     72.0
4  Notebook  Stationery    3.5      8     28.0
5  Backpack        Bags   29.9      3     89.7

pandas multiplies the two columns row by row. No loop needed.

Summarise by group

groupby splits the rows into groups, calculates something for each, and combines the answers:

print(df.groupby("category")["revenue"].sum())

category
Bags          149.5
Kitchen        72.0
Stationery     83.0
Name: revenue, dtype: float64

To put the best group first:

print(df.groupby("category")["revenue"].sum().sort_values(ascending=False))

category
Bags          149.5
Stationery     83.0
Kitchen        72.0
Name: revenue, dtype: float64

Missing values

Missing data is shown as NaN ("not a number"). Suppose one price was not recorded. You can count the gaps and decide how to handle them:

df2 = df.copy()
df2.loc[1, "price"] = None          # make one price missing
print(df2.isna().sum())             # count missing values per column
df2["price"] = df2["price"].fillna(df2["price"].median())
print(df2["price"].tolist())

product     0
category    0
price       1
units       0
revenue     0
dtype: int64
[3.5, 12.0, 29.9, 12.0, 3.5, 29.9]

Here we filled the gap with the median price, the middle value. You could also drop the row with dropna(). The right choice depends on your data, and you should say which you chose.

Other everyday checks

• df.duplicated().sum() counts repeated rows.
• df["category"].value_counts() counts how often each value appears.

Try it yourself

Add a discounted column that is 10 percent below price. Then find the total units sold per category.

Summary

• New columns can be calculated from existing ones.
• groupby summarises by category, and isna, fillna and dropna handle missing values.
• Always note how you cleaned the data, because it changes the result.