Wie verbinde ich am besten eine Spalte einer data.table mit einer anderen Spalte derselben data.table?
Ich habe eine DatentabelleDT mit dem Strom (F0YR) und der nächste (F1YR) Geschäftsjahresende (FYE) in ganzen Zahlen. Da jedes nächste FYE irgendwann zu einem aktuellen FYE wird, steht die ganze Zahl in beiden SpaltenF1YR undF0YR. Außerdem enthalten meine Daten monatliche Beobachtungen, sodass dasselbe FYE mehrmals im Datensatz enthalten ist:
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
...
Für jedenID undF1YR Kombination möchte ich den Wert für dieID undF0YR Kombination. Als Beispiel: Firma A hatte einen Wert von2 zumFOYR==4. Jetzt möchte ich eine zusätzliche Spalte für alle Kombinationen mitID=="A" undF1YR==4 die auf 2 gesetzt ist, neben dem bereits vorhandenen Wert von 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
...
(Beachten Sie, dass ich benutzemult="last" hier, weil, obwohl sich die Werte nur mit den Änderungen von F0YR oder F1YR ändern sollten, dies manchmal nicht der Fall ist und dies nur mein Gleichstand ist).
Das sieht verbesserungsfähig aus. Zuallererst muss ich eine Kopie meiner DT machen. Zweitens schließe ich mich da grundsätzlich gleich andata.tablehaben alle Spaltennamen den gleichen Namen und ich muss sie umbenennen. Ich dachte, dass einself join wäre der Weg nach vorne, aber ich habe versucht und versucht und konnte keine schöne Lösung bekommen. Ich habe die Hoffnung, dass es da draußen etwas Leichtes gibt, das ich einfach nicht sehe ... Hat jemand eine Ahnung? Oder sind meine Daten so eingerichtet, dass es tatsächlich schwierig ist (möglicherweise, weil ich monatliche Beobachtungen habe, aber nur vierteljährliche oder jährliche Werte ändern möchte).