R data table unique record count based on all combination of a given list of values from 2 columns

user_1616785 0
Viewed 209

I have a data.table in R as below

Col1          Col2         
Col1Value1    Col2Value1   
Col1Value1    Col2Value2
Col1Value1    Col2Value3
Col1Value2    Col2Value1   
Col1Value2    Col2Value3
Col1Value3    Col2Value1
Col1Value3    Col2Value2
Col1Value3    Col2Value3

I want to get the count of records for each combination between given values in Col1 - (Col1Value1,Col1Value2) against values in Col2 - Col1(Col2Value1,Col2Value2) and if no records for a combination then return 0

counts <- dt[, length(unique(Col2)), by=.(Col1, Col2)]

The above code returns all combinations, but

- A combination with 0 records are not returned
- Not able to restrict to a given list

Expected result

Col1           Col2        Count
Col1Value1     Col2Value1    1
Col1Value1     Col2Value2    1
Col1Value2     Col2Value1    1
Col1Value2     Col2Value2    0
data.table r
4 Answers
DT[CJ(Col1, Col2, unique = TRUE), on = .(Col1, Col2), .(count = .N), by = .EACHI]

#          Col1       Col2 count
# 1: Col1Value1 Col2Value1     1
# 2: Col1Value1 Col2Value2     1
# 3: Col1Value1 Col2Value3     1
# 4: Col1Value2 Col2Value1     1
# 5: Col1Value2 Col2Value2     0
# 6: Col1Value2 Col2Value3     1
# 7: Col1Value3 Col2Value1     1
# 8: Col1Value3 Col2Value2     1
# 9: Col1Value3 Col2Value3     1

Data

DT <- fread(
  "Col1          Col2         
  Col1Value1    Col2Value1   
  Col1Value1    Col2Value2
  Col1Value1    Col2Value3
  Col1Value2    Col2Value1   
  Col1Value2    Col2Value3
  Col1Value3    Col2Value1
  Col1Value3    Col2Value2
  Col1Value3    Col2Value3"
)

If you want to limit combinations then you could filter beforehand as Harshal did using dplyr:

a <- c("Col1Value1", "Col1Value2")
b <- c("Col2Value1", "Col2Value2")
DT[Col1 %in% a & Col2 %in% b
   ][CJ(Col1, Col2, unique = TRUE), on = .(Col1, Col2), .(count = .N), by = .EACHI]
user_45522950

In base R, you can do: 

data.frame(table(dt))

        Var1       Var2 Freq
1 Col1Value1 Col2Value1    1
2 Col1Value2 Col2Value1    1
3 Col1Value3 Col2Value1    1
4 Col1Value1 Col2Value2    1
5 Col1Value2 Col2Value2    0
6 Col1Value3 Col2Value2    1
7 Col1Value1 Col2Value3    1
8 Col1Value2 Col2Value3    1
9 Col1Value3 Col2Value3    1
user_86845510

You can use table like so:

data.table(with(dt, table(Col1, Col2)))

         Col1       Col2 N
1: Col1Value1 Col2Value1 1
2: Col1Value2 Col2Value1 1
3: Col1Value3 Col2Value1 1
4: Col1Value1 Col2Value2 1
5: Col1Value2 Col2Value2 0
6: Col1Value3 Col2Value2 1
7: Col1Value1 Col2Value3 1
8: Col1Value2 Col2Value3 1
9: Col1Value3 Col2Value3 1

DATA

dt <- setDT(read.table(text="Col1          Col2         
                 Col1Value1    Col2Value1   
                 Col1Value1    Col2Value2
                 Col1Value1    Col2Value3
                 Col1Value2    Col2Value1   
                 Col1Value2    Col2Value3
                 Col1Value3    Col2Value1
                 Col1Value3    Col2Value2
                 Col1Value3    Col2Value3", header=TRUE,stringsAsFactors=FALSE) )
user_10424780

You can try below code:

a<-c("Col1Value1", "Col1Value2")
b<-c("Col2Value1", "Col2Value2")

df2<-df %>% select(Col1, Col2) %>% filter(Col1 %in% a) %>% filter(Col2 %in% b)  %>% group_by(Col1, Col2) %>% summarise(count = n()) %>% as.data.frame()

expand.grid(a,b) %>% left_join(df2, by = c("Var1"="Col1", "Var2"="Col2")) %>% mutate(count2 = ifelse(is.na(count), 0, count)) %>% select(-count)

Below is the output:

        Var1       Var2 count2
1 Col1Value1 Col2Value1      1
2 Col1Value2 Col2Value1      1
3 Col1Value1 Col2Value2      1
4 Col1Value2 Col2Value2      0
user_80073730
Related