Incremental increase in row values until change in adjacent column found

user_12042094 0
Viewed 224

I need to breakdown survey data to allow for duplicate answers.

Existing dataset:

       initial_value
row1        1
row2        1
row3        1
row4        2
row5        3
row6        3

And I wish to create a duplicate column with 0.1 incremental increases until a change in value is found:

       initial_value    desired_value
row1        1                1.1
row2        1                1.2
row3        1                1.3 
row4        2                2.1
row5        3                3.1
row6        3                3.2
row7        3                3.3
tidyverse dataframe r
4 Answers

An option would be to use seq after grouping by the 'intial_value' in base R

df1$desired_value <-  with(df1, ave(initial_value, initial_value, 
    FUN = function(x) seq(x[1] + 0.1, length.out = length(x), by = .1)))

Or using paste with rowid from data.table

library(data.table)
setDT(df1)[, desired_value := as.integer(paste0(intial_value, ".", 
       rowid(initial_value)))]

data

df1 <- structure(list(initial_value = c(1L, 1L, 1L, 2L, 3L, 3L, 3L)),
class = "data.frame", row.names = c("row1", 
"row2", "row3", "row4", "row5", "row6", "row7"))
user_37322710

Here is another base R option using ave

within(df,desired_value <- initial_value+0.1*ave(1:nrow(df),initial_value,FUN = seq_along))

which gives

     initial_value desired_value
row1             1           1.1
row2             1           1.2
row3             1           1.3
row4             2           2.1
row5             3           3.1
row6             3           3.2
row7             3           3.3
user_121587570

Another data.table solution using grouping and .N:

library(data.table) 

setDT(df1)[, desired_value := initial_value + seq_len(.N) * 0.1, .(initial_value)][]

#>    initial_value desired_value
#> 1:             1           1.1
#> 2:             1           1.2
#> 3:             1           1.3
#> 4:             2           2.1
#> 5:             3           3.1
#> 6:             3           3.2
#> 7:             3           3.3

dplyr solution can be simpler as well:

library(dplyr)

df1 %>% 
  group_by(initial_value) %>% 
  mutate(desired_value = initial_value + row_number() * 0.1)
user_64614620

With this solution you are flexible even if you have more than 10 lines per identifier.

library(dplyr)

df <- data.frame(initial_value = c(1,1,1,2,3,3,3,rep(4, 20)))

df %>%
    group_by(initial_value) %>% 
    mutate(desired_value = initial_value + row_number() / 10^(ceiling(log10(n()+0.1)))) %>% 
    ungroup()
user_99182650
Related