¿Cómo unir mejor una columna de un data.table con otra columna del mismo data.table?
Tengo una tabla de datosDT con la corriente (F0YR) y el siguiente (F1YR) fin de año fiscal (FYE) codificado como enteros. Como cada FYE siguiente se convertirá en un FYE actual, el número entero estará en la columnaF1YR yF0YR. Además, mis datos contienen observaciones mensuales, por lo que el mismo FYE estará en el conjunto de datos varias veces:
library(data.table)
DT <- data.table(ID = rep(c("A", "B"), each=9),
MONTH = rep(100L:108L, times=2),
F0YR = rep(c(1L, 4L, 7L), each=3, times=2),
F1YR = rep(c(4L, 7L, 9L), each=3, times=2),
value = c(rep(1:5, each=3), 6, 6, 7),
key = "ID,F0YR")
DT
ID MONTH F0YR F1YR value
[1,] A 100 1 4 1
[2,] A 101 1 4 1
[3,] A 102 1 4 1
[4,] A 103 4 7 2
[5,] A 104 4 7 2
[6,] A 105 4 7 2
[7,] A 106 7 9 3
[8,] A 107 7 9 3
[9,] A 108 7 9 3
[10,] B 100 1 4 4
[11,] B 101 1 4 4
...
Para cadaID yF1YR combinación, quiero obtener el valor para elID yF0YR combinación. Como ejemplo: la empresa A tenía un valor de2 paraFOYR==4. Ahora, quiero una columna adicional para todas las combinaciones conID=="A" yF1YR==4 que se establece en 2, junto al valor ya existente de 1.
intDT <- DT[CJ(unique(ID), unique(F0YR)), list(ID, F0YR, valueNew = value), mult="last"]
setkey(intDT, ID, F0YR)
setkey(DT, ID, F1YR)
DT <- intDT[DT]
setnames(DT, c("F0YR.1", "F0YR"), c("F0YR", "F1YR"))
DT
ID F1YR valueNew MONTH F0YR value
[1,] A 4 2 100 1 1
[2,] A 4 2 101 1 1
[3,] A 4 2 102 1 1
[4,] A 7 3 103 4 2
[5,] A 7 3 104 4 2
[6,] A 7 3 105 4 2
[7,] A 9 NA 106 7 3
[8,] A 9 NA 107 7 3
[9,] A 9 NA 108 7 3
[10,] B 4 5 100 1 4
[11,] B 4 5 101 1 4
...
(Tenga en cuenta que yo usomult="last" aquí porque, aunque los valores solo deberían cambiar con los cambios F0YR o F1YR, a veces no lo hacen y esto es solo mi desempate.
Esto se ve mejorable. En primer lugar, tengo que hacer una copia de mi DT. En segundo lugar, ya que me uno básicamente es el mismo.data.table, todos los nombres de columna tienen el mismo nombre y tengo que cambiarles el nombre. Pensé que unself join sería el camino a seguir, pero lo intenté y no pude obtener una buena solución. Tengo la esperanza de que haya algo fácil por ahí que simplemente no veo ... ¿Alguien tiene una pista? O mis datos están configurados de tal manera que son realmente difíciles (quizás porque tengo observaciones mensuales, pero quiero unirme solo a valores cambiantes trimestrales o anuales).