pythontojob
Log inSign up
Chapter 04

Selecting columns and rows — by name or by place

Pointing at part of a table: columns with square brackets, rows by label with loc and by position with iloc, single cells with at — and knowing which of the two ways you are using, because after a cut or a sort they stop agreeing.

45 minPython 3.12
  1. 1Encounter
  2. 2Understand
  3. 3Worked
  4. 4Predict
  5. 5Apply
  6. 6Stretch

The problem we are solving

You have read the shop's order file and checked it, the way chapter three taught. Now people start asking you questions about it.

The stock team says: "Just send us the product and the quantity of every order — nothing else." The accountant says: "What exactly was order 1006, and what did it cost?" And your manager says: "Show me the last three orders that came in."

None of those is a calculation over the whole table. Each one is about pointing: at some columns, at one row, at the last few rows, at a single cell. Printing all of it and reading with your finger works for eight rows. It does not work for eight thousand, and it does not work inside a program, which has no finger.

Pandas has several ways to point, and the reason there are several is the real subject of this chapter. A row can be pointed at in two different ways: by its name (its label in the index) or by its place (first, second, last). When a table is freshly read, those two happen to be the same numbers, so beginners never notice there are two. The moment you take the last three rows, or sort, or filter, they stop being the same — and code that mixed them up starts returning the wrong row without a single error.

So this chapter is about selecting, but mostly it is about always knowing which of the two you are using.

By the end of this chapter you can

  • Select one column and get a Series, or several and get a DataFrame — and know which one you are holding
  • Select rows by label with .loc and by position with .iloc, and explain why their slices end differently
  • Select rows and columns together with .loc[rows, columns] and predict the shape of the result — down to a single value with .loc or .at
  • Give rows meaningful labels with set_index(), and see how that changes what .loc means
  • Explain why changing a selection does not change the original table under pandas 3, and change the original correctly; read and fix the KeyError and IndexError messages selection produces

Prerequisites: Reading a CSV, and checking what arrived.

Make the file first

In your project folder, create orders.csv with exactly these nine lines. This file will be used for the rest of the course, so save it somewhere you will find again:

text
order_id,date,branch,product,category,quantity,price
1001,2024-03-01,north,pen,stationery,12,15.0
1002,2024-03-01,south,notebook,stationery,5,60.0
1003,2024-03-02,north,bag,accessories,2,850.0
1004,2024-03-02,east,bottle,accessories,7,120.0
1005,2024-03-03,north,eraser,stationery,30,8.0
1006,2024-03-03,south,bag,accessories,1,850.0
1007,2024-03-04,east,pen,stationery,20,15.0
1008,2024-03-04,north,bottle,accessories,4,120.0

Each line is one order: an order number, the day, the branch of the shop that sold it, what was sold, the category of the product, how many, and the price of one item.

Before you write the code

Selecting looks so small that people start typing brackets immediately. Spend one minute on these steps first, because every selection mistake in this chapter comes from skipping one of them.

1. Say the question in one sentence, and underline the nouns. The nouns tell you what you are pointing at:

  • "the product and quantity of every order" — two columns, all rows
  • "order 1006" — one row, named by its order number
  • "the last three orders" — rows by position, counted from the end
  • "the price of order 1006" — one cell: a row and a column together

2. Look at the table before you point into it. You can only point at names that exist. Three things matter for selection: the size, the exact column names, and the index — the labels down the left.

python
import pandas as pd

orders = pd.read_csv("orders.csv")

print(orders.shape)
print(list(orders.columns))
print(orders.index)
text
(8, 7)
['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price']
RangeIndex(start=0, stop=8, step=1)

The column names come back as a list, inside quotes — so you can see 'price' is lowercase and has no stray spaces. The index is a RangeIndex from 0 to 7: the labels are simply a count. Remember that, because it is the coincidence the whole chapter is about. Right now, the row labelled 2 is also the row in position 2. That will not always be true.

3. Sketch the answer before you produce it. What should come out — one number, one column, or a smaller table? How many rows and columns? For the stock team's request the answer is a table of 8 rows and 2 columns. For "order 1006" it is one row. For "the price of order 1006" it is one number, 850.0. If you know the shape you expect, a wrong selection is obvious the moment you see the result.

4. Choose the tool from what you know. This list is the chapter in one picture:

  • You know column names → square brackets: orders["quantity"], orders[["product", "quantity"]]
  • You know a row's label, its name in the index → .loc: orders.loc[2]
  • You know a row's position — first, last, fifth → .iloc: orders.iloc[-3:]
  • You need rows and columns together → .loc[rows, cols] or .iloc[rows, cols]: orders.loc[2:4, ["product", "price"]]
  • You need exactly one cell → .at[row, col] or .loc[row, col]: orders.at[5, "price"]

5. Decide how you will check. After any selection, print .shape or type() of the result and compare with your sketch. It is one line, and it catches most of the mistakes below before they become wrong numbers.

The rest of the chapter goes through these tools one at a time, each with its own solved example.

One column: a name in square brackets

Put a column name in square brackets and you get that column:

python
quantity = orders["quantity"]

print(type(quantity))
print(quantity)
text
<class 'pandas.Series'>
0    12
1     5
2     2
3     7
4    30
5     1
6    20
7     4
Name: quantity, dtype: int64

What comes back is a Series — exactly the object from chapter two: values, an index down the left, a Name (the column it came from) and one dtype. Notice that the index came along with it. The 0 to 7 are not decoration: they still say which row each number belongs to.

Because it is a Series, every Series method works on it directly:

python
print(orders["quantity"].sum())
text
81

That is the usual reason to select one column: to ask a question of it.

Several columns: a list inside the brackets

For the stock team, you need two columns. Put a list of names inside the brackets:

python
stock = orders[["product", "quantity"]]

print(type(stock))
print(stock)
text
<class 'pandas.DataFrame'>
    product  quantity
0       pen        12
1  notebook         5
2       bag         2
3    bottle         7
4    eraser        30
5       bag         1
6       pen        20
7    bottle         4

The double brackets are not special syntax, and that is worth seeing clearly. The outer pair is the selection; the inner pair is an ordinary Python list, ["product", "quantity"]. You could build the list first and pass it in — wanted = ["product", "quantity"] then orders[wanted] — and it would mean exactly the same. The columns come back in the order of the list, so this is also how you reorder columns.

The result is a DataFrame, because a table with two columns is a table. And here is a distinction that catches people for months: a list with one name in it still gives a DataFrame.

python
print(orders["product"].shape)
print(orders[["product"]].shape)
text
(8,)
(8, 1)

(8,) is a Series — one dimension, eight values. (8, 1) is a DataFrame with eight rows and one column. They print differently, they have different methods, and when you save them to a file, the DataFrame keeps its column header. The rule is simple: a name gives a Series; a list gives a DataFrame — however many names are in the list.

Why not orders.quantity?

You will see column access written with a dot in many tutorials:

python
print(orders.branch.head(2))
text
0    north
1    south
Name: branch, dtype: str

It works here, and it is shorter. But it only works when the column name happens to be a valid Python name that is not already used for something else — and a DataFrame has hundreds of methods and attributes. Here is a small table whose columns are called count and size, two perfectly reasonable names for a shop:

python
d = pd.DataFrame({"count": [1, 2], "size": [3, 4]})

print(d.size)
print(d["size"])
text
4
0    3
1    4
Name: size, dtype: int64

d.size did not give you the column at all. It gave you the DataFrame's own size attribute — the number of cells, 2 × 2 = 4 — silently. No error, just a wrong answer. A name with a space in it, like unit price, cannot be written with a dot at all. The brackets have none of these problems, so the rule for code you keep is: use df["col"]. The dot is fine for a quick look in the terminal, nothing more.

Rows by label: .loc

Rows are not selected with plain brackets. (Plain brackets with a name mean "column", and pandas will not guess which you meant.) Rows get their own two tools, and the first is .loc — label-based location. You give it the label from the index:

python
print(orders.loc[2])
text
order_id           1003
date         2024-03-02
branch            north
product             bag
category    accessories
quantity              2
price             850.0
Name: 2, dtype: object

One row comes back as a Series, turned on its side: the column names have become its index, and its Name is the row's label, 2.

Look at the dtype: object. A column has one type, but a row runs across columns — a number, some text, a decimal — so the only type that can hold all of them is the general object. That is one reason pandas thinks in columns: a row mixes types, a column does not.

Now the important part. Ask .loc for a range of labels:

python
print(orders.loc[2:4])
text
order_id        date branch product     category  quantity  price
2      1003  2024-03-02  north     bag  accessories         2  850.0
3      1004  2024-03-02   east  bottle  accessories         7  120.0
4      1005  2024-03-03  north  eraser   stationery        30    8.0

Three rows: 2, 3 and 4. A .loc slice includes its end. That surprises everyone who knows Python, where range(2, 4) and list[2:4] stop before 4. It is not an inconsistency; there is a reason.

Labels are not always numbers. If the labels were product names, you would write something like loc["bag":"eraser"]. To exclude the end, you would have to name the label after "eraser" — and with names, there is no "next one" you can know without looking. So with labels, you say the first one you want and the last one you want, and you get both. Positions are different: the next position after 4 is always 5, so Python's "stop before" rule costs nothing there.

Rows by position: .iloc

The second tool is .iloc — integer location. It ignores the labels completely and counts places, from 0, exactly like a Python list:

python
print(orders.iloc[2:4])
text
order_id        date branch product     category  quantity  price
2      1003  2024-03-02  north     bag  accessories         2  850.0
3      1004  2024-03-02   east  bottle  accessories         7  120.0

Two rows, positions 2 and 3; position 4 is excluded, as in all of Python. Put the two outputs side by side: loc[2:4] gave three rows and iloc[2:4] gave two, on the same table, with the same numbers in the brackets.

Because .iloc is positional, negative numbers count from the end — which answers your manager's question directly:

python
print(orders.iloc[-3:])
text
order_id        date branch product     category  quantity  price
5      1006  2024-03-03  south     bag  accessories         1  850.0
6      1007  2024-03-04   east     pen   stationery        20   15.0
7      1008  2024-03-04  north  bottle  accessories         4  120.0

"The last three" is a question about position, so .iloc is the tool that matches it. .loc has no idea what "last" means; it only knows names.

Labels and positions are not the same thing

Until now, label 2 and position 2 were the same row, so you could not tell the two tools apart by their results. Take the last three orders as their own table, and they come apart:

python
last3 = orders.tail(3)

print(last3)
text
order_id        date branch product     category  quantity  price
5      1006  2024-03-03  south     bag  accessories         1  850.0
6      1007  2024-03-04   east     pen   stationery        20   15.0
7      1008  2024-03-04  north  bottle  accessories         4  120.0

The rows kept their labels: 5, 6, 7. Pandas does not renumber them, on purpose — the label is how a row stays the same row after you cut the table. Now ask for "the first row" both ways:

python
print(last3.iloc[0])
text
order_id           1006
date         2024-03-03
branch            south
product             bag
category    accessories
quantity              1
price             850.0
Name: 5, dtype: object
python
last3.loc[0]
text
KeyError: 0

.iloc[0] means "the row in first place", which is the order labelled 5. .loc[0] means "the row whose label is 0" — and there is no such row in this table, so pandas raises KeyError. This is the mistake that bites later in the course: after you filter rows or sort them — both coming later in the course — positions change and labels do not. The rule to carry forward:

  • Asking about place — first, last, top three after sorting? Use .iloc.
  • Asking about identity — this order, this product? Use .loc.

And the bad case is not the KeyError. The bad case is when label 0 does exist somewhere else in the table: then .loc[0] quietly returns a different row from the one you meant.

.loc asks for a name, .iloc asks for a place. On a freshly read table the two agree by coincidence, so code that mixes them up passes every check you run on the first day — and returns the wrong row, with no error, the first time the table has been cut, filtered or sorted.

Giving rows meaningful labels: set_index

The accountant asked about order 1006. With the default index you would have to know that order 1006 sits under label 5 — a fact about the file, not about the order. The real name of a row is its order number, so make the order number the index:

python
by_id = orders.set_index("order_id")

print(by_id.head(3))
print(by_id.shape)
text
date branch   product     category  quantity  price
order_id
1001      2024-03-01  north       pen   stationery        12   15.0
1002      2024-03-01  south  notebook   stationery         5   60.0
1003      2024-03-02  north       bag  accessories         2  850.0
(8, 6)

Three things changed. The order numbers moved to the left, into the index. The index has a name now, order_id, printed on its own line above it. And the shape is (8, 6) instead of (8, 7) — order_id is no longer a column, it is the index. Also note set_index returned a new table, which we named by_id; orders itself is untouched.

Now .loc speaks the language of the question:

python
print(by_id.loc[1006])
text
date         2024-03-03
branch            south
product             bag
category    accessories
quantity              1
price             850.0
Name: 1006, dtype: object

And a label slice is now a range of order numbers, both ends included:

python
print(by_id.loc[1003:1005, ["product", "quantity"]])
text
product  quantity
order_id                  
1003         bag         2
1004      bottle         7
1005      eraser        30

.iloc has not changed at all — positions are positions, whatever the labels are:

python
print(by_id.iloc[0:2])
text
date branch   product    category  quantity  price
order_id
1001      2024-03-01  north       pen  stationery        12   15.0
1002      2024-03-01  south  notebook  stationery         5   60.0

Here the two tools have clearly separated: by_id.loc[0] would be a KeyError now, because 0 is not an order number, while by_id.iloc[0] is order 1001. If you ever need the order number back as a column, by_id.reset_index() moves it back.

Choose a column whose values are unique. An index is meant to name rows, and a name shared by two rows does not point at one. Watch what happens with product, which repeats:

python
by_product = orders.set_index("product")

print(by_product.index.is_unique)
print(by_product.loc["bag"])
text
False
         order_id        date branch     category  quantity  price
product
bag          1003  2024-03-02  north  accessories         2  850.0
bag          1006  2024-03-03  south  accessories         1  850.0

No error — but loc["bag"] returned a two-row DataFrame instead of one row as a Series. Code that expected one row (say, reading its price as a single number) now gets two. That is why index.is_unique is worth one line of checking whenever you set an index you mean to look rows up by.

Rows and columns together

Both .loc and .iloc take two things separated by a comma: rows first, columns second. Each part can be a single label, a list, or a slice:

python
print(orders.loc[2:4, ["product", "price"]])
text
product  price
2     bag  850.0
3  bottle  120.0
4  eraser    8.0
python
print(orders.loc[[0, 3], ["branch", "product"]])
text
branch product
0  north     pen
3   east  bottle

A slice works for columns too, with .loc's inclusive rule — "every column from branch to category". A bare : means "all rows":

python
print(orders.loc[:, "branch":"category"])
text
branch   product     category
0  north       pen   stationery
1  south  notebook   stationery
2  north       bag  accessories
3   east    bottle  accessories
4  north    eraser   stationery
5  south       bag  accessories
6   east       pen   stationery
7  north    bottle  accessories

.iloc does the same by position. Columns are counted too, from 0, so branch is column 2 and product is column 3:

python
print(orders.iloc[[0, 3], [2, 3]])
text
branch product
0  north     pen
3   east  bottle

Same result as the .loc version two blocks up — but notice how much harder it is to read. [2, 3] says nothing; ["branch", "product"] says everything, and still works if someone adds a column at the front of the file. Prefer names when you know the names.

What shape will come back?

Whether you get a single value, a Series or a DataFrame depends only on what you put on each side of the comma. A single label removes that dimension; a list or slice keeps it:

python
print(type(by_id.loc[1006, "price"]).__name__)
print(type(by_id.loc[1006, ["product", "price"]]).__name__)
print(type(by_id.loc[[1003, 1006], "price"]).__name__)
print(type(by_id.loc[[1003, 1006], ["product", "price"]]).__name__)
text
float64
Series
Series
DataFrame
  • one label, one label → a single value
  • one label, a list or slice → a Series (one row)
  • a list or slice, one label → a Series (one column)
  • a list or slice, a list or slice → a DataFrame

This is step 3 of the planning — sketch the answer — turned into a rule. If you want a table back, even of one row, put a list on both sides: by_id.loc[[1006], ["price"]].

One value: .loc and .at

The accountant's question — what did order 1006 cost? — is one cell: a row label and a column name.

python
print(by_id.loc[1006, "price"])
print(by_id.at[1006, "price"])
text
850.0
850.0

Both give the same number. .at accepts only a single row and a single column, nothing else, which makes it slightly faster and makes your intention obvious: this is one cell. (.iat is the positional twin, by_id.iat[5, 5].) Use whichever reads better; .loc is fine.

A single value is an ordinary number, so you can calculate with it straight away. The value of order 1004 — quantity times price:

python
print(by_id.at[1004, "quantity"] * by_id.at[1004, "price"])
text
840.0

A selection is its own object

There is one more thing to understand before you use selections in real programs: what happens when you change one.

python
import pandas as pd

orders = pd.read_csv("orders.csv")

prices = orders["price"]
prices[0] = 999.0

print(prices.head(2))
print(orders.loc[0, "price"])
text
0    999.0
1     60.0
Name: price, dtype: float64
15.0

prices changed. orders did not. In pandas 3, every selection behaves as a separate copy: you can change it freely and the original table is never touched. (Internally pandas avoids actually copying the data until you write to it — that is the name of the mechanism, Copy-on-Write — but the rule you need is the behaviour, not the mechanism.)

The same holds for a selection of several columns:

python
stock = orders[["product", "price"]]
stock["price"] = 0

print(orders["price"].head(2))
text
0    15.0
1    60.0
Name: price, dtype: float64

This is the safe behaviour: a function that takes a selection and edits it cannot damage your main table behind your back. But it has one consequence you must know. If you mean to change the original, you have to do it on the original, in one step, with .loc:

python
orders.loc[0, "price"] = 16.0

print(orders.loc[0, "price"])
text
16.0

The tempting two-step version — select a column, then set a cell in it — does not work, and pandas 3 tells you so:

python
orders["price"][0] = 1.0
text
ChainedAssignmentError: A value is being set on a copy of a DataFrame or Series through chained assignment.
Such chained assignment never works to update the original DataFrame or Series, because the intermediate object on which we are setting values always behaves as a copy (due to Copy-on-Write).

Try using '.loc[row_indexer, col_indexer] = value' instead, to perform the assignment in a single step.

See the documentation for a more detailed explanation: https://pandas.pydata.org/pandas-docs/stable/user_guide/copy_on_write.html#chained-assignment
python
print(orders.loc[0, "price"])
text
16.0

Still 16.0 — the assignment went into a temporary copy and was thrown away. orders["price"] produced a selection, and [0] = 1.0 changed that, not orders. That is called chained assignment: two brackets in a row on the left of =. The fix is always the one the warning names: one .loc with both the row and the column, orders.loc[0, "price"] = 1.0.

If you read older tutorials, you will see this territory described with a SettingWithCopyWarning and advice that sometimes the original changed and sometimes it did not. That was pandas 1 and 2. In pandas 3 the rule is simple and always the same: a selection never changes the original; .loc on the original does.

A complete example

All three requests from the start of the chapter, answered in one program. select_orders.py:

python
import pandas as pd

orders = pd.read_csv("orders.csv")

# 1. Check what arrived before pointing into it
print("Shape  :", orders.shape)
print("Columns:", list(orders.columns))

# 2. Name the rows by what they are
by_id = orders.set_index("order_id")
print("Order ids unique:", by_id.index.is_unique)
print()

# Stock team: two columns, every row
stock = orders[["product", "quantity"]]
print("Stock view:", stock.shape)
print(stock.head(3))
print()

# Accountant: one order by its number, then one cell
print(by_id.loc[1006, ["product", "quantity", "price"]])
print("Order 1006 total:", by_id.at[1006, "quantity"] * by_id.at[1006, "price"])
print()

# Manager: the last three orders, a question about position
print(by_id.iloc[-3:, [1, 2, 4]])
text
Shape  : (8, 7)
Columns: ['order_id', 'date', 'branch', 'product', 'category', 'quantity', 'price']
Order ids unique: True

Stock view: (8, 2)
    product  quantity
0       pen        12
1  notebook         5
2       bag         2

product       bag
quantity        1
price       850.0
Name: 1006, dtype: object
Order 1006 total: 850.0

         branch product  quantity
order_id
1006      south     bag         1
1007       east     pen        20
1008      north  bottle         4

Why it is written this way:

  • It looks before it points. shape and list(columns) are printed first, so a misspelt or space-padded column name would be visible before any selection fails.
  • It checks the index is unique before using it to look orders up, so by_id.loc[1006] is guaranteed to be one row and by_id.at[...] one number.
  • Each request uses the tool that matches its question. Columns by name with brackets; an order by its identity with .loc and .at; "the last three" by position with .iloc.
  • It prints the shape of the stock view — (8, 2) is exactly the sketch: every order, two columns.
  • The last selection is the one to improve. [1, 2, 4] happens to mean branch, product, quantity in by_id — note the positions moved by one because order_id became the index. Positions are fragile. In your own code, by_id.iloc[-3:][["branch", "product", "quantity"]] or by_id.loc[by_id.index[-3:], ["branch", "product", "quantity"]] would say the same thing with names.

When it breaks

KeyError: 'Price' There is no column with exactly that name. Column names are case-sensitive: price and Price are different names. Print list(df.columns) and copy the name from there. With a list of names, orders[["product", "Price"]], the message becomes KeyError: "['Price'] not in index" and names exactly which one is missing; with a dot, orders.Price, it is AttributeError: 'DataFrame' object has no attribute 'Price'. Same cause, same fix.

KeyError: 'price' — although price is plainly in the file The header probably has spaces after its commas, order_id, product, price, and those spaces became part of the names:

python
from io import StringIO

import pandas as pd

raw = "order_id, product, price\n1001, pen, 15.0\n"
padded = pd.read_csv(StringIO(raw))

print(list(padded.columns))
padded["price"]
text
['order_id', ' product', ' price']
KeyError: 'price'

The list shows the leading space in ' price', which is exactly why chapter three told you to print the columns as a list. Fix it when reading, with pd.read_csv("file.csv", skipinitialspace=True), or afterwards with df.columns = df.columns.str.strip().

KeyError: ('product', 'price') You wrote orders["product", "price"] — one pair of brackets. Without the inner list, Python passes the two names as a single tuple, and pandas looks for one column named ('product', 'price'). Write orders[["product", "price"]].

KeyError: 0 from .loc You used a position with .loc. Either the table was cut (tail, a filter, a sort) so label 0 is gone, or you set an index and the labels are order numbers now. If you mean "first row", write .iloc[0]. On a table whose index is text, the same mistake made with a slice — orders.set_index("product").loc[0:2] — raises TypeError: cannot do slice indexing on Index with these indexers [0] of type int instead; the fix is the same, .iloc[0:2].

IndexError: single positional indexer is out-of-bounds .iloc was given a position that does not exist — orders.iloc[8] on an eight-row table, whose positions run from 0 to 7. Check df.shape; the last row is always df.iloc[-1]. The same message appears for a column position that is too large, orders.iloc[:, 7].

ValueError: Location based indexing can only have [integer, integer slice (START point is INCLUDED, END point is EXCLUDED), listlike of integers, boolean array] types You gave .iloc a column name: orders.iloc[0, "branch"]. .iloc only takes positions. Use .loc with a label — orders.loc[0, "branch"] — or, if you really need the position, look it up: orders.columns.get_loc("branch") returns 2.