How to number rows within a group in dataframe

dplyr
dplyr n()
dplyr row_number()
Learn how to number rows within a group in dataframe with this comprehensive R tutorial. Includes practical examples and code snippets.
Published

January 27, 2022

Introduction

Numbering rows within groups is a common data manipulation task that allows you to create sequential identifiers for observations within specific categories. This technique is particularly useful when you need to rank items, create unique identifiers within subsets, or prepare data for further analysis that requires ordered observations within groups.

Getting Started

library(tidyverse)
library(palmerpenguins)

Example 1: Basic Usage

The Problem

We want to number each penguin observation within its species group, creating a sequential counter that restarts at 1 for each new species.

Step 1: Examine the Data Structure

Let’s first look at our penguin data to understand the grouping variable.

penguins |>
  select(species, island, bill_length_mm) |>
  head(10)

This shows us the species column that we’ll use for grouping, along with some other variables for context.

Step 2: Add Row Numbers Within Groups

We’ll use row_number() within group_by() to create sequential numbering for each species.

penguins_numbered <- penguins |>
  group_by(species) |>
  mutate(row_within_species = row_number()) |>
  ungroup()

This creates a new column where numbering starts at 1 for each species group.

Step 3: Verify the Results

Let’s examine how the numbering worked across different species.

penguins_numbered |>
  select(species, row_within_species, island) |>
  slice(c(1:3, 150:153, 270:273))

Notice how the row numbers reset to 1 when we move from Adelie to Chinstrap to Gentoo species.

Example 2: Practical Application

The Problem

Imagine you’re a researcher studying penguin populations and need to identify the top 3 heaviest penguins within each species for a nutrition study. You need to rank penguins by body mass within their species groups.

Step 1: Sort and Number by Body Mass

We’ll arrange penguins by body mass within each species, then add row numbers to identify rankings.

top_penguins <- penguins |>
  filter(!is.na(body_mass_g)) |>
  group_by(species) |>
  arrange(species, desc(body_mass_g)) |>
  mutate(weight_rank = row_number())

This sorts penguins from heaviest to lightest within each species and assigns rank numbers.

Step 2: Extract Top Rankings

Now we can easily filter for the top-ranked penguins within each species group.

top_3_heaviest <- top_penguins |>
  filter(weight_rank <= 3) |>
  select(species, body_mass_g, weight_rank, sex, island)

This gives us exactly 3 penguins per species, ranked by their body mass from heaviest to lightest.

Step 3: Compare with Alternative Numbering

We can also use rank() for handling ties differently than row_number().

penguins |>
  filter(!is.na(body_mass_g)) |>
  group_by(species) |>
  mutate(
    row_num = row_number(desc(body_mass_g)),
    rank_num = rank(desc(body_mass_g))
  ) |>
  filter(row_num <= 3) |>
  select(species, body_mass_g, row_num, rank_num)

The rank() function handles ties by giving them the same rank, while row_number() assigns consecutive integers even for tied values.

Summary

  • Use row_number() with group_by() to create sequential numbering within groups that resets for each new group
  • Combine with arrange() to number rows based on sorted order within groups, useful for ranking observations
  • The row_number() function assigns consecutive integers even when there are tied values, unlike rank() which gives tied observations the same rank
  • Always use ungroup() after grouped operations to avoid unexpected behavior in subsequent data manipulations

  • This technique is essential for creating within-group identifiers, selecting top N observations per group, and preparing data for analyses requiring ordered observations within categories