Блог

У меня есть набор данных следующим образом:

 DT <- structure(list(year = structure(c(1993, 1993, 1993, 1997, 1997, 
1997, 1999, 1999, 1999, 2003, 2003, 2005, 2005, 2005, 2009, 2009, 
2009, 2011, 2011, 2011, 2015, 2015, 2017, 2017, 2017), comment = "year"), 
    State = structure(c("Kansas", "Kansas", "Kansas", "Kansas", 
    "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", 
    "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", 
    "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", 
    "Kansas", "Kansas", "Kansas"), class = "AsIs", comment = "state"), 
    State_Abbr = structure(c("KS", "KS", "KS", "KS", "KS", "KS", 
    "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS", 
    "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS"), class = "AsIs", comment = "state_po"), 
    Party = structure(c("republican", "democrat", "Other", "republican", 
    "democrat", "Other", "republican", "democrat", "Other", "republican", 
    "Other", "democrat", "republican", "Other", "republican", 
    "democrat", "Other", "democrat", "republican", "Other", "republican", 
    "Other", "democrat", "republican", "Other"), class = "AsIs", comment = "party"), 
    Percentage = structure(c(0.626967802302283, 0.310289787269175, 
    0.0627424104285421, 0.620238525135418, 0.344369476385061, 
    0.035391998479521, 0.652661584410013, 0.315878201849193, 
    0.0314602137407939, 0.825223659651155, 0.174776340348845, 
    0.274872411697912, 0.691627798218281, 0.033499790083807, 
    0.600583964516102, 0.364584658335329, 0.0348313771485682, 
    0.263785496339944, 0.70094378363408, 0.0352707200259761, 
    0.531464769317622, 0.468535230682378, 0.322381278217064, 
    0.621752543886607, 0.0558661778963293), comment = "totalvotes"), 
    cyear = structure(c(1992L, 1992L, 1992L, 1996L, 1996L, 1996L, 
    1998L, 1998L, 1998L, 2002L, 2002L, 2004L, 2004L, 2004L, 2008L, 
    2008L, 2008L, 2010L, 2010L, 2010L, 2014L, 2014L, 2016L, 2016L, 
    2016L), comment = "year")), row.names = c(NA, -25L), class = c("data.table", 
"data.frame"))
  

Небольшая часть данных показана ниже:

Я хотел бы создать новый столбец с номером строки, если разница между годом по группе больше 2. Это будет строка 4, 5 и 6 на картинке.

 DT <- setDT(DT)[year-shift(year)>2, newcolumn := 1 , by=c("State", "Party")]
  

Который я прочитал как: если год в строке более чем на два больше, чем год в предыдущей строке (из той же группы by=c("State", "Party") , новый столбец получит значение 1.

Однако каким-то образом он не создает единицу для каждой стороны, а только по годам, и результат выглядит следующим образом:

 DT_new <- structure(list(year = structure(c(1993, 1993, 1993, 1997, 1997, 
1997, 1999, 1999, 1999, 2003, 2003, 2005, 2005, 2005, 2009, 2009, 
2009, 2011, 2011, 2011, 2015, 2015, 2017, 2017, 2017), comment = "year"), 
    State = structure(c("Kansas", "Kansas", "Kansas", "Kansas", 
    "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", 
    "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", 
    "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", "Kansas", 
    "Kansas", "Kansas", "Kansas"), class = "AsIs", comment = "state"), 
    State_Abbr = structure(c("KS", "KS", "KS", "KS", "KS", "KS", 
    "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS", 
    "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS", "KS"), class = "AsIs", comment = "state_po"), 
    Party = structure(c("republican", "democrat", "Other", "republican", 
    "democrat", "Other", "republican", "democrat", "Other", "republican", 
    "Other", "democrat", "republican", "Other", "republican", 
    "democrat", "Other", "democrat", "republican", "Other", "republican", 
    "Other", "democrat", "republican", "Other"), class = "AsIs", comment = "party"), 
    Percentage = structure(c(0.626967802302283, 0.310289787269175, 
    0.0627424104285421, 0.620238525135418, 0.344369476385061, 
    0.035391998479521, 0.652661584410013, 0.315878201849193, 
    0.0314602137407939, 0.825223659651155, 0.174776340348845, 
    0.274872411697912, 0.691627798218281, 0.033499790083807, 
    0.600583964516102, 0.364584658335329, 0.0348313771485682, 
    0.263785496339944, 0.70094378363408, 0.0352707200259761, 
    0.531464769317622, 0.468535230682378, 0.322381278217064, 
    0.621752543886607, 0.0558661778963293), comment = "totalvotes"), 
    cyear = structure(c(1992L, 1992L, 1992L, 1996L, 1996L, 1996L, 
    1998L, 1998L, 1998L, 2002L, 2002L, 2004L, 2004L, 2004L, 2008L, 
    2008L, 2008L, 2010L, 2010L, 2010L, 2014L, 2014L, 2016L, 2016L, 
    2016L), comment = "year"), newcolum = c(NA, NA, NA, 1, NA, 
    NA, NA, NA, NA, 1, NA, NA, NA, NA, 1, NA, NA, NA, NA, NA, 
    1, NA, NA, NA, NA)), row.names = c(NA, -25L), class = c("data.table", 
"data.frame"))
  

Что я делаю не так?

Вы можете попробовать проверить условие в j :

 library(data.table)

DT[, newcolumn := as.integer(year - shift(year) > 2), .(State, Party)]
  

Работает ли это:

 > library(dplyr)
> DT %>% group_by(State, Party) %>% mutate(newcol = case_when(
                                                        cyear - lag(cyear) > 2 ~ 1, 
                                                        TRUE ~ 0))
# A tibble: 25 x 7
# Groups:   State, Party [3]
    year State    State_Abbr Party      Percentage cyear newcol
   <dbl> <I<chr>> <I<chr>>   <I<chr>>        <dbl> <int>  <dbl>
 1  1993 Kansas   KS         republican     0.627   1992      0
 2  1993 Kansas   KS         democrat       0.310   1992      0
 3  1993 Kansas   KS         Other          0.0627  1992      0
 4  1997 Kansas   KS         republican     0.620   1996      1
 5  1997 Kansas   KS         democrat       0.344   1996      1
 6  1997 Kansas   KS         Other          0.0354  1996      1
 7  1999 Kansas   KS         republican     0.653   1998      0
 8  1999 Kansas   KS         democrat       0.316   1998      0
 9  1999 Kansas   KS         Other          0.0315  1998      0
10  2003 Kansas   KS         republican     0.825   2002      1
# ... with 15 more rows
>