使用data.table的非等额联接:输出中缺少列 [英] Non-equi join using data.table: column missing from the output

问题描述

我正在使用data.table进行左非等式联接:

I am doing a left non-equi join using data.table:

OUTPUT <- DT2[DT1, on=.(DOB, FORENAME, SURNAME, POSTCODE, START_DATE <= MONTH, EXPIRY_DATE >= MONTH)]

OUTPUT包含正确的左连接，但缺少MONTH列(DT1中存在).

The OUTPUT contains a correct left join, with the exception that the MONTH column (which is present in DT1) is missing.

这是data.table中的错误吗?

NB:当然，START_DATE，EXPIRY_DATE和MONTH具有相同的YYYY-MM-DD IDate格式.根据这些非平等标准，联接的结果是正确的.只是该列丢失了，我需要在以后的工作中使用它.

NB: Of course, START_DATE, EXPIRY_DATE and MONTH are in the same YYYY-MM-DD, IDate format. The results of the join are correct based on these non-equi criteria. It is just that the column is missing and I need to use it in further work.

编辑1 :简化的可复制示例

DT1 <- structure(list(ID = c(1, 2, 3), FORENAME = c("JOHN", "JACK", 
"ROB"), SURNAME = c("JOHNSON", "JACKSON", "ROBINSON"), MONTH = structure(c(16953L, 
16953L, 16953L), class = c("IDate", "Date"))), .Names = c("ID", 
"FORENAME", "SURNAME", "MONTH"), row.names = c(NA, -3L), class = c("data.table", 
"data.frame"))

DT2 <- structure(list(CERT_NUMBER = 999, FORENAME = "JOHN", SURNAME = "JOHNSON", 
    START_DATE = structure(16801L, class = c("IDate", "Date")), 
    EXPIRY_DATE = structure(17166L, class = c("IDate", "Date"
    ))), .Names = c("CERT_NUMBER", "FORENAME", "SURNAME", "START_DATE", 
"EXPIRY_DATE"), row.names = c(NA, -1L), class = c("data.table", 
"data.frame"))

OUTPUT <- DT2[DT1, on=.(FORENAME, SURNAME, START_DATE <= MONTH, EXPIRY_DATE >= MONTH)]

> OUTPUT
   CERT_NUMBER FORENAME  SURNAME START_DATE EXPIRY_DATE ID
1:         999     JOHN  JOHNSON 2016-06-01  2016-06-01  1
2:          NA     JACK  JACKSON 2016-06-01  2016-06-01  2
3:          NA      ROB ROBINSON 2016-06-01  2016-06-01  3

• FORENAME和SURNAME结合在一起并出现在输出中.
• MONTH也是(非等价的)联接，并且在输出中不存在.
• FORENAME and SURNAME are joined on and are present in the output.
• MONTH is also (non-equi) joined on, and is absent from the output.

为什么这是预期的行为?

Why is this expected behaviour?

即使这是预期的行为，对我而言也无济于事，因为我需要保留MONTH以便进行进一步的数据处理.

Even if it is expected behaviour, it is not helpful in my case, because I need to retain MONTH for further data manipulation.

我的预期输出将是同一张表，但MONTH列将保留在DT1中.毕竟，我希望从左联接中得到的是保留在左表(DT1)中的每一行和所有列，以及所有要从右表(DT2)中添加的只有匹配的行.

My expected output would be the same table, but with the MONTH column retained as it is in DT1. After all, what I expect from a left join is for every row and column to be retained from the left table (DT1) and for all columns and only matched rows to be added from the right table (DT2).

   CERT_NUMBER FORENAME  SURNAME START_DATE EXPIRY_DATE ID      MONTH
1:         999     JOHN  JOHNSON 2016-01-01  2016-12-31  1 2016-06-01
2:          NA     JACK  JACKSON       <NA>        <NA>  2 2016-06-01
3:          NA      ROB ROBINSON       <NA>        <NA>  3 2016-06-01

编辑2 :显然，在我的代码产生的输出中，START和END日期也是错误的！只有第1个人拥有的证书的开始日期为1月1日，结束日期为12月31日！预期的输出是应该的.但是实际输出使一切都变成了1月1日.

Edit 2: apparently in the output produced by my code, the START and END dates are also wrong! Only person 1 had a certificate with a start date on 1-Jan and end date on 31-Dec! The expected output is what it should be. But the actual output made everything 1-Jan.

推荐答案

在data.table中，格式为x[i]的联接传统上使用i中的值，但使用x中的列名.即使这与返回两者的SQL不同，对于 equi 联接，此默认设置也很有意义，因为我们对i中的所有行都感兴趣，如果它们匹配，则两个data.table都具有等于值，如果没有，我们需要保留结果中来自i的那些不匹配值.

In data.table, joins of the form x[i] traditionally uses values from i but uses column names from x. Even though this is different from SQL which returns both, this default makes a lot of sense for equi joins since we are interested in all rows from i and if they match then both data.tables have equal values anyway, and if they don't we need to keep those unmatched values from i in result.

但是对于 non-equi 联接，由于值可能不完全匹配 ，即可能落在某个范围内，因此在某些情况下，我们必须返回输出类似于SQL(或识别这种情况并返回用户期望的结果，类似于等联接的情况).尚未完成此操作，但目前已为此提供了一条规定，即使用x.前缀显式引用这些列.我同意，这不方便.希望这会很快得到解决.

But for non-equi joins, since the values might not match exactly, i.e., can fall within a range, there might be cases where we will have to return outputs similar to SQL (or identify such cases and return the result user expects, similar to the case of equi joins). This hasn't been done yet, but I've placed a provision for it at the moment, which is to refer to the columns explicitly with a x. prefix. It is not convenient, I agree. Hopefully this'll be taken care of automatically soon.

以下是使用x.前缀获取结果的方法.

Here's how to get your result using x. prefix.

ans <- DT2[DT1, .(CERT_NUMBER, FORENAME, SURNAME, x.START_DATE, x.EXPIRY_DATE, ID, MONTH), 
         on=.(FORENAME, SURNAME, START_DATE <= MONTH, EXPIRY_DATE >= MONTH)]

IIRC在GitHub项目页面上也存在与此相关的问题.

IIRC there's an issue filed on the GitHub project page about this as well.

这篇关于使用data.table的非等额联接:输出中缺少列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！