SyntaxPilot
Courses/Data Analytics with Python/Lesson 16
Video coming soonThis lesson’s video is being made

Start with the notes below; the video appears here as soon as it is on YouTube.

Data Analytics with Python · Lesson 16 of 90

pandas 6: sorting, ranking and creating columns

sort_valuesascendingsort_indexnlargestnsmallestrank (method)new column assignmentassignvectorised arithmeticnp.wherenp.selectrenamedropinsert

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 (also index={...} for row labels). Returns a new table.
  • drop(columns="col") or drop(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 returns None.
  • 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']
  1. Revenue, a grade with np.select, and a rank with method="min" turned into whole numbers.
  2. Drop weather, rename cups, sort best first.
  3. 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

  1. How do you sort a DataFrame by several columns with different directions? df.sort_values(["a", "b"], ascending=[False, True]).
  2. sort_values vs sort_index? sort_values orders rows by column values; sort_index orders them by index labels (e.g. to restore the original order after sorting).
  3. Does sort_values change the DataFrame? No, it returns a new one. Assign the result (the inplace=True option exists but is discouraged).
  4. nlargest vs sort_values().head()? Same result; nlargest states the intent and avoids a full sort, so it is faster on large data.
  5. Explain rank methods average, min, dense and first. Ties get the average rank; the lowest rank with gaps after; the lowest rank without gaps; or ranks by order of appearance.
  6. Why does df.new = ... not create a column? Attribute assignment sets a Python attribute; pandas warns and creates no column. Use df["new"] = ....
  7. df["x"] = ... vs df.assign(x=...)? The first changes df in place; assign returns a new DataFrame, so it fits in method chains.
  8. 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). Avoid apply with if when a vectorised way exists.
  9. How do you reorder columns? df[["c", "a", "b"]], or insert a column at a position.
  10. What does drop raise for a missing column, and how do you avoid it? KeyError: "[...] not found in axis"; fix the name, or pass errors="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 with sort_values (ascending=False for biggest first; lists for several columns), and restore label order with sort_index. Save the result.
  • nlargest / nsmallest answer top-N questions directly.
  • rank writes positions without moving rows; method= decides ties (average, min, max, dense, first).
  • Create columns with square brackets and vectorised arithmetic; df.new = ... creates nothing. assign returns a new table and fits in chains.
  • Labels: np.where for two, np.select for many (strictest condition first).
  • Tidy with rename, drop (both return new tables), insert (in place), and df[[...]] for column order.
  • Next episode: pandas 7: apply, map, binning and method chaining.