Pandas DataFrames and Series

Real-World Scenario

An analyst gets an export of customer orders and has to pull out a few columns, keep only the rows that matter, add computed fields, and sort the result. Those four moves — select, filter, compute, sort — are most of daily Pandas work.

Selecting Columns

Single brackets give you one column; a list inside the brackets gives you a DataFrame.

import pandas as pd

df = pd.DataFrame({
    "customer": ["Alice", "Bob", "Carol"],
    "product":  ["Laptop", "Mouse", "Laptop"],
    "count":    [1, 2, 1],
})

print(type(df["product"]))
print(type(df[["customer", "product"]]))
print(df[["customer", "product"]])
print(df.customer.tolist())
print(type(df.product), type(df.count))

$ python select_cols.py
<class 'pandas.Series'>
<class 'pandas.DataFrame'>
  customer product
0    Alice  Laptop
1      Bob   Mouse
2    Carol  Laptop
['Alice', 'Bob', 'Carol']
<class 'method'> <class 'method'>

df["product"] is a Series and df[[...]] is a DataFrame — the double brackets are a list of names. Attribute access worked for df.customer, but df.product and df.count both came back as methods: DataFrame.product() and DataFrame.count() exist, and method names win over column names. Bracket access has no such trap.

Rows by Label and by Position

loc works with index labels; iloc works with integer positions.

import pandas as pd

df = pd.DataFrame({
    "product": ["Laptop", "Mouse", "Keyboard", "Monitor", "Webcam"],
    "price":   [1200.0, 25.0, 45.0, 350.0, 80.0],
}, index=["P001", "P002", "P003", "P004", "P005"])

print(df.loc["P001", "price"])
print(df.loc["P002":"P004"])
print(df.iloc[1:4])
print(df.loc[["P001", "P004"], ["product"]])

$ python loc_iloc.py
1200.0
       product  price
P002     Mouse   25.0
P003  Keyboard   45.0
P004   Monitor  350.0
       product  price
P002     Mouse   25.0
P003  Keyboard   45.0
P004   Monitor  350.0
      product
P001   Laptop
P004  Monitor

The two slices return the same three rows by different rules. The label slice "P002":"P004" includes its end label, while the position slice 1:4 stops before position 4, like any Python slice. A list of labels picks rows out of order, and wrapping "product" in a list keeps the result a DataFrame.

When loc and iloc Disagree

With an integer index, loc[0] means “the row labelled 0”, which is not always the first row.

import pandas as pd

df = pd.DataFrame({"name": ["Alice", "Bob", "Carol"],
                   "salary": [95000, 88000, 105000]})
ranked = df.sort_values("salary", ascending=False)

print(ranked)
print("loc[0]: ", ranked.loc[0, "name"])
print("iloc[0]:", ranked.iloc[0]["name"])

$ python loc_vs_iloc.py
    name  salary
2  Carol  105000
0  Alice   95000
1    Bob   88000
loc[0]:  Alice
iloc[0]: Carol

After sorting, the row labelled 0 (Alice) sits in the middle. loc[0] still finds Alice by her label, while iloc[0] takes whatever is physically first — Carol, the top earner. Code that uses loc[0] to mean “the first row” works until the day the data gets sorted.

Filtering with Boolean Masks

A comparison produces a True/False Series; indexing with it keeps the True rows. Combining two conditions needs care.

import pandas as pd

df = pd.DataFrame({
    "customer": ["Alice", "Bob", "Carol", "Dave", "Eve"],
    "country":  ["US", "UK", "US", "DE", "US"],
    "revenue":  [4500, 1200, 8900, 3400, 6700],
})

try:
    print(df[df["country"] == "US" & df["revenue"] > 5000])
except TypeError as e:
    print("TypeError:", e)

print(df[(df["country"] == "US") & (df["revenue"] > 5000)])

$ python filter_bug.py
TypeError: Cannot perform 'rand_' with a dtyped [int64] array and scalar of type [bool]
  customer country  revenue
2    Carol      US     8900
4      Eve      US     6700

Without parentheses, Python groups the expression as df["country"] == ("US" & df["revenue"]) > 5000, because & binds more tightly than == and >. The error’s rand_ is that reflected &, attempted against the int64 revenue column — nothing about the message mentions the real mistake. With each condition in its own parentheses, & combines two boolean Series, and only Carol and Eve pass both tests.

isin() and query()

isin tests membership in a list, and query expresses the same filters as a string.

import pandas as pd

df = pd.DataFrame({
    "customer": ["Alice", "Bob", "Carol", "Dave", "Eve"],
    "country":  ["US", "UK", "US", "DE", "US"],
    "revenue":  [4500, 1200, 8900, 3400, 6700],
})

print(df[df["country"].isin(["UK", "DE"])])
min_revenue = 5000
print(df.query("country == 'US' and revenue > @min_revenue"))

$ python query.py
  customer country  revenue
1      Bob      UK     1200
3     Dave      DE     3400
  customer country  revenue
2    Carol      US     8900
4      Eve      US     6700

Inside query you can write and, because Pandas parses the string itself rather than handing it to Python. The @ prefix pulls in the local variable min_revenue; without it, query would look for a column with that name.

Adding Computed Columns

New columns come from arithmetic on existing ones, np.where for two-way choices, and np.select for several.

import numpy as np
import pandas as pd

df = pd.DataFrame({"product": ["Laptop", "Mouse", "Keyboard", "Monitor"],
                   "cost": [800.0, 15.0, 30.0, 200.0],
                   "quantity": [50, 300, 200, 20]})

df["price"] = df["cost"] * 1.5
df["total"] = df["price"] * df["quantity"]
df["big"] = np.where(df["total"] > 10000, "yes", "no")
df["tier"] = np.select([df["total"] > 50000, df["total"] > 8000],
                       ["High", "Medium"], default="Low")
df["label"] = df["product"].str.upper().str[:3]
print(df)

$ python new_cols.py
    product   cost  quantity   price    total  big    tier label
0    Laptop  800.0        50  1200.0  60000.0  yes    High   LAP
1     Mouse   15.0       300    22.5   6750.0   no     Low   MOU
2  Keyboard   30.0       200    45.0   9000.0   no  Medium   KEY
3   Monitor  200.0        20   300.0   6000.0   no     Low   MON

np.select checks its conditions in order and takes the first match, so the Laptop row (60,000) is High even though it also passes the > 8000 test. The Monitor row’s 6,000 passes neither and gets the default. The .str accessor runs string methods on every value in the column at once.

Sorting

sort_values accepts several columns, each with its own direction.

import pandas as pd

df = pd.DataFrame({
    "name":   ["Alice", "Bob", "Carol", "Dave", "Eve"],
    "dept":   ["Eng", "Eng", "Sales", "Sales", "Eng"],
    "salary": [95000, 88000, 72000, 76000, 105000],
})

print(df.sort_values(["dept", "salary"], ascending=[True, False]))
print(df.nlargest(2, "salary"))

$ python sort.py
    name   dept  salary
4    Eve    Eng  105000
0  Alice    Eng   95000
1    Bob    Eng   88000
3   Dave  Sales   76000
2  Carol  Sales   72000
    name dept  salary
4    Eve  Eng  105000
0  Alice  Eng   95000

Rows are ordered by dept first, and salary only breaks ties within a department — so Eve leads Eng, but Dave’s 76,000 still comes after every Eng row. nlargest is a shortcut for “sort descending, take the top n”.

Exploring a Column

value_counts, unique, and isna are the quickest way to understand a new column.

import pandas as pd

orders = pd.DataFrame({
    "status":  ["shipped", "delivered", "shipped", None, "delivered", "shipped"],
    "country": ["US", "UK", "US", "DE", "US", "UK"],
})

print(orders["status"].value_counts())
print(orders["status"].value_counts(normalize=True).round(2))
print(orders["country"].unique(), orders["country"].nunique())
print(orders.isna().sum())

$ python explore.py
status
shipped      3
delivered    2
Name: count, dtype: int64
status
shipped      0.6
delivered    0.4
Name: proportion, dtype: float64
<StringArray>
['US', 'UK', 'DE']
Length: 3, dtype: str 3
status     1
country    0
dtype: int64

value_counts silently skips the missing status, so its counts add up to 5, not 6, and the proportions are shares of those 5. isna().sum() is where the missing row shows up. Pass dropna=False to value_counts when missing values matter to the answer.

In Pandas 3, unique() on a text column returns a StringArray rather than a NumPy array.

Practice

1. In loc_iloc.py, select the price of the last two products using iloc only.

import pandas as pd

df = pd.DataFrame({
    "product": ["Laptop", "Mouse", "Keyboard", "Monitor", "Webcam"],
    "price":   [1200.0, 25.0, 45.0, 350.0, 80.0],
}, index=["P001", "P002", "P003", "P004", "P005"])

print(df.iloc[-2:, 1])

$ python practice_iloc.py
P004    350.0
P005     80.0
Name: price, dtype: float64

2. In filter_bug.py, keep the customers who are not in the US or who earn under 2,000.

import pandas as pd

df = pd.DataFrame({
    "customer": ["Alice", "Bob", "Carol", "Dave", "Eve"],
    "country":  ["US", "UK", "US", "DE", "US"],
    "revenue":  [4500, 1200, 8900, 3400, 6700],
})

print(df[~(df["country"] == "US") | (df["revenue"] < 2000)])

$ python practice_or.py
  customer country  revenue
1      Bob      UK     1200
3     Dave      DE     3400

3. In explore.py, make value_counts report the missing status as its own row.

import pandas as pd

status = pd.Series(["shipped", "delivered", "shipped", None, "delivered", "shipped"])
print(status.value_counts(dropna=False))

$ python practice_counts.py
shipped      3
delivered    2
NaN          1
Name: count, dtype: int64

4. In new_cols.py, add a margin column equal to price - cost, then print only the products whose margin is above 100, sorted from highest margin down.

import pandas as pd

df = pd.DataFrame({"product": ["Laptop", "Mouse", "Keyboard", "Monitor"],
                   "cost": [800.0, 15.0, 30.0, 200.0]})
df["price"] = df["cost"] * 1.5
df["margin"] = df["price"] - df["cost"]
print(df[df["margin"] > 100].sort_values("margin", ascending=False))

$ python practice_margin.py
  product   cost   price  margin
0  Laptop  800.0  1200.0   400.0