Selecting rows and columns with loc and iloc

pandas has two ways of picking rows and columns. loc selects by label: the values in the index and the column names. iloc selects by position: 0 for the first row, 1 for the second, exactly like a Python list. Both take the rows first and the columns second, df.loc[rows, columns]. The difference that catches people is the end of a slice: a label slice includes its end, so df.loc["b":"d"] returns the rows b, c and d, while a position slice stops before its end, so df.iloc[1:3] returns positions 1 and 2 only. Rows are more often chosen by a condition than by a name, with a boolean mask, one True or False per row, and masks are combined with &, | and ~, each condition in its own parentheses. This lesson covers columns, then rows and cells with both tools, masks and the pd.col expressions new in pandas 3, and the errors each one gives when it is used the wrong way.

The bookings table

The examples use an invented table of 20 train bookings: a booking reference, the travel class (the codes SL, 3A, 2A and 1A, from the cheapest to the dearest), the fare and the status (CNF for confirmed, RAC and WL for the two kinds of waiting). It sits next to the examples as a CSV file:

booking,cls,fare,status
B01,SL,455,CNF
B02,3A,1610,CNF
B03,3A,1495,CNF
B04,2A,2240,WL
B05,SL,480,RAC
B06,3A,1730,WL
B07,1A,3890,CNF
B08,3A,1580,CNF
B09,SL,455,CNF
B10,2A,2310,CNF
B11,3A,1655,RAC
B12,3A,1520,CNF
B13,SL,470,WL
B14,2A,2195,CNF
B15,3A,1425,CNF
B16,1A,4120,WL
B17,3A,1790,CNF
B18,SL,455,CNF
B19,2A,2260,RAC
B20,3A,1500,CNF

Selecting columns

import pandas as pd

bookings = pd.read_csv("bookings.csv")
print(bookings.shape)

fare = bookings["fare"]  # one name: a Series
print(type(fare).__name__, fare.head(3).tolist())

subset = bookings[["booking", "fare"]]  # a list of names: a DataFrame, in that order
print(type(subset).__name__, subset.columns.tolist())

print(bookings.select_dtypes(include="number").columns.tolist())
print(bookings.select_dtypes(include="str").columns.tolist())
print(bookings.columns.drop("status").tolist())  # every column but one

(20, 4)
Series [455, 1610, 1495]
DataFrame ['booking', 'fare']
['fare']
['booking', 'cls', 'status']
['booking', 'cls', 'fare']

• One name in square brackets returns that column as a Series.
• A list of names returns a DataFrame with those columns, in the order you listed them. The double brackets are a list inside the indexing brackets.
• select_dtypes picks columns by type: include="number" for every numeric column, include="str" for the text columns of pandas 3. It saves typing names and keeps working when columns are added.
• columns.drop gives every column name but the ones you name, handy for “everything except”.

Rows and cells with loc and iloc

To show labels and positions apart, the next example gives the first five bookings the labels a to e:

import pandas as pd

df = pd.read_csv("bookings.csv").head(5)
df.index = ["a", "b", "c", "d", "e"]  # letters, so labels and positions look different
print(df)

print(df.loc["b":"d"])  # labels: from b up to and including d
print(df.iloc[1:3])  # positions: 1 and 2, stopping before 3
print(df.loc[["e", "a"], ["booking", "fare"]])  # rows and columns, by label, in the order asked
print(df.iloc[[-1, 0], [0, 2]])  # the same cells by position
print(df.loc["c", "fare"], df.iloc[2, 2])  # one cell: a plain value

  booking cls  fare status
a     B01  SL   455    CNF
b     B02  3A  1610    CNF
c     B03  3A  1495    CNF
d     B04  2A  2240     WL
e     B05  SL   480    RAC
  booking cls  fare status
b     B02  3A  1610    CNF
c     B03  3A  1495    CNF
d     B04  2A  2240     WL
  booking cls  fare status
b     B02  3A  1610    CNF
c     B03  3A  1495    CNF
  booking  fare
e     B05   480
a     B01   455
  booking  fare
e     B05   480
a     B01   455
1495 1495

• loc["b":"d"] returns three rows, b, c and d, because a label slice includes its end. With labels that is the only sensible rule: you know the label you want to stop at, but not necessarily the label after it.
• iloc[1:3] returns two rows, positions 1 and 2, because a position slice stops before its end, as list[1:3] does in Python.
• A list of labels or positions picks exactly those rows in exactly that order, and the part after the comma picks columns the same way. iloc also accepts negative positions: -1 is the last row.
• Asking for one row and one column, loc["c", "fare"] or iloc[2, 2], returns a plain value, 1495, not a Series.

When the labels are numbers

With the default numbered index, labels and positions start out equal, which hides the difference until something changes the order. Sorting does:

import pandas as pd

df = pd.read_csv("bookings.csv").head(4)
by_fare = df.sort_values("fare", ascending=False)  # sorting keeps each row's label
print(by_fare)

print("loc[0]:", by_fare.loc[0, "booking"], "  iloc[0]:", by_fare.iloc[0]["booking"])
print(by_fare.loc[1:2])  # from label 1 to label 2, in the order the rows are in now
print(by_fare.iloc[1:2])  # position 1 only: the slice stops before position 2

  booking cls  fare status
3     B04  2A  2240     WL
1     B02  3A  1610    CNF
2     B03  3A  1495    CNF
0     B01  SL   455    CNF
loc[0]: B01   iloc[0]: B04
  booking cls  fare status
1     B02  3A  1610    CNF
2     B03  3A  1495    CNF
  booking cls  fare status
1     B02  3A  1610    CNF

Sorting by fare kept every row’s label, so the row labelled 0, booking B01, is now last. loc[0] still finds B01, while iloc[0] finds the row that is first now, B04. Slices behave the same way: loc[1:2] runs from the row labelled 1 to the row labelled 2 in the order the rows are in now, and iloc[1:2] takes only position 1. When you mean “the first ten rows”, ask for positions, with iloc[:10] or head(10); when you mean a particular key, use a label.

Rows that match a condition: boolean masks

Comparing a column with a value gives a mask: a Series of True and False with the same index as the table. Passing the mask to loc (or to plain square brackets) keeps the rows where it is True:

import pandas as pd

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

is_3a = bookings["cls"] == "3A"
print(is_3a.head(4).tolist(), int(is_3a.sum()), "bookings in 3A")

wanted = (bookings["cls"] == "3A") & (bookings["status"] == "CNF") & (bookings["fare"] > 1500)
print(bookings.loc[wanted, ["booking", "fare"]])

waiting = (bookings["status"] == "WL") | (bookings["status"] == "RAC")
print("waiting or RAC:", bookings.loc[waiting, "booking"].tolist())
print("everything else:", bookings.loc[~waiting, "booking"].tolist())

[False, True, True, False] 9 bookings in 3A
   booking  fare
1      B02  1610
7      B08  1580
11     B12  1520
16     B17  1790
waiting or RAC: ['B04', 'B05', 'B06', 'B11', 'B13', 'B16', 'B19']
everything else: ['B01', 'B02', 'B03', 'B07', 'B08', 'B09', 'B10', 'B12', 'B14', 'B15', 'B17', 'B18', 'B20']

• bookings["cls"] == "3A" is True for 9 of the 20 rows. True counts as 1, so sum() counts the matches.
• & means and, | means or and ~ means not. They work row by row on whole masks.
• loc[mask, ["booking", "fare"]] filters the rows and picks the columns in one step.
• B20 costs exactly 1,500 and is not in the result, because > 1500 excludes the boundary. Decide on purpose whether a limit is inclusive: >= would have included it.
• ~waiting returns every booking the first mask did not, 13 of them, so the two lists together cover all 20 rows. That only holds when the column has no gaps; the lesson on data types shows how missing values change it.

Two errors and what they mean

Both of the classic mistakes with masks raise an error rather than giving a wrong answer, which is the good news:

import pandas as pd

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

try:
    bookings[(bookings["cls"] == "3A") and (bookings["fare"] > 1500)]
except ValueError as error:
    print("with and:", error)

try:
    bookings[bookings["cls"] == "3A" & bookings["fare"] > 1500]
except TypeError as error:
    print("without parentheses:", error)

with and: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
without parentheses: Cannot perform 'rand_' with a dtyped [int64] array and scalar of type [bool]

• and instead of &. Python’s and needs a single True or False, and a Series of 20 answers cannot be turned into one, so pandas raises “The truth value of a Series is ambiguous”. Use & for row-by-row logic, and .any() or .all() only when you really want one answer about the whole column.
• Missing parentheses. Python applies & and | before == and >, so without parentheses it tries to combine the text “3A” with the fare column first, and pandas refuses with a TypeError. Put every comparison in its own parentheses. The same precedence rule also means & is applied before |, a subtle source of wrong filters that the next lesson’s isin avoids.

pd.col: naming a column without naming the frame

pandas 3 adds pd.col, an expression that stands for a column of whatever DataFrame it is used on:

import pandas as pd

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

# pd.col("fare") stands for the fare column of whatever frame it is used on.
wanted = (pd.col("cls") == "3A") & (pd.col("status") == "CNF") & (pd.col("fare") > 1500)
print(bookings.loc[wanted, ["booking", "fare"]])

# The same expression works in a chain, where there is no name for the frame yet.
print(bookings[pd.col("fare") > 3000].assign(fare_k=pd.col("fare") / 1000))

   booking  fare
1      B02  1610
7      B08  1580
11     B12  1520
16     B17  1790
   booking cls  fare status  fare_k
6      B07  1A  3890    CNF    3.89
15     B16  1A  4120     WL    4.12

The first selection is the same as before, written without repeating bookings[...] three times. The second shows why it exists: inside a chain, the intermediate frames have no names, and before pandas 3 you had to write a small function, lambda d: d["fare"] > 3000, to refer to them. pd.col("fare") / 1000 does the same job in assign.

Single cells and attribute access

import warnings

import pandas as pd

bookings = pd.read_csv("bookings.csv")
print(bookings.at[6, "fare"], bookings.iat[6, 2])  # one cell, by label and by position

orders = pd.DataFrame({"count": [3, 5], "unit price": [120, 80]})
print(type(orders.count).__name__)  # a method of every DataFrame, not the column
print(orders["count"].tolist(), orders["unit price"].tolist())  # brackets reach any name

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    orders.total = [360, 400]  # sets a Python attribute, not a column
print("total" in orders.columns, "-", caught[0].message)

3890 3890
method
[3, 5] [120, 80]
False - Pandas doesn't allow columns to be created via a new attribute name - see https://pandas.pydata.org/pandas-docs/stable/indexing.html#attribute-access

• at reads or writes one cell by label and iat by position. pandas provides them for exactly that job, getting or setting a single value quickly, so reach for them in code that touches one cell at a time.
• df.fare works as a shortcut for df["fare"], but only for names that are valid Python names and do not clash with a DataFrame method. orders.count is the count method, not the column called “count”, and a column called “unit price” cannot be written with a dot at all.
• Assigning orders.total = [...] does not create a column. It sets a Python attribute on the object and pandas warns. Use brackets, orders["total"] = ..., in any code you want to keep.

Practise: five selections

Using bookings.csv, write one expression for each:

1. The booking and fare columns of every confirmed booking.
2. The first three rows, by position.
3. The bookings in class 1A or 2A.
4. The bookings that are not confirmed and cost more than 2,000.
5. The fare of booking B07, as a single number.

For the last one, think about which column should be the index first.

import pandas as pd

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

# 1. The booking and fare of every confirmed booking.
confirmed = bookings.loc[bookings["status"] == "CNF", ["booking", "fare"]]
# 2. The first three rows, by position.
first_three = bookings.iloc[:3]
# 3. Bookings in 1A or 2A.
upper = bookings[(bookings["cls"] == "1A") | (bookings["cls"] == "2A")]
# 4. Bookings that are not confirmed and cost more than 2,000.
unconfirmed_dear = bookings[~(bookings["status"] == "CNF") & (bookings["fare"] > 2000)]
# 5. The fare of booking B07, as one number.
b07_fare = bookings.set_index("booking").at["B07", "fare"]

print(len(confirmed), confirmed["fare"].sum())
print(first_three["booking"].tolist())
print(upper["booking"].tolist())
print(unconfirmed_dear)
print(b07_fare)

13 20680
['B01', 'B02', 'B03']
['B04', 'B07', 'B10', 'B14', 'B16', 'B19']
   booking cls  fare status
3      B04  2A  2240     WL
15     B16  1A  4120     WL
18     B19  2A  2260    RAC
3890

There are 13 confirmed bookings, worth 20,680 together. iloc[:3] takes the first three rows by position. Classes 1A and 2A hold 6 bookings, and three of them are both unconfirmed and dearer than 2,000. Moving booking into the index makes at["B07", "fare"] a direct lookup of one number, 3890.

Interview questions

Fresher · What is the difference between loc and iloc?

loc selects by label: the index values for rows and the names for columns. iloc selects by integer position, counting from 0, like a Python list. Both take rows first and columns second, as in df.loc["b", "fare"] or df.iloc[1, 2], and both accept a single value, a list, a slice or a boolean mask for rows. The key difference is slicing: a label slice includes its end, so df.loc["b":"d"] gives b, c and d, while a position slice excludes it, so df.iloc[1:3] gives positions 1 and 2. With the default numbered index the two look alike until sorting or filtering changes the order: then loc[0] is the row labelled 0, wherever it is, and iloc[0] is whatever row is first.

Key takeaways

• One column name returns a Series; a list of names returns a DataFrame in that order. select_dtypes picks columns by type.
• loc works with labels and includes the end of a slice; iloc works with positions and stops before the end.
• After a sort or a filter, labels and positions differ: use iloc or head for “the first N rows”.
• Combine conditions with &, | and ~, never and, or or not, and wrap every comparison in parentheses.
• pd.col("name") refers to a column of the frame it is used on, which keeps pandas 3 chains short.
• Use at and iat for single cells, and square brackets rather than dots for column names.