R data.table: (dynamic) perspective cross-node

Question

I was wondering if there is an option for the CJ () method in data.table to transfer vectors formed using the evaluated condition, and not to perform a full cross join.

Data

library(data.table)
df<-data.table(
  ID=c(18L,  18L,  18L,  46L,  74L,  74L, 165L, 165L), 
  cat=c(1300L, 1320L, 1325L, 1300L, 1300L, 1325L, 1300L, 1325L),
  low=c(24.625, 16.250, 14.500, 43.625, 58.250, 45.375, 90.750, 77.875),
  high=c(26.625, 17.500, 15.500, 45.625, 60.000, 47.375, 92.750, 79.875)
  )

df
    ID  cat    low   high
1:  18 1300 24.625 26.625
2:  18 1320 16.250 17.500
3:  18 1325 14.500 15.500
4:  46 1300 43.625 45.625
5:  74 1300 58.250 60.000
6:  74 1325 45.375 47.375
7: 165 1300 90.750 92.750
8: 165 1325 77.875 79.875

Here I have a total of 8 observations of 4 different objects (ID 18, 46, 74 and 165). Each element is recorded in several categories (cat 1300, 1320, 1325) and two measurements are performed (low and high).

Desired Result

Now I want to create a table that for each element (ID) combines the low value of each category (cat) with all the high values ​​of the categories, which are larger in cross-connection. So my desired result looks like

    ID  cat  cat_large    low   high
1:  18 1300      1320  24.625 17.500
2:  18 1300      1325  24.625 15.500
3:  18 1320      1325  16.250 15.500
4:  74 1300      1325  58.250 47.375
5: 165 1300      1325  90.750 79.875

cat_high, , /.

, df[,CJ(low=low,high=high),by=.(ID)], . /.

Answer 1

:

df[, c(
  CJ(cat = cat, lcat = cat, sorted = FALSE),
  CJ(low = low, high = high, sorted = FALSE)  
), by=ID][lcat > cat]

    ID  cat lcat    low   high
1:  18 1300 1320 24.625 17.500
2:  18 1300 1325 24.625 15.500
3:  18 1320 1325 16.250 15.500
4:  74 1300 1325 58.250 47.375
5: 165 1300 1325 90.750 79.875

Answer 2

, .EACHI equi. i., , :

df[, c(.SD,.(larger_cat=cat))][
  df, on=.(ID==ID, cat > cat), .(larger_cat, low=i.low, high), by=.EACHI, nomatch=0
]

#    ID  cat larger_cat    low   high
#1:  18 1300       1320 24.625 17.500
#2:  18 1300       1325 24.625 15.500
#3:  18 1320       1325 16.250 15.500
#4:  74 1300       1325 58.250 47.375
#5: 165 1300       1325 90.750 79.875

Answer 3

dplyr, , .

library(dplyr)
library(tidyr)

df2 <- df %>%
  group_by(ID) %>%
  complete(low, high) %>%
  mutate(cat_large = cat) %>%
  group_by(ID, low) %>%
  mutate(cat = na.omit(cat)) %>%
  group_by(ID, high) %>%
  mutate(cat_large = na.omit(cat_large)) %>%
  filter(low > high) %>%
  arrange(ID, desc(low), desc(high)) %>%
  select(ID, cat, cat_large, low, high)
df2
# A tibble: 5 x 5
# Groups:   ID, high [4]
     ID   cat cat_large    low   high
  <int> <int>     <int>  <dbl>  <dbl>
1    18  1300      1320 24.625 17.500
2    18  1300      1325 24.625 15.500
3    18  1320      1325 16.250 15.500
4    74  1300      1325 58.250 47.375
5   165  1300      1325 90.750 79.875