Pandas Essentials: Tables for ML Data
Your user table now has a text column,
plan, next to the numbers, and a column of 0s and 1s that says who later left. Some cells are empty. You want to know which plan loses more users, and you want a clean X and y to hand to a model. A NumPy array has no column names and one dtype. pandas gives you a labelled table that reads files, finds the gaps and groups the rows.Learning Objectives
After this lesson, you will be able to:
- Build a DataFrame, read it from and write it to a CSV file, and say what a Parquet file keeps that a CSV loses
- Select rows and columns with loc and iloc, and predict what a filtered table's labels mean
- Filter rows with boolean masks combined by &, | and ~
- Find missing values, then drop or fill them on purpose
- Summarise groups with groupby and value_counts, and turn selected columns into X and y
Prerequisites
Before You Start
This lesson uses the NumPy basics from the previous lesson (shape, dtype, masks,
axis), and nothing else from it. Install the library with pip install pandas in your virtual environment. In this page's code cells it is already available, running pandas 2.2.#The Problem: The Same Feature Table, Now with a Plan
The four users from the NumPy lesson now have a
plan as well: sessions, minutes and plan. This is invented sample data. Put it in one array and try to average the minutes.pythonrunnable cell
import numpy as np
rows = [[3, 42.0, "free"], [10, 15.5, "pro"], [1, 8.0, "free"], [7, 30.5, "pro"]]
X = np.array(rows)
print(X.dtype.kind)
print(X[:, 1])
try:
X[:, 1].mean()
except TypeError:
print("TypeError: cannot average text")Output:
text
U
['42.0' '15.5' '8.0' '30.5']
TypeError: cannot average text
An array has one dtype, so the text in
plan turned every number into text. The minutes are now the strings '42.0', '15.5' and so on, and an average of strings fails. There is a second problem you cannot see in the output: nothing in X says that column 1 is minutes. You have to remember it.A pandas DataFrame is a table in which each column has its own dtype and its own name. Import the library by convention as
pd.#pandas: Series and DataFrame
A
Series is a one-dimensional array with an index of labels. A DataFrame is a set of Series (the columns) sharing one index.pythonrunnable cell
import pandas as pd
df = pd.DataFrame({
"sessions": [3, 10, 1, 7],
"minutes": [42.0, 15.5, 8.0, 30.5],
"plan": ["free", "pro", "free", "pro"],
})
print(df)
print(df["minutes"].mean())
print(df.shape, list(df.columns))
print(df[["sessions", "minutes"]].dtypes)
print(type(df["minutes"]).__name__, type(df[["minutes"]]).__name__)
print(df["sessions"] * 2)Output:
text
sessions minutes plan
0 3 42.0 free
1 10 15.5 pro
2 1 8.0 free
3 7 30.5 pro
24.0
(4, 3) ['sessions', 'minutes', 'plan']
sessions int64
minutes float64
dtype: object
Series DataFrame
0 6
1 20
2 2
3 14
Name: sessions, dtype: int64
The numbers kept their types, the text sits beside them, and the average of
minutes works. Arithmetic on a Series is vectorised like an array and keeps the index. One column in single brackets gives a Series, and a list of columns in double brackets gives a DataFrame. The left-hand numbers 0 to 3 are the index: a label for each row, which is a position by default and can be something else, as you will see below.#Reading and Writing a CSV
Real data lives in files. A CSV (comma-separated values) file is plain text, one row per line, with a header line of column names. The cell below first writes a small CSV so it runs anywhere, then reads it back. Two cells are empty on purpose: user
u5 has no minutes and u8 has no sessions.pythonrunnable cell
from pathlib import Path
import pandas as pd
Path("users.csv").write_text("""user_id,sessions,minutes,plan,churned
u1,3,42.0,free,0
u2,10,15.5,pro,1
u3,1,8.0,free,0
u4,7,30.5,pro,1
u5,5,,free,0
u6,12,55.0,pro,0
u7,2,9.5,free,1
u8,,21.0,pro,0
u9,8,33.0,free,0
u10,4,12.5,pro,1
""")
df = pd.read_csv("users.csv")
print(df)
print(df.shape)
print(df[["sessions", "minutes", "churned"]].dtypes)
print(df.describe().round(2))Output:
text
user_id sessions minutes plan churned
0 u1 3.0 42.0 free 0
1 u2 10.0 15.5 pro 1
2 u3 1.0 8.0 free 0
3 u4 7.0 30.5 pro 1
4 u5 5.0 NaN free 0
5 u6 12.0 55.0 pro 0
6 u7 2.0 9.5 free 1
7 u8 NaN 21.0 pro 0
8 u9 8.0 33.0 free 0
9 u10 4.0 12.5 pro 1
(10, 5)
sessions float64
minutes float64
churned int64
dtype: object
sessions minutes churned
count 9.00 9.00 10.00
mean 5.78 25.22 0.40
std 3.73 16.10 0.52
min 1.00 8.00 0.00
25% 3.00 12.50 0.00
50% 5.00 21.00 0.00
75% 8.00 33.00 1.00
max 12.00 55.00 1.00
Look at the
sessions column: it printed as 3.0, not 3. The missing cell became NaN, a float marker for "not a number", and an integer column cannot hold it, so pandas made the whole column float. That is a useful clue that a column has gaps.First inspection routine for any new file:
df.shape, df.head(), df.dtypes, df.describe(), and df.isna().sum() (shown below). The describe counts are lower than the row count for columns with gaps.Writing goes the other way with
to_csv. Pass index=False unless the row labels carry meaning, because otherwise the index is written as an extra unnamed first column, and reading the file back gives you that column as data.pythonrunnable cell
import pandas as pd
df = pd.read_csv("users.csv")
df.to_csv("copy.csv") # index written as a first column
df.to_csv("clean.csv", index=False)
print(pd.read_csv("copy.csv").columns.tolist())
print(pd.read_csv("clean.csv").columns.tolist())
print(pd.read_csv("copy.csv", index_col=0).columns.tolist())Output:
text
['Unnamed: 0', 'user_id', 'sessions', 'minutes', 'plan', 'churned']
['user_id', 'sessions', 'minutes', 'plan', 'churned']
['user_id', 'sessions', 'minutes', 'plan', 'churned']
The first line shows the trap: an
Unnamed: 0 column that is just the old row numbers. Either write with index=False, or read with index_col=0 to turn that first column back into the index.#Parquet: Typed Columns for Pipelines
A CSV is text, so it stores no types. Every
read_csv has to guess them again, and some guesses lose information. Parquet is a binary, column-oriented file format that stores each column's type with the data. Data pipelines commonly use it for intermediate and training tables for that reason. First see what the CSV loses, with a runnable cell.pythonrunnable cell
import pandas as pd
df = pd.DataFrame({
"user_id": ["u1", "u2", "u3"],
"zip": ["07030", "10001", "94105"],
"plan": pd.Categorical(["free", "pro", "free"]),
"signup": pd.to_datetime(["2024-01-05", "2024-02-11", "2024-03-20"]),
"minutes": [42.0, 15.5, 8.0],
})
df.to_csv("typed.csv", index=False)
back = pd.read_csv("typed.csv")
print(back["zip"].tolist())
print(back["plan"].dtype == "category")
print(type(back["signup"].iloc[0]).__name__)
fixed = pd.read_csv("typed.csv", dtype={"zip": str}, parse_dates=["signup"])
print(fixed["zip"].tolist())
print(type(fixed["signup"].iloc[0]).__name__)Output:
text
[7030, 10001, 94105]
False
str
['07030', '10001', '94105']
Timestamp
The zip code
07030 came back as the number 7030, losing its leading zero. The categorical plan came back as plain text, and the signup dates came back as strings. You can repair each one with dtype= and parse_dates= when you read, but you have to remember to, every time, for every column. Parquet keeps the types, so none of that is needed.Parquet needs a library that pandas calls to read and write it, usually
pyarrow (pip install pyarrow). The Python in this page's browser is Pyodide 0.26.4, and that version has no pyarrow package, so the cell below is read-only: it has no Run button, and you run it on your own machine after installing the library. The output shown was produced with pandas 2.2.0 and pyarrow 25.0.1 on desktop Python.pythonreference · read-only
import pyarrow # noqa: F401 needs: pip install pyarrow
import pandas as pd
df = pd.DataFrame({
"user_id": ["u1", "u2", "u3"],
"zip": ["07030", "10001", "94105"],
"plan": pd.Categorical(["free", "pro", "free"]),
"signup": pd.to_datetime(["2024-01-05", "2024-02-11", "2024-03-20"]),
"minutes": [42.0, 15.5, 8.0],
})
df.to_parquet("users.parquet", index=False)
back = pd.read_parquet("users.parquet")
print(back["zip"].tolist())
print(back["plan"].dtype.name)
print(type(back["signup"].iloc[0]).__name__)
few = pd.read_parquet("users.parquet", columns=["user_id", "minutes"])
print(few.shape, list(few.columns))Output:
text
['07030', '10001', '94105']
category
Timestamp
(3, 2) ['user_id', 'minutes']
The leading zero, the category and the timestamps all survive the round trip. The last two lines show a second benefit of a column-oriented file:
columns=[...] reads only the columns you ask for, which matters when a table has hundreds of them. Keep CSV for files people open in a spreadsheet, and use Parquet between the steps of a pipeline. The rest of this lesson reads the CSV, because the browser can.#Selecting with loc and iloc
iloc selects by position, like a list. loc selects by label. Both take [rows, columns].pythonrunnable cell
import pandas as pd
df = pd.read_csv("users.csv")
print(df.iloc[0]) # first row, by position
print(df.iloc[0:3, 1:3]) # first three rows, columns 1 and 2
print(df.loc[2, "plan"]) # row labelled 2, column "plan"
by_user = df.set_index("user_id")
print(by_user.loc["u4", "minutes"])
print(by_user.loc["u2":"u4", ["sessions", "plan"]])Output:
text
user_id u1
sessions 3.0
minutes 42.0
plan free
churned 0
Name: 0, dtype: object
sessions minutes
0 3.0 42.0
1 10.0 15.5
2 1.0 8.0
free
30.5
sessions plan
user_id
u2 10.0 pro
u3 1.0 free
u4 7.0 pro
With the default index (0, 1, 2 ...) a label and a position look the same, which hides the difference. Once the index is something else, such as user ids,
loc["u4"] works and iloc["u4"] raises a TypeError. Note also that a loc slice includes its end label ("u2":"u4" gives three rows), while an iloc slice excludes the end, like a list.#Filtering Rows
Filtering a DataFrame is a mask, exactly as in NumPy. Combine conditions with
&, | and ~, with parentheses around each one.pythonrunnable cell
import pandas as pd
df = pd.read_csv("users.csv")
pro = df[df["plan"] == "pro"]
print(pro)
heavy_pro = df[(df["plan"] == "pro") & (df["sessions"] >= 8)]
print(heavy_pro)
print(df["plan"].isin(["pro"]).sum())Output:
text
user_id sessions minutes plan churned
1 u2 10.0 15.5 pro 1
3 u4 7.0 30.5 pro 1
5 u6 12.0 55.0 pro 0
7 u8 NaN 21.0 pro 0
9 u10 4.0 12.5 pro 1
user_id sessions minutes plan churned
1 u2 10.0 15.5 pro 1
5 u6 12.0 55.0 pro 0
5
A filtered DataFrame keeps the original index labels. That causes the most common
loc versus iloc surprise.What Do You Think?
f = df[df['sessions'] > 5] keeps rows with index labels 1, 3, 5 and 8. What do f.iloc[1] and f.loc[1] return?
#Missing Values
Missing cells are
NaN. First find them, then decide what to do. Dropping rows loses data, and filling invents it, so pick the choice that suits the column.pythonrunnable cell
import numpy as np
import pandas as pd
df = pd.read_csv("users.csv")
print(df.isna().sum())
print(df[df["minutes"].isna()])
print(df["minutes"].mean(), df["minutes"].count(), len(df))
print(df.dropna().shape)
filled = df.copy()
for col in ["sessions", "minutes"]:
filled[col] = filled[col].fillna(filled[col].median())
print(filled.isna().sum().sum())
print(np.nan == np.nan)Output:
text
user_id 0
sessions 1
minutes 1
plan 0
churned 0
dtype: int64
user_id sessions minutes plan churned
4 u5 5.0 NaN free 0
25.22222222222222 9 10
(8, 5)
0
False
isna().sum() counts gaps per column. pandas skips NaN when it computes a mean: the mean above is over 9 values while the table has 10 rows. A NumPy array does not skip: its mean would be nan, as the previous lesson showed. dropna() removed two rows here, a fifth of this tiny table. Filling with the median keeps every row and is less affected by outliers than the mean. For real model training you would compute that fill value on the training rows only, and reuse it on test data.The last line prints
False: NaN is not equal to anything, itself included. So df["minutes"] == np.nan is never true. Always use .isna().#groupby and value_counts
groupby splits rows by a column's values, applies a summary to each group, and puts the results in a table. value_counts counts how often each value occurs.pythonrunnable cell
import pandas as pd
df = pd.read_csv("users.csv")
for col in ["sessions", "minutes"]:
df[col] = df[col].fillna(df[col].median())
print(df.groupby("plan")["churned"].mean())
summary = df.groupby("plan").agg(
n=("user_id", "count"),
avg_minutes=("minutes", "mean"),
churn_rate=("churned", "mean"),
).round(2)
print(summary)
print(df["plan"].value_counts())
print(df["churned"].value_counts(normalize=True))Output:
text
plan
free 0.2
pro 0.6
Name: churned, dtype: float64
n avg_minutes churn_rate
plan
free 5 22.7 0.2
pro 5 26.9 0.6
plan
free 5
pro 5
Name: count, dtype: int64
churned
0 0.6
1 0.4
Name: proportion, dtype: float64
Because
churned is 0 or 1, its mean is the churn rate: 0.2 for the free plan and 0.6 for the pro plan in this sample. With only five users per plan, that is not evidence of anything. It shows the mechanics, and on a real dataset you would also check how many rows each group has, which is what the n column is for. value_counts(normalize=True) gives fractions, and it is the quickest check of how balanced your labels are before you train.#From a Table Back to X and y
Adding a column is an assignment, and the arithmetic is vectorised like NumPy's. Selecting the feature columns and calling
to_numpy() then hands you the arrays from the previous lesson.pythonrunnable cell
import pandas as pd
df = pd.read_csv("users.csv").dropna()
df["minutes_per_session"] = (df["minutes"] / df["sessions"]).round(2)
print(df.head(4))
X = df[["sessions", "minutes"]].to_numpy()
y = df["churned"].to_numpy()
print(X.shape, y.shape)Output:
text
user_id sessions minutes plan churned minutes_per_session
0 u1 3.0 42.0 free 0 14.00
1 u2 10.0 15.5 pro 1 1.55
2 u3 1.0 8.0 free 0 8.00
3 u4 7.0 30.5 pro 1 4.36
(8, 2) (8,)
This is the bridge back to NumPy: an
X of shape (n_samples, n_features) and a y of shape (n_samples,), with the names left behind in the table.#Common Mistakes
Chained assignment
df[mask]["col"] = value is two steps: the first, df[mask], may make a temporary copy, and the second assigns into that copy. The original table does not change, and pandas warns you. The warning is a SettingWithCopyWarning in pandas 2, which is what this page runs, and a ChainedAssignmentError in pandas 3. The cell catches the warning so you can see it was raised, whichever version you have.pythonrunnable cell
import warnings
import pandas as pd
df = pd.DataFrame({"plan": ["free", "pro", "pro"], "x": [1, 2, 3]})
with warnings.catch_warnings(record=True) as caught:
warnings.simplefilter("always")
df[df["plan"] == "pro"]["x"] = 99 # assigns into a temporary copy
print(df["x"].tolist(), "warned:", len(caught) > 0)
df.loc[df["plan"] == "pro", "x"] = 99 # one step, changes df
print(df["x"].tolist())Output:
text
[1, 2, 3] warned: True
[1, 99, 99]
Assign through
df.loc[mask, "col"] = value, in one step.Using and instead of &
and, or and not ask for one True or False. Masks are arrays of them, so df[(df["plan"] == "pro") and (df["sessions"] > 5)] raises ValueError: The truth value of a Series is ambiguous. Use &, | and ~, with parentheses around each comparison.Comparing to NaN
df["x"] == np.nan is False everywhere. Use df["x"].isna() to find gaps, as in the missing values section.Writing the index into the CSV
df.to_csv("f.csv") writes the row labels as a first column, and read_csv brings it back as Unnamed: 0. Write with index=False.Trusting the guessed types
read_csv guesses each column's type from the text. A column of ids with leading zeros becomes numbers, dates stay text, and one empty cell turns an integer column into floats. Check df.dtypes after loading, and pass dtype= and parse_dates= when the guess is wrong.#Try It
You have the users file from earlier. In the starter, fill in the steps: fill missing numbers with the column median, build
X and y for a model, report the churn rate of heavy users (sessions above the median) against the rest, then write the cleaned table to a CSV and read it back to confirm that nothing was lost.pythonplayground.py · Pyodide
Tests · Expected output: (10, 2) (10,), then 0.5 0.33, then {'free': 3.0, 'pro': 7.0}, then True
For step 3, a mask like
df["sessions"] > df["sessions"].median() selects the heavy users, and .mean() on churned gives the rate. For step 5, DataFrame.equals compares two tables, including their dtypes. If it prints False, check whether you forgot index=False.#Going Further
The next lesson, Pandas Advanced, covers combining tables with
merge, pivot tables, apply, and time series. The plotting lesson after it draws arrays and DataFrames. For how tables feed models, the Python for ML lesson builds on exactly the X and y you made here. Reading from a database instead of a file is the database lesson's job. pandas can also read JSON with read_json, and write Parquet with options such as compression, which the pandas documentation lists.#Key Takeaways
Recap
Key Takeaways
- A DataFrame is labelled columns around arrays, each column with its own dtype. One bracket pair gives a Series, a list inside brackets gives a DataFrame.
- read_csv guesses types, and one empty cell turns an integer column into floats. Write with index=False. Parquet keeps the types and needs pyarrow.
- loc is by label and includes its end label, iloc is by position and excludes it. Filtering keeps the old labels.
- Masks select with &, | and ~, never and, or, not. NaN is found with isna, not ==, and pandas skips it in a mean while NumPy does not.
- groupby summarises per group, value_counts counts values, and the mean of a 0/1 column is a rate. Report group sizes beside it.
- Assign with df.loc[mask, col] = value in one step, and use to_numpy to turn chosen columns into X and y.
Quick Check1 / 6
`by_user` has the index `u1`, `u2`, `u3`, `u4` in that order. How many rows do `by_user.loc["u2":"u4"]` and `by_user.iloc[1:3]` return?