pandas nlargest per group: 7 Pitfalls When Picking the Top N Rows per Day in a Mentions Panel

To get the top N rows per group in pandas, don't call nlargest on a DataFrameGroupBy (that method doesn't exist). Use df.groupby(key)[col].nlargest(n) when you only need the values, or sort once and take groupby(key).head(n) when you need whole rows. The sort-then-head route also gives a repeatable tie-break and lets you decide how missing values are handled. The choice matters when there are ties, missing values, or duplicate index labels, and a daily news-mentions panel usually has all three. The pandas DataFrame.nlargest reference says that method "is equivalent to df.sort_values(columns, ascending=False).head(n), but more performant". The SeriesGroupBy.nlargest reference notes that keep='all' can return more than n rows. Every snippet below was run on pandas 3.0.6.
Facts
- What: seven ways a "top N rows per group" query in pandas (
nlargestper group) returns rows you did not expect, plus one fix that holds up. - Who / source: pandas 3.0.6 and NumPy 2.5.5 on Python 3.13.5; behaviour checked against the pandas API reference.
- Coverage or where: a simulated panel of 12 placeholder tickers (
S01–S12) × 20 business days (240 rows), plus a 750,000-row simulated panel for timing. - Window & units: one group = one trading day; values are daily mention counts, not prices.
- Source & as-of: simulated data (NumPy
default_rng(20261121)), prepared 2026-11-21 (Asia/Shanghai).
All data here is synthetic. No real stock, feed, or ranking is involved, and "top 3" only means "most-mentioned rows in a fake table".
The test panel
One row per date and ticker, with a Poisson count of mentions. About 5% of cells are blanked to mimic feed gaps, and one day (2026-03-13) is a near-outage where only two tickers have data.
import numpy as np import pandas as pd rng = np.random.default_rng(20261121) dates = pd.bdate_range("2026-03-02", periods=20) stocks = [f"S{i:02d}" for i in range(1, 13)] panel = pd.DataFrame( [(d, s, rng.poisson(6)) for d in dates for s in stocks], columns=["date", "stock", "mentions"], ).astype({"mentions": "float64"}) panel.loc[rng.random(len(panel)) < 0.05, "mentions"] = np.nan # scattered gaps outage = dates[9] # 2026-03-13 keep_ok = panel["stock"].isin(["S03", "S08"]) panel.loc[(panel["date"] == outage) & ~keep_ok, "mentions"] = np.nan print(len(panel), int(panel["mentions"].isna().sum())) # 240 22 In a real pipeline the ticker list would come from a mapping like a per-stock concept tag lookup. Here they are just labels.
Pitfall 1: DataFrameGroupBy has no nlargest
The first thing most people try:
panel.groupby("date").nlargest(3, "mentions") # AttributeError: 'DataFrameGroupBy' object has no attribute 'nlargest' nlargest exists on DataFrame, Series, and SeriesGroupBy, but not on DataFrameGroupBy. You have to choose one column first, which leads to pitfall 2.
Pitfall 2: SeriesGroupBy.nlargest returns only the values, under a 2-level index
top_s = panel.groupby("date")["mentions"].nlargest(3) print(top_s.head(6)) date 2026-03-02 0 8.0 3 7.0 4 6.0 2026-03-03 12 9.0 23 9.0 15 8.0 Name: mentions, dtype: float64 The stock column is gone. The index is ['date', None]: the group key, then the original row label. To get full rows back, take the last level and look the rows up:
row_ids = top_s.index.get_level_values(-1) top_rows = panel.loc[row_ids] date stock mentions 0 2026-03-02 S01 8.0 3 2026-03-02 S04 7.0 4 2026-03-02 S05 6.0 12 2026-03-03 S01 9.0 23 2026-03-03 S12 9.0 15 2026-03-03 S04 8.0 That works only while the row labels are unique. Pitfall 6 shows what happens when they aren't.
Pitfall 3: apply + nlargest hides or drops the group column
The usual Stack Overflow answer is groupby(...).apply(lambda g: g.nlargest(n, col)). In pandas 3 the grouping column is no longer passed into the function, so the result depends on group_keys:
top_a = panel.groupby("date").apply(lambda g: g.nlargest(3, "mentions")) print(top_a.columns.tolist(), list(top_a.index.names)) # ['stock', 'mentions'] ['date', None] top_a2 = panel.groupby("date", group_keys=False).apply(lambda g: g.nlargest(3, "mentions")) print(top_a2.columns.tolist(), list(top_a2.index.names)) # ['stock', 'mentions'] [None] With the default, date moves into the index. With group_keys=False, which older answers suggest to "keep the original shape", date disappears completely and you have a list of tickers with no day attached. If you copy code written for pandas 1.x, check the columns before you save the result.
Pitfall 4: keep='first' means "first in row order", not "first by a rule you chose"
Mention counts are small integers, so ties at the cut-off are common. keep='first' breaks them by the order the rows happen to be in. I shuffled the same panel and ran the same query:
shuffled = panel.sample(frac=1, random_state=7) a = panel.groupby("date")["mentions"].nlargest(3) b = shuffled.groupby("date")["mentions"].nlargest(3) pick_a = panel.loc[a.index.get_level_values(-1)].groupby("date")["stock"].apply(lambda x: tuple(sorted(x))) pick_b = panel.loc[b.index.get_level_values(-1)].groupby("date")["stock"].apply(lambda x: tuple(sorted(x))) print(int(pick_a.ne(pick_b).sum()), "of", len(pick_a)) # 5 of 20 On 5 of the 20 days, the top-3 set changed only because the rows were in a different order. On 2026-03-02 three tickers tie at 6 for the last slot:
date stock mentions 2026-03-02 S01 8.0 2026-03-02 S04 7.0 2026-03-02 S07 6.0 2026-03-02 S05 6.0 2026-03-02 S12 6.0 original order picks: ('S01', 'S04', 'S05') | shuffled picks: ('S01', 'S04', 'S12') If your loader sorts by fetch time, or a database query has no ORDER BY, yesterday's "top 3" can change with no new data. keep='all' avoids the arbitrary choice but returns a variable number of rows: on this panel it returned more than 3 rows on 11 of 20 days, and up to 7 rows on a normal day.
Pitfall 5: a thin day is padded with NaN
I expected nlargest(3) to return two rows on the near-outage day. On pandas 3.0.6 it returned three, and the third is missing:
nl = panel.groupby("date")["mentions"].nlargest(3) print(nl.loc[outage]) 115 10.0 110 6.0 108 NaN Name: mentions, dtype: float64 sort_values(...).groupby("date").head(3) does the same, because sort_values puts NaN last by default:
date stock mentions 2026-03-13 S08 10.0 2026-03-13 S03 6.0 2026-03-13 S01 NaN With keep='all' it gets worse: the ten missing rows all "tie" at the boundary, so that day returned 12 rows (min 3 max 12 in the run log). Downstream, a NaN row in a top-N table often turns into "S01 was in the top 3 with 0 mentions" after a fillna(0). Drop missing values before ranking, not after. Check this on your own pandas version; I only tested 3.0.6.
Pitfall 6: duplicate index labels make the .loc join return extra rows
The .loc lookup from pitfall 2 assumes each row label appears once. Concatenating two loads that each start at 0 breaks that:
week1 = panel[panel["date"] < dates[5]] week2 = panel[panel["date"] >= dates[5]].reset_index(drop=True) # second load restarts at 0 stacked = pd.concat([week1, week2]) # no ignore_index print(int(stacked.index.duplicated().sum())) # 60 t = stacked.groupby("date")["mentions"].nlargest(3) joined = stacked.loc[t.index.get_level_values(-1)] print(len(t), len(joined)) # 60 90 60 picks became 90 rows, and nothing raised an error. pd.concat(..., ignore_index=True) or a reset_index(drop=True) before ranking prevents it. assert stacked.index.is_unique is a cheap guard.
Pitfall 7: counts stored as strings
Scraped counts often arrive as text. nlargest refuses them, which is the good outcome:
raw = panel.assign(mentions=panel["mentions"].map(lambda v: "" if pd.isna(v) else str(int(v)))) print(raw["mentions"].dtype) # str raw.groupby("date")["mentions"].nlargest(3) # TypeError: Cannot use method 'nlargest' with dtype str The bad outcome is switching to sort_values to get rid of the error. Strings sort lexicographically, so "8" beats "12":
string sort picks: [['S11', '8'], ['S03', '6'], ['S10', '6']] numeric nlargest picks: [['S05', 12.0], ['S11', 8.0], ['S03', 6.0]] That is 2026-03-04. The ticker with the most mentions that day drops out. Convert first with pd.to_numeric(raw["mentions"], errors="coerce").
The function I use now
It drops NaN before ranking, resets the index, breaks ties by an explicit column, and keeps every column:
def top_n_per_group(df, by, col, n, tiebreak, ties="drop"): """Top-n rows per group: NaN excluded, deterministic tie-break, all columns kept.""" clean = df.dropna(subset=[col]).reset_index(drop=True) if ties == "all": r = clean.groupby(by)[col].rank(method="min", ascending=False) return clean[r <= n].sort_values([by, col, tiebreak], ascending=[True, False, True]) ordered = clean.sort_values([by, col, tiebreak], ascending=[True, False, True]) return ordered.groupby(by).head(n) top = top_n_per_group(panel, "date", "mentions", 3, tiebreak="stock") print(top.groupby("date").size().value_counts().to_dict()) # {3: 19, 2: 1} top_sh = top_n_per_group(panel.sample(frac=1, random_state=7), "date", "mentions", 3, tiebreak="stock") print(top.reset_index(drop=True).equals(top_sh.reset_index(drop=True))) # True Now the near-outage day returns two real rows instead of a padded NaN, and shuffling the input changes nothing. ties="all" reproduces keep='all' without the NaN rows: 75 rows, the same as the non-missing count from nlargest(3, keep="all"). Using the ticker as the tie-break is arbitrary but documented. If you need a different rule, such as the earlier first-mention time, sort by that column instead.
The same pattern works for any per-group cut: top three tickers per board per day, or the busiest days for one ticker in a single-stock heat series.
Speed: nlargest is not the fast path here
The docs call DataFrame.nlargest "more performant" than sort + head, but that is for one frame. Per group, the overhead is in the groupby machinery. On a simulated 250-day × 3,000-ticker panel (750,000 rows), top 5 per day, best of three runs on one machine:
def t_apply(): return big.groupby("date", group_keys=False).apply(lambda g: g.nlargest(5, "mentions")) def t_series(): s = big.groupby("date")["mentions"].nlargest(5); return big.loc[s.index.get_level_values(-1)] def t_sorthead(): return big.sort_values(["date", "mentions"], ascending=[True, False]).groupby("date").head(5) def t_rank(): return big[big.groupby("date")["mentions"].rank(method="first", ascending=False) <= 5] apply + nlargest 175.5 ms rows=1250 SeriesGroupBy.nlargest + .loc 165.0 ms rows=1250 sort_values + groupby.head 67.9 ms rows=1250 groupby.rank <= n 58.7 ms rows=1250 Sort + head and rank were about 2.4–3× faster than the two nlargest routes. Absolute numbers depend on your machine, so rerun snippets.py before quoting them. Note that rank(method="first") breaks ties by row order, the same weakness as pitfall 4, so add a sort before it if the result must be repeatable.
Checklist before you trust a top-N-per-group table
- Select a column (
groupby(key)[col]) or use sort +head;DataFrameGroupBy.nlargestdoes not exist. - Run
assert df.index.is_uniquebefore any.loclookup by row label. - Make sure the ranked column is numeric (
pd.to_numeric(..., errors="coerce")). - Drop NaN before ranking and count rows per group afterwards; expect fewer than n on thin days.
- Pick an explicit tie-break column, or use
keep='all'and plan for a variable row count. - On pandas 3, check that the group key is still a column after any
apply.
FAQ
Is groupby().head(n) the same as nlargest per group?
Only after you sort by the value column in descending order. head(n) takes the first n rows in the current order.
How do I get the bottom N per group?
SeriesGroupBy.nsmallest(n), or sort ascending and use head(n). The same NaN, tie, and index issues apply.
Can I rank within several keys, such as board and date?
Yes. Pass a list to by, for example top_n_per_group(df, ["date", "board"], ...), and add both keys to the sort list.
The full script (snippets.py) and its output reproduce every number above.
Lava (熔岩, lava.ai-dolphin.com) — A-share stock heat & sentiment monitoring by AI-Dolphin. All examples in this post use simulated data.
中文简介: 在 pandas 里按组取前 N 行(nlargest per group),DataFrameGroupBy 没有 nlargest;SeriesGroupBy.nlargest 只返回数值和两层索引;apply 在 pandas 3 下会把分组列挪进索引甚至丢掉。本文用模拟的每日新闻提及面板实测了并列值按行序取舍、缺数日被 NaN 补位、重复索引让 .loc 多出 30 行、字符串计数按字典序排序这几个坑,并给出「先去 NaN、显式 tie-break、再 sort + head」的可复用函数和 75 万行的计时对比(pandas 3.0.6 实跑)。
