pandas for Real Data

If NumPy is the engine, pandas is the steering wheel. It gives your data names, and lets you ask questions in almost plain English. Your boss at HomeWorth has a question already. Are rich districts really that much more expensive? Let's find out.

• Named columns, plain questions
• Your boss has a question
• Are rich districts expensive?

Asking questions

We load the housing table. Describe gives a summary of each column, the count, mean, minimum, and maximum. Notice that house value stops at exactly five. That is suspicious, and we will return to it. Now we filter. Inside the square brackets is a condition, income above eight. Pandas keeps only the rows that match. There are six hundred and ninety of them, and their mean price is more than double the overall average. Your boss has an answer, in four lines.

import pandas as pd
from sklearn.datasets import fetch_california_housing

df = fetch_california_housing(as_frame=True).frame

print(df[["MedInc", "HouseAge", "MedHouseVal"]].describe().round(2).to_string())
print()
rich = df[df["MedInc"] > 8]
print("Rich districts:", len(rich))
print("Their mean price:", round(rich["MedHouseVal"].mean(), 2))

         MedInc  HouseAge  MedHouseVal
count  20640.00  20640.00     20640.00
mean       3.87     28.64         2.07
std        1.90     12.59         1.15
min        0.50      1.00         0.15
25%        2.56     18.00         1.20
50%        3.53     29.00         1.80
75%        4.74     37.00         2.65
max       15.00     52.00         5.00

Rich districts: 690
Their mean price: 4.61

New columns and groups

Pandas also lets us create new columns, and this is where machine learning really begins. Rooms per person is a brand new fact, made by dividing two columns. Cut turns house age into three groups. Then group by does something wonderful. It splits the table by age group, takes the mean price of each, and joins the results together. In three lines, we have learned that older districts in this data are not cheaper, which you might not have expected.

import pandas as pd
from sklearn.datasets import fetch_california_housing

df = fetch_california_housing(as_frame=True).frame
df["rooms_per_person"] = df["AveRooms"] / df["AveOccup"]
df["age_group"] = pd.cut(df["HouseAge"], [0, 20, 40, 60],
                         labels=["new", "mid", "old"])

summary = df.groupby("age_group", observed=True)["MedHouseVal"].mean()
print(summary.round(2).to_string())
print()
print(df["rooms_per_person"].describe().round(2).to_string())

age_group
new    1.93
mid    2.07
old    2.29

count    20640.00
mean         1.98
std          1.15
min          0.00
25%          1.52
50%          1.94
75%          2.30
max         55.22

The six moves

Almost everything you do in pandas uses six moves. Select the columns you want. Filter the rows you want. Create new columns. Group and summarise. Sort. And join two tables together. Practise these six, and you can answer nearly any question about a table.

Summary