pandas for beginners: filter, group, merge and clean a table in Python

Imagine a spreadsheet you can control with code instead of a mouse. That is a pandas DataFrame: rows, named columns, and a long list of operations to filter, sort, summarise and combine them. Data engineers and analysts use it all the time for data that fits on one computer. Every output below came from really running the code.

If you know SQL, keep the first-queries guide in mind. Almost everything here is a SQL idea with different spelling, and the table at the end lines them up.

1Make a DataFrame

Real work starts by reading a file with pd.read_csv("orders.csv"). To keep this guide runnable anywhere, the data is typed in directly. It is 8 orders, and the same ones used in the SQL guides.

import pandas as pd

df = pd.DataFrame({
    "order_id": [1, 2, 3, 4, 5, 6, 7, 8],
    "customer": ["Asha", "Ravi", "Meena", "Kiran", "Asha", "Divya", "Ravi", "Meena"],
    "city":     ["Pune", "Mumbai", "Chennai", "Pune", "Pune", "Bengaluru", "Mumbai", "Chennai"],
    "amount":   [1200, 800, 450, 2500, 300, 1500, 950, 2200],
    "status":   ["paid", "paid", "refunded", "paid", "paid", "pending", "paid", "paid"],
})

print(df.shape)
print(df.columns.tolist())
print(df.head(3).to_string())

(8, 5)
['order_id', 'customer', 'city', 'amount', 'status']
   order_id customer     city  amount    status
0         1     Asha     Pune    1200      paid
1         2     Ravi   Mumbai     800      paid
2         3    Meena  Chennai     450  refunded

shape gives (rows, columns). head(3) shows the first 3 rows. The number on the far left is the index, a label for each row, and by default it counts from 0.

2Pick columns

Square brackets with a column name give you 1 column. A list of names gives you a smaller table.

print(df["amount"].sum())
print(df[["customer", "amount"]].head(3).to_string())

9900
  customer  amount
0     Asha    1200
1     Ravi     800
2    Meena     450

This is SELECT customer, amount in SQL.

3Filter rows

Put a True/False test inside the brackets, and only the True rows stay. This is WHERE.

print(df[df["amount"] > 1000].to_string())

   order_id customer       city  amount   status
0         1     Asha       Pune    1200     paid
3         4    Kiran       Pune    2500     paid
5         6    Divya  Bengaluru    1500  pending
7         8    Meena    Chennai    2200     paid

Combine tests with & for "and" and | for "or", and wrap each test in brackets. Python's and and or do not work here.

print(df[(df["city"] == "Pune") & (df["amount"] > 1000)].to_string())

   order_id customer  city  amount status
0         1     Asha  Pune    1200   paid
3         4    Kiran  Pune    2500   paid

Use isin for "any of these", the same as SQL IN:

print(df[df["city"].isin(["Mumbai", "Chennai"])]["order_id"].tolist())

[2, 3, 7, 8]

4Add a column

Assign to a new column name, and pandas calculates it for every row at once. There is no loop.

df["is_large"] = df["amount"] >= 1500
print(df[["order_id", "amount", "is_large"]].head(4).to_string())

   order_id  amount  is_large
0         1    1200     False
1         2     800     False
2         3     450     False
3         4    2500      True

Orders of 1500 or more are marked True.

5Sort

sort_values is ORDER BY. Use ascending=False for largest first.

print(df.sort_values("amount", ascending=False).head(3)[["customer", "amount"]].to_string())

  customer  amount
3    Kiran    2500
7    Meena    2200
5    Divya    1500

6Group and summarise

groupby is GROUP BY. Pick the column to group by, then say what to calculate for each group. Naming the results with agg keeps the column names clear:

summary = (
    df[df["status"] == "paid"]
    .groupby("city")
    .agg(orders=("order_id", "count"), revenue=("amount", "sum"))
    .reset_index()
    .sort_values("revenue", ascending=False)
)
print(summary.to_string(index=False))

   city  orders  revenue
   Pune       3     4000
Chennai       1     2200
 Mumbai       2     1750

First the refunded and pending orders were filtered out, then the paid ones were grouped by city. reset_index() turns the group names back into a normal column, which is what you want most of the time.

7Merge 2 tables

merge is JOIN. You need a column the 2 tables share. Here is a second table with each customer's segment, and notice that Divya is missing from it.

segments = pd.DataFrame({
    "customer": ["Asha", "Ravi", "Meena", "Kiran"],
    "segment":  ["gold", "silver", "gold", "bronze"],
})

joined = df.merge(segments, on="customer", how="left")
print(joined[["order_id", "customer", "segment"]].to_string(index=False))

 order_id customer segment
        1     Asha    gold
        2     Ravi  silver
        3    Meena    gold
        4    Kiran  bronze
        5     Asha    gold
        6    Divya     NaN
        7     Ravi  silver
        8    Meena    gold

how="left" keeps every order, like a SQL LEFT JOIN, and Divya's order has no segment, so it shows NaN. The other choices are "inner", "right" and "outer", the same as in the joins guide.

8Missing values

NaN means "not a number", pandas' way of saying a value is missing. First count them, per column:

print(joined.isna().sum().to_string())

order_id    0
customer    0
city        0
amount      0
status      0
is_large    0
segment     1

Only segment has gaps, and there is one. You then choose what to do: fill the gap with a default, or drop the row.

filled = joined.assign(segment=joined["segment"].fillna("unknown"))
print(filled[filled["customer"] == "Divya"][["customer", "segment"]].to_string(index=False))
print(len(joined.dropna()), "rows left if you drop rows with any gap")

customer segment
   Divya unknown
7 rows left if you drop rows with any gap

Neither choice is always right. Filling keeps all your orders, and dropping loses Divya's order from the result. Decide based on the question, as with NULLs in SQL.

9Dates

Dates from a file arrive as plain text. Convert them, and pandas can pull out the parts.

dates = pd.DataFrame({
    "order_date": ["2026-03-01", "2026-03-15", "2026-04-02", "2026-04-20"],
    "amount": [1200, 800, 450, 2500],
})
dates["order_date"] = pd.to_datetime(dates["order_date"])
dates["month"] = dates["order_date"].dt.month

print(dates.groupby("month")["amount"].sum().reset_index().to_string(index=False))

 month  amount
     3    2000
     4    2950

March has 1200 + 800 and April has 450 + 2500. The .dt part gives access to the month, year, weekday and more.

10pandas and SQL side by side

When should you use which? SQL runs inside the database, so use it for big tables and the first heavy steps. pandas runs on your computer's memory, so use it for data that fits there, and for cleaning and reshaping that is awkward in SQL. For data too big for one machine, the same ideas continue in PySpark.

Mistakes to avoid

• Using `and` instead of `&` when filtering. It raises an error.
• Forgetting the brackets around each test in a combined filter.
• Expecting a function to change the table in place. Most pandas operations return a new table, so keep the result in a variable.
• Ignoring NaN. Count the missing values first, then decide what to do.

Keep going with the Pandas for Data Manipulation track, and try the free Pivot Table Creation problem to practise reshaping.