-4

I am trying to do left join in data.table, I want to join panelFull and panel on the basis of OutletID.

From panel I want CellID column to be inserted in panelFull:

> panel[1:15,]
    Period CellID OutletID      ACV
 1:    215   1268   M44600  9563317
 2:    215   1268   M44800  8966339
 3:    215   1268   M45100  7043924
 4:    215   1268   M45200  9013918
 5:    215   1268   M45300 10009468
 6:    215   1268   M46900 22148703
 7:    215   1268   M48400 18661734
 8:    215   1268   M51000  8531347
 9:    215   1268   M51500  9125734
10:    215   1268   M51600  8575727
11:    215   1268   M53700 12148614
12:    215   1268   M57000  9678589
13:    215   1268   M59400 17261166
14:    215   1268   M60200  7939758
15:    215   1268   M60700  6840897

> panelFull[1:15,]
    OutletID pno
 1:   CP0001 204
 2:   CP0001 205
 3:   CP0001 206
 4:   CP0001 207
 5:   CP0001 208
 6:   CP0001 209
 7:   CP0001 210
 8:   CP0001 211
 9:   CP0001 212
10:   CP0001 213
11:   CP0001 214
12:   CP0001 215
13:   CP0006 204
14:   CP0006 205
15:   CP0006 206

I want something like:

OutletID pno CellID

How do I do that with data.table?

Jaap
  • 81,064
  • 34
  • 182
  • 193
Archit gupta
  • 147
  • 1
  • 2
  • 11
  • Next time, please post your data in a reproducible form. See the "Used data" section Jaap had to add to his answer (because you omitted it) and maybe this guidance: http://stackoverflow.com/a/28481250/1191259 – Frank Oct 01 '15 at 14:06

2 Answers2

7

The following should give you the result you want:

panelFull[panel, CellID := CellID, on = "OutletID"]

For the provided datasets this will result in a column with only NA-values as there are no matching OutletID's between the two datasets. Therefore I slightly adapted the content of the panelFull dataset (you can find a dput at the end of this answer). The join then results in:

> panelFull
    OutletID pno CellID
 1:   CP0001 204     NA
 2:   CP0001 205     NA
 3:   CP0001 206     NA
 4:   CP0001 207     NA
 5:   CP0001 208     NA
 6:   CP0001 209     NA
 7:   CP0001 210     NA
 8:   CP0001 211     NA
 9:   CP0001 212     NA
10:   CP0001 213     NA
11:   CP0001 214     NA
12:   CP0001 215     NA
13:   CP0006 204     NA
14:   CP0006 205     NA
15:   CP0006 206     NA
16:   M60700 215   1268

Used data:

panelFull <- structure(list(OutletID = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L), .Label = c("CP0001", "CP0006", "M60700"), class = "factor"), pno = c(204L, 205L, 206L, 207L, 208L, 209L, 210L, 211L, 212L, 213L, 214L, 215L, 204L, 205L, 206L, 215L)), .Names = c("OutletID", "pno"), class = c("data.table", "data.frame"), row.names = c(NA, -16L))
panel <- structure(list(Period = c(215L, 215L, 215L, 215L, 215L, 215L, 215L, 215L, 215L, 215L, 215L, 215L, 215L, 215L, 215L), CellID = c(1268L, 1268L, 1268L, 1268L, 1268L, 1268L, 1268L, 1268L, 1268L, 1268L, 1268L, 1268L, 1268L, 1268L, 1268L), OutletID = structure(1:15, .Label = c("M44600", "M44800", "M45100", "M45200", "M45300", "M46900", "M48400", "M51000", "M51500", "M51600", "M53700", "M57000", "M59400", "M60200", "M60700"), class = "factor"), ACV = c(9563317L, 8966339L, 7043924L, 9013918L, 10009468L, 22148703L, 18661734L, 8531347L, 9125734L, 8575727L, 12148614L, 9678589L, 17261166L, 7939758L, 6840897L)), .Names = c("Period", "CellID", "OutletID", "ACV"), class = c("data.table", "data.frame"), row.names = c(NA, -15L))
Jaap
  • 81,064
  • 34
  • 182
  • 193
  • there are matching OutletID – Archit gupta Oct 01 '15 at 09:51
  • @Architgupta Not in the data you provided in the question, but I can imagine there are in the full datasets. For illustrative purposes I added a row to `panelFull` with a matching `OutletID`. – Jaap Oct 01 '15 at 10:03
  • @Architgupta, if this answer fulfill your request, please consider accepting it by ticking on the check mark next to it. Then your question can leave the "unanswered questions" list :-) – Cath Oct 01 '15 at 11:21
0

I tried this and it is working

setkey(panelFull,CELLDEF)   
setkey(cells,CellID)

panelFull =  cells[panelFull]
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
Archit gupta
  • 147
  • 1
  • 2
  • 11
  • this is not the best way to do a 'left-update-join' with [tag:data.table], the method as showed in my answer is preferrable; [see here for an explanation](https://stackoverflow.com/a/34600831/2204410) – Jaap Nov 30 '18 at 17:12