Resuma el índice de autounión mientras evita el producto cartesiano en R data.table
Con una columna 2data.table, Me gustaría resumir las relaciones por pares en la columna 1 sumando el número de elementos compartidos en la columna 2. En otras palabras, ¿cuántos elementos Y compartidos tiene cada combinación de valores X en pares?
Por ejemplo, puedo hacer esto en un proceso de 2 pasos, primero haciendo una unión cruzada cartesiana, luego resumiendo así:
d = data.table(X=c(1,1,1,2,2,2,2,3,3,3,4,4), Y=c(1,2,3,1,2,3,4,1,5,6,4,5))
setkey(d, Y)
d2 = d[d, allow.cartesian=TRUE]
d2[, .N, by=c("X", "i.X")]
# X i.X N
#1: 1 1 3
#2: 2 1 3
#3: 3 1 1
#4: 1 2 3
#5: 2 2 4
#6: 3 2 1
#7: 1 3 1
#8: 2 3 1
#9: 3 3 3
#10: 4 2 1
#11: 2 4 1
#12: 4 4 2
#13: 4 3 1
#14: 3 4 1
La segunda fila de este resultado indica queX=1 comparte 3 valores Y conX=2; mientrasX=3 comparte solo 1 valor de yX=4.
¿Hay alguna manera de hacer esto sin pasar por el paso de unión cartesiana, lo que lleva a grandes tablas ineficientes? Quiero hacer algo como esto en una mesa con millones de filas, y la unión cartesiana se encuentra con el2^31 límite de tamaño del vector (además de volverse lento).
Me estoy imaginando algo como esto:
d[d, list(X, length(Y)), by=c("X", "i.X")]
Pero esto da el errori.X not found
Puedo hacer esto en SQL con el siguiente código, pero no puedo entender cómo traducir esto a la sintaxis data.table:
CREATE TABLE test (X integer, Y integer);
INSERT INTO test VALUES(1, 1);
INSERT INTO test VALUES(1, 2);
INSERT INTO test VALUES(1, 3);
INSERT INTO test VALUES(2, 1);
INSERT INTO test VALUES(2, 2);
INSERT INTO test VALUES(2, 3);
INSERT INTO test VALUES(2, 4);
INSERT INTO test VALUES(3, 1);
INSERT INTO test VALUES(3, 5);
INSERT INTO test VALUES(3, 6);
INSERT INTO test VALUES(4, 4);
INSERT INTO test VALUES(4, 5);
SELECT A.X, B.X, COUNT(A.Y) as N FROM test as A JOIN test as B WHERE A.Y==B.Y GROUP BY A.X, B.X;
El punto es que la columna que quiero resumir es la misma que la columna a la que me estoy uniendo. Esta pregunta es similar a estas, pero no exactamente:
Datos R. Tabla Unir sobre condicionales
Cómo auto unirse a un data.table en una condición
La diferencia clave es que quieroresumir la columna de índice, que parece imposible de hacer con by = .EACHI.