adlisval adlisval - 1 month ago 7
R Question

R compare current row to the next row (on same column)

I have something like:

ISBN Date Quantity
3457 2004 10
3457 2004 6
3457 2004 10
3457 2005 7
3457 2005 12
9885 2013 10
9885 2013 6
9855 2013 10
9885 2014 7
9885 2014 12


And I want to get:

ISBN Date Quantity Year
3457 2004 10 1st Year
3457 2004 6 1st Year
3457 2004 10 1st Year
3457 2005 7 2nd Year
3457 2005 12 2nd Year
9885 2013 10 1st Year
9885 2013 6 1st Year
9855 2013 10 1st Year
9885 2014 7 2nd Year
9885 2014 12 2nd Year


And I have this code:

df<-df %>% group_by(ISBN) %>% mutate(Year = ifelse(DateYear > DateYear,"1st Year","2nd Year"))


But I only get "2nd Year" everywhere, so I guess the comparison in
ifelse
is not actually comparing the rows in the column "Date". I guess I have to use a for loop, but was thinking that it's some other way in R. How couldI get to the result I need?

Answer

Use windowing logic:

library(dplyr)
library(readr)

df_foo = read.table(textConnection("ISBN   Date  Quantity
3457   2004  10
3457   2004  6
3457   2004  10
3457   2005  7
3457   2005  12
9885   2013  10
9885   2013  6
9855   2013  10
9885   2014  7
9885   2014  12"), header = TRUE, stringsAsFactors = FALSE)


df_foo %>% 
  group_by(ISBN) %>% 
  arrange(Date) %>% 
  mutate(
    ifelse(
    cumsum(Date != lag(Date, default = first(Date))),
    "2nd Year", "1st Year"
    )
  )