pandas · Chapter 2 of 22
The index and alignment
By the end of this chapter you can say what a DataFrame's index is, make a column the row labels with set_index and put them back with reset_index, look a value up by its label, and do arithmetic between two Series that pandas pairs by label: with a missing value where a label is on one side only, or with fill_value, reindex, sort_index and the labels on one side or both. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Tables from zero
- 4 tasks
- pandas
- Free chapter
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.
Why you need this
Every Sunday Gopal counts the 120 products on the warehouse shelves, and on Monday he wants one number for each sku: how many pieces left the shelf since last Sunday. That is how he knows what is selling and what to ask the weavers for. Gopal and Sunil take turns, and they walk the shelves in different orders, so the two counts do not list the skus in the same order.
With what you know from chapter 1, you would read both counts and subtract one on_hand column from the other. Here are four dupattas from two real counts, 20 and 27 September, the second in Sunil's shelf order:
import pandas as pd
sunday_20 = pd.DataFrame({
"sku": ["KG-DUP-001", "KG-DUP-002", "KG-DUP-003", "KG-DUP-004"],
"on_hand": [22, 25, 11, 11],
})
sunday_27 = pd.DataFrame({
"sku": ["KG-DUP-003", "KG-DUP-001", "KG-DUP-004", "KG-DUP-002"],
"on_hand": [9, 20, 11, 25],
})
print(sunday_27["on_hand"] - sunday_20["on_hand"])
0 -13
1 -5
2 0
3 14
Name: on_hand, dtype: int64
No error, and three of the four numbers are wrong. Row 0 of one table is KG-DUP-003 and row 0 of the other is KG-DUP-001, so pandas subtracted two different products. The right changes are -2, -2, 0 and 0: nothing sold 13 pieces, and nothing gained 14. It is Gopal's sorted column from chapter 1 again, in another form: the numbers were paired by where they sat, not by what they were.
The cure is to tell pandas what each row is. That is the index.
The lesson
The labels of a table
Every DataFrame has an index: one label for each row. read_csv gives the rows 0, 1, 2 and so on, a RangeIndex, which is just a counter:
weavers = pd.read_csv("/workspace/data/weavers.csv")
print(weavers.index)
RangeIndex(start=0, stop=39, step=1)
Those labels say only where a row was in the file. A weaver already has a better name for her row: her weaver_id. set_index makes a column the index:
by_id = weavers.set_index("weaver_id")
print(by_id.head(3))
print(list(by_id.columns))
print(by_id.shape)
name village looms joined
weaver_id
W01 Abdul Kalam Ansari Sarai Mohana 2 2019-06-17
W02 Rehana Bano Sarai Mohana 2 2022-12-02
W03 Mohd. Yusuf Ansari Bajardiha 3 2022-12-20
['name', 'village', 'looms', 'joined']
(39, 4)
- The weaver ids are now the row labels, printed on the left in place of 0, 1, 2.
- weaver_id left the columns: there are four now, not five, and the shape says so.
- The index has a name, the column's old name, printed on a line of its own above the labels.
by_id.index.nameis'weaver_id'. - set_index returns a new DataFrame; weavers itself is unchanged.
The index is now an Index of text:
print(by_id.index)
Index(['W01', 'W02', 'W03', 'W04', 'W05', 'W06', 'W07', 'W08', 'W09', 'W10',
'W11', 'W12', 'W13', 'W14', 'W15', 'W16', 'W17', 'W18', 'W19', 'W20',
'W21', 'W22', 'W23', 'W24', 'W25', 'W26', 'W27', 'W28', 'W29', 'W30',
'W31', 'W32', 'W33', 'W34', 'W35', 'W36', 'W37', 'W38', 'W39'],
dtype='str', name='weaver_id')
Looking a value up by its label
A column of an indexed table is a Series with the same labels, and a label in square brackets gives one value, as a key does in a dict:
villages = by_id["village"]
print(villages["W07"])
print(by_id["looms"]["W07"])
Ramnagar
1
The first brackets pick the column, the second the label. A label that is not there is a KeyError, the last line of a long traceback:
KeyError: 'W40'
W40 is Nasreen Bano, who joined in August and is not in weavers.csv yet.
A good index names each row once. by_id.index.is_unique is True. Make the village the index and it is False, because the 39 weavers live in seven villages, and a lookup gives back every row with that label:
by_village = weavers.set_index("village")
print(by_village.index.is_unique)
print(by_village["looms"]["Lohta"])
False
village
Lohta 5
Lohta 3
Lohta 4
Name: looms, dtype: int64
Choose a column that names one row: a sku, a weaver id, an order id.
Back to columns: reset_index
reset_index does the opposite: the labels go back to being a column, first in the table, and the rows get 0, 1, 2 again. With drop=True the labels are thrown away instead:
print(by_id.reset_index().head(2))
print(by_id.reset_index(drop=True).head(2))
weaver_id name village looms joined
0 W01 Abdul Kalam Ansari Sarai Mohana 2 2019-06-17
1 W02 Rehana Bano Sarai Mohana 2 2022-12-02
name village looms joined
0 Abdul Kalam Ansari Sarai Mohana 2 2019-06-17
1 Rehana Bano Sarai Mohana 2 2022-12-02
Use drop=True only when the labels mean nothing, like an old 0, 1, 2.
Alignment: pandas pairs by label
Now the reason the index matters. When you add, subtract or multiply two Series, pandas does not pair the first value with the first value. It pairs each label with the same label on the other side, wherever it sits. This is alignment.
Here are the four dupattas again as Series labelled by sku, with one change: Gopal counted only three on each Sunday, and not the same three. before minus after is how many pieces left the shelf:
before = pd.Series([22, 25, 11], index=["KG-DUP-001", "KG-DUP-002", "KG-DUP-003"])
after = pd.Series([9, 20, 11], index=["KG-DUP-003", "KG-DUP-001", "KG-DUP-004"])
print(before - after)
KG-DUP-001 2.0
KG-DUP-002 NaN
KG-DUP-003 2.0
KG-DUP-004 NaN
dtype: float64
What pandas did, step by step:
- It took every label from both sides, KG-DUP-001 to KG-DUP-004, and sorted them. That is the result's index.
- For each label it found the value on each side, wherever it sat: KG-DUP-001 is first in before and second in after, so 22 minus 20 is 2.
- KG-DUP-002 is only in before, and KG-DUP-004 only in after. There is nothing to subtract, so the result there is NaN, a missing value.
- One NaN makes the whole Series float64, so 2 prints as 2.0.
This is a feature. A sum between two tables can never pair the wrong rows, however they are sorted, and a label that is on one side only shows up as a missing value instead of quietly borrowing a neighbour's number. Handling missing values properly is chapter 8; here, a NaN means "one side had no value for this label".
A missing label counted as 0: fill_value
Sometimes a missing label has a plain meaning. Last week's pieces sold on the website and in the store, by sku: a sku that one channel did not sell sold 0 there. + gives NaN for those, and the add method with fill_value=0 treats a label missing on one side as 0 first:
website = pd.Series([3, 5, 2], index=["KG-STL-004", "KG-STL-009", "KG-STL-011"])
store = pd.Series([1, 4], index=["KG-STL-009", "KG-STL-002"])
print(website + store)
print(website.add(store, fill_value=0))
KG-STL-002 NaN
KG-STL-004 NaN
KG-STL-009 6.0
KG-STL-011 NaN
dtype: float64
KG-STL-002 4.0
KG-STL-004 3.0
KG-STL-009 6.0
KG-STL-011 2.0
dtype: float64
Each operator has a method with the same job and a fill_value: add for +, sub for -, mul for * and div for /. a.sub(b) is a - b, in that order. A label missing on both sides would still be NaN, but alignment never makes one: every label comes from one side or the other.
The labels in your order: reindex and sort_index
Alignment sorts the labels, and a Series may hold only some of the skus you want. reindex takes a list of labels and gives a Series with exactly those, in that order: a label it has keeps its value, a label it lacks gets NaN (or fill_value), and a label not in the list is left out. sort_index sorts by the labels:
sold = pd.Series([5.0, 9.0, 2.0], index=["KG-STL-011", "KG-STL-009", "KG-STL-004"])
shelf = ["KG-STL-001", "KG-STL-004", "KG-STL-009", "KG-STL-011"]
print(sold.sort_index())
print(sold.reindex(shelf))
print(sold.reindex(shelf, fill_value=0))
KG-STL-004 2.0
KG-STL-009 9.0
KG-STL-011 5.0
dtype: float64
KG-STL-001 NaN
KG-STL-004 2.0
KG-STL-009 9.0
KG-STL-011 5.0
dtype: float64
KG-STL-001 0.0
KG-STL-004 2.0
KG-STL-009 9.0
KG-STL-011 5.0
dtype: float64
reindex works with any list of labels, including a column of another table: the catalogue's sku column gives every product in catalogue order.
Which labels are on one side
To see what alignment will do before it does it, compare the two indexes. difference gives the labels in the first and not in the second, sorted; intersection the labels in both:
print(website.index.difference(store.index))
print(store.index.difference(website.index))
print(website.index.intersection(store.index))
print(list(website.index.difference(store.index)))
Index(['KG-STL-004', 'KG-STL-011'], dtype='str')
Index(['KG-STL-002'], dtype='str')
Index(['KG-STL-009'], dtype='str')
['KG-STL-004', 'KG-STL-011']
Both return an Index; list() turns one into a plain list.
The one place alignment surprises
Alignment pairs labels, so two Series of the same length can share no labels at all. Here the catalogue's prices are labelled by sku and its costs by 0 to 119:
catalogue = pd.read_csv("/workspace/data/products.csv")
cost = catalogue["cost_rs"]
mrp = catalogue.set_index("sku")["mrp_rs"]
print(len(cost), len(mrp))
margin = mrp - cost
print(len(margin))
print(margin.head(3))
120 120
240
sku
0 NaN
1 NaN
2 NaN
dtype: float64
120 values minus 120 values gave 240, every one NaN. No label is on both sides: 0 is not KG-SAR-001. The same happens with two RangeIndexes that do not overlap: a Series labelled 0, 1, 2 plus one labelled 5, 6, 7 is six NaNs. When a sum comes out all NaN, print both indexes first.
Your turn
Four tasks, one file each. Task 1 indexes the catalogue by sku and looks a price up by its sku. Task 2 is Gopal's weekly change by sku from two counts whose rows are in different orders, and the skus counted last week but not this week. Task 3 counts a sku missing from a count as 0 and gives every product of the catalogue, in its order. Task 4 is an interview question: Gopal's shelf value comes back twice as long and all missing, and Devika will ask why two Series of the same length added up to NaN. Try things in ipython first: from by_sku import by_sku.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
by_sku.py: the catalogue by sku
Write two functions in by_sku.py. Each is given the catalogue as read_csv reads products.csv, with its rows labelled 0, 1, 2 and so on.
by_sku(catalogue) returns a DataFrame of the same products whose row labels (the index) are the skus, in the file's order, with the index named sku. sku is no longer a column: for products.csv the columns are name, category, fabric, weaver_id, cost_rs, mrp_rs and launched, in that order.
price_of(catalogue, sku) returns the mrp_rs of the product with that sku, as one whole number: price_of(catalogue, "KG-DUP-004") is 1699.
The Check also tries a small catalogue of 3 products whose columns are sku, name and mrp_rs; then by_sku's columns are name and mrp_rs.
- Task 2
stock_change.py: Gopal's change from one Sunday to the next
Write two functions in stock_change.py. Each is given two of Gopal's stock counts, last_week and this_week, as read_csv reads a file like stock/stock_2026-09-06.csv: the columns sku, on_hand and counted_by, rows labelled 0, 1, 2 and so on. The two counts list the skus in different orders, so row 0 of one is not row 0 of the other.
stock_change(last_week, this_week) returns a Series labelled by sku: this week's on_hand minus last week's, for each sku. A sku that is in only one of the two counts gives a missing value (NaN). The order of the labels does not matter.
not_counted(last_week, this_week) returns a Python list of the skus that are in last week's count but not in this week's, in alphabetical order, or an empty list when there are none.
The Check tries the counts of 6 and 13 September (the second in another order), two small counts of 3 skus that share 2, a count and the same skus in another order, and a count of 4 skus against a count of 1.
- Task 3
full_change.py: every product's change, a missing count as 0
Gopal's rule: a sku that is not in a count had no pieces on the shelf that Sunday. Write full_change(last_week, this_week, catalogue) in full_change.py. last_week and this_week are two stock counts as read_csv reads them (sku, on_hand, counted_by), with their skus in different orders and some skus missing from one or both; catalogue is the catalogue as read_csv reads products.csv.
Return a Series with one value for each sku of the catalogue, labelled by sku, in the catalogue's order: this week's on_hand minus last week's, where a sku missing from a count counts as 0 pieces that week. A sku in neither count gives 0. Whole or decimal numbers are both fine. A sku that is counted but is not in the catalogue is left out.
The Check tries the counts of 6 and 13 September with a few rows taken out of each (one sku out of both), and two small counts with a catalogue of 4 stoles, where one counted saree is not in the catalogue and one stole is in neither count.
- Task 4 · interview question
Interview: shelf_value.py, the shelf value that was NaN
Gopal wants what the pieces on his shelves cost Kargha: for each sku, on_hand times the catalogue's cost_rs. His count and the catalogue both have 120 rows, but his shelf_value(count, catalogue) returns 240 values, and every one is NaN. Fix it so it returns a Series labelled by sku, with one value for each sku of the catalogue: that sku's on_hand times its cost_rs. A sku of the catalogue that is not in the count gives a missing value (NaN). The order of the labels does not matter. count is a stock count as read_csv reads it (sku, on_hand, counted_by) and catalogue is products.csv as read_csv reads it, so for the count of 27 September the result has 120 values.
The Check tries the count of 27 September in another order and a count of just 3 skus.
Devika will ask: why did two Series of the same length add up to NaN, and how do you see it coming?
What you will learn
- The index: the row labels, which are names, not positions.
- set_index and reset_index: a column becomes the labels, and back.
- Lookup by label: one value of a Series by its label, like a dict.
- Alignment: arithmetic between two Series pairs values by label.
- fill_value: treat a label missing on one side as 0.
- reindex and sort_index: the labels in the order you want.
- difference and intersection: the labels on one side only, or both.
More in Tables from zero
Build The index and alignment yourself
Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.