Pandas - the field notebook that counts for itself

You come back from the morning round, @name, and you have four rows in your notebook. Lion: 120 individuals, savanna, endangered. Elephant: 450, forest, not endangered. Cheetah: 85, savanna, endangered. Giraffe: 200, savanna, not endangered. A single row holds three completely different kinds of information: the species name, which is text, the head count, which is a number, and a plain yes-or-no answer. NumPy from the previous lesson will average a column of numbers faster than you can blink, but before it does, you have to hand it numbers and nothing else. And a field notebook is not a column of numbers. It is a table.

Pandas is the library that turns Python into a tool for tables like that. It does not replace NumPy - it is built on top of it and does its arithmetic with exactly the same arrays underneath. What it adds are two things NumPy does not have: every column gets a name, and every column may carry its own data type. That is precisely the difference between a raw measurement and a properly kept expedition journal.

Why a NumPy array will not do here

Before we reach for a new tool, it is worth seeing with your own eyes where the old one stops working. Let us try to push two rows of the notebook into a NumPy array, the way we did with plain numbers last time. The function

np.array

will accept a list of lists without blinking, because formally nothing bad is happening. What interests us is what comes out the other side: the attribute

dtype

reports the single type NumPy picked to describe the whole contents of the array, because in NumPy every element has to share one type.

1import numpy as np
2
3raw = np.array([["Lion", 120], ["Elephant", 450]])
4
5print(raw)
6# [['Lion' '120']
7#  ['Elephant' '450']]
8
9print(raw.dtype)  # <U21

Look at the quotes around the numbers in the printout:

'120'

instead of

120

. Since the array must have one type, and "Lion" cannot be turned into a number, NumPy went the other way and turned the numbers into text. The code

<U21

means exactly that - "a string of up to twenty-one characters". Your measurements have stopped being measurements: adding them together would glue strings end to end, and an average would not go through at all. On top of that sits a second problem, quieter but no less painful. The columns have no names, so for the rest of the analysis you have to keep remembering that the head count lives at position one. Pandas solves both problems at once, and that is what it was built for.

Installing it and the

pd

alias

Pandas is not part of the Python standard library, so you have to install it. That job belongs to

pip

, the Python package manager, using the same command you have used for other libraries before. The installation will pull NumPy along with it if you do not have it yet, because Pandas will not start without NumPy - that is a dependency, not a suggestion. You run the command in a terminal and not inside a code file, and only once per environment. If you work in Google Colab or in the Anaconda distribution, Pandas is already there and you can skip this step entirely.

1pip install pandas

Nothing has happened inside your program yet: the package has landed on disk, but no code file knows about it. That takes a separate command in Python itself. The custom is to give the library the short alias

pd

using the keyword

as

, which creates a second, shorter name for the same thing. It is only a convention, but a convention so universal that every example in the documentation, every answer you will find online and every exercise in this module assumes

pd

.

1import pandas as pd

That one line pulls the library into memory and hangs the label

pd

on it. Nothing has been loaded, computed or displayed - your data will show up only in the commands that follow. The variant

import pandas

is correct too, except that you would then have to spell out

pandas.DataFrame

at every call, which gets tiring somewhere around the thirtieth line of a script. What will not work is

import pd

: the package is called

pandas

, so Python stops with

ModuleNotFoundError: No module named 'pd'

. You are the one who invents the alias, on this very line, and only from here on does it exist.

Series - one column with labels

Let us start with the smallest piece of the notebook: a single column. The list

[120, 450, 85, 200]

stores four head counts, but says nothing at all about who the eighty-five belong to. Pandas has a structure for that, called Series. A Series is one-dimensional: it is a single run of values, not a table with rows and columns at the same time. On top of the values it carries an index, a set of labels with one label per value, plus an optional name for the whole run, which you pass through the

name

parameter. Learn that definition precisely, because in a moment we will set Series against a structure that really is a table.

1populations = pd.Series([120, 450, 85, 200], name="population")
2
3print(populations)
4# 0    120
5# 1    450
6# 2     85
7# 3    200
8# Name: population, dtype: int64

On the left of the printout you can see an index you never supplied: Pandas made one up for you, as consecutive numbers starting at zero. That matters, because a Series always has an index, even when you do not ask for one. On the right stand your values, underneath them the name of the series and

dtype: int64

, the type shared by every element. In this respect a Series behaves like a NumPy array, and that is no coincidence - there really is a NumPy array inside it. The list you passed in was not modified in any way; Pandas copied the values out of it into a new structure.

The index does not have to be a run of numbers, and that is exactly where its power lies. You can supply your own labels through the

index

parameter, passing a list exactly as long as the list of values. From then on, instead of remembering that eighty-five was the third measurement, you reach for the value by species name, in square brackets, precisely the way you reach into a dictionary.

1species = pd.Series(
2    [120, 450, 85, 200],
3    index=["Lion", "Elephant", "Cheetah", "Giraffe"],
4    name="population",
5)
6
7print(species["Lion"])     # 120
8print(species["Cheetah"])  # 85

The labels have replaced the numbers from zero, but the values are the same and in the same order - an index describes the values, it does not shuffle them. Notice that the species names are not a second column. This is still one column of numbers, only now each of them has a tag pinned to it. If you wanted to keep the species and the head count as two equal columns, you would need a different structure, and you will meet it in the next section.

Since there is a NumPy array inside a Series, the same tricks work here as in the previous lesson. The comparison

species > 100

does not return a single

True

; it runs the comparison separately for every value and hands back a Series filled with booleans. A series like that is called a boolean mask. Put it back inside square brackets and it selects only those elements where the answer came out

True

.

1print(species > 100)
2# Lion         True
3# Elephant     True
4# Cheetah     False
5# Giraffe      True
6# Name: population, dtype: bool
7
8print(species[species > 100])
9# Lion        120
10# Elephant    450
11# Giraffe     200
12# Name: population, dtype: int64

The cheetah has vanished from the result, because eighty-five does not clear a hundred, but it has not vanished from

species

- filtering returns a new series and leaves the original untouched. Notice too that the labels travelled with the values: "Elephant" still stands next to four hundred and fifty even though only every other row survived. We will carry this behaviour over to whole tables in a moment, and it will turn out to be the foundation of filtering in Pandas.

DataFrame - the whole table at once

One Series is one column, and your notebook has four of them. You need a structure that holds rows and columns at the same time, and in Pandas that structure is the DataFrame. A DataFrame is two-dimensional: it has as many rows as you have observations and as many columns as you have traits to describe. Every single column of a DataFrame is a Series, so the two structures are not rivals - one is made out of the other. The simplest way to build a table is to pass a dictionary: the key becomes the column name, and the value, a list, fills that column from top to bottom. All the lists must be the same length, otherwise the rows would not close.

1safari_data = pd.DataFrame({
2    "species": ["Lion", "Elephant", "Cheetah", "Giraffe"],
3    "population": [120, 450, 85, 200],
4    "habitat": ["Savanna", "Forest", "Savanna", "Savanna"],
5    "endangered": [True, False, True, False],
6})
7
8print(safari_data)
9#     species  population  habitat  endangered
10# 0      Lion         120  Savanna        True
11# 1  Elephant         450   Forest       False
12# 2   Cheetah          85  Savanna        True
13# 3   Giraffe         200  Savanna       False

The four dictionary keys have become column headers, in the order you typed them. On the left the index from zero has appeared again, invented automatically just as it was for a Series. The most important thing, though, is what you cannot see here: the numbers did not turn into text. The

population

column holds integers,

species

and

habitat

hold text and

endangered

holds booleans, and all three types live side by side in one table. That is exactly what the NumPy array at the start of the lesson could not do. Nothing else happened either - Pandas sorted nothing, computed nothing and corrected nothing.

Three names that do not mean "table"

A few names circle around the DataFrame that are easy to mix up and that mean something entirely different. Series, as you now know, is the one-dimensional structure - a single column with an index, not a table. Panel used to be a three-dimensional structure in Pandas, a stack of several tables laid one on another, but it was retired and since version 1.0 it simply does not exist; trying to use

pd.Panel

ends in an

AttributeError

. Table, in turn, has never been part of Pandas at all - a class by that name shows up in other libraries, for instance ones that handle astronomical files, and it has nothing to do with our table. Nor is a list of nested dictionaries a table: that is an ordinary Python structure which you may indeed hand to

pd.DataFrame

, but which on its own is neither a Series nor any other Pandas structure. You can check all of this instead of taking my word for it, using the function

hasattr

, which answers the question "does this object have such a name inside it".

1print(hasattr(pd, "DataFrame"))  # True
2print(hasattr(pd, "Series"))     # True
3print(hasattr(pd, "Panel"))      # False
4print(hasattr(pd, "Table"))      # False

Two truths and two falsehoods, in black and white. The Pandas library is home to exactly two core data structures: the one-dimensional

Series

and the two-dimensional

DataFrame

. When somebody talks about a table with rows and columns in Pandas, they are talking about a DataFrame - and that is the only correct answer to that question. The

hasattr

calls themselves created nothing and changed nothing along the way; they only reported what is there and what is not.

Field data arrives in a CSV file

A dictionary is fine for four rows. With three thousand readings from GPS collars nobody is going to retype the data by hand - you get it as a CSV file, comma-separated values. It is an ordinary text file with a very simple build: the first line is a header holding the column names, and every line after it is one observation. Open such a file in any editor and you will see something like this.

1species,population,habitat,endangered
2Lion,120,Savanna,True
3Elephant,450,Forest,False
4Cheetah,85,Savanna,True
5Giraffe,200,Savanna,False

Five lines of text: one header and four observations, exactly the same data we were typing into a dictionary a moment ago. No types, no brackets, no structure beyond the commas. Turning that file into a finished table is the job of the function

read_csv

. You give it the file name as text and you get a DataFrame back. We assign the result to a variable called

df

- the traditional shorthand for DataFrame, and the name you will see in almost every example online.

1df = pd.read_csv("safari_species.csv")
2
3print(df.shape)                 # (4, 4)
4print(df["population"].sum())   # 855

Four rows and four columns, and the sum of the head counts came out as a number rather than as glued-together text - which means

read_csv

worked out the column types by itself. The header from the file became the column names, the lines of text became rows of the table, and you did not write a single line of conversion. Memorise this call character by character, because you will be typing it daily:

pd

,

.

,

read_csv

,

(

,

'data.csv'

,

)

. The order is fixed - first the library alias, then the dot, the function name, the opening bracket, the file name in quotes and the closing bracket. The function belongs to the

pd

module and not to the table, so

df.read_csv(...)

makes no sense: at the moment of loading you do not have a

df

yet. The twin functions

pd.read_excel

and

pd.read_json

work the same way for spreadsheets and JSON files, except that the first one needs an extra package for Excel support.

The first look:

head

and

tail

You have loaded the file and the first thing you want is a peek inside. Printing the whole DataFrame with three thousand rows will flood your terminal and explain nothing. For a glance at the top of the table there is the method

head

, which returns the first rows, and for a glance at the bottom there is

tail

. Both take an optional number of rows, and if you do not give one, they take five. Our table has only four rows, so a bare

df.head()

would show it whole - to make the difference visible we will ask for two explicitly.

1print(df.head(2))
2#     species  population  habitat  endangered
3# 0      Lion         120  Savanna        True
4# 1  Elephant         450   Forest       False
5
6print(df.tail(2))
7#    species  population  habitat  endangered
8# 2  Cheetah          85  Savanna        True
9# 3  Giraffe         200  Savanna       False

Two rows from the top and two from the bottom, each one complete, with all four columns. And now a warning about three misunderstandings that grow out of the word "head" itself. This method does not return the column headers, even though "header" sounds so similar - the column names come from

df.columns

. Neither does it return the first element of each column; the first row as such is pulled out by

df.iloc[0]

, which we will meet in a moment. And it most certainly sorts nothing: the order of rows in the result is exactly the order in the file, and sorting has its own method,

sort_values

, near the end of this lesson.

head

returns a new DataFrame holding a slice of the data, and

df

stays as it was.

The dataset's ID card:

info

and

describe

A preview of a few rows tells you what the data looks like, but not how much of it there is or whether values are missing somewhere. For that kind of ID card there is the method

info

. It prints the class of the object, the range of the index, and then a small table with one line per column: the number, the name, the count of non-empty values under

Non-Null Count

and the type under

Dtype

. At the end it adds a type summary and the memory footprint. It is a command worth running on every new dataset you touch.

1df.info()          # prints the ID card of the table
2
3print(df.info())   # the same ID card, with a stray None underneath

Those two lines do almost the same thing, and that is exactly why they stand next to each other.

info

is one of the few methods that print by themselves and return no result, or more precisely return

None

. Wrapping it in

print

improves nothing; it merely adds a lonely

None

at the end, which confuses a few students every year. So remember: you call

info

bare, without

print

. The method changes nothing in the table - it is a pure read, just like

head

.

Once you know how many rows you have and of what types, it is time for the numbers. The method

describe

computes a full set of descriptive statistics for every numeric column: the count of values under

count

, the mean under

mean

, the standard deviation under

std

, the minimum, the three quartiles marked

25%

,

50%

and

75%

, and the maximum. The call is made of five parts and is worth knowing by heart:

df

,

.

,

describe

,

(

,

)

.

1print(df.describe())
2#        population
3# count    4.000000
4# mean   213.750000
5# std    164.690366
6# min     85.000000
7# 25%    111.250000
8# 50%    160.000000
9# 75%    262.500000
10# max    450.000000

Eight lines of statistics, but only one column in the result. That is not a bug: by default

describe

considers numeric columns only, and in our table the only numeric column is

population

. The textual

species

and

habitat

and the boolean

endangered

were skipped and left in the table untouched. If the dataset had no numeric column at all,

describe

would switch to text statistics instead: the count of values, the count of unique values and the most frequent one. A mean of two hundred and thirteen and three quarters against a maximum of four hundred and fifty tells you straight away that one species towers over the rest.

The shape of the table:

shape

,

columns

and

dtypes

Sometimes you do not need the whole ID card, only one specific number - how many rows survived a filter, for example. Three attributes serve that purpose, and an attribute is a property of an object rather than a method. The attribute

shape

returns a tuple in the order rows, columns. The attribute

columns

gives back the list of column names, and

dtypes

gives the type of each column separately. Because these are attributes and not methods, you write them without brackets:

df.shape

, not

df.shape()

. That second version ends in

TypeError: 'tuple' object is not callable

, because you are trying to call a finished tuple as if it were a function.

1print(df.shape)    # (4, 4)
2print(df.columns)
3# Index(['species', 'population', 'habitat', 'endangered'], dtype='str')
4print(df.dtypes)
5# species         str
6# population    int64
7# habitat         str
8# endangered     bool
9# dtype: object

The tuple

(4, 4)

always reads the same way: four rows by four columns, never the other way round, so

df.shape[0]

is the number of observations. The column names come back wrapped in an

Index

object - the very same class that handles row labels, because in Pandas the column headers are an index too. If you would rather have a plain Python list, append

.tolist()

. One warning about the printed types: the

str

ending on text columns appears in Pandas from version 3.0 onwards, and in earlier versions you will see

object

in that spot. It is the same thing under an older name and it changes nothing in your code.

Selecting columns: one bracket or two

Analysis rarely concerns the whole table at once - most of the time you pull a slice out of it. You point at a column by its name in square brackets, and here the first trap is waiting, because the number of brackets decides the type of the result. One pair of brackets with a single name returns a Series, the one-dimensional column from the start of the lesson. Two pairs of brackets return a DataFrame, because what you put inside is an ordinary Python list of column names - hence the second pair, no magic involved. The difference shows up best in the

shape

attribute, which has one number for a one-dimensional structure and two for a two-dimensional one.

1populations = df["population"]
2subset = df[["species", "population"]]
3
4print(populations.shape)  # (4,)
5print(subset.shape)       # (4, 2)

The same set of numbers, two different shapes. The tuple

(4,)

with a single value is the signature of a Series: four values lined up in a single run. The tuple

(4, 2)

is the signature of a DataFrame: four rows by two columns. The order of names in the list decides the order of columns in the result, so

df[["population", "species"]]

would give the same contents in the opposite arrangement. Both variables are new objects -

df

still has its full set of four columns and neither of these lines trimmed it.

Selecting rows:

iloc

and

loc

Columns are chosen by name, and rows? Rows have two tools, and confusing them is a classic beginner's mistake.

iloc

is short for integer location, that is, a position counted from zero, exactly as in a Python list.

loc

reaches for a label from the index, that is, for what you see down the left-hand side of the printout. In our table the index is the numbers from zero to three, so both spellings look alike, yet they behave differently over ranges:

iloc

excludes the end of the range, just as list slices do, while

loc

includes it, because labels are not positions. To either tool you may pass two arguments separated by a comma - rows first, then columns.

1print(df.iloc[0])
2# species          Lion
3# population        120
4# habitat       Savanna
5# endangered       True
6# Name: 0, dtype: object
7
8print(df.iloc[0, 1])                    # 120
9print(df.iloc[0:3].shape)               # (3, 4)
10print(df.loc[0:2, "species"].tolist())  # ['Lion', 'Elephant', 'Cheetah']

The first printout shows what a row in Pandas really is: a Series whose index is the column names and whose values have mixed types, which is why

dtype

reads

object

. The second reaches for row zero and column one, counting from zero, and so lands on the lion's head count. The third and fourth are the promised difference:

iloc[0:3]

yields three rows, because three falls outside the range, and

loc[0:2]

also yields three rows, because two fits inside it. The spelling

df.iloc[0]

will serve you once more as a counter-example - it is the thing that returns the first element of every column, which people sometimes wrongly expect from

head

.

Filtering: a boolean mask over the whole table

Now for the most important skill in this lesson. A question from the field is rarely "show me row number two" and almost always "show me the species with more than a hundred individuals". The answer comes in two steps, both of which you already met on a single Series. First you build a boolean mask: the comparison

df["population"] > 100

checks the condition separately for each row and returns a Series of

True

and

False

values exactly as long as the table.

1mask = df["population"] > 100
2
3print(mask)
4# 0     True
5# 1     True
6# 2    False
7# 3     True
8# Name: population, dtype: bool

Four rows of the table gave four boolean answers, and the index stayed the same - it is the index that binds the mask to the rows. Notice that there is not a single head count in the result: a mask stores no data, only a decision about each row. On its own it has filtered nothing yet, and

df

after this line still has four rows. Only putting the mask inside the table's square brackets keeps the rows marked

True

.

1print(df[df["population"] > 100])
2#     species  population  habitat  endangered
3# 0      Lion         120  Savanna        True
4# 1  Elephant         450   Forest       False
5# 3   Giraffe         200  Savanna       False

Three rows instead of four, and the cheetah dropped out, because eighty-five does not meet the condition. Look at the index on the left: the numbers 0, 1 and 3 remain, with a hole where two used to be. Pandas does not renumber rows after filtering, which means you always know where a row came from in the original. The whole construction is built in four steps and the order is worth memorising: first

df[

, then

df['population']

, then

> 100

, and finally

]

. In other words: you open a bracket on the table, build a condition on a column inside it and close the bracket. And once again the sentence that matters most: the original

df

still has four rows after this operation, because filtering returns a new table.

Conditions can be combined, as long as you keep two details of the syntax in mind. Instead of the words

and

and

or

you use the symbols

&

and

|

, because Python is comparing whole series and not single values. Each condition also has to sit inside its own round brackets, because

&

binds more tightly than

>

and without the brackets Python would evaluate it in the wrong order.

1print(df[(df["population"] > 100) & (df["endangered"] == False)])
2#     species  population  habitat  endangered
3# 1  Elephant         450   Forest       False
4# 3   Giraffe         200  Savanna       False

Two species are left: numerous and not endangered. The symbol

&

means "both conditions at once", while

|

would mean "at least one of them". Had you written

and

in that spot, you would have got

ValueError: The truth value of a Series is ambiguous

- Python cannot tell whether a series of four booleans is true or false, and it rightly refuses to guess. The result is, as always, a new table; after these three blocks

df

is exactly what it was when it came out of the file.

New columns and the

apply

method

Field work is not only about picking out what you already have, but also about adding information you did not. You create a new column by plain assignment to a name the table does not yet contain. On the right-hand side you can write an operation on an existing column, and Pandas will perform it for every row separately, with no loop at all - the same vectorisation you met with NumPy arrays.

1df["population_thousands"] = df["population"] / 1000
2
3print(df["population_thousands"].tolist())
4# [0.12, 0.45, 0.085, 0.2]

From now on the table has five columns instead of four, and this is one of the few operations that change

df

in place, with no need to assign the result back. It is worth filing away in your head: assigning to

df["something"]

modifies the table, whereas filtering,

head

or

describe

merely return new objects. The

population

column came to no harm - it still holds integers, and the new fractional values landed beside it, in a column of their own.

Not every transformation fits into a single arithmetic operation. When you want to run your own logic for each value, you reach for the method

apply

, which takes a function and runs it one element at a time over the column. Usually you hand it a lambda, a function with no name written on one line: after the word

lambda

comes the argument name, a colon, and then the expression whose result gets returned.

1df["status"] = df["population"].apply(
2    lambda x: "endangered" if x < 100 else "stable"
3)
4
5print(df["status"].tolist())
6# ['stable', 'stable', 'endangered', 'stable']

The function received one hundred and twenty, four hundred and fifty, eighty-five and two hundred in turn, and returned four strings, out of which Pandas built a new column. The argument

x

is a single value and not the whole column, because

apply

called on a Series works element by element. The

population

column itself was again left untouched -

apply

overwrites nothing in it, it only reads. Notice that the cheetah with its eighty-five got the status

endangered

even though the

endangered

column already said

True

; those are two independent sources of the same fact, and in a real dataset it is worth checking whether they agree.

You can also overwrite a column by putting a name on the left that already exists. I do that reluctantly and I advise you against it too, because after such an assignment you will not get the original measurements back - only the result stays in memory. When I need a recalculated version, I prefer to make a copy of the table with the method

copy

and recalculate the copy. It costs one line and it saves you from repeating the whole loading process from scratch.

1projection = df.copy()
2projection["population"] = projection["population"] * 1.1
3
4print(projection["population"].round(1).tolist())  # [132.0, 495.0, 93.5, 220.0]
5print(df["population"].tolist())                   # [120, 450, 85, 200]

The ten per cent growth forecast landed in

projection

, while

df

still holds the numbers measured in the field - which was the whole point. Notice in passing that after multiplying by a fraction the values stopped being whole: ninety-three and a half animals is biological nonsense, but mathematically that is how multiplication works and Pandas switched the column to a floating-point type. The

round

call in the printout is there for a reason as well; without it you would see

495.00000000000006

, because a computer stores fractions in binary and one tenth has no exact binary form. Had you skipped

copy

and assigned the result straight into

df

, the

population

column in the original would have changed its type and its values too.

Dropping columns:

drop

and the axis

Sooner or later a column lands in the table that was only ever needed for a moment. Throwing things out is the job of the method

drop

, but it comes with a catch: the same command removes both rows and columns, so you have to say which dimension you mean. That is what the

axis

parameter is for. Memorise it once and for all:

axis=0

is rows,

axis=1

is columns. The numbering is not arbitrary - it echoes the

shape

tuple, where rows sit at position zero and columns at position one.

1df["temp"] = 1
2clean = df.drop("temp", axis=1)
3
4print("temp" in clean.columns)  # False
5print("temp" in df.columns)     # True
6
7df = df.drop("temp", axis=1)
8print("temp" in df.columns)     # False

The second printout is the interesting one. The

temp

column disappeared from

clean

, yet it stayed in

df

, because

drop

removes nothing from the original - it returns a new table without the named column. Only assigning the result back to

df

, as on the fifth line, makes the change stick. There is admittedly a parameter

inplace=True

which modifies the table in place, but I recommend the assignment: you can see with your own eyes that the variable received new contents, and calls can be chained one after another.

Three variants of this command will not work, and I see all three of them in students' work. The spelling

df.drop('temp', axis=0)

passes the syntax check but ends in

KeyError: "['temp'] not found in axis"

, because with axis zero Pandas goes looking for a row labelled

temp

, and there is no such row. The spelling

df.delete(column='temp')

gives an

AttributeError

, because a DataFrame has no

delete

method - it gets confused with

np.delete

from NumPy, which works on arrays. The spelling

df.remove('temp')

also ends in an

AttributeError

, because

remove

is a method of Python lists, not of Pandas tables. Exactly one form is correct:

df.drop('temp', axis=1)

. For completeness, there is an equivalent spelling

df.drop(columns='temp')

, which reads better in long scripts, but you have to know the

axis=1

version because you will meet it in every piece of documentation. The method

df.rename(columns={"population": "count_2024"})

behaves the same way for renaming columns and likewise returns a copy instead of fixing the original.

Holes in the data

Field data comes with holes in it: a collar failed, an observer never made it out, a page got soaked. Pandas records a missing value as NaN, not a number. To build such a holey table in Python you use

None

, which Pandas converts to NaN by itself. You find the gaps with the method

isnull

, which returns a table of booleans of the same shape as the data, where

True

means "there is a hole here". Appending

.sum()

adds those values up by column, and because

True

counts as one, you get the number of missing entries in each column.

1notes = pd.DataFrame({
2    "species": ["Lion", "Elephant", "Cheetah", "Giraffe"],
3    "population": [120, None, 85, 200],
4    "habitat": ["Savanna", "Forest", None, "Savanna"],
5})
6
7print(notes.isnull().sum())
8# species       0
9# population    1
10# habitat       1
11# dtype: int64
12
13print(notes["population"].tolist())  # [120.0, nan, 85.0, 200.0]

One hole in the head counts, one in the habitats, a full set of species names - all of it from a single line of code. The last printout shows a side effect that is easy to forget: the

population

column has stopped being integer and one hundred and twenty now displays as

120.0

. That happens because NaN is a floating-point value, so the whole column had to move to that type. Apart from that the data has not shifted by a millimetre -

isnull

repairs nothing, it only points a finger.

The simplest reaction to missing values is to throw out the incomplete observations with the method

dropna

. By default it removes the entire row in which even one value is missing, and just like

drop

it returns a new table instead of fixing the old one.

1print(notes.dropna().shape)  # (2, 3)
2print(notes.shape)           # (4, 3)

Out of four observations two are left, because the elephant lost its head count and the cheetah its habitat. Notice how expensive that cleanliness was: you deleted half the dataset to patch two cells, and the correct species names and the cheetah's correct head count went out with them. On a large dataset with scattered holes

dropna

is perfectly fine; on a small one it can cut the ground from under your feet. The original

notes

still has four rows, so nothing was lost for good.

The other way out is to fill the holes with the method

fillna

, to which you give a replacement value. Choosing that value is a scientific decision, not a technical one. Putting a zero into a head count column means "this species is not here at all", which is simply untrue and will drag down every average you compute afterwards. So for measurements I recommend filling with the mean of the remaining observations: it invents no new information, it merely inserts a typical value.

1average = notes["population"].mean()
2print(average)  # 135.0
3
4print(notes["population"].fillna(0).tolist())        # [120.0, 0.0, 85.0, 200.0]
5print(notes["population"].fillna(average).tolist())  # [120.0, 135.0, 85.0, 200.0]

The mean came out as one hundred and thirty-five, that is four hundred and five divided by three and not by four - the statistical methods in Pandas skip NaN when counting. Compare the two results: the zero version shoves into the middle of the dataset a value lower than everything else, while the mean version inserts a number that does not distort the picture. Neither call changed

notes

, because

fillna

, like

dropna

, returns a copy - to keep the correction you have to assign the result back to the column.

Grouping:

groupby

So far we have looked at rows one at a time. The real questions from the reserve sound different, though: not "how many lions are there" but "which zone sustains the larger populations, the savanna or the forest". That is a question about groups, and the method that answers it is

groupby

, to which you give the name of the column to split on. The call is made of six parts:

df

,

.

,

groupby

,

(

,

'habitat'

,

)

. On its own it returns no table yet, only a grouping object, which is a plan for the split. The result appears only once you append an operation to it -

mean

, for instance.

1by_habitat = df.groupby("habitat")
2
3print(by_habitat["population"].mean())
4# habitat
5# Forest     450.0
6# Savanna    135.0
7# Name: population, dtype: float64

Two habitats, two means. What matters most is what happened to the index: the habitat names moved from a column into the row labels of the result, because after grouping it is they that identify a row. The spelling

by_habitat["population"]

says "compute on the head count column only", and it is worth using, because trying to average a text column makes no sense.

df

itself is unchanged after this operation: grouping moves nothing and removes nothing from the original, it only reads it and builds a summary alongside.

A grouping object can be questioned in many ways and you do not have to rebuild it each time. The method

sum

adds the values within a group, while

size

counts the rows, which answers the question "how many species fell into each zone".

1print(by_habitat["population"].sum())
2# habitat
3# Forest     450
4# Savanna    405
5# Name: population, dtype: int64
6
7print(by_habitat.size())
8# habitat
9# Forest     1
10# Savanna    3
11# dtype: int64

The sum for the forest came out higher than for the savanna, although the mean said the same thing far more emphatically - which is exactly why it pays to look at both numbers together. The

size

printout explains the difference: one species lives in the forest and three in the savanna, so four hundred and fifty elephants beat three smaller populations on the sum, while the gap in the mean is more than twofold. Notice that

size

needs no column to be named, because it counts rows rather than values.

When you want several statistics at once, instead of calling the methods one after another you hand a dictionary to the method

agg

: the key is a column name and the value is a list of operation names written as text.

1stats = df.groupby("habitat").agg({
2    "population": ["mean", "sum", "count"],
3    "endangered": "sum",
4})
5
6print(stats)
7#         population            endangered
8#               mean  sum count        sum
9# habitat
10# Forest       450.0  450     1          0
11# Savanna      135.0  405     3          2

Four statistics in one small table, and the header has gone two levels deep: the top row says which column the figure came from, the bottom row which operation was applied. The last column hides a small trick worth remembering: summing booleans gives you the number of

True

values, so

endangered

with a sum of two means two endangered species in the savanna. This whole operation, once again, only read the data -

df

still has the same rows and columns it had before the grouping.

Sorting:

sort_values

Summaries answer questions about groups, but often a plain ranking is all you need: which species is the most numerous. Ordering rows is the job of the method

sort_values

, to which you give a column name. By default it sorts ascending, and the parameter

ascending=False

flips the order to descending. You may also pass a list of columns, in which case Pandas sorts by the first one and settles ties with the second.

1ranking = df.sort_values("population", ascending=False)
2
3print(ranking["species"].tolist())  # ['Elephant', 'Giraffe', 'Lion', 'Cheetah']
4print(ranking.index.tolist())       # [1, 3, 0, 2]
5print(df["species"].tolist())       # ['Lion', 'Elephant', 'Cheetah', 'Giraffe']

The ranking from elephant down to cheetah fell into place in a single line. The second printout shows the thing that surprises everybody the first time: the index travelled with the rows, so the top of the table is now the row labelled 1 and not 0. That is deliberate, because it tells you where each row sat in the original. The third printout is the most important sentence in this section: the order inside

df

has not changed one bit, because

sort_values

returns a sorted copy. There is also

sort_index

, which orders the table by row labels and is useful precisely for getting back to the starting state after a sort.

Joining two notebooks:

merge

and

concat

On an expedition, everything rarely fits in one notebook. Suppose that next to the species table you have a second one describing habitats: their names and their areas. You want to line them up, adding to each animal the area of its zone. Joining tables on a shared column is the job of the function

pd.merge

, familiar to anyone who has seen a JOIN in SQL. You give it two tables, the name of the shared column in the

on

parameter and the joining style in

how

. The value

"left"

means "keep every row from the left-hand table", which in our case means every animal.

1habitats = pd.DataFrame({
2    "habitat": ["Savanna", "Forest", "Wetland"],
3    "area_km2": [1200, 800, 300],
4})
5
6merged = pd.merge(df, habitats, on="habitat", how="left")
7
8print(merged[["species", "habitat", "area_km2"]])
9#     species  habitat  area_km2
10# 0      Lion  Savanna      1200
11# 1  Elephant   Forest       800
12# 2   Cheetah  Savanna      1200
13# 3   Giraffe  Savanna      1200

Every animal received the area of its zone, and the value twelve hundred was repeated three times, because three species live in the savanna - a join copies data from the smaller table wherever it fits. Notice what the result does not contain: the wetland from the

habitats

table appears nowhere, because no species in

df

lives there, and a

"left"

join looks only at the left-hand table. Had you passed

how="outer"

, a fifth row would have arrived with the wetland and empty NaN values in the animal columns. The

"inner"

variant would leave only the habitats present in both tables, and

"right"

would be the mirror image of

"left"

.

A completely different problem shows up when two tables have the same columns but different rows - when two camps have sent in notebooks of identical build, for instance. Then you do not join them on a key; you stack one under the other with the function

pd.concat

, to which you give a list of tables.

1north = pd.DataFrame({"species": ["Lion", "Zebra"], "population": [120, 300]})
2south = pd.DataFrame({"species": ["Hyena", "Impala"], "population": [60, 540]})
3
4both = pd.concat([north, south], ignore_index=True)
5
6print(both["species"].tolist())  # ['Lion', 'Zebra', 'Hyena', 'Impala']
7print(both.index.tolist())       # [0, 1, 2, 3]

Four rows from two camps in one table, in the order given in the list. The parameter

ignore_index=True

matters more than it looks: without it the resulting index would read 0, 1, 0, 1, because each table would bring along its own labels and duplicates would appear. By default

concat

adds rows, that is, it works along axis zero; with

axis=1

it would set columns side by side instead. Both source tables are left as they were -

north

still has its two rows.

Saving the results

An analysis that dies when you close the terminal is not yet a result of the expedition. You save a finished table with the method

to_csv

, giving it a file name. One parameter is practically obligatory here:

index=False

. Without it Pandas writes the row labels into the file as well, as a first column with no header, and on the next load

read_csv

will treat that column as ordinary data and name it

Unnamed: 0

. After a few such rounds the file sprouts columns nobody ever ordered.

1report = df[["species", "population", "status"]]
2report.to_csv("safari_report.csv", index=False)
3
4with open("safari_report.csv") as f:
5    print(f.read())
6# species,population,status
7# Lion,120,stable
8# Elephant,450,stable
9# Cheetah,85,endangered
10# Giraffe,200,stable

The file looks exactly like the one we started the loading from: a header and four rows separated by commas. Only the three chosen columns were written, because

to_csv

is called on whatever you hand it, and we handed it a slice - the remaining columns still sit in

df

and came to no harm. The method returns nothing and changes nothing in the table; it simply lays a copy of it down on disk. The twins

to_excel

and

to_json

write the same DataFrame in other formats, the first one again needing an extra package for spreadsheet support.

Summary

• A DataFrame is a table with rows and columns - the only such structure in Pandas. A Series is one-dimensional: a single column with an index.

  Panel

  was removed in version 1.0, and a

  Table

  class has never existed in Pandas at all.
• You build a table from a dictionary with

  pd.DataFrame({...})

  , where the key is the column name and the value is a list.
• You load a file with six pieces:

  pd

  ,

  .

  ,

  read_csv

  ,

  (

  ,

  'data.csv'

  ,

  )

  .

• df.head()

  returns the first five rows of the table. It does not return the headers (that is

  df.columns

  ), it does not return the first element of each column (that is

  df.iloc[0]

  ) and it sorts nothing (that is

  sort_values

  ).
• You call

  df.info()

  without

  print

  , because it prints by itself and returns

  None

  .

  df.describe()

  computes statistics for numeric columns only.
• You filter in four steps:

  df[

  ,

  df['population']

  ,

  > 100

  ,

  ]

  . Conditions are combined with

  &

  and

  |

  , each in its own brackets.
• You drop a column with

  df.drop('temp', axis=1)

  , because

  axis=1

  is columns and

  axis=0

  is rows. The methods

  delete

  and

  remove

  do not exist on a DataFrame.
• You group with six pieces:

  df

  ,

  .

  ,

  groupby

  ,

  (

  ,

  'habitat'

  ,

  )

  , and the result appears only once you append an operation such as

  mean()

  .
• Almost every Pandas method returns a new table and leaves the original alone. The exception is assignment to

  df["column"]

  , which changes the table in place.

In the next lesson we will turn these same columns into charts, and you will see how one glance at a picture replaces reading a hundred rows. For now remember one thing, @name: Pandas does not collect the data for you and it does not correct the observer's mistakes - all it gives you is a notebook in which every column has a name, a type of its own and the whole table within reach the moment you ask a question.