Q:

Manipulating a vector based on a condition in R

I am trying to find an efficient way to execute the following code snippet but on a rather large data frame (2M+ records).  I've tried using sapply and a for loop but neither are very good.
The 'ACCOUNT_CLOSE_DATE' happens to be blank but it is the last 'AS_OF_DATE' for a given 'U_ID'.  I want to insert this particular value into the 'ACCOUNT_CLOSE_DATE' column for the 'U_ID'.  Any help would be greatly appreciated! 
 library(reshape2)
 cnames = c("Date", "999:999", "888:888", "777:777")
 dates = c("31JAN2005", "28FEB2005", "31MAR2005")
 val = ""
 mydf = data.frame(cbind(dates, matrix(val, nrow = 3, ncol = 3)))
 colnames(mydf) = cnames
 nmydf = melt(mydf, id.vars = "Date")
 nmydf$Date = as.character(nmydf$Date)
 colnames(nmydf) = c("AS_OF_DATE", "U_ID", "ACCOUNT_CLOSE_DATE")
 uvals = c("999:999", "888:888", "777:777")
 nmydf[nmydf$U_ID == uvals[1],"ACCOUNT_CLOSE_DATE"] = 
   nmydf[nmydf$U_ID == uvals[1], "AS_OF_DATE"][length(nmydf[nmydf$U_ID == uvals[1], "AS_OF_DATE"])]

A:

You can do a data.table join in order to get the closing dates per group of "U_ID" to the table like follows: 
library(data.table)
cnames <-  c("Date", "999:999", "888:888", "777:777")
dates <-  c("31JAN2005", "28FEB2005", "31MAR2005")
val <-  ""
mydt <-  data.table(cbind(dates, matrix(val, nrow = 3, ncol = 3)))
setnames(mydt, cnames)
nmydt <-  melt(mydt, id.vars = "Date")[,Date:variable]
setnames(nmydt, c("AS_OF_DATE", "U_ID"))
closing_dates <- setnames(nmydt[, .SD[.N], by=U_ID], c("U_ID", "ACCOUNT_CLOSE_DATE"))
merge(nmydt, closing_dates, by="U_ID", all.x=TRUE)

#       U_ID AS_OF_DATE ACCOUNT_CLOSE_DATE
# 1: 999:999  31JAN2005          31MAR2005
# 2: 999:999  28FEB2005          31MAR2005
# 3: 999:999  31MAR2005          31MAR2005
# 4: 888:888  31JAN2005          31MAR2005
# 5: 888:888  28FEB2005          31MAR2005
# 6: 888:888  31MAR2005          31MAR2005
# 7: 777:777  31JAN2005          31MAR2005
# 8: 777:777  28FEB2005          31MAR2005
# 9: 777:777  31MAR2005          31MAR2005

This solution should also provide efficiency boost.

