Repetead sequential numbers by group

Question

Let's consider one has the following dataframe:

date	x	counter
2021-09-30	a	1
2021-09-30	b	2
2021-09-30	c	3
2021-12-31	e	1
2021-12-31	f	2
2021-12-31	g	3
2022-03-31	t	1
2022-03-31	u	2
2022-03-31	z	3

I need to create a new increasing and monotonic ID by the date variable.

For instance, the new dataframe should appear as follows:

date	x	counter	new counter
2021-09-30	a	1	1
2021-09-30	b	2	1
2021-09-30	c	3	1
2021-12-31	e	1	2
2021-12-31	f	2	2
2021-12-31	g	3	2
2022-03-31	t	1	3
2022-03-31	u	2	3
2022-03-31	z	3	3

I'm running the R version 3.6.3; in the hope my question is clear enough.

does `ǹew counter` depend on other columns or is it just based on the row number? — danlooo, Mar 15 '22 at 09:04
my fault in typing the table in the question @danlooo . I'm going to the edit the question!! — QuantumGorilla, Mar 15 '22 at 09:10

score 1 · Answer 1 · answered Mar 15 '22 at 09:07

You can use dplyr::cur_group_id() to do the job.

library(dplyr)

df %>% 
  group_by(date) %>% 
  mutate(new_counter = cur_group_id())

# A tibble: 9 × 4
# Groups:   date [3]
  date       x     counter new_counter
  <chr>      <chr>   <int>       <int>
1 2021-09-30 a           1           1
2 2021-09-30 b           2           1
3 2021-09-30 c           3           1
4 2021-12-31 e           1           2
5 2021-12-31 f           2           2
6 2022-03-31 g           3           3
7 2022-03-31 t           1           3
8 2022-03-31 u           2           3
9 2022-03-31 z           3           3

Repetead sequential numbers by group

1 Answers1