Updating column based on previous row values

Question

I have this dataframe df1.

    User|Date|Index|
    a   |1   |1    |
    a   |1   |2    |
    a   |1   |3    |
    a   |1   |0    |
    a   |1   |5    |
    a   |1   |6    |
    a   |2   |0    |
    b   |4   |1    |
    b   |4   |2    |
    b   |4   |3    |

I want to update the Index column, in the following way:

Group the data by User, Date;
Assume the rows are correctly ordered;
Go through the column Index, when finding a 0 value, update it to 1, and correct the following lines, incrementing by 1 based on the previous line, until another 0 is found.

I've narrowed it down to this, but I'm not sure how complete the replace part to do what I want.

    df1 %>%
    group_by(User, Date) %>%
    mutate(Index = replace(Index,)

Can anybody help me?

EDIT: The dataframe above is a simplification. This is the code.

    df1 <-structure(list(User = c(2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3,3), 
    Date = c(16864, 16864, 16864, 16864, 16864, 16879, 16879,16879, 16879, 16879, 16879, 16879, 16879, 16879), 
    Index = c(16,17, 0, 19, 20, 1, 2, 3, 0, 5, 0, 0, 8, 9)), 
    class = "data.frame", .Names = c("User","Date", "Index"), row.names = c(NA, -14L))

This is the current look:

    User|Date    |Index|
    2   |16864   |16   |
    2   |16864   |17   |
    2   |16864   |0    |
    2   |16864   |19   |
    2   |16864   |20   |
    3   |16879   |1    |
    3   |16879   |2    |
    3   |16879   |3    |
    3   |16879   |0    |
    3   |16879   |5    |
    3   |16879   |0    |
    3   |16879   |0    |
    3   |16879   |8    |
    3   |16879   |9    |

The desired output is:

    User|Date    |Index|
    2   |16864   |16   |
    2   |16864   |17   |
    2   |16864   |1    |
    2   |16864   |2    |
    2   |16864   |3    |
    3   |16879   |1    |
    3   |16879   |2    |
    3   |16879   |3    |
    3   |16879   |1    |
    3   |16879   |2    |
    3   |16879   |1    |
    3   |16879   |1    |
    3   |16879   |2    |
    3   |16879   |3    |

Can you show expected output? Also please have a [look at this link](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and revise — Sotos, Jun 30 '16 at 14:41
Is there a reason to not simply replace the zeroes with 1s as a first step? If you are replacing all of them, I don't see why this needs to be done iteratively. — mkt, Jun 30 '16 at 15:03
No special reason for 0. I guess it could start already as 1. — JessicaJones, Jun 30 '16 at 15:43

David Arenburg · Accepted Answer · 2016-07-04T20:59:04.640

There is probably a smarter way to achieve this, but here's my attempt with a custom function

myfun <- function(x)  { 
  indx <- which(x == 0L)
  c(x[1L:(indx[1L] - 1L)], sequence(c(diff(indx), length(x) - last(indx) + 1L)))
}

df1 %>%
  group_by(User, Date) %>%
  mutate(Index = myfun(Index))

# Source: local data frame [14 x 3]
# Groups: User, Date [2]
#     User  Date Index
#    (dbl) (dbl) (dbl)
# 1      2 16864    16
# 2      2 16864    17
# 3      2 16864     1
# 4      2 16864     2
# 5      2 16864     3
# 6      3 16879     1
# 7      3 16879     2
# 8      3 16879     3
# 9      3 16879     1
# 10     3 16879     2
# 11     3 16879     1
# 12     3 16879     1
# 13     3 16879     2
# 14     3 16879     3

When I apply this function it to a similar data frame, it returns an error of incompatible size. Any idea why this might be? I've tried to use the debug, but I haven't been able to figure out why. Do you have any suggestion? — JessicaJones, Aug 12 '16 at 00:27

Updating column based on previous row values

1 Answers1