40 min of teaching25 min of exercisesbeginner
head, info, describe, shape, columns and dtypes.loc and iloc, and filter rows with boolean masks combined by & and |.groupby and agg over one or two keys.merge, and reshape them with melt and pivot_table.Between 2007 and 2009 a field team on the Palmer Archipelago in Antarctica measured 344 penguins of three species: bill length and depth, flipper length, body mass, sex, island and year. The obvious questions are table questions. Which species is heaviest? Are males heavier than females in every species? Does any species live on more than one island? None of them fits a NumPy array, because the table mixes text ("Gentoo"), numbers (5076.0) and gaps where a measurement could not be taken. pandas exists for exactly this kind of data: a DataFrameDataFramepandas’ table type: labelled rows and columns, each column with its own data type. is a table whose columns have names and their own types, and whose rows have labels, with the arithmetic of episode 1.2 underneath.
The data are the Palmer penguins (Horst, Hill & Gorman 2020), released under CC0 and bundled in data/penguins.csv.
| index | species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex | year |
|---|---|---|---|---|---|---|---|---|
| 0 | Adelie | Torgersen | 39.1 | 18.7 | 181.0 | 3750.0 | male | 2007 |
| 1 | Adelie | Torgersen | 39.5 | 17.4 | 186.0 | 3800.0 | female | 2007 |
| 2 | Adelie | Torgersen | 40.3 | 18.0 | 195.0 | 3250.0 | female | 2007 |
| 3 | Adelie | Torgersen | NaN | NaN | NaN | NaN | NaN | 2007 |
| 4 | Adelie | Torgersen | 36.7 | 19.3 | 193.0 | 3450.0 | female | 2007 |
read_csv parses the file, guesses a type for each column and turns the string NA into NaN, the marker for a missing value (NaN)missing value (NaN)An absent measurement, shown as NaN; most models cannot handle it without imputation or dropping.. Row 3 is a penguin with no measurements at all; keep an eye on it. Before analysing anything, look at the table from four angles:
((344, 8),
['species',
'island',
'bill_length_mm',
'bill_depth_mm',
'flipper_length_mm',
'body_mass_g',
'sex',
'year'])species str
island str
bill_length_mm float64
bill_depth_mm float64
flipper_length_mm float64
body_mass_g float64
sex str
year int64
dtype: objectFour float64 columns, one int64, and three text columns with dtype str. (pandas 3 introduced the str dtype; older versions show object for text, and you will still see that in tutorials.) info() adds how many values in each column are not missing, which is the fastest way to find gaps:
<class 'pandas.DataFrame'>
RangeIndex: 344 entries, 0 to 343
Data columns (total 8 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 species 344 non-null str
1 island 344 non-null str
2 bill_length_mm 342 non-null float64
3 bill_depth_mm 342 non-null float64
4 flipper_length_mm 342 non-null float64
5 body_mass_g 342 non-null float64
6 sex 333 non-null str
7 year 344 non-null int64
dtypes: float64(4), int64(1), str(3)
memory usage: 21.6 KB342 of 344 measurements, 333 of 344 sexes. describe() summarises every numeric column at once:
| index | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | year |
|---|---|---|---|---|---|
| count | 342.0 | 342.0 | 342.0 | 342.0 | 344.0 |
| mean | 43.9 | 17.2 | 200.9 | 4201.8 | 2008.0 |
| std | 5.5 | 2.0 | 14.1 | 802.0 | 0.8 |
| min | 32.1 | 13.1 | 172.0 | 2700.0 | 2007.0 |
| 25% | 39.2 | 15.6 | 190.0 | 3550.0 | 2007.0 |
| 50% | 44.4 | 17.3 | 197.0 | 4050.0 | 2008.0 |
| 75% | 48.5 | 18.7 | 213.0 | 4750.0 | 2009.0 |
| max | 59.6 | 21.5 | 231.0 | 6300.0 | 2009.0 |
A body mass of 2.7 to 6.3 kg, flippers of 172 to 231 mm. The count row repeats the missing-value story, and year is counted as a number because it is one, even though you will treat it as a label.
This is a real study, not a teaching toy: Gorman, Williams & Fraser (2014, PLoS ONE 9(3): e90081) studied sexual size dimorphism and its link to foraging ecology in the three Pygoscelis species near Palmer Station. Read the columns as experimental design: species is the organism, island the site, year the season, sex the factor the study was about, and the four measurements are the responses. Every question you ask of this table in the next hour (mean by species, difference by sex, counts per island) is a question the authors asked too.
A single column is a SeriesSeriesA single labelled column of data in pandas; one column of a DataFrame.: a 1-D labelled array with a name and a dtype.
0 3750.0
1 3800.0
2 3250.0
Name: body_mass_g, dtype: float64The numbers on the left are the index (pandas)index (pandas)The row labels of a DataFrame or Series; by default the integers 0, 1, 2, …, the row labels. By default they are 0, 1, 2, …, but they are labels, not positions: after filtering or sorting the index keeps its original values, so “the row labelled 169” and “the 170th row” are not the same thing. A Series behaves like a NumPy array (mass.mean(), mass / 1000, mass > 5000) and like a dictionary (mass[169] looks up the label). For text columns, value_counts() is the first thing to run:
loc and ilocTwo indexers cover every selection, and the difference is the single most important thing in this episode. locloc and ilocloc selects rows/columns by label; iloc selects by integer position. selects by label (row label, column name); iloc selects by integer position. Both take [rows, columns].
species Gentoo
body_mass_g 6300.0
Name: 169, dtype: object| index | species | island |
|---|---|---|
| 0 | Adelie | Torgersen |
| 1 | Adelie | Torgersen |
| 2 | Adelie | Torgersen |
Right now, labels and positions coincide, so loc[169] and iloc[169] return the same penguin. Filter the table and they stop agreeing, which is why it matters to say which one you mean. One more difference: loc slices are inclusive of the end label (loc[0:2] is three rows), because labels have no natural “one past the end”, while iloc slices follow the Python rule.
If you have met these structures before: a DataFrame is a dict of equal-length typed columns, not a list of row records, which is why column operations are cheap and row-by-row loops are the wrong tool. A groupby is SELECT ... GROUP BY; merge is a JOIN; a boolean mask is a WHERE. The index is a primary key that is allowed to be non-unique and survives filtering, hence loc (by key) versus iloc (by offset). Column selection df["col"] works like a dict lookup, so df.col attribute access, which you will see in the wild, is just sugar that breaks on column names with spaces.
A comparison on a column gives a boolean Series, a boolean maskboolean maskAn array of True/False values used to select the elements where it is True. with the same index as the table, and indexing the table with it keeps the rows where it is True:
Combine conditions with & (and), | (or) and ~ (not), each in parentheses, exactly as in NumPy:
| index | species | island | flipper_length_mm | body_mass_g |
|---|---|---|---|---|
| 153 | Gentoo | Biscoe | 230.0 | 5700.0 |
| 155 | Gentoo | Biscoe | 218.0 | 5700.0 |
| 163 | Gentoo | Biscoe | 216.0 | 5550.0 |
| 165 | Gentoo | Biscoe | 213.0 | 5850.0 |
| 167 | Gentoo | Biscoe | 217.0 | 5850.0 |
To test membership in a set, use .isin() instead of chaining == with |:
species
Adelie 108
Chinstrap 68
Name: count, dtype: int64Notice that gentoo’s index starts at 152, not 0: the labels came along. gentoo.iloc[0] is the first Gentoo; gentoo.loc[0] is a KeyError, because no row is labelled 0 any more. If you want fresh positions, call gentoo.reset_index(drop=True).
Body mass in kilograms and a bill shape ratio are both one array expression away. The two idioms:
| index | species | body_mass_kg | bill_ratio |
|---|---|---|---|
| 0 | Adelie | 3.75 | 2.090909 |
| 1 | Adelie | 3.80 | 2.270115 |
| 2 | Adelie | 3.25 | 2.238889 |
df["new"] = ... modifies the table in place; .assign(new=...) returns a new table and chains nicely. Missing inputs give missing outputs: the penguin in row 3 gets NaN in both new columns, silently and correctly.
Chained assignment does nothing
penguins[penguins["species"] == "Gentoo"]["body_mass_kg"] = 0 looks like it sets Gentoo masses to zero. It does not. The filter produces a copy, the assignment lands on the copy, and the copy is thrown away. pandas 3 enforces this (copy-on-write) rather than sometimes working and sometimes warning. Write what you mean in one indexer: penguins.loc[penguins["species"] == "Gentoo", "body_mass_kg"] = 0.
“Mean body mass by species” is a split-apply-combine operation, groupbygroupbySplit a table into groups by one or more columns, apply a function to each, and combine the results.: split the rows into groups by a key, apply a function to each group, combine the results into one row per group.
Which species do you expect to be heaviest, and by how much? Gentoo penguins are the largest of the three; write down a guess for the gap before running the cell.
species
Adelie 3701.0
Chinstrap 3733.0
Gentoo 5076.0
Name: body_mass_g, dtype: float64Gentoo penguins are about 1.4 kg heavier than the other two, which barely differ from each other. Several statistics at once go through .agg:
| index | mean | std | count |
|---|---|---|---|
| species | index | index | index |
| Adelie | 3700.7 | 458.6 | 151 |
| Chinstrap | 3733.1 | 384.3 | 68 |
| Gentoo | 5076.0 | 504.1 | 123 |
count counts non-missing values, so Adelie shows 151 rather than 152: the penguin with no measurements. Two keys give one row per combination, and unstack() moves the inner key into columns so the comparison reads across:
| sex | female | male |
|---|---|---|
| species | index | index |
| Adelie | 3369.0 | 4043.0 |
| Chinstrap | 3527.0 | 3939.0 |
| Gentoo | 4680.0 | 5485.0 |
Males are heavier than females in all three species, by 400 to 800 g. That table, in one line, is the finding in the title of the paper this dataset comes from.
Missing keys vanish from groupby
Group by sex and the 11 penguins with unknown sex are dropped without a message, because NaN is not a group by default. Their masses are not in the table above. If you need them counted, pass dropna=False to groupby; either way, know that it happened.
Related information often lives in another table. Here is a two-column lookup from the common name to the scientific name of each species, typed by hand; merge (join)merge (join)Combine two tables side by side by matching values in a shared key column. attaches it to every row by matching the shared species column:
names = pd.DataFrame({
"species": ["Adelie", "Chinstrap", "Gentoo"],
"scientific_name": ["Pygoscelis adeliae", "Pygoscelis antarcticus", "Pygoscelis papua"],
})
merged = penguins.merge(names, on="species", how="left")
print(penguins.shape, "->", merged.shape)
merged[["species", "scientific_name", "island"]].drop_duplicates().head()(344, 10) -> (344, 11)| index | species | scientific_name | island |
|---|---|---|---|
| 0 | Adelie | Pygoscelis adeliae | Torgersen |
| 20 | Adelie | Pygoscelis adeliae | Biscoe |
| 30 | Adelie | Pygoscelis adeliae | Dream |
| 152 | Gentoo | Pygoscelis papua | Biscoe |
| 276 | Chinstrap | Pygoscelis antarcticus | Dream |
how="left" keeps every row of the left table and fills NaN where the right table has no match; how="inner" keeps only matched rows. The row count is the thing to check every time you merge: 344 in, 344 out means every penguin matched exactly one name. If the lookup table had listed a species twice, the merge would have duplicated those penguins, and nothing would have warned you. Chapter 5 merges thousands of measured compounds with computed descriptors on a molecule identifier; the habit of printing the shape before and after starts here.
melt and pivot_tableThe penguin table is wide: four measurements sit in four columns. For some tasks, plotting all four with the same code or grouping by “which measurement”, you want them long, one row per penguin per measurement. That is meltmelt and pivotmelt turns wide tables long (one row per measurement); pivot turns long tables wide.:
(1376, 6)| index | species | island | sex | year | measurement | value |
|---|---|---|---|---|---|---|
| 0 | Adelie | Torgersen | male | 2007 | bill_length_mm | 39.1 |
| 1 | Adelie | Torgersen | female | 2007 | bill_length_mm | 39.5 |
| 2 | Adelie | Torgersen | female | 2007 | bill_length_mm | 40.3 |
344 penguins × 4 measurements = 1376 rows. The id_vars are repeated on every row so nothing is lost; measurement says which column the number came from. Going the other way, pivot_table summarises a long or wide table into a grid, with one key on the rows, one on the columns and an aggregation in the cells.
A table of mean body mass with species as rows and islands as columns has nine cells. How many will be empty? Think about whether every species was measured on every island.
| island | Biscoe | Dream | Torgersen |
|---|---|---|---|
| species | index | index | index |
| Adelie | 3710.0 | 3688.0 | 3706.0 |
| Chinstrap | NaN | 3733.0 | NaN |
| Gentoo | 5076.0 | NaN | NaN |
Four cells are empty: Chinstrap penguins were measured only on Dream, Gentoo only on Biscoe, and only Adelie on all three islands. That is a fact about the study design, not a data error, and it is the kind of thing pivot_table makes visible in one glance. aggfunc="count" gives the sample sizes behind the same grid.
Every real dataset has gaps. The first thing to run on any new table:
species 0
island 0
bill_length_mm 2
bill_depth_mm 2
flipper_length_mm 2
body_mass_g 2
sex 11
year 0
body_mass_kg 2
bill_ratio 2
dtype: int64Two rows have no measurements, eleven have no sex (the two unmeasured penguins are among them). Look at the rows before deciding what to do:
| index | species | island | year | body_mass_g |
|---|---|---|---|---|
| 3 | Adelie | Torgersen | 2007 | NaN |
| 8 | Adelie | Torgersen | 2007 | 3475.0 |
| 9 | Adelie | Torgersen | 2007 | 4250.0 |
| 10 | Adelie | Torgersen | 2007 | 3300.0 |
| 11 | Adelie | Torgersen | 2007 | 3700.0 |
There are three responses, and the right one depends on why the value is missing and what you will do next:
dropna(subset=measurements) drops only rows missing a measurement; bare dropna() drops any row with any gap, which here would also discard nine fully measured penguins just because their sex is unknown.sex is missing.Never fill gaps with 0 or −999: a model cannot tell a placeholder from a measurement, and the mean of a column with a few −999s is nonsense.
species, island and sex take a handful of values each. Converting them to pandas’ categorical datacategorical dataA column whose values come from a fixed set of categories, such as species or island. type records the allowed categories once and stores each value as a small integer:
(CategoricalDtype(categories=['Adelie', 'Chinstrap', 'Gentoo'], ordered=False, categories_dtype=str),
['Adelie', 'Chinstrap', 'Gentoo'])The benefits are memory (a few hundred bytes instead of tens of kilobytes for this column), a fixed category order for plots and tables, and an explicit statement that this column is a label, not free text. The cost is one extra keyword: when grouping by a categorical, pass observed=True so that pandas reports only combinations that actually occur rather than every possible one.
The penguin table obeys three rules that make all of the above work without special cases. This is tidy datatidy dataOne row per observation, one column per variable, one table per kind of thing.:
Untidy tables are everywhere in science: a spreadsheet with one column per year, a plate layout where the position of a cell encodes the sample, a sheet with the sample name in a merged header row. They look fine to a human and are nearly unusable by code. melt is the usual repair for “variables in column headers”; pivot_table is how you produce a wide summary for a reader after the analysis, not before. Keep the long, tidy table as the source of truth and derive everything else from it.
Heaviest penguin per species
Find the heaviest penguin of each species and show its species, island, sex and body mass. Two routes work: groupby with idxmax, or sort by mass and keep the first row of each group with .groupby(...).head(1).
| index | species | island | sex | body_mass_g |
|---|---|---|---|---|
| 109 | Adelie | Biscoe | male | 4775.0 |
| 313 | Chinstrap | Dream | male | 4800.0 |
| 169 | Gentoo | Biscoe | male | 6300.0 |
idxmax returns the index label of the maximum within each group, which is exactly what loc wants. The result shows a 6.3 kg Gentoo male on Biscoe, and that all three heaviest birds are male, consistent with the grouped means. The sort-then-head(1) route gives the same rows:
Where is sex missing?
Compute the fraction of penguins with unknown sex in each species, as a percentage with one decimal. Then check whether the gaps cluster in one island or year. Is there any reason to think the missingness is related to the measurements?
species
Adelie 3.9
Chinstrap 0.0
Gentoo 4.0
Name: sex, dtype: float64A boolean Series has mean equal to the fraction of True, so grouping the mask by species gives the fraction missing per species directly: about 4% of Adelie and Gentoo, none of the Chinstrap.
species island year
Adelie Torgersen 2007 5
Gentoo Biscoe 2009 3
Adelie Dream 2007 1
Gentoo Biscoe 2007 1
2008 1
Torgersen 2007 0
Chinstrap Torgersen 2007 0
Gentoo Dream 2007 0
Chinstrap Dream 2007 0
Adelie Biscoe 2007 0
Chinstrap Biscoe 2007 0
Adelie Biscoe 2008 0
Chinstrap Biscoe 2008 0
Adelie Biscoe 2009 0
Chinstrap Biscoe 2009 0
Name: count, dtype: int64Five of the eleven are Adelie penguins from Torgersen in 2007, one more is an Adelie from Dream the same year, and the other five are Gentoo on Biscoe spread over all three years; the clusters are small, and the masses of the nine measured birds (3.0 to 4.9 kg) sit well inside the normal range for their species. With such numbers you cannot tell whether sex was simply not recorded or could not be determined, so the safe choice is to keep these rows for any analysis that does not use sex and drop them only for the ones that do.
Dimorphism table: melt, group, pivot
Using the long table, compute the mean of every measurement for each species and sex, then pivot to a table with one row per (species, measurement) and one column per sex, and add a column male_minus_female_pct giving the male–female difference as a percentage of the female mean. Which measurement differs most between the sexes, and in which species?
| index | sex | female | male | male_minus_female_pct |
|---|---|---|---|---|
| species | measurement | index | index | index |
| Adelie | body_mass_g | 3368.8 | 4043.5 | 20.0 |
| Gentoo | body_mass_g | 4679.7 | 5484.8 | 17.2 |
| Chinstrap | body_mass_g | 3527.2 | 3939.0 | 11.7 |
| Gentoo | bill_depth_mm | 14.2 | 15.7 | 10.4 |
| Chinstrap | bill_length_mm | 46.6 | 51.1 | 9.7 |
| bill_depth_mm | 17.6 | 19.3 | 9.5 | |
| Gentoo | bill_length_mm | 45.6 | 49.5 | 8.6 |
| Adelie | bill_length_mm | 37.3 | 40.4 | 8.4 |
| bill_depth_mm | 17.6 | 19.1 | 8.2 | |
| Chinstrap | flipper_length_mm | 191.7 | 199.9 | 4.3 |
| Gentoo | flipper_length_mm | 212.7 | 221.5 | 4.2 |
| Adelie | flipper_length_mm | 187.8 | 192.4 | 2.5 |
The melt made this a single groupby over three keys instead of four separate calculations, one per measurement column. unstack("sex") is the pivot step: it moves the innermost key into columns so male and female sit side by side. Body mass shows the largest sex difference in every species (12–20%, with Adelie at the top), the two bill measurements come next (8–10%), and flipper length differs least (2–4%). Males are larger on every measurement in every species, but the size of the gap depends on what you measure, which is why the paper’s title says dimorphism rather than just bigger. The .assign(... lambda t: ...) form lets you refer to the table being built inside a chain. The same chain, with a different id_vars list, is how you will compare descriptor distributions across compound classes in Chapter 5.
From here on, every dataset in the course arrives as a DataFrame and leaves as the X and y of episode 1.2: X = df[features] selects the feature columns, y = df["target"] the labels, and scikit-learn accepts both directly. Episode 1.4 plots this same penguin table, long form and all, and episode 1.5 applies the inspect-filter-group-check-for-gaps routine to the ESOL solubility data that Chapter 2 models. In Chapter 5 the rows become molecules and the columns computed descriptors, merged on a compound identifier; in Part III the table is a gene-expression matrix with thousands of columns, and melt, groupby and pivot_table are still the verbs you reach for.