How to identify and rename values with same IDs in a column in R

Rabin KC 0
Viewed 27

I have a dataset that looks like this:

set.seed(42)
x <- c("a","a","a","a","b","b","b","b","c","c","c","c","a","a","a","a")
r <- c(1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4)
y <- c(round(rnorm(16,77,24)))
df <- as.data.frame(cbind(x,r,y))
df 

   x r   y
1  a 1 110
2  a 2  63
3  a 3  86
4  a 4  92
5  b 1  87
6  b 2  74
7  b 3 113
8  b 4  75
9  c 1 125
10 c 2  75
11 c 3 108
12 c 4 132
13 a 1  44
14 a 2  70
15 a 3  74
16 a 4  92

Each xs are pigs with observations taken 4 times. But "a" was named twice for a different pig, so I now have different pigs with the same name "a".

The resulting dataset I hope to create looks like this::

df_clean
   x x_1 r   y
1  a   a 1  70
2  a   a 2  13
3  a   a 3  18
4  a   a 4 109
5  b   b 1  70
6  b   b 2  34
7  b   b 3  73
8  b   b 4 106
9  c   c 1 122
10 c   c 2  67
11 c   c 3  71
12 c   c 4  35
13 a  a1 1  88
14 a  a1 2  62
15 a  a1 3  88
16 a  a1 4  94

An advice on how this might be done would be very helpful.

Thank you!

rename dplyr duplicates dataframe r
2 Answers

Using rleid from data.table we can do:

library(dplyr)
library(data.table)

df %>% 
  mutate(x_1 = rleid(x)) %>%
  group_by(x) %>%
  mutate(x_1 = if_else(rleid(x_1) == 1, x, paste0(x, rleid(x_1) - 1)))
#> # A tibble: 16 x 4
#> # Groups:   x [3]
#>    x     r     y     x_1  
#>    <chr> <chr> <chr> <chr>
#>  1 a     1     110   a    
#>  2 a     2     63    a    
#>  3 a     3     86    a    
#>  4 a     4     92    a    
#>  5 b     1     87    b    
#>  6 b     2     74    b    
#>  7 b     3     113   b    
#>  8 b     4     75    b    
#>  9 c     1     125   c    
#> 10 c     2     75    c    
#> 11 c     3     108   c    
#> 12 c     4     132   c    
#> 13 a     1     44    a1   
#> 14 a     2     70    a1   
#> 15 a     3     74    a1   
#> 16 a     4     92    a1

Created on 2022-09-23 with reprex v2.0.2

Allan Cameron0

A variation on the theme, this only works if the observations are strictly in groups of 4:

set.seed(42)
x <- c("a","a","a","a","b","b","b","b","c","c","c","c","a","a","a","a")
r <- c(1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4)
y <- c(round(rnorm(16,77,24)))
df <- as.data.frame(cbind(x,r,y))

library(dplyr)
library(tibble)

df1 <- 
  df|>
  group_by(x)|>
  mutate(x_1 = ifelse(row_number() %/% 5 == 0, x, paste0(x, row_number() %/% 5)))




df1
#> # A tibble: 16 x 4
#> # Groups:   x [3]
#>    x     r     y     x_1  
#>    <chr> <chr> <chr> <chr>
#>  1 a     1     110   a    
#>  2 a     2     63    a    
#>  3 a     3     86    a    
#>  4 a     4     92    a    
#>  5 b     1     87    b    
#>  6 b     2     74    b    
#>  7 b     3     113   b    
#>  8 b     4     75    b    
#>  9 c     1     125   c    
#> 10 c     2     75    c    
#> 11 c     3     108   c    
#> 12 c     4     132   c    
#> 13 a     1     44    a1   
#> 14 a     2     70    a1   
#> 15 a     3     74    a1   
#> 16 a     4     92    a1

Created on 2022-09-23 by the reprex package (v2.0.1)

usernx6t0
Related