Vfrdtyky

I am looking for solutions using data.table ― I have a data.table with the following columns:

data <- data.frame(GROUP=c(3,3,4,4,5,6),

                    YEAR=c(1979,1985,1999,2011,2012,1994),

                    NAME=c("S","A","J","L","G","A"))



data <- as.data.table(data)

Data.table:

GROUP  YEAR    NAME

3      1979    Smith 

3      1985    Anderson

4      1999    James

4      2011    Liam

5      2012    George

6      1994    Adams

For each group we want to select one row using the following rule:

• If there is a year > 2000, select the row with minimum year above 2000.


• If there not a year > 2000, select the row with the maximum year.

Desired output:

GROUP  YEAR    NAME

3      1985    Anderson

4      2011    Liam

5      2012    George

6      1994    Adams

Thanks! I have been struggling with this for a while.

data.table should be a lot simpler if you subset the special .I row counter:

library(data.table)

setDT(data)

data[

  data[

        ,

        if(any(YEAR > 2000)) 

           .I[which.min(2000 - YEAR)] else

           .I[which.max(YEAR)],

        by=GROUP

      ]$V1

]

#   GROUP YEAR NAME

#1:     3 1985    A

#2:     4 2011    L

#3:     5 2012    G

#4:     6 1994    A

Thanks to @r2evans for the background info -

.I is an integer vector equivalent to seq_len(nrow(x)).

Ref:
http://rdrr.io/cran/data.table/man/special-symbols.html

So, all I'm doing here is getting the matching row index for the whole of data for each of the calculations at each by= level. Then using these row indexes to subset data again.

You could also do a couple rolling joins:

res = unique(data[, .(GROUP)])



# get row with YEAR above 2000

res[, w := data[c(.SD, YEAR = 2000), on=.(GROUP, YEAR), roll=-Inf, which=TRUE]]



# if none found, get row with nearest YEAR below   

res[is.na(w), w := data[c(.SD, YEAR = 2000), on=.(GROUP, YEAR), roll=Inf, which=TRUE]]



# subset by row numbers

data[res$w]



   GROUP YEAR NAME

1:     3 1985    A

2:     4 2011    L

3:     5 2012    G

4:     6 1994    A

Using the dplyr package I got your output like this (though it may not be the simplest answer):

 library(dplyr)

 library(magrittr)



 data <- data.frame(GROUP=c(3,3,4,4,5,6),

                    YEAR=c(1979,1985,1999,2011,2012,1994),

                    NAME=c("S","A","J","L","G","A"))



 data %>%

   subset(YEAR < 2000) %>%

   group_by(GROUP) %>%

   summarise(MAX=max(YEAR)) %>%

   join(data %>%

          subset(YEAR > 2000) %>%

          group_by(GROUP) %>%

          summarise(MIN=min(YEAR)), type="full") %>%

   mutate(YEAR=ifelse(is.na(MIN), MAX, MIN)) %>%

   select(c(GROUP, YEAR)) %>%

   join(data)

Results:

   GROUP YEAR NAME

      3  1985   A

      4  2011   L

      5  2012   G

      6  1994   A

EDIT: Sorry, my first answer didn't take into account the min/max conditions. Hope this helps