DEV Community

Cover image for pandas nlargest per group: 7 Pitfalls When Picking the Top N Rows per Day in a Mentions Panel
dustin liang
dustin liang

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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))
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode
         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
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

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])
Enter fullscreen mode Exit fullscreen mode
115    10.0
110     6.0
108     NaN
Name: mentions, dtype: float64
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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]]
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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 实跑)。

Top comments (0)