R: Create a counter column that goes up every second row in a group-CodePudding

I would like to create a column where the counter goes up every second row (e.g., 1, 1, 2, 2, 3, 3), and have that counter be attached to the group name. This is an example of a dataframe that I have:

group numbering
g1         x
g1         y
g2         x
g2         y
g2         x
g2         y
g3         x
g3         y

The output would look something like this:

group numbering counter
g1         x      g1_1
g1         y      g1_1
g2         x      g2_1
g2         y      g2_1
g2         x      g2_2
g2         y      g2_2
g3         x      g3_1
g3         y      g3_1

CodePudding user response：

Use group_by and row_number. This can be used irrespective of the size of the dataframe

library(tidyverse)
df %>%
  group_by(group, numbering) %>%
  mutate(counter = str_c(group, row_number(), sep='_'))
 group numbering counter
  <chr> <chr>     <chr>  
1 g1    x         g1_1   
2 g1    y         g1_1   
3 g2    x         g2_1   
4 g2    y         g2_1   
5 g2    x         g2_2   
6 g2    y         g2_2   
7 g3    x         g3_1   
8 g3    y         g3_1

in base R:

transform(df, counter = ave(group, group, numbering, 
                             FUN = \(x)paste(x, seq_along(x), sep='_')))
  group numbering counter
1    g1         x    g1_1
2    g1         y    g1_1
3    g2         x    g2_1
4    g2         y    g2_1
5    g2         x    g2_2
6    g2         y    g2_2
7    g3         x    g3_1
8    g3         y    g3_1

CodePudding user response：

We could use gl to create the index - which doesn't have to depend on the 'numbering' column and should always work irrespective of the size of the group

library(dplyr)
library(stringr)
df1 %>% 
 group_by(group) %>% 
 mutate(counter = str_c(group, as.integer(gl(n(), 2, n())),
      sep = "_")) %>% 
 ungroup

-output

# A tibble: 8 × 3
  group numbering counter
  <chr> <chr>     <chr>  
1 g1    x         g1_1   
2 g1    y         g1_1   
3 g2    x         g2_1   
4 g2    y         g2_1   
5 g2    x         g2_2   
6 g2    y         g2_2   
7 g3    x         g3_1   
8 g3    y         g3_1

Or if it is based on the grouping with 'numbering

df1 %>% 
  add_count(group, numbering, name = 'counter') %>% 
  mutate(counter = str_c(group, '_', counter))