CODEWITHSUNDEEP
r
velvetrock
velvetrock

Reputation: 585

Calculate the advancement day by day of a variable's value

Here is my data:

ID       Day       number.of.day
ID1      Day1          5
ID1      Day1          5
ID1      Day1          5
ID1      Day1          5
ID1      Day1          5
ID1      Day2          4
ID1      Day2          4
ID1      Day2          4
ID1      Day2          4
ID1      Day3          1
ID1      Day4          1
ID2      Day1          2
ID2      Day1          2
ID2      Day2          3
ID2      Day2          3
ID2      Day2          3

Update:

I want to calculate the advancement day by day of the number.of.day per ID, here is the expected result:

ID       Day       number.of.day      advance
ID1      Day1          5                NA
ID1      Day1          5                NA
ID1      Day1          5                NA
ID1      Day1          5                NA
ID1      Day1          5                NA
ID1      Day2          4                (4-5)/5
ID1      Day2          4                NA
ID1      Day2          4                NA
ID1      Day2          4                NA
ID1      Day3          1                (1-4)/4
ID1      Day4          1                (1-1)/1
ID2      Day1          2                NA
ID2      Day1          2                NA
ID2      Day2          3                (3-2)/2
ID2      Day2          3                NA
ID2      Day2          3                NA

Hope to get your reply!

Upvotes: 2

Views: 60

Answers (3)

David Arenburg
David Arenburg

Reputation: 92282

Here's a simple and efficient solution using data.table

library(data.table)
setDT(df)[!duplicated(df), advance := c(NA, diff(number.of.day)/number.of.day[-.N])]
#      ID  Day number.of.day advance
#  1: ID1 Day1             5      NA
#  2: ID1 Day1             5      NA
#  3: ID1 Day1             5      NA
#  4: ID1 Day1             5      NA
#  5: ID1 Day1             5      NA
#  6: ID1 Day2             4   -0.20
#  7: ID1 Day2             4      NA
#  8: ID1 Day2             4      NA
#  9: ID1 Day2             4      NA
# 10: ID1 Day3             1   -0.75
# 11: ID1 Day4             1    0.00
# 12: ID2 Day1             2    1.00
# 13: ID2 Day1             2      NA
# 14: ID2 Day2             3    0.50
# 15: ID2 Day2             3      NA
# 16: ID2 Day2             3      NA

Upvotes: 2

RHertel
RHertel

Reputation: 23788

Here's another suggestion that is quite simple and that uses only base R:

new_day <- which(diff(as.numeric(df$Day))>0)
day_change <- c(diff(df$number.of.day),0)
res <- day_change/df$number.of.day
temp <- res[new_day]
res[res==0] <- NA
res[new_day] <- temp
res <- c(NA,res[-length(res)])
df <- cbind(df,res)
#> df
#    ID  Day number.of.day   res
#1  ID1 Day1             5    NA
#2  ID1 Day1             5    NA
#3  ID1 Day1             5    NA
#4  ID1 Day1             5    NA
#5  ID1 Day1             5    NA
#6  ID1 Day2             4 -0.20
#7  ID1 Day2             4    NA
#8  ID1 Day2             4    NA
#9  ID1 Day2             4    NA
#10 ID1 Day3             1 -0.75
#11 ID1 Day4             1  0.00
#12 ID2 Day1             2  1.00
#13 ID2 Day1             2    NA
#14 ID2 Day2             3  0.50
#15 ID2 Day2             3    NA
#16 ID2 Day2             3    NA

Upvotes: 2

Pierre L
Pierre L

Reputation: 28441

library(dplyr)    
newdf <- df %>% group_by(ID) %>% 
mutate(advance = c(NA, head((lead(number.of.day)-number.of.day)/number.of.day, -1)), 
       diff = c(0, diff(as.numeric(Day))))
is.na(newdf$advance) <- newdf$diff == 0L
newdf[,-5]
# Source: local data frame [16 x 4]
# Groups: ID
# 
#     ID  Day number.of.day advance
# 1  ID1 Day1             5      NA
# 2  ID1 Day1             5      NA
# 3  ID1 Day1             5      NA
# 4  ID1 Day1             5      NA
# 5  ID1 Day1             5      NA
# 6  ID1 Day2             4   -0.20
# 7  ID1 Day2             4      NA
# 8  ID1 Day2             4      NA
# 9  ID1 Day2             4      NA
# 10 ID1 Day3             1   -0.75
# 11 ID1 Day4             1    0.00
# 12 ID2 Day1             2      NA
# 13 ID2 Day1             2      NA
# 14 ID2 Day2             3    0.50
# 15 ID2 Day2             3      NA
# 16 ID2 Day2             3      NA

Upvotes: 2

Related Questions