These are the notes for Data Analytics with Python, episode 16. In episode 15 we kept only the rows that match a condition. Now Ramesh, who runs a chai stall, has three new questions: which were my best days, in order?, what position did each day get? and how much money did I make each day? To answer them we invent sorting (sort_values, sort_index), quick top-N answers (nlargest, nsmallest), ranking with tie rules (rank(method=...)), new calculated columns (vectorised arithmetic, assign), labels from conditions (np.where, np.select), and tidying columns (rename, drop, insert, column order). Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5, NumPy 2.5.3, pyarrow 26.0.0); outputs are unedited.
Most examples use Ramesh's week of chai sales (episode 12). The first line only hides pandas' long internal traceback so errors are short:
import sys; sys.tracebacklimit = 0
import pandas as pd
week = pd.DataFrame({
"day": ["Mon", "Tue", "Wed", "Thu",
"Fri", "Sat", "Sun"],
"cups": [320, 280, 410, 300, 350, 390, 450],
"price": [10, 10, 12, 10, 10, 12, 12],
"weather": ["sunny", "rain", "cold", "sunny",
"sunny", "cold", "rain"],
})
Every later block assumes this table has just been created (each block starts again from the original week).
The problem: "line up my best days"
Sorting means putting things in order by some value, like a teacher lining students up by height at a school assembly. In episode 5 we sorted lists with Python's built-in sorted(), so the obvious attempt is:
print(sorted(week))
['cups', 'day', 'price', 'weather']
Only the column names, in alphabetical order. When Python loops over a DataFrame, it walks over the column labels, not the rows, so sorted() sorted the names. A DataFrame is not a list of rows.
sort_values: sort rows by a column
DataFrame.sort_values(by) returns a new DataFrame with the rows reordered by the column by. Ascending order means smallest first (the default, ascending=True); descending means biggest first (ascending=False).
print(week.sort_values("cups"))
print(week.sort_values("cups", ascending=False))
day cups price weather
1 Tue 280 10 rain
3 Thu 300 10 sunny
0 Mon 320 10 sunny
4 Fri 350 10 sunny
5 Sat 390 12 cold
2 Wed 410 12 cold
6 Sun 450 12 rain
day cups price weather
6 Sun 450 12 rain
2 Wed 410 12 cold
5 Sat 390 12 cold
4 Fri 350 10 sunny
0 Mon 320 10 sunny
3 Thu 300 10 sunny
1 Tue 280 10 rain
The whole row moves together: Sunday keeps its index label 6 wherever it ends up. Text columns sort alphabetically ("Fri" < "Mon"), and missing values (NaN) go to the end by default (na_position="last"; use "first" to put them at the top).
sort_values does not change week itself. If you don't save the result, nothing happened:
week.sort_values("cups", ascending=False)
print(week.head(3))
day cups price weather
0 Mon 320 10 sunny
1 Tue 280 10 rain
2 Wed 410 12 cold
Save it: week = week.sort_values("cups", ascending=False).
Sorting by two columns, and sort_index
A phone book is sorted by surname, and then by first name when two surnames are the same. The first column decides the order; the second only breaks ties (equal values). In pandas, pass a list of columns, and ascending can be a list too, one True/False per column:
by = week.sort_values(["price", "cups"], ascending=[False, True])
print(by)
print(by.sort_index())
day cups price weather
5 Sat 390 12 cold
2 Wed 410 12 cold
6 Sun 450 12 rain
1 Tue 280 10 rain
3 Thu 300 10 sunny
0 Mon 320 10 sunny
4 Fri 350 10 sunny
day cups price weather
0 Mon 320 10 sunny
1 Tue 280 10 rain
2 Wed 410 12 cold
3 Thu 300 10 sunny
4 Fri 350 10 sunny
5 Sat 390 12 cold
6 Sun 450 12 rain
Price high to low first (the ₹12 days), then cups low to high inside each price. sort_index() sorts by the index labels instead of a column, which puts the rows back in their original order (labels 0 to 6). sort_index(ascending=False) reverses it. Both methods also accept axis=1 to sort the columns by their names, e.g. week.sort_index(axis=1).
Top N: nlargest and nsmallest
"Top three days?" You can sort descending and take head(3), but at a race nobody reads the whole results list for the medals: they look at the podium. nlargest(n, column) returns the n rows with the biggest values in that column, and nsmallest(n, column) the n smallest:
print(week.nlargest(3, "cups"))
print(week.nsmallest(2, "cups"))
day cups price weather
6 Sun 450 12 rain
2 Wed 410 12 cold
5 Sat 390 12 cold
day cups price weather
1 Tue 280 10 rain
3 Thu 300 10 sunny
Same as sort_values(...).head(n), but the intent is obvious, and on big tables it is faster because pandas doesn't need to sort everything. If there are ties at the cut-off, keep="first" (default), "last" or "all" decides which rows come back.
Ranking: rank and ties
Sorting moves rows. Sometimes you want to keep the table as it is and just write each row's position next to it, like the rank on a report card. That is rank(). By default the smallest value gets rank 1; with ascending=False, the biggest does:
week["cups_rank"] = week["cups"].rank(ascending=False)
print(week[["day", "cups", "cups_rank"]])
day cups cups_rank
0 Mon 320 5.0
1 Tue 280 7.0
2 Wed 410 2.0
3 Thu 300 6.0
4 Fri 350 4.0
5 Sat 390 3.0
6 Sun 450 1.0
The rows did not move. Ranks are floats (5.0) because the default tie rule can produce halves.
Tie rules: method=
When values are equal (ties), who is 2nd? The method option chooses the rule. The price column has four days at ₹10 and three at ₹12:
p = week["price"]
print(pd.DataFrame({"price": p,
"average": p.rank(),
"min": p.rank(method="min"),
"dense": p.rank(method="dense"),
"first": p.rank(method="first")}))
price average min dense first
0 10 2.5 1.0 1.0 1.0
1 10 2.5 1.0 1.0 2.0
2 12 6.0 5.0 2.0 5.0
3 10 2.5 1.0 1.0 3.0
4 10 2.5 1.0 1.0 4.0
5 12 6.0 5.0 2.0 6.0
6 12 6.0 5.0 2.0 7.0
| method | Rule | The four ₹10 days | The ₹12 days | Used for |
|---|---|---|---|---|
average (default) |
tied values get the average of the ranks they cover | (1+2+3+4)/4 = 2.5 | (5+6+7)/3 = 6 | statistics (e.g. Spearman correlation, episode 50) |
min |
all tied get the lowest rank; the next group skips ahead | 1 | 5 | sports tables, "joint 1st" |
max |
all tied get the highest rank | 4 | 7 | rare |
dense |
like min, but no gaps between groups |
1 | 2 | "level 1, level 2" |
first |
ties broken by order of appearance | 1, 2, 3, 4 | 5, 6, 7 | a unique position for every row |
rank(pct=True) gives the rank as a fraction between 0 and 1 (a percentile rank), and .astype(int) turns float ranks into whole numbers when there are no halves.
Creating new columns
Revenue is the money that comes in: cups × price. That is a new column, calculated from two others. In episode 14 we read columns with the dot shortcut (week.cups), so the obvious attempt is:
week.revenue = week.cups * week.price
print(week.columns)
Index(['day', 'cups', 'price', 'weather'], dtype='str')
main.py:11: UserWarning: 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
week.revenue = week.cups * week.price
A UserWarning (a warning, not an error: the program carries on) and no revenue column. Assigning with a dot only sticks a normal Python attribute (a name attached to an object) on the DataFrame; it doesn't create a column. Always create columns with square brackets:
week["revenue"] = week["cups"] * week["price"]
week["cost"] = week["cups"] * 4 + 500
week["profit"] = week["revenue"] - week["cost"]
print(week)
day cups price weather revenue cost profit
0 Mon 320 10 sunny 3200 1780 1420
1 Tue 280 10 rain 2800 1620 1180
2 Wed 410 12 cold 4920 2140 2780
3 Thu 300 10 sunny 3000 1700 1300
4 Fri 350 10 sunny 3500 1900 1600
5 Sat 390 12 cold 4680 2060 2620
6 Sun 450 12 rain 5400 2300 3100
week["cups"] * week["price"] multiplies row by row, all at once: vectorised arithmetic (episode 8). + - * / // % ** all work between two columns or between a column and a number. New columns are added at the right end. Assigning to an existing name replaces that column.
assign: new columns on a new table
df["x"] = ... changes df itself (in place). df.assign(name=value, ...) returns a new DataFrame with the extra columns and leaves the original untouched, like writing on a photocopy instead of the original register. A value can be a lambda (a small one-line function, episode 7): pandas calls it with the table built so far, so a later column can use an earlier one in the same assign:
out = week.assign(revenue=week["cups"] * week["price"],
big=lambda d: d["revenue"] > 4000)
print(out)
print(week.columns.tolist())
day cups price weather revenue big
0 Mon 320 10 sunny 3200 False
1 Tue 280 10 rain 2800 False
2 Wed 410 12 cold 4920 True
3 Thu 300 10 sunny 3000 False
4 Fri 350 10 sunny 3500 False
5 Sat 390 12 cold 4680 True
6 Sun 450 12 rain 5400 True
['day', 'cups', 'price', 'weather']
Because assign returns a table, you can call the next method straight after it. That is method chaining (see the mini project, and episode 17).
Labels from a condition: np.where
Ramesh wants a label per day: "good" if revenue is above ₹4000, else "normal". The obvious attempt is an if:
week["revenue"] = week["cups"] * week["price"]
if week["revenue"] > 4000:
week["label"] = "good"
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
if needs one True/False; week["revenue"] > 4000 is seven of them, so Python can't decide. np.where(condition, value_if_true, value_if_false) works row by row:
import numpy as np
week["revenue"] = week["cups"] * week["price"]
week["label"] = np.where(week["revenue"] > 4000, "good", "normal")
print(week[["day", "revenue", "label"]])
day revenue label
0 Mon 3200 normal
1 Tue 2800 normal
2 Wed 4920 good
3 Thu 3000 normal
4 Fri 3500 normal
5 Sat 4680 good
6 Sun 5400 good
The values can also be columns: np.where(week["weather"] == "rain", week["cups"] + 20, week["cups"]). (pandas has a similar method, Series.where(cond, other), which keeps values where the condition is True and replaces the rest.)
Many labels: np.select
For three or more labels, nesting np.where gets unreadable. np.select(conditions, choices, default=...) takes a list of conditions and a matching list of labels; for each row, the first True condition wins, and default covers rows where none is True:
import numpy as np
week["revenue"] = week["cups"] * week["price"]
conds = [week["revenue"] >= 5000,
week["revenue"] >= 3500]
week["grade"] = np.select(conds, ["star", "good"], default="slow")
print(week[["day", "revenue", "grade"]])
day revenue grade
0 Mon 3200 slow
1 Tue 2800 slow
2 Wed 4920 good
3 Thu 3000 slow
4 Fri 3500 good
5 Sat 4680 good
6 Sun 5400 star
Sunday (₹5400) also passes >= 3500, but the first condition matched first. So order the conditions from strictest to loosest. The two lists must have the same length.
Tidying columns: rename, drop, insert, order
rename(columns={"old": "new"})changes column names (alsoindex={...}for row labels). Returns a new table.drop(columns="col")ordrop(columns=[...])removes columns;drop(index=[...])removes rows by label. Returns a new table.insert(position, "name", values)adds a column at a chosen position (0 = first). Changes the table in place and returnsNone.- Column order:
df[["c", "a", "b"]](double brackets, episode 14) returns the columns in the order you list.
week["revenue"] = week["cups"] * week["price"]
week = week.rename(columns={"cups": "cups_sold", "price": "price_rs"})
week = week.drop(columns="weather")
week.insert(1, "is_weekend", week["day"].isin(["Sat", "Sun"]))
print(week)
day is_weekend cups_sold price_rs revenue
0 Mon False 320 10 3200
1 Tue False 280 10 2800
2 Wed False 410 12 4920
3 Thu False 300 10 3000
4 Fri False 350 10 3500
5 Sat True 390 12 4680
6 Sun True 450 12 5400
week["revenue"] = week["cups"] * week["price"]
print(week[["day", "revenue", "cups", "price"]])
print(week.columns.tolist())
day revenue cups price
0 Mon 3200 320 10
1 Tue 2800 280 10
2 Wed 4920 410 12
3 Thu 3000 300 10
4 Fri 3500 350 10
5 Sat 4680 390 12
6 Sun 5400 450 12
['day', 'cups', 'price', 'weather', 'revenue']
Two errors you will meet:
print(week.drop(columns="weathr"))
KeyError: "['weathr'] not found in axis"
week.insert(1, "cups", 0)
ValueError: cannot insert cups, already exists
Here axis means the direction: the columns. insert never overwrites; use week["cups"] = 0 to replace a column (or allow_duplicates=True if you really want two columns with one name, which you almost never do).
Mini project: a weekly report in one chain
Round brackets around the whole expression let each step sit on its own line:
import numpy as np
report = (week
.assign(revenue=week["cups"] * week["price"])
.assign(grade=lambda d: np.select(
[d["revenue"] >= 5000, d["revenue"] >= 3500],
["star", "good"], default="slow"))
.assign(rank=lambda d: d["revenue"].rank(ascending=False, method="min").astype(int))
.drop(columns=["weather"])
.rename(columns={"cups": "cups_sold"})
.sort_values("revenue", ascending=False))
print(report)
print(report.nlargest(3, "revenue")["day"].tolist())
day cups_sold price revenue grade rank
6 Sun 450 12 5400 star 1
2 Wed 410 12 4920 good 2
5 Sat 390 12 4680 good 3
4 Fri 350 10 3500 good 4
0 Mon 320 10 3200 slow 5
3 Thu 300 10 3000 slow 6
1 Tue 280 10 2800 slow 7
['Sun', 'Wed', 'Sat']
- Revenue, a grade with
np.select, and a rank withmethod="min"turned into whole numbers. - Drop weather, rename cups, sort best first.
nlargest(3, ...)["day"].tolist()names the podium as a plain list.
Read top to bottom, the chain tells the story of the analysis.
Common mistakes
| Code | Real error / symptom | Why | Fix |
|---|---|---|---|
sorted(week) |
['cups', 'day', 'price', 'weather'] (no error) |
Looping over a DataFrame gives column names | week.sort_values("cups") |
week.sort_values("Cups") |
KeyError: 'Cups' |
Column names are case-sensitive | Check week.columns |
week.sort_values("cups") without saving |
(no error: week unchanged) |
Returns a new table | week = week.sort_values("cups") |
week.revenue = week.cups * week.price |
UserWarning: 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 |
A dot creates an attribute, not a column | week["revenue"] = ... |
if week["revenue"] > 4000: |
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). |
if needs one True/False |
np.where(cond, "good", "normal") |
np.select with loose condition first |
(no error: everyone gets the loose label) | First True condition wins | Strictest condition first |
week.drop(columns="weathr") |
KeyError: "['weathr'] not found in axis" |
Typo in the name | Check spelling; errors="ignore" only if a missing column is OK |
week.insert(1, "cups", 0) |
ValueError: cannot insert cups, already exists |
insert never overwrites |
week["cups"] = 0 |
week = week.insert(...) |
(no error: week becomes None) |
insert works in place and returns None |
Call it without week = |
Practice
All practice questions use this table:
import pandas as pd, numpy as np
marks = pd.DataFrame({
"name": ["Asha", "Ravi", "Meena", "Kabir", "Zoya", "Dev"],
"maths": [81, 67, 92, 74, 88, 67],
"science": [75, 80, 95, 60, 70, 85],
"city": ["Pune", "Agra", "Pune", "Delhi", "Agra", "Pune"],
})
1. Sort students by maths, highest first. Then by city A to Z, and within a city by maths highest first.
Solution
print(marks.sort_values("maths", ascending=False))
print(marks.sort_values(["city", "maths"], ascending=[True, False]))
name maths science city
2 Meena 92 95 Pune
4 Zoya 88 70 Agra
0 Asha 81 75 Pune
3 Kabir 74 60 Delhi
1 Ravi 67 80 Agra
5 Dev 67 85 Pune
name maths science city
4 Zoya 88 70 Agra
1 Ravi 67 80 Agra
3 Kabir 74 60 Delhi
2 Meena 92 95 Pune
0 Asha 81 75 Pune
5 Dev 67 85 Pune
2. The two best students in science, and the student with the lowest maths.
Solution
print(marks.nlargest(2, "science")[["name", "science"]])
print(marks.nsmallest(1, "maths")[["name", "maths"]])
name science
2 Meena 95
5 Dev 85
name maths
1 Ravi 67
Ravi and Dev both have 67; keep="first" (the default) returns the first one. keep="all" would return both.
3. Rank maths with the biggest first, using min and dense. Why do Ravi and Dev get the same rank?
Solution
marks["r_min"] = marks["maths"].rank(ascending=False, method="min")
marks["r_dense"] = marks["maths"].rank(ascending=False, method="dense")
print(marks[["name", "maths", "r_min", "r_dense"]])
name maths r_min r_dense
0 Asha 81 3.0 3.0
1 Ravi 67 5.0 5.0
2 Meena 92 1.0 1.0
3 Kabir 74 4.0 4.0
4 Zoya 88 2.0 2.0
5 Dev 67 5.0 5.0
Both have 67, a tie, so they share the rank. With dense there are no gaps; here the tie is at the bottom, so min and dense agree.
4. Add total (maths + science) and avg (rounded to 1 decimal), then a column result that is "distinction" for an average of 85 or more, "first" for 70 or more, and "pass" otherwise.
Solution
marks["total"] = marks["maths"] + marks["science"]
marks["avg"] = (marks["total"] / 2).round(1)
marks["result"] = np.select([marks["avg"] >= 85, marks["avg"] >= 70],
["distinction", "first"], default="pass")
print(marks[["name", "total", "avg", "result"]])
name total avg result
0 Asha 156 78.0 first
1 Ravi 147 73.5 first
2 Meena 187 93.5 distinction
3 Kabir 134 67.0 pass
4 Zoya 158 79.0 first
5 Dev 152 76.0 first
5. With one chain and without changing marks: add total, keep only Pune students, rename name to student, drop city, and sort by total, best first.
Solution
pune = (marks
.assign(total=marks["maths"] + marks["science"])
.query("city == 'Pune'")
.rename(columns={"name": "student"})
.drop(columns="city")
.sort_values("total", ascending=False))
print(pune)
print(marks.columns.tolist())
student maths science total
2 Meena 92 95 187
0 Asha 81 75 156
5 Dev 67 85 152
['name', 'maths', 'science', 'city']
Quick quiz
s = pd.Series([30, 10, 10, 20])
print(s.rank(method="dense")[1])
A. 1.0 B. 1.5 C. 2.0 D. 3.0
Answer
A. Ascending, so 10 is the smallest; both 10s share rank 1, 20 is 2 and 30 is 3 (dense: no gaps). With the default average it would be 1.5.
Interview questions
- How do you sort a DataFrame by several columns with different directions?
df.sort_values(["a", "b"], ascending=[False, True]). sort_valuesvssort_index?sort_valuesorders rows by column values;sort_indexorders them by index labels (e.g. to restore the original order after sorting).- Does
sort_valueschange the DataFrame? No, it returns a new one. Assign the result (theinplace=Trueoption exists but is discouraged). nlargestvssort_values().head()? Same result;nlargeststates the intent and avoids a full sort, so it is faster on large data.- Explain
rankmethodsaverage,min,denseandfirst. Ties get the average rank; the lowest rank with gaps after; the lowest rank without gaps; or ranks by order of appearance. - Why does
df.new = ...not create a column? Attribute assignment sets a Python attribute; pandas warns and creates no column. Usedf["new"] = .... df["x"] = ...vsdf.assign(x=...)? The first changesdfin place;assignreturns a new DataFrame, so it fits in method chains.- How do you create a column from a condition?
np.where(cond, a, b)for two outcomes,np.select(conds, choices, default)for many (first True wins). Avoidapplywithifwhen a vectorised way exists. - How do you reorder columns?
df[["c", "a", "b"]], orinserta column at a position. - What does
dropraise for a missing column, and how do you avoid it?KeyError: "[...] not found in axis"; fix the name, or passerrors="ignore".
Glossary
| Term | Meaning | Example |
|---|---|---|
| Sorting | Putting rows in order by some value | best days first |
sorted() |
Python's built-in sort for lists; on a DataFrame it sorts column names | sorted(week) |
sort_values |
Sort rows by one or more columns; returns a new table | week.sort_values("cups") |
| Ascending | Smallest first (A to Z, 1 to 9) | ascending=True |
| Descending | Biggest first | ascending=False |
| Tie | Two or more equal values | four days at ₹10 |
sort_index |
Sort rows (or columns) by their labels | by.sort_index() |
nlargest |
The n rows with the biggest values in a column | week.nlargest(3, "cups") |
nsmallest |
The n rows with the smallest values in a column | week.nsmallest(2, "cups") |
| Top-N | A question about the best (or worst) N items | top 3 days |
rank |
A position number for each row, rows don't move | cups.rank(ascending=False) |
method= (rank) |
The tie rule: average, min, max, dense, first | rank(method="dense") |
| Dense rank | Ties share a rank, no gaps after them | 1, 1, 2 |
| Percentile rank | Rank as a fraction of the total | rank(pct=True) |
| Revenue | Money that comes in from sales | cups × price |
| New column | A column created from a calculation | week["revenue"] = ... |
| Vectorised arithmetic | Maths on whole columns at once, row by row | week["cups"] * week["price"] |
| Attribute | A name attached to a Python object with a dot | week.revenue = ... (not a column!) |
| UserWarning | A warning: the program continues, but something is probably wrong | attribute assignment |
| In place | Changing the object itself instead of returning a new one | df["x"] = ..., insert |
assign |
Return a new table with extra columns | week.assign(rev=...) |
| Lambda | A small one-line function | lambda d: d["rev"] > 4000 |
np.where |
Pick one of two values per row from a condition | np.where(c, "good", "normal") |
np.select |
Pick from many values per row; first True condition wins | np.select(conds, choices, default="slow") |
default |
The value when no condition is True | default="slow" |
rename |
Change column (or index) names | rename(columns={"cups": "cups_sold"}) |
drop |
Remove columns or rows by label | drop(columns="weather") |
insert |
Add a column at a position, in place | insert(1, "is_weekend", ...) |
| Column order | The left-to-right order of columns | df[["day", "revenue"]] |
| Axis | A direction in a table: rows (0) or columns (1) | "not found in axis" |
| Method chaining | Calling methods one after another on returned tables | (df.assign(...).sort_values(...)) |
Summary
sorted(df)sorts the column names. Sort rows withsort_values(ascending=Falsefor biggest first; lists for several columns), and restore label order withsort_index. Save the result.nlargest/nsmallestanswer top-N questions directly.rankwrites positions without moving rows;method=decides ties (average,min,max,dense,first).- Create columns with square brackets and vectorised arithmetic;
df.new = ...creates nothing.assignreturns a new table and fits in chains. - Labels:
np.wherefor two,np.selectfor many (strictest condition first). - Tidy with
rename,drop(both return new tables),insert(in place), anddf[[...]]for column order. - Next episode: pandas 7: apply, map, binning and method chaining.