R adding a new column in a data frame by recursivly filling 1:3 value based on another column

user_6023565 0
Viewed 58

I have a dataframe, e.g.:

df <- data.frame(Group = rep(c("A", "B", "C"),  times = c(4, 4, 6))
> df
   Group
1      A
2      A
3      B
4      B
5      B
6      B
7      C
8      C
9      C
10     C
11     C
12     C

I want to add a new column Type by recursively filling value 1:3 based on the Group column.

The resulting DF is like

> df2
   Group Type
1      A    1
2      A    2
3      B    1
4      B    2
5      B    3
6      B    1
7      C    1
8      C    2
9      C    3
10     C    1
11     C    2
12     C    3

I don't know how to achieve this. Thanks a lot for your help

dplyr dataframe r
5 Answers

You could just create a sequence of values 1:3 that repeats lots of times and then subset that within each group (updated based on suggestion in comments:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
  df <- data.frame(Group = rep(c("A", "B", "C"),  
                               times = c(4, 4, 6)))
  df <- df %>% 
    group_by(Group) %>% 
    mutate(Type = rep(1:3, length=n()))
  df
#> # A tibble: 14 × 2
#> # Groups:   Group [3]
#>    Group  Type
#>    <chr> <int>
#>  1 A         1
#>  2 A         2
#>  3 A         3
#>  4 A         1
#>  5 B         1
#>  6 B         2
#>  7 B         3
#>  8 B         1
#>  9 C         1
#> 10 C         2
#> 11 C         3
#> 12 C         1
#> 13 C         2
#> 14 C         3

Created on 2022-02-13 by the reprex package (v2.0.1)

user_82064340

You could also use %% remainder:

library(dplyr)

df %>%
  group_by(Group) %>%
  mutate(Type = (row_number() - 1) %% 3 + 1)

Output

   Group  Type
   <chr> <dbl>
 1 A         1
 2 A         2
 3 A         3
 4 A         1
 5 B         1
 6 B         2
 7 B         3
 8 B         1
 9 C         1
10 C         2
11 C         3
12 C         1
13 C         2
14 C         3
user_34606700

Using rowid from data.table

library(data.table)
setDT(df)[, Type := rowid(gl(.N, 3, .N)), Group]

-output

> df
     Group  Type
    <char> <int>
 1:      A     1
 2:      A     2
 3:      A     3
 4:      A     1
 5:      B     1
 6:      B     2
 7:      B     3
 8:      B     1
 9:      C     1
10:      C     2
11:      C     3
12:      C     1
13:      C     2
14:      C     3
user_37322710

Here is another dplyr solution using ifelse:

library(dplyr)

df %>% 
  group_by(Group) %>%
  mutate(Type = 1:n(),
         Type = ifelse(Type>=4, Type-3, Type))

Group  Type
   <chr> <dbl>
 1 A         1
 2 A         2
 3 B         1
 4 B         2
 5 B         3
 6 B         1
 7 C         1
 8 C         2
 9 C         3
10 C         1
11 C         2
12 C         3

data:

structure(list(Group = c("A", "A", "B", "B", "B", "B", "C", "C", 
"C", "C", "C", "C")), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"))
user_133216470

A base R approach:

df$Type <- ave(df$Group, df$Group, FUN = \(x) rep_len(1:3, length(x)))

df

#>    Group Type
#> 1      A    1
#> 2      A    2
#> 3      A    3
#> 4      A    1
#> 5      B    1
#> 6      B    2
#> 7      B    3
#> 8      B    1
#> 9      C    1
#> 10     C    2
#> 11     C    3
#> 12     C    1
#> 13     C    2
#> 14     C    3

Or using dplyr:

library(dplyr)

df %>% 
  mutate(Type = ave(Group, Group, FUN = \(x) rep_len(1:3, length(x))))

#>    Group Type
#> 1      A    1
#> 2      A    2
#> 3      A    3
#> 4      A    1
#> 5      B    1
#> 6      B    2
#> 7      B    3
#> 8      B    1
#> 9      C    1
#> 10     C    2
#> 11     C    3
#> 12     C    1
#> 13     C    2
#> 14     C    3
user_115644870
Related