Pandas set_index(): Row Labels, Multiple Keys and Dates
Use set_index() to turn columns into row labels. Choose single or multiple keys, validate business identifiers and select rows by labels or dates.
Create the student table 🔝
An index supplies row labels. It is separate from row position and does not automatically enforce unique, nonmissing keys.
import pandas as pd
def student_data():
return pd.DataFrame({
"NAME": ["Ravi", "Raju", "Alex", "Ron", "King", "Jack"],
"ID": [1, 2, 3, 4, 5, 6],
"MATH": [80, 40, 70, 70, 70, 30],
"ENGLISH": [80, 70, 40, 50, 60, 30]
})
df = student_data()
print(df)Expected output
NAME ID MATH ENGLISH
0 Ravi 1 80 80
1 Raju 2 40 70
2 Alex 3 70 40
3 Ron 4 70 50
4 King 5 70 60
5 Jack 6 30 30Choose keys and options 🔝
keys accepts a column label, multiple column labels or arrays of matching length. drop=True removes the index columns from the returned table; append=True adds levels to the existing index. inplace=False returns a table. The legacy verify_integrity option is deprecated in Pandas 3.0; prefer explicit duplicate checks with duplicated() or Index.is_unique.
Assign the returned DataFrame 🔝
Calling set_index() without assigning its result leaves df unchanged. Store the returned table to use its new labels.
df = student_data()
df.set_index("NAME")
print("Unchanged index:", df.index.tolist())
indexed = df.set_index("NAME")
print(indexed)
assert df.index.tolist() == list(range(6))
assert indexed.index.name == "NAME"Expected output
Unchanged index: [0, 1, 2, 3, 4, 5]
ID MATH ENGLISH
NAME
Ravi 1 80 80
Raju 2 40 70
Alex 3 70 40
Ron 4 70 50
King 5 70 60
Jack 6 30 30Modify a table with inplace=True 🔝
Use a fresh copy when the original table is still needed. In-place indexing returns None.
df = student_data()
working = df.copy()
returned = working.set_index("NAME", inplace=True)
print(working)
print("Return:", returned)
assert returned is None
assert "NAME" in df.columnsExpected output
ID MATH ENGLISH
NAME
Ravi 1 80 80
Raju 2 40 70
Alex 3 70 40
Ron 4 70 50
King 5 70 60
Jack 6 30 30
Return: NoneKeep the index column with drop=False 🔝
The label also remains a column. This may suit an export, but can make operations using the same name for a column and an index level ambiguous. Reset or rename the index deliberately before such operations.
df = student_data()
kept = df.set_index("NAME", drop=False)
print(kept)
assert "NAME" in kept.columns
assert kept.index.tolist() == kept["NAME"].tolist()Expected output
NAME ID MATH ENGLISH
NAME
Ravi Ravi 1 80 80
Raju Raju 2 40 70
Alex Alex 3 70 40
Ron Ron 4 70 50
King King 5 70 60
Jack Jack 6 30 30Inspect duplicate labels before indexing 🔝
Duplicate labels are allowed by default. Decide whether they represent valid repeated observations or a key problem. Use duplicated() to show all conflicting source rows.
df = student_data()
df.loc[5, "NAME"] = "Ron"
conflicts = df.loc[df["NAME"].duplicated(keep=False)]
indexed = df.set_index("NAME")
print("Conflicting names:")
print(conflicts)
print("Unique index:", indexed.index.is_unique)
print("All Ron rows:")
print(indexed.loc[["Ron"]])
assert conflicts["ID"].tolist() == [4, 6]
assert not indexed.index.is_uniqueExpected output
Conflicting names:
NAME ID MATH ENGLISH
3 Ron 4 70 50
5 Ron 6 30 30
Unique index: False
All Ron rows:
ID MATH ENGLISH
NAME
Ron 4 70 50
Ron 6 30 30Require a unique, nonmissing business key 🔝
When uniqueness is a requirement, validate it explicitly and raise a clear error before using the table. Missing values and duplicates are separate checks. This approach does not depend on the deprecated verify_integrity parameter.
def index_by_key(frame, key):
if frame[key].isna().any():
raise ValueError("Missing business key")
if frame[key].duplicated().any():
raise ValueError("Duplicate business key")
return frame.set_index(key)
duplicate = student_data()
duplicate.loc[5, "NAME"] = "Ron"
try:
index_by_key(duplicate, "NAME")
except ValueError as error:
print(error)
valid = index_by_key(student_data(), "ID")
print("Valid unique index:", valid.index.is_unique)
assert valid.index.is_uniqueExpected output
Duplicate business key
Valid unique index: TrueAppend a level to the existing index 🔝
append=True retains the original index as a level. This option adds index levels; it does not append rows.
df = student_data()
appended = df.set_index("NAME", append=True)
print(appended)
print("Levels:", appended.index.nlevels)
assert appended.index.nlevels == 2
assert appended.index[0] == (0, "Ravi")Expected output
ID MATH ENGLISH
NAME
0 Ravi 1 80 80
1 Raju 2 40 70
2 Alex 3 70 40
3 Ron 4 70 50
4 King 5 70 60
5 Jack 6 30 30
Levels: 2Replace the existing index 🔝
append=False is the default and replaces existing labels. Keep important previous labels as columns with reset_index() first if you need them later.
df = student_data()
df.index = pd.Index([10, 20, 30, 40, 50, 60], name="source_row")
replaced = df.set_index("NAME", append=False)
print(replaced)
assert replaced.index.nlevels == 1
assert replaced.index.tolist() == df["NAME"].tolist()Expected output
ID MATH ENGLISH
NAME
Ravi 1 80 80
Raju 2 40 70
Alex 3 70 40
Ron 4 70 50
King 5 70 60
Jack 6 30 30Create an index from multiple columns 🔝
Multiple keys produce a MultiIndex. Use a tuple for a full label and a list of tuples when you want a DataFrame result.
df = student_data()
multi = df.set_index(["NAME", "ID"])
print(multi)
print("One student:")
print(multi.loc[[("Raju", 2)]])
assert multi.index.names == ["NAME", "ID"]
assert multi.loc[("Raju", 2), "MATH"] == 40Expected output
MATH ENGLISH
NAME ID
Ravi 1 80 80
Raju 2 40 70
Alex 3 70 40
Ron 4 70 50
King 5 70 60
Jack 6 30 30
One student:
MATH ENGLISH
NAME ID
Raju 2 40 70Compare label selection and position selection 🔝
loc uses labels; iloc uses positions. Both are indexers and take square brackets. A list selector preserves a two-dimensional DataFrame.
indexed = student_data().set_index("ID")
print("Label 2:")
print(indexed.loc[[2]])
print("Position 2:")
print(indexed.iloc[[2]])
assert indexed.loc[2, "NAME"] == "Raju"
assert indexed.iloc[2]["NAME"] == "Alex"Expected output
Label 2:
NAME MATH ENGLISH
ID
2 Raju 40 70
Position 2:
NAME MATH ENGLISH
ID
3 Alex 70 40Create a sorted DatetimeIndex 🔝
Convert the date column using to_datetime() before indexing. Sorting gives predictable range selection. Here every date is known and parseable.
visits = pd.DataFrame({
"st_date": ["2020-03-05", "2019-04-20", "2019-03-01", "2020-03-01", "2020-04-01"],
"visits": [15, 12, 10, 20, 25]
})
visits["st_date"] = pd.to_datetime(visits["st_date"], errors="raise")
dated = visits.set_index("st_date").sort_index()
print(dated)
assert isinstance(dated.index, pd.DatetimeIndex)
assert dated.index.is_monotonic_increasingExpected output
visits
st_date
2019-03-01 10
2019-04-20 12
2020-03-01 20
2020-03-05 15
2020-04-01 25Select a month by date labels 🔝
Use loc for row selection. A partial month string matches dates in that month; bracket selection on a DataFrame normally selects columns.
march = dated.loc["2020-03"]
print(march)
print("March visits:", march["visits"].sum())
assert len(march) == 2 and march["visits"].sum() == 35Expected output
visits
st_date
2020-03-01 20
2020-03-05 15
March visits: 35Select an inclusive date range 🔝
A label slice includes its endpoints. Partial month boundaries include the matching months. See date selection exercises, date_range() and period_range().
period = dated.loc["2019-03":"2019-04"]
print(period)
assert len(period) == 2
assert period["visits"].sum() == 22Expected output
visits
st_date
2019-03-01 10
2019-04-20 12Practical example: index validated order IDs 🔝
This synthetic order table contains a repeated ID and a missing ID. Put every conflicting row in review rather than arbitrarily retaining one. Data Cleaning explains additional validation steps.
raw = pd.DataFrame({
"order_id": pd.Series([101, 102, 102, None, 105], dtype="Int64"),
"product": ["Pen", "Book", "Notebook", "Bag", "Pencil"],
"amount": [5, 20, 15, 30, 8]
})
invalid = raw["order_id"].isna() | raw["order_id"].duplicated(keep=False)
review = raw.loc[invalid].copy()
accepted = raw.loc[~invalid].copy()
orders = index_by_key(accepted, "order_id")
print("Indexed orders:")
print(orders)
print("Review rows:")
print(review)
assert orders.index.tolist() == [101, 105]
assert len(review) == 3
assert len(orders) + len(review) == len(raw)Expected output
Indexed orders:
product amount
order_id
101 Pen 5
105 Pencil 8
Review rows:
order_id product amount
1 102 Book 20
2 102 Notebook 15
3 <NA> Bag 30Update one order by its label 🔝
Use loc to change the intended row. Missing labels can create rows during assignment, so check membership if adding an order is not intended. Indexing is not a substitute for checking the requested key.
updated = orders.copy()
requested_id = 105
if requested_id not in updated.index:
raise KeyError("Unknown order ID")
updated.loc[requested_id, "amount"] = 9
print(updated)
assert updated.loc[105, "amount"] == 9
assert orders.loc[105, "amount"] == 8Expected output
product amount
order_id
101 Pen 5
105 Pencil 9Restore the identifier column before export 🔝
Use reset_index() to make the index a column. Then index=False avoids exporting an extra index column. Resetting with drop=True would discard the index labels instead.
export_table = updated.reset_index()
print(export_table.to_csv(index=False).rstrip())
assert "order_id" in export_table.columns
assert export_table["order_id"].tolist() == [101, 105]
# Optional Colab export:
# export_table.to_csv("validated_orders.csv", index=False)Expected output
order_id,product,amount
101,Pen,5
105,Pencil,9Common questions 🔝
Does set_index() sort rows? No; use sort_index() explicitly. Does it reject missing keys? No; validate them if your rules require known keys. Why did a column disappear? drop=True moved it to the index. Why can loc return multiple rows? A label may be duplicated. Does setting the index speed up every operation? Performance depends on the data and operation; choose labels for clear selection and measure important workloads. Can I keep the old index? Use append=True for another level or reset_index() to keep it as a column. Continue with merge() and Input and Output.
Exercise: select and export one student 🔝
Set ID as the index of a fresh student table, select ID 3 as a DataFrame and restore ID as a column. Predict the name and Maths mark before running the solution.
Exercise solution 🔝
ID 3 is Alex with Maths mark 70. Selecting with a list keeps the result as a DataFrame.
indexed = index_by_key(student_data(), "ID")
answer = indexed.loc[[3]].reset_index()
print(answer)
assert answer["NAME"].tolist() == ["Alex"]
assert answer["MATH"].tolist() == [70]
assert answer["ID"].tolist() == [3]Expected output
ID NAME MATH ENGLISH
0 3 Alex 70 40Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change order IDs and dates, then compare label selection, review rows and exports. Save your own copy to keep edits. All sample tables are included in the notebook.
Continue with the Data Cleaning hub, where(), loc, at, NumPy where(), fillna() and iloc.
Reference: Pandas DataFrame.set_index documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials