DataFrames

Part of the free LogicWiz Python course, Module 10: NumPy and pandas. The examples and practice run in the app.

Shankar bhaiya's order book has rows (one per order) and columns (item, qty, price). That is a table, and in pandas a table is a DataFrame: the thing data analysts use all day.

You'll learn to

• Create a DataFrame from a dict of lists or a list of dicts
• Read its shape, columns, index and dtypes
• Select rows and columns with `[]`, `loc` and `iloc`
• Filter rows with Boolean conditions

Learn

A table of Series

A DataFrame is a 2D table: rows and named columns. Each column is a Series, and all columns share one index (the row labels).

You can build one from:

• a dict of lists: each key is a column
• a list of dicts: each dict is a row (handy for data from JSON or a form)

Later (10.6) you'll load one straight from a CSV file.

Making a DataFrame

import pandas as pd

orders = pd.DataFrame({
    "item": ["Chai", "Samosa", "Dosa", "Chai"],
    "qty": [3, 2, 1, 5],
    "price": [10, 15, 60, 10],
})
print(orders)
print(orders.shape, orders.columns.tolist(), orders.index.tolist())

rows = [{"name": "Riya", "marks": 82}, {"name": "Kabir", "marks": 91}]
print(pd.DataFrame(rows))

Picking columns

import pandas as pd

orders = pd.DataFrame({
    "item": ["Chai", "Samosa", "Dosa", "Chai"],
    "qty": [3, 2, 1, 5],
    "price": [10, 15, 60, 10],
})
print(orders["qty"])                 # one column: a Series
print(orders[["item", "qty"]])       # a list of columns: a DataFrame
print(orders["qty"].sum(), orders.qty.max())
print(orders.dtypes)

orders.qty also works for simple column names, but orders["qty"] always works (even for names with spaces).

loc and iloc on a table

import pandas as pd

students = pd.DataFrame({"eng": [78, 65, 90], "maths": [92, 58, 85]},
                        index=["Riya", "Kabir", "Meher"])
print(students.loc["Kabir"])                 # one row, as a Series
print(students.loc["Riya", "maths"])         # one cell, by labels
print(students.iloc[2, 0])                   # one cell, by positions
print(students.loc["Riya":"Kabir", "maths"].tolist())
print(students.iloc[:2])                     # first two rows

Both take [rows, columns]. loc uses labels (slices include the end); iloc uses positions (slices exclude it).

Filtering rows

import pandas as pd

orders = pd.DataFrame({
    "item": ["Chai", "Samosa", "Dosa", "Chai"],
    "qty": [3, 2, 1, 5],
    "price": [10, 15, 60, 10],
})
print(orders[orders["qty"] >= 3])
print(orders[(orders["item"] == "Chai") & (orders["qty"] > 3)])
print(orders.loc[orders["price"] > 12, "item"].tolist())
print(orders[orders["item"].isin(["Dosa", "Samosa"])])

The mask orders["qty"] >= 3 is a Series of True/False, one per row. Putting it inside orders[...] keeps the True rows, with their original index labels.

Two classic slips:

• df["item", "qty"] raises KeyError. A list of columns needs double brackets: df[["item", "qty"]].
• Conditions must be combined with & / |, each in brackets, never with and / or (the same rule as NumPy).

CBSE IP practical: "Create a DataFrame for examination results and display its row labels, column labels, data types of each column and its dimensions." That is df.index, df.columns, df.dtypes and df.shape. Other favourites: filter rows ("students with marks above 75"), and pick a cell or row with loc/iloc.

In one line

A DataFrame is a table of Series sharing one index; df["col"] is a Series, df[["a", "b"]] a DataFrame; loc[rows, cols] by label, iloc by position; filter with df[mask] and &/|.

Interview corner

What is a DataFrame?

A 2D labelled table in pandas, made of columns that are Series sharing one row index. Columns can have different dtypes. It supports selection by label (loc) or position (iloc), Boolean filtering, grouping, joining and reading/writing files such as CSV.

How do you select rows by a condition on multiple columns?

Build Boolean masks and combine them with & (and), | (or) and ~ (not), each condition in brackets: df[(df.item == "Chai") & (df.qty > 3)]. Alternatives are df.query("item == 'Chai' and qty > 3") and df.loc[mask, cols] to also pick columns.

What does `df.loc[0:2]` return compared with `df.iloc[0:2]`?

loc is label-based and inclusive, so it returns rows labelled 0, 1 and 2 (if they exist). iloc is position-based and exclusive, so it returns the first two rows. They differ whenever the index is not the default 0..n-1.