add missing rows to a data table

Question

I have a data table:

library(data.table)
(f <- data.table(id1=c(1,2,3,1,2,3),
                 id2=as.factor(c("a","a","b","c","b","d")),
                 v=1:6,
                 key=c("id1","id2")))
   id1 id2 v
1:   1   a 1
2:   1   c 4
3:   2   a 2
4:   2   b 5
5:   3   b 3
6:   3   d 6
> str(f)
Classes ‘data.table’ and 'data.frame':  6 obs. of  3 variables:
 $ id1: num  1 1 2 2 3 3
 $ id2: Factor w/ 4 levels "a","b","c","d": 1 3 1 2 2 4
 $ v  : int  1 4 2 5 3 6
 - attr(*, "sorted")= chr  "id1" "id2"
 - attr(*, ".internal.selfref")=<externalptr>

How do I add the "missing" rows?

I.e., for each existing id1 I want all possible values of id2 to be present (with v=0).

So, I need to add 6 rows (3 possible values of id1 * 4 possible values of id2 - 6 existing rows).

Frank · Accepted Answer · 2017-08-01 20:10:30Z

21

I'd get the unique values in id1 and id2 and do a join using data.table's cross join function CJ as follows:

# if you've already set the key:
ans <- f[CJ(id1, id2, unique=TRUE)][is.na(v), v := 0L][]

# or, if f is not keyed:
ans <- f[CJ(id1 = id1, id2 = id2, unique=TRUE), on=.(id1, id2)][is.na(v), v := 0L][]

ans

edited Aug 1, 2017 at 20:10

Frank

66.9k8 gold badges104 silver badges190 bronze badges

answered Mar 17, 2014 at 18:49

Arun

119k28 gold badges290 silver badges396 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jake Burkhead · Accepted Answer · 2014-03-17 18:55:30Z

2

f[, {
  tab = table(id2)
  x = as.numeric(tab)
  x[x != 0] = v
  list(id2 = names(tab), v = x)
}, by = id1]

##    id1 id2 v
##  1:   1   a 1
##  2:   1   b 0
##  3:   1   c 4
##  4:   1   d 0
##  5:   2   a 2
##  6:   2   b 5
##  7:   2   c 0
##  8:   2   d 0
##  9:   3   a 0
## 10:   3   b 3
## 11:   3   c 0
## 12:   3   d 6

edited Mar 17, 2014 at 18:55

answered Mar 17, 2014 at 18:36

Jake Burkhead

6,5452 gold badges24 silver badges32 bronze badges

Collectives™ on Stack Overflow

add missing rows to a data table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related