How to use dplyr to look up a calculated value in the same column one row above?-CodePudding

In the reproducible code below, with dplyr I'm trying to create a dataframe column nameCntSpltGrp that, by Excel analogy, looks at its own calculated value in the row above in deriving the value for the current cell. However the calculation for that column doesn't work in the reproducible code and is commented out. How can I fix this, so each cell in that nameCntSpltGrp column looks at the calculated value one row above?

Illustrated below is how the calculation correctly works in XLS for nameCntSpltGrp :

Reproducible code:

library(dplyr)

myData <- 
  data.frame(
    Name = c("R","R","T","R","N","N"),
    Group = c(0,0,0,0,1,1)
  )

myDataFill <- myData %>%
  group_by(Name, Group) %>% 
    mutate(nameCnt = row_number()) %>% 
    mutate(subGrpRank = ifelse(Group > 0,row_number(),0))%>% 
    mutate(nameCntSplt = ifelse(subGrpRank == 1, nameCnt,0)) %>% 
  ungroup() # %>%
  
  # mutate(nameCntSpltGrp = ifelse(nameCntSplt > 0, nameCntSplt,
  #                                  ifelse(Name == lag(Name),lag(nameCntSpltGrp),0)))

myDataFill
print.data.frame(myDataFill)

CodePudding user response：

this is the closest I come to your excel example, I hope it helps:

myDataFill <- myData %>%
  group_by(Name, Group) %>% 
  mutate(nameCnt = row_number()) %>% 
  mutate(subGrpRank = ifelse(Group > 0,row_number(),0))%>% 
  mutate(nameCntSplt = ifelse(subGrpRank == 1, nameCnt,0)) %>% 
  ungroup() %>% 
  mutate(nameCntSpltGrp = nameCntSplt) %>% 
  mutate(nameCntSpltGrp = ifelse(nameCntSplt > 0, 
                                 nameCntSplt,
                                 ifelse(Group == lag(Group), 
                                       lag(nameCntSpltGrp), 0)))