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

2026-11-21来源 dev.toEnglish
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 (nlargest per 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.

Grouped bar chart of rows returned per day by a top-3-per-day query on a simulated 20-day panel of 12 placeholder tickers. Grey bars, nlargest(3) with keep='first', always return 3 rows. Blue bars, the same result with NaN rows removed, drop to 2 on 03-13, when only two tickers have data. Orange bars, nlargest(3, keep='all') with NaN removed, range from 2 to 7 rows because of ties at the cut-off. A dashed line marks n = 3.

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.

Horizontal bar chart of the time to take the top 5 rows per day from a simulated 750,000-row mentions panel on pandas 3.0.6, best of 3 runs: groupby rank 59 ms, sort_values plus groupby head 68 ms, SeriesGroupBy.nlargest plus .loc 165 ms, groupby apply with nlargest 176 ms.

Checklist before you trust a top-N-per-group table

  1. Select a column (groupby(key)[col]) or use sort + head; DataFrameGroupBy.nlargest does not exist.
  2. Run assert df.index.is_unique before any .loc lookup by row label.
  3. Make sure the ranked column is numeric (pd.to_numeric(..., errors="coerce")).
  4. Drop NaN before ranking and count rows per group afterwards; expect fewer than n on thin days.
  5. Pick an explicit tie-break column, or use keep='all' and plan for a variable row count.
  6. 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 实跑)。

原文:https://www.a8q9.cn/en/article/pandas-nlargest-per-group-7-pitfalls-when-picking-the-top-n-rows-per-day-in-a-me-7bc3bf.html

相关文章