Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Group a data.table using a column which is list

Tags:

r

data.table

I have a really big problem and looping through the data.table to do what I want is too slow, so I am trying to get around looping. Let assume I have a data.table as follows:

a <- data.table(i = c(1,2,3), j = c(2,2,6), k = list(c("a","b"),c("a","c"),c("b")))

> a
  i j   k
1: 1 2 a,b
2: 2 2 a,c
3: 3 6   b

And I want to group based on the values in k. So something like this:

a[, sum(j), by = k]

right now I am getting the following error:

 Error in `[.data.table`(a, , sum(i), by = k) : 
 The items in the 'by' or 'keyby' list are length (2,2,1). Each must be same length as rows in x or number of rows returned by i (3).

The answer I am looking for is to group first all the rows having "a" in column k and calculate sum(j) and then all rows having "b" and so on. So the desired answer would be:

k V1 
a 4
b 8
c 2

Any hint how to do it efficiently? I cant melt the column K by repeating the rows since the size of the data.table would be too big for my case.

like image 847
newbie Avatar asked Jul 31 '16 15:07

newbie


People also ask

How do you group columns in a table?

Select the data (including any summary rows or columns). On the Data tab, in the Outline group, click Group > Group Rows or Group Columns. Optionally, if you want to outline an inner, nested group — select the rows or columns within the outlined data range, and repeat step 3.

Is data table DT == true?

data. table(DT) is TRUE. To better description, I put parts of my original code here. So you may understand where goes wrong.

What is grouping in tables?

Grouping allows the user to visually aggregate/group data together. When you're in the standard “Grid” layout, you can group on columns of type "Text", "Number", "Date", “Dropdown”, “Person”, and more!


3 Answers

I think this might work:

a[, .(k = unlist(k)), by=.(i,j)][,sum(j),by=k]

   k V1
1: a  4
2: b  8
3: c  2
like image 115
Mike H. Avatar answered Nov 02 '22 17:11

Mike H.


If we are using tidyr, a compact option would be

library(tidyr)
unnest(a, k)[, sum(j) ,k]
#   k V1
#1: a  4
#2: b  8
#3: c  2

Or using the dplyr/tidyr pipes

unnest(a, k) %>%
       group_by(k) %>%
       summarise(V1 = sum(j))
#     k    V1
#   <chr> <dbl>
#1     a     4
#2     b     8
#3     c     2
like image 37
akrun Avatar answered Nov 02 '22 16:11

akrun


Since by-group operations can be slow, I'd consider...

dat = a[rep(1:.N, lengths(k)), c(.SD, .(k = unlist(a$k))), .SDcols=setdiff(names(a), "k")]

   i j k
1: 1 2 a
2: 1 2 b
3: 2 2 a
4: 2 2 c
5: 3 6 b

We're repeating rows of cols i:j to match the unlisted k. The data should be kept in this format instead of using a list column, probably. From there, as in @MikeyMike's answer, we can dat[, sum(j), by=k].

In data.table 1.9.7+, we can similarly do

dat = a[, c(.SD[rep(.I, lengths(k))], .(k = unlist(k))), .SDcols=i:j]
like image 35
Frank Avatar answered Nov 02 '22 18:11

Frank