Pandas¶
70 min Foundations Needs: Linear Algebra with NumPy
NumPy gives you fast grids of numbers. Pandas gives those grids names, mixed types and an index, which is what turns an array into a dataset you can reason about.
Why this matters¶
Most of the work in machine learning happens before any model is trained. Data arrives with missing values, inconsistent spellings, numbers stored as text and rows that appear twice. Pandas is how you find those problems and fix them, and it is the library you will spend more hours in than any other this year.
It is also what interviewers reach for. Being fluent here is closer to knowing SQL than to knowing a framework, because it does not go out of fashion.
A DataFrame is a labelled table¶
Two objects, and you will use both constantly.
A DataFrame is the whole table. A Series is one column together with the index. Every column you pull out of a DataFrame comes back as a Series, and most of the methods you learn work on both.
What the labels buy you¶
Underneath, a DataFrame is still NumPy arrays. Everything you learned about vectorised operations still applies. What pandas adds is names, an index, the ability to mix text with numbers in one table, and a notion of missing data.
Loading and looking¶
Useful arguments you will reach for: sep=";" for European exports,
na_values=["", "NA", "unknown"] to mark junk as missing on the way in, and
parse_dates=["joined"] so date columns arrive as dates.
Before doing anything else, look at the thing.
df.head() # first five rows
df.shape # (rows, columns)
df.info() # column names, types, non-null counts
df.describe() # count, mean, std, min, quartiles, max for numeric columns
df["city"].value_counts()
info() is the one to internalise. In a single output it tells you how many
rows you have, which columns are the wrong type, and which have missing values.
Look before you transform
Every mistake in this page is cheaper to catch at this stage. Run head,
info and describe before writing a single line of cleaning code, and
again after each significant change.
Selecting¶
This is where beginners lose the most time, because there are several ways to select and they are not interchangeable.
Columns¶
df["salary"] # one column, a Series
df[["name", "salary"]] # several columns, a DataFrame
df.drop(columns=["notes"]) # returns a new DataFrame without that column
The double brackets confuse people. The inner brackets are a Python list of column names, and the outer ones are the selection. Ask for one name and you get a Series, ask for a list and you get a table.
Rows by label or position¶
df.loc[2] # the row whose index label is 2
df.iloc[2] # the third row, whatever its label
df.loc[2, "salary"] # one cell, by label
df.iloc[0:3, 0:2] # first three rows, first two columns
On a freshly loaded file the index is 0, 1, 2 and the two behave identically.
Sort or filter the data and they diverge immediately, because loc follows the
label while iloc follows the position. When in doubt, ask yourself whether you
mean "the row called 5" or "the sixth row".
Rows by condition¶
df[df["salary"] > 50000]
df[df["city"] == "Tirana"]
df[df["city"].isin(["Tirana", "Durres"])]
df[~df["city"].isin(["Tirana"])]
This is the same boolean mask idea from NumPy, and you will use it more than everything else on this page combined.
Brackets around every condition
Use & for and, | for or, and wrap each condition in its own brackets.
Without them Python's operator precedence evaluates the & first and you
get an error that explains nothing. Also use & rather than the word and,
which cannot work on a whole column.
The copy trap¶
You met this in NumPy, where a slice was a view into the same memory. Pandas has its own version of the problem and it produces a warning that beginners learn to ignore.
SettingWithCopyWarning
Pandas cannot tell whether subset is a view into df or an independent
table, so it warns you that your assignment might not do what you expect.
Two fixes, depending on what you meant. If you wanted a separate table, say
so with subset = df[df["city"] == "Tirana"].copy(). If you wanted to change
the original, address it directly:
Never chain two selections when assigning. df[df["a"] > 1]["b"] = 0 looks
reasonable and silently does nothing at all.
Missing data¶
Finding it is easy. Deciding what to do is the actual skill.
df.isna().sum() # count of missing values per column
df.isna().sum().sum() # total across the table
df[df["salary"].isna()] # the rows that are missing a salary
df = df.dropna(subset=["salary"]) # drop rows missing a salary
df = df.drop(columns=["notes"]) # drop a column that is mostly empty
df["salary_missing"] = df["salary"].isna() # flag before filling
df["salary"] = df["salary"].fillna(df["salary"].median())
Median rather than mean when the column is skewed, which salaries always are. A handful of executives will drag a mean somewhere no real employee sits.
ML connection
Missing values are rarely missing at random. If income is blank more often for people who declined to answer, then filling those blanks with the average quietly invents a population that does not exist. The flag column costs nothing and lets a model learn that the absence itself carried information.
Types and text¶
The joined column in the diagram was text, not a date. Fixing types is
unglamorous and it unlocks everything downstream.
df["salary"] = pd.to_numeric(df["salary"], errors="coerce")
df["joined"] = pd.to_datetime(df["joined"], errors="coerce")
df["city"] = df["city"].astype("category")
errors="coerce" turns anything unparseable into a missing value rather than
raising. Combine it with isna() afterwards to find exactly which rows were
junk.
Text needs its own cleaning, because Tirana, tirana and Tirana are three
different cities as far as pandas is concerned.
df["city"] = df["city"].str.strip().str.title()
df["email"] = df["email"].str.lower()
df = df.drop_duplicates()
df = df.drop_duplicates(subset=["email"], keep="last")
Once dates are real dates, they become useful:
df["year_joined"] = df["joined"].dt.year
df["tenure_days"] = (pd.Timestamp.today() - df["joined"]).dt.days
Creating columns¶
df["monthly"] = df["salary"] / 12
df["senior"] = df["salary"] > 55000
df["band"] = np.where(df["salary"] > 55000, "high", "standard")
df["region"] = df["city"].map({"Tirana": "Central", "Durres": "Coast"})
map on a dictionary is the clean way to recode categories. Anything it does
not find becomes missing, which is a feature: it tells you about spellings you
did not anticipate.
When the logic will not fit in an expression, apply takes a function, but
reach for it last. It runs a Python loop underneath and gives up the speed you
came to pandas for.
Grouping¶
Split the table into piles, compute something for each pile, stack the answers back together.
df.groupby("city")["salary"].mean()
df.groupby("city")["salary"].agg(["count", "mean", "median", "max"])
df.groupby(["city", "band"])["salary"].mean()
df.groupby("city").agg(headcount=("name", "count"), payroll=("salary", "sum"))
The grouping key becomes the index of the result, which is why the output looks
different from a normal table. reset_index() turns it back into an ordinary
column when you want one.
Merging two tables¶
Real data arrives split across files: employees in one, departments in another.
merge joins them on a shared key.
merged = employees.merge(departments, on="dept_id", how="inner")
merged = employees.merge(departments, on="dept_id", how="left")
merged = employees.merge(departments, left_on="dept", right_on="id", how="left")
how |
What you keep |
|---|---|
inner |
Only rows whose key appears in both tables |
left |
Every row of the left table, with blanks where there was no match |
right |
Every row of the right table |
outer |
Everything from both, with blanks on either side |
Always check the row count after a merge
Rows vanishing means keys did not match, often because one side is text and the other is a number, or because of trailing spaces. Rows multiplying means the key is not unique on one side and you have accidentally created duplicates. Both are common and neither raises an error.
how="left" plus validate="many_to_one" will make pandas complain loudly
instead of silently producing a wrong table.
Saving your work¶
Pass index=False unless the index means something, otherwise you get a stray
unnamed column every time you reload the file. to_excel, to_parquet and
to_json work the same way, and parquet is worth knowing for anything large
because it keeps your data types.
The whole thing on one screen¶
import pandas as pd
import numpy as np
df = pd.read_csv("employees.csv", na_values=["", "NA", "unknown"])
df["city"] = df["city"].str.strip().str.title()
df["salary"] = pd.to_numeric(df["salary"], errors="coerce")
df["joined"] = pd.to_datetime(df["joined"], errors="coerce")
df = df.drop_duplicates()
df["salary_missing"] = df["salary"].isna()
df["salary"] = df["salary"].fillna(df["salary"].median())
df["tenure_days"] = (pd.Timestamp.today() - df["joined"]).dt.days
df = df.merge(departments, on="dept_id", how="left")
summary = df.groupby("city").agg(headcount=("name", "count"), pay=("salary", "median"))
df.to_csv("clean.csv", index=False)
Twelve lines, and it is the shape of most real data preparation jobs. Load, standardise text, fix types, deduplicate, handle missing values, derive what you need, join what is elsewhere, summarise, save.
Where this sits in the lifecycle¶
| Lifecycle stage | What you use |
|---|---|
| Collect | read_csv, read_excel, read_sql, merge |
| Prepare | dropna, fillna, astype, str methods, drop_duplicates |
| Prepare | new columns, map, np.where, groupby |
| Evaluate | value_counts, describe, crosstab to check what you built |
Collect and Prepare are the first two boxes of the loop from Fundamentals, and they are where most of your time goes.
When it goes wrong¶
KeyError: 'salary'
The column does not exist under that name. Print df.columns.tolist().
Usually it is a trailing space or a different capitalisation in the file
header. df.columns = df.columns.str.strip().str.lower() right after
loading prevents a whole category of this.
SettingWithCopyWarning
You assigned into something that may be a view. Use .copy() if you wanted
a separate table, or df.loc[condition, "column"] = value if you wanted to
change the original.
ValueError: The truth value of a Series is ambiguous
You used and, or or not on a whole column. Use &, | and ~, with
brackets around each condition.
My filter returned an empty table
Compare against the real values with df["city"].unique(). It is almost
always whitespace or capitalisation, which is why cleaning text comes before
filtering.
After merging I have more rows than I started with
The key is not unique in the right-hand table, so each left row matched
several right rows. Check with departments["dept_id"].duplicated().sum().
A column of numbers has dtype object
Something in it is not a number: a currency symbol, a comma, or the word
unknown. pd.to_numeric(col, errors="coerce") then isna() shows you
exactly which rows.
Check yourself¶
Use any small CSV you have, or one column of the data from the project.
- Load a file and, without looking at it in a spreadsheet, say how many rows it has, which columns are the wrong type, and which have missing values.
- Select two columns, then the rows where a numeric column exceeds its own median. Do it in one expression.
- Take a column with inconsistent text values and reduce it to a clean set.
Prove it with
value_counts()before and after. - Fill a column's missing values, and add a flag column recording which rows were filled. Explain why you chose mean or median.
- Group by a category and produce count and median in one call, then reset the index so the result is an ordinary table.
- Merge two tables on a key, then check that the row count is what you expected and explain any difference.
Quick reference¶
| Task | Code |
|---|---|
| Load | pd.read_csv("f.csv", na_values=["NA"]) |
| First look | df.head(), df.info(), df.describe() |
| Size | df.shape |
| One column | df["col"] |
| Several columns | df[["a", "b"]] |
| Row by label, by position | df.loc[2], df.iloc[2] |
| Filter | df[(df["a"] > 1) & (df["b"] == "x")] |
| Missing counts | df.isna().sum() |
| Drop, fill | df.dropna(subset=["a"]), df["a"].fillna(value) |
| Fix types | pd.to_numeric(...), pd.to_datetime(...), .astype("category") |
| Clean text | df["a"].str.strip().str.title() |
| Duplicates | df.drop_duplicates(subset=["id"]) |
| New column | df["new"] = df["a"] / 12 |
| Recode | df["a"].map({"x": 1}), np.where(cond, "y", "n") |
| Group | df.groupby("city")["salary"].agg(["count", "median"]) |
| Join | a.merge(b, on="key", how="left") |
| Save | df.to_csv("out.csv", index=False) |
Questions to sit with¶
- You fill missing salaries with the column median. What have you told the model, and what have you hidden from it?
- An inner join quietly dropped a quarter of your rows. Nothing raised an error. What process would catch that before it reaches a model?
df["city"].value_counts()shows Tirana with 40 and Tirane with 12. Is merging them a data cleaning decision or a modelling decision, and does the distinction matter?- Pandas makes it easy to transform data until it gives the answer you wanted. What would you keep, in your code or your notes, so that somebody else could tell the difference between cleaning and tuning?
Next¶
You can shape data. Now make it visible, which is how you find the problems that
info() cannot show you.
Worth your time: the pandas user guide for depth, and Python for Data Analysis by the author of pandas, free to read online.