Create column representing instance rather than total count

Question

Let's say I have the following dataframe:

ID <- c(15, 25, 90, 1, 23, 543)

animal <- c("fish", "dog", "fish", "cat", "dog", "fish")

df <- data.frame(ID, animal)

How could I create a third column to represent the instance (from top to bottom) that a repeat animal appears? for example, a column "Instance" in the order (1, 1, 2, 1, 2, 3)? I know I can use group_by to receive the total count, but this is not exactly what I'm after. Thanks.

score 0 · Accepted Answer · answered Mar 10 '23 at 14:27

You need row_number() by groups of animal.

library(dplyr)

df %>%
  mutate(Instance = row_number(), .by = animal)

#    ID animal Instance
# 1  15   fish        1
# 2  25    dog        1
# 3  90   fish        2
# 4   1    cat        1
# 5  23    dog        2
# 6 543   fish        3

With built-in packages, you can use ave:

ave(df$animal, df$animal, FUN = seq)

# [1] "1" "1" "2" "1" "2" "3"

Create column representing instance rather than total count

1 Answers1