Series and DataFrame

In short: In pandas a Series is a column with labels, and a DataFrame is a table of such columns; the index carries the row names and lines up the arithmetic by them.

Problem

“How much money sits in the stockroom?”

The manager asks how much the stock in the stockroom is worth. The answer is price times stock for each product, then a sum. Next they will want to see the number for one product, by name.

You can already work it out with NumPy. But in the lesson on summaries argmax returned 22, a position, and you had to take the name from a separate names array. If you sort one array and forget the other, the names and the numbers drift apart and nobody notices.

pandas keeps a label next to every number: which product it is. In this lesson you will see how.

The idea

A column with labels: Series

A Series is a NumPy array with a label next to each value. The labels are called the index. Here are three prices with the product names as the index:

Pythondata: the shop tables

import pandas as pd

prices = pd.Series([1.2, 1.6, 7.9], index=['White bread', 'Fresh milk', 'White cheese'])
print(prices)
print(prices['White cheese'])
print(prices.index)
prices.to_numpy()

import pandas as pd prices = pd.Series([1.2, 1.6, 7.9], index=['White bread', 'Fresh milk', 'White cheese']) print(prices) print(prices['White cheese']) print(prices.index) prices.to_numpy()

▶ RunRestore the code

prices['White cheese'] gives 7.9: the value is taken by label, not by place. prices.index shows the labels, and to_numpy() gives the bare numbers as an array.

The idea

A table of columns: DataFrame

A DataFrame is several Series with a shared index, side by side. Each column has a name and its own type. You saw it in the first look; now look at it piece by piece:

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
print(products.columns)
print(products.index)
products.dtypes

import pandas as pd products = pd.read_csv('products.csv') print(products.columns) print(products.index) products.dtypes

▶ RunRestore the code

• columns are the names of the columns: 6 of them.
• index is a RangeIndex from 0 to 24: read_csv numbers the rows from 0 unless you tell it otherwise. The id column is a different thing and starts at 1.
• dtypes is the type of each column. int64 and float64 are numbers, str is text.

See it

A column is a Series

You take a column with its name in square brackets, as in the first look. The result is a Series:

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
price = products['price']
print(type(price))
print(price.head(3))

import pandas as pd products = pd.read_csv('products.csv') price = products['price'] print(type(price)) print(price.head(3))

▶ RunRestore the code

On the left is the index of the table (0, 1, 2), and at the bottom is the name of the column (Name: price) and its type. Everything you can do with a Series, you can do with every column.

1. What is products['stock']?

A NumPy array with just the numbersA Series: the stock values with the index of the table (0 to 24)A DataFrame with one column

Check

The idea

Names in the index: set_index

The numbers 0 to 24 tell the manager nothing. set_index('name') makes the name column the index: the product names become the labels of the rows.

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
by_name = products.set_index('name')
print(by_name[['price', 'stock']].head(3))
print(by_name['stock']['Coffee beans'])
print(by_name['price'].argmax(), by_name['price'].idxmax())

import pandas as pd products = pd.read_csv('products.csv') by_name = products.set_index('name') print(by_name[['price', 'stock']].head(3)) print(by_name['stock']['Coffee beans']) print(by_name['price'].argmax(), by_name['price'].idxmax())

▶ RunRestore the code

Now the stock of coffee is by_name['stock']['Coffee beans']: 15 packs. And the most expensive product is an answer with a name:

• argmax() still gives the position: 22.
• idxmax() gives the label of the largest value: “Knife set”. There is also idxmin().

The labels travel with the numbers: whatever you do with a Series, the name stays next to the price.

The idea

And back: reset_index

set_index does not change products; it returns a new table. The name column is now its index, so there are 5 columns instead of 6. reset_index() turns the index back into an ordinary column and numbers the rows from 0 again:

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
by_name = products.set_index('name')
print(products.shape, by_name.shape)
by_name.reset_index().head(3)

import pandas as pd products = pd.read_csv('products.csv') by_name = products.set_index('name') print(products.shape, by_name.shape) by_name.reset_index().head(3)

▶ RunRestore the code

What goes in the index depends on the question. For “by name” you put the names there; for a plain table that you will write to a file you turn them back into a column.

Try it

The cheapest product

Which product is the cheapest? Put the names in the index (by_name), and store in cheapest the name of the product with the smallest price.

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
by_name = ...
cheapest = ...
print(cheapest)

import pandas as pd products = pd.read_csv('products.csv') by_name = ... cheapest = ... print(cheapest)

Check▶ Run

See it

The index lines up the arithmetic

The supplier sends a new price list. The products are the same, but in a different order. With NumPy arrays you would subtract the first from the first and the second from the second. With Series, pandas subtracts the values with the same label, wherever they are:

Pythondata: the shop tables

import pandas as pd

old_prices = pd.Series([1.2, 1.6, 7.9, 13.9], index=['White bread', 'Fresh milk', 'White cheese', 'Coffee beans'])
new_prices = pd.Series([14.9, 1.3, 8.4, 1.6], index=['Coffee beans', 'White bread', 'White cheese', 'Fresh milk'])
print((new_prices - old_prices).round(2))
print((new_prices.to_numpy() - old_prices.to_numpy()).round(2))

import pandas as pd old_prices = pd.Series([1.2, 1.6, 7.9, 13.9], index=['White bread', 'Fresh milk', 'White cheese', 'Coffee beans']) new_prices = pd.Series([14.9, 1.3, 8.4, 1.6], index=['Coffee beans', 'White bread', 'White cheese', 'Fresh milk']) print((new_prices - old_prices).round(2)) print((new_prices.to_numpy() - old_prices.to_numpy()).round(2))

▶ RunRestore the code

With Series, coffee went up by €1, bread by €0.10 and cheese by €0.50. With the arrays the first number is €13.70: the new price of coffee minus the old price of bread. The last one is -€12.30: milk minus coffee. The numbers look perfectly serious and they are wrong.

This is called alignment by index. The result is ordered by label, alphabetically.

See it

A label that is missing

If a label is in only one of the Series, pandas has nothing to compute it with:

Pythondata: the shop tables

import pandas as pd

last_week = pd.Series([42, 30, 6], index=['White bread', 'Fresh milk', 'Coffee beans'])
this_week = pd.Series([31, 47, 8, 5], index=['Fresh milk', 'White bread', 'Coffee beans', 'White cheese'])
this_week - last_week

import pandas as pd last_week = pd.Series([42, 30, 6], index=['White bread', 'Fresh milk', 'Coffee beans']) this_week = pd.Series([31, 47, 8, 5], index=['Fresh milk', 'White bread', 'Coffee beans', 'White cheese']) this_week - last_week

▶ RunRestore the code

White cheese is only in this week, so its difference is NaN. That is honest: we do not know how much cheese we sold last week. You saw in summaries that missing is not zero, and pandas keeps to that.

More precisely, if you are curious

If you know that missing means zero (a product we did not sell last week), say so explicitly: this_week.sub(last_week, fill_value=0). That way the decision is visible in the code, not hidden. Alignment applies to +, -, *, / and to comparisons between two Series.

Try it

What went up

The two price lists are in a different order. Store in change how much the price of each product changed (new minus old), rounded to 2 places, with the product names in the index.

Pythondata: the shop tables

import pandas as pd

old_prices = pd.Series([1.2, 1.6, 7.9, 13.9], index=['White bread', 'Fresh milk', 'White cheese', 'Coffee beans'])
new_prices = pd.Series([14.9, 1.3, 8.4, 1.6], index=['Coffee beans', 'White bread', 'White cheese', 'Fresh milk'])
change = ...
print(change)

import pandas as pd old_prices = pd.Series([1.2, 1.6, 7.9, 13.9], index=['White bread', 'Fresh milk', 'White cheese', 'Coffee beans']) new_prices = pd.Series([14.9, 1.3, 8.4, 1.6], index=['Coffee beans', 'White bread', 'White cheese', 'Fresh milk']) change = ... print(change)

Check▶ Run

Exercise

The stock in the stockroom

Answer the manager. Put the product names in the index. Store in stock_value the value of the stock of each product (price times stock), in total its sum, and in real_total the sum without the gift card, rounded to 2 places.

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
by_name = ...
stock_value = ...
total = ...
real_total = ...
print(total, real_total)

import pandas as pd products = pd.read_csv('products.csv') by_name = ... stock_value = ... total = ... real_total = ... print(total, real_total)

Check▶ Run

Test

Check yourself

1. The table products was read with read_csv. What is products.index?

The id columnThe row numbers from 0 to 24 (a RangeIndex)The names of the columns

2. After by_name = products.set_index('name'), what do by_name['price'].argmax() and by_name['price'].idxmax() give?

22 and 22“Knife set” and 2222 and “Knife set”

3. The week this_week has White cheese and last_week does not. What is (this_week - last_week)['White cheese']?

The cheese sold this week0NaN

4. Two price lists are Series with the product names, but in a different order. Why is new.to_numpy() - old.to_numpy() dangerous?

It subtracts by place: the coffee from the new list minus the bread from the old oneIt is not dangerous; it gives the same as new - oldIt raises an error because the order differs

Check

Start the lesson over

Related lessons

• Builds on: The first table · The NumPy array · Vectorization · Summaries and missing values
• Continues in: Selecting: [], loc and iloc · Filters and sorting · New columns · Types and missing values
• Tasks on this topic: Tables with pandas