R :Looping through each 5 rows of data frame and imputing incremental value

Question

I am trying to impute incremental values for each 5 rows of the data frame. I am new to R and not sure how to achieve this.

Input data:

state Value 
  a    1
  b    2
  a    3
  c    4
  a    5
  e    6
  f    7
  w    8
  f    9
  s    10
  e    11
  r    12
  s    13
  s    14

Desired Output:

state Value Increment
  a    1     1
  b    2     1
  a    3     1
  c    4     1
  a    5     1
  e    6     2
  f    7     2
  w    8     2
  f    9     2
  s    10    2
  e    11    3
  r    12    3
  s    13    3
  s    14    3

Possible duplicate of [R - Make a repetitive sequence with 'rep'](https://stackoverflow.com/questions/49557890/r-make-a-repetitive-sequence-with-rep) — Ferroao, Dec 10 '18 at 17:36
I get that it's an unclear question, but maybe whoever has downvoted all 5 answers so far could also explain what's wrong with the question and/or answers. For the OP: it's helpful to at least give a clear sense of the logic of what you're trying to do, and include the code you've tried so far — camille, Dec 10 '18 at 18:06

AntoniosK · Answer 1 · 2018-12-10T17:58:26.210

Here's a dplyr solution that checks whether the remainder of dividing the row number minus one with 5 is 0. If it is 0 it increases the value of the new column by 1.

dt = read.table(text = 
"state Value 
a    1
b    2
a    3
c    4
a    5
e    6
f    7
w    8
f    9
s    10
e    11
r    12
s    13
s    14", header=T)

library(dplyr)

dt %>% mutate(Increment = cumsum((row_number()-1) %% 5 == 0))

#    state Value Increment
# 1      a     1         1
# 2      b     2         1
# 3      a     3         1
# 4      c     4         1
# 5      a     5         1
# 6      e     6         2
# 7      f     7         2
# 8      w     8         2
# 9      f     9         2
# 10     s    10         2
# 11     e    11         3
# 12     r    12         3
# 13     s    13         3
# 14     s    14         3

Taher A. Ghaleb · Accepted Answer · 2018-12-10T18:08:36.647

This is your data:

df = read.table(text = 
                "state Value 
                     a     1
                     b     2
                     a     3
                     c     4
                     a     5
                     e     6
                     f     7
                     w     8
                     f     9
                     s     10
                     e     11
                     r     12
                     s     13
                     s     14", 
                header=T)

You can now use rownames to help you to impute the incremental values. The line of code below gives you your desired output, by taking the row indices, dividing them by 5 and then obtaining the ceiling (i.e., the closest greater integer).

df$Increment <- ceiling(as.numeric(rownames(df))/5)

Which will give you your expected output:

#    state Value Increment
# 1      a     1         1
# 2      b     2         1
# 3      a     3         1
# 4      c     4         1
# 5      a     5         1
# 6      e     6         2
# 7      f     7         2
# 8      w     8         2
# 9      f     9         2
# 10     s    10         2
# 11     e    11         3
# 12     r    12         3
# 13     s    13         3
# 14     s    14         3

Hope it helps.

Ferroao · Answer 3 · 2018-12-10T23:38:58.360

1

try:

dt = read.table(text = 
                  "state Value 
a    1
b    2
a    3
c    4
a    5
e    6
f    7
w    8
f    9
s    10
e    11
r    12
s    13
s    14", header=T)

dt$Increment<- unlist(lapply(1:ceiling(nrow(dt)/5), function(x) rep(x, 5) ))[1:nrow(dt)]
dt

edited Dec 10 '18 at 23:38

answered Dec 10 '18 at 17:38

Ferroao

3,042
28
53

does not give desired output – User7598 Dec 10 '18 at 18:15

score 1 · Answer 4 · answered Dec 10 '18 at 17:47

The following function will do what you want.
Arguments:

DF - the input data.frame;
N- the number of repeats of each value in the increment;
newcol - the name of the increment column, defaults to "Increment".

Just assign the result to the new df.

fun <- function(DF, N, newcol = "Increment"){
  n <- nrow(DF)
  f <- rep_len(c(1, rep(0, N - 1)), length.out = n)
  DF[[newcol]] <- cumsum(f)
  DF
}

fun(df1, N = 5)

Data.

set.seed(1234)    # Make the results reproducible
n <- 14
state <- sample(letters, n, TRUE)
Value <- seq_len(n)
df1 <- data.frame(state, Value)

score 1 · Answer 5 · answered Dec 10 '18 at 17:52

Try:

rep(c(1:((nrow(df)/5)+1)),
    each=5,
    length.out=dim(df)[1])

Which gives:

> df$Increment<-rep(c(1:((nrow(df)/5)+1)),
+     each=5,
+     length.out=dim(df)[1])
> df
   state Value Increment
1      a     1         1
2      b     2         1
3      a     3         1
4      c     4         1
5      a     5         1
6      e     6         2
7      f     7         2
8      w     8         2
9      f     9         2
10     s    10         2
11     e    11         3
12     r    12         3
13     s    13         3
14     s    14         3

Where df is:

dt = read.table(text = 
"state Value 
a    1
b    2
a    3
c    4
a    5
e    6
f    7
w    8
f    9
s    10
e    11
r    12
s    13
s    14", header=T)

R :Looping through each 5 rows of data frame and imputing incremental value

5 Answers5