[datatable-help] Adding a column in place and join surprises

[David Kulp]

Suppose I have a data.table with a unique identifier and value and another data.table with a cross-reference to that identifier and lots of other measurement data.  E.g. suppose my lookup table is "locs" and my measurement data is "obsv" as follows:

obsv=data.table(id=1:7, loc=c(10,20,10,10,30,10,20), mvar=rnorm(7), key='id')
locs=data.table(loc=c(30,20,10),name=c("foo","bar","baz"), other=letters[1:3], key='loc')

I simply want to add the 'name' column from locs to the obsv table using :=.  But this quickly becomes really complicated because (1) the keys for the two data.tables differ (appropriately), (2) the key for locs is an integer, and (3) the return columns of a join always include the matching columns.

First of all, the gotcha is that locs[obsv[,loc]] doesn't work.  This is because obsv[,loc] returns a numeric column, which is treated as indexing the row numbers.  Surprise!  

> locs[obsv[,loc]] 
     loc name other
[1,]  NA <NA>  <NA>
[2,]  NA <NA>  <NA>
[3,]  NA <NA>  <NA>
[4,]  NA <NA>  <NA>
[5,]  NA <NA>  <NA>
[6,]  NA <NA>  <NA>
[7,]  NA <NA>  <NA>

This unexpected and silent behavior could easily cause very bad results.  (When I first did this test I used 1,2 and 3 instead of 10,20 and 30 and everything seemed to work!)  I think this inconsistency should be addressed.  For example, consider modifying joins so that they only happen when the i argument is a data.table or a list.  If it is a character, then it should fail.  Part of the problem here is the inconsistency that A[,col1] returns a vector of characters, but A[,list(col1,col2)] returns a data.table.  If instead, data.tables were always returned unless, say, a simplify=TRUE argument was provided, then we'd be in better shape because locs[obsv[,loc]] would always be a join and locs[obsv[,loc,simplify=TRUE]] would be a row retrieval as for data.frame.

Anyway, the solution to the above seems to be to create a list object for i:

> locs[list(obsv[,loc])]
Error in `[.data.table`(locs, list(obsv[, loc])) : 
  typeof x.loc (integer) != typeof i.V1 (double)

but that doesn't work because obsv$loc is class numeric and locs$loc is class integer.  This is because locs$loc is silently changed to integer when the key is set.  So, to perform a lookup we need to coerce to integer as follows:

> locs[list(as.integer(obsv[,loc]))]
     loc name other
[1,]   1  baz     c
[2,]   2  bar     b
[3,]   1  baz     c
[4,]   1  baz     c
[5,]   3  foo     a
[6,]   1  baz     c
[7,]   2  bar     b

But if I want a right-hand-side value for assignment, then I need just a single column.  However, there is an inconsistency: a join adds the extra join column names even if I specify only the column I want.

> locs[list(as.integer(obsv[,loc])),name]
     loc name
[1,]   1  baz
[2,]   2  bar
[3,]   1  baz
[4,]   1  baz
[5,]   3  foo
[6,]   1  baz
[7,]   2  bar

In my opinion, the j column should always return only the columns that the user requested.  If the user wants additional columns (possibly from the join) then they can be requested explicitly.  Anyway, the workaround for this is to make a chain statement:

> locs[list(as.integer(obsv[,loc]))][,name]
[1] "baz" "bar" "baz" "baz" "foo" "baz" "bar"

That looks good!  Now to assign using := I write

> obsv[,locname:=locs[list(as.integer(obsv[,loc]))][,name]]
     id loc       mvar locname
[1,]  1   1 -1.8721465     baz
[2,]  2   2 -0.5341284     bar
[3,]  3   1 -2.2243473     baz
[4,]  4   1 -0.3093871     baz
[5,]  5   3 -0.7879228     foo
[6,]  6   1 -0.5672113     baz
[7,]  7   2  1.0390285     bar

I would argue that this is surprisingly obtuse and not intuitive.  Things would get even more complicated if locs had a 2-column key!  

I suppose one reply is that I should just temporarily set the key for obsv and then reassign the entire obsv data.table.  I.e.,

> setkey(obsv,loc)
> obsv=locs[obsv]
> setkey(obsv,id)

This works, but is somehow to my eyes particularly dissatisfying.  Keys must be reset twice.  Potentially large datasets must be reassigned in their entirety.  Another solution that performs in-place assignment is similar:

> setkey(obsv,loc)
> obsv[,locname:=locs[obsv][,name]]
     id loc       mvar locname
[1,]  1   1 -0.6648842     baz
[2,]  3   1 -0.4477593     baz
[3,]  4   1 -1.1300506     baz
[4,]  6   1 -0.3041305     baz
[5,]  2   2 -0.8239177     bar
[6,]  7   2 -0.3416380     bar
[7,]  5   3  1.2745693     foo
> setkey(obsv,id)

This is not so bad, but it would be a lot nicer to not have to set keys and to simply say:

> obsv[,locname := locs[obsv,name]]

This could be achieved if (1) joins were performed by matching commonly named columns (like an SQL natural join) if the the two tables did not share keys of the same cardinality and types and (2) only explicitly listed columns were returned.  In my opinion, this idea of "natural joins" based on column names would simplify joins a lot, while making them more generally useful and intuitive.  If column names differed, then you might specify a list instead, e.g.

> A[list(id=B$a_id), val]

or maybe specify the mapping as an optional parameter that could be used if A and B did not have common columns and if A and B's keys differed, e.g.

> A[B, val, map=c("id=a_id")]

If joins matched by name, then the implementation could check if the key was sufficiently satisfied to be used and otherwise it would just perform a more conventional non-key'd join.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.r-forge.r-project.org/pipermail/datatable-help/attachments/20120801/10aeae22/attachment.html>