CODEWITHSUNDEEP
r
DrPaulVella
DrPaulVella

Reputation: 449

count occurrences of unique string row by row

say I have a df like so:

number date
123    2022-01-01
567    2022-01-01
123    2022-01-04
763    2022-01-05
567    2022-01-06
123    2022-01-09

I want to count the occurrence of each number by expanding the range row-by-row. so my expected output would be

number date        occurrence
123    2022-01-01  1
567    2022-01-01  1
123    2022-01-04  2
763    2022-01-05  1
567    2022-01-06  2
123    2022-01-09  3

Upvotes: 1

Views: 41

Answers (2)

r2evans
r2evans

Reputation: 160417

base R

dat$occurrence <- ave(dat$number, dat$number, FUN = seq_along)
dat
#   number       date occurrence
# 1    123 2022-01-01          1
# 2    567 2022-01-01          1
# 3    123 2022-01-04          2
# 4    763 2022-01-05          1
# 5    567 2022-01-06          2
# 6    123 2022-01-09          3

data.table

library(data.table)
as.data.table(dat)[, occurrence := seq_len(.N), by = number ][]
#    number       date occurrence
#     <int>     <char>      <int>
# 1:    123 2022-01-01          1
# 2:    567 2022-01-01          1
# 3:    123 2022-01-04          2
# 4:    763 2022-01-05          1
# 5:    567 2022-01-06          2
# 6:    123 2022-01-09          3

Data

dat <- structure(list(number = c(123L, 567L, 123L, 763L, 567L, 123L), date = c("2022-01-01", "2022-01-01", "2022-01-04", "2022-01-05", "2022-01-06", "2022-01-09")), row.names = c(NA, -6L), class = "data.frame")

Upvotes: 0

Bruno
Bruno

Reputation: 4151

row number plus group by should do it.

library(tidyverse)

example_data <- read_table('number date
123    2022-01-01
567    2022-01-01
123    2022-01-04
763    2022-01-05
567    2022-01-06
123    2022-01-09')


example_data |> 
  group_by(number) |> 
  mutate(occurance = row_number())
#> # A tibble: 6 x 3
#> # Groups:   number [3]
#>   number date       occurance
#>    <dbl> <date>         <int>
#> 1    123 2022-01-01         1
#> 2    567 2022-01-01         1
#> 3    123 2022-01-04         2
#> 4    763 2022-01-05         1
#> 5    567 2022-01-06         2
#> 6    123 2022-01-09         3

Created on 2022-01-24 by the reprex package (v2.0.1)

Upvotes: 2

Related Questions