joy_of_joining_data.table.R

library(data.table)
dt <- data.table(dat, key="product,year")
# Create a table that list all years for all products.
py <- CJ(product=levels(dt[, product]), 
         year=unique(dt[,year]))
# Create a mapping table from old to new products.
# The new products are named product, so that I can 
# join them with the original data and get a new colulmn
# with the old product category
mapping <- data.table(
  old.product=c("Mobile", "Mobile", 
                "Video rental shop", "Video rental shop"),
  product=c("Mobile", "Smartphone", 
            "Online movie rental", "Video rental shop"),
  key="product")
# Add a column for year, so that when I join this mapping
# table expands to original data 
mapping <- mapping[py]
setkeyv(mapping, key(dt))
# Create a temporary table that includes the price changes
# that is based on the original data, but expanded with the
# mapping table, so that for each product I have all years.
tmp <- dt[mapping][, category:=NULL]
setnames(tmp, c("product", "old.product", "price.change"), 
         c("new.product", "product", "new.price.change"))
setkeyv(tmp, c("product", "year"))
# Join the temporary table with the original data 
dt <- dt[tmp]
# Remove the tables I no longer need
rm(tmp, mapping, py)
# Set the price change to either the original price change
# or take the price change of the old product
dt[, price.change:=ifelse(is.na(new.price.change), 
                          price.change, 
                          new.price.change)]
# Remove columns I no longer need
dt[, `:=`(new.price.change=NULL,
          product=NULL)]
setnames(dt, "new.product", "product")
# Add a column that calculates the price change 
# for each product
dt[order(year), 
   price.index:=cumprod(price.change+1),
   by=list(product)]

# Plot the result
xyplot(price.index ~ year | category, 
       groups=product, data=dt, 
       as.table=TRUE, 
       scales=list(cex=c(1.1, 1.1), # increase font size
                   alternating=1,   # axes labels left/bottom 
                   tck = c(1,0)),   # ticks only with labels
       par.settings = my.settings, 
       cex=1.1, # change size of symbols and countries
       xlab=list(label="Year", fontsize=14),
       ylab=list(label="Price index", fontsize=14),
       par.strip.text=list(col="white", font=2),       
       panel=panel.superpose,
       panel.groups=function(x,y, group.number,...){           
         panel.xyplot(x,y,t="b",...) 
         panel.grid(v=-1, h=-1, lty=3)
         p <- levels(dt$product)[group.number] 
         xt <- x[x==max(x)] # find latest year
         yt <- y[x==max(x)] # find value at latest year         
         panel.text(xt, yt, labels=p, 
                    pos=2,  # show labels on right side
                    ...)
       }              
)