I have a panel dataset of firms with multiple variables. I create one dummy variable treat2 which equals 1 when a given firm takes one action in two consecutive months within a given year and 0 otherwise using dcast. I am looking for a way to combine my original data with the newly-created variable. I use melt and merge commands without success. Could anyone help me on this?
Here's an example with one additional variable (sector) and what I tried so far:
firmid <-c(1,1,1,1,1,1,
2,2,2,2,
3,4,4,5,5,5)
year <-c(2010,2010,2010,
2011,2011,2011,
2010,2010,2011,2011,
2010,2011,2011,2010,
2011,2011)
month <- c(1,2,3,1,2,3,
1,2,1,2,1,1,
2,1,2,3)
sector <- c(1,1,1,1,1,1,
1,1,1,1,1,
2,2,2,2,2)
dfdata <-as.data.frame(cbind(firmid,year,month, sector))
dfdata <-as.data.table(dfdata)
new_DT <- dcast(dfdata, firmid + year ~ month, value.var = "firmid",
fun.aggregate = length, drop = FALSE)
#create the dummy variable treat2
new_DT1<- (new_DT[, treat2 := ifelse(rowSums(.SD) >= 2, 1, 0), .SDcols = patterns("^[0-9]+$")][])
#attempt to come back to original dataset
new_DT2 <- melt(new_DT1, measure.vars = patterns("^[0-9]+$"), variable.name = "month", value.name = "treat")
new_DT2 <-as.data.frame(new_DT2)
dfdata1 <-as.data.frame(cbind(firmid,year,month, sector))
dfdata2 <- as.data.table(dfdata1)
dfdata2 <- dcast(dfdata2, firmid + year ~ month, value.var = "firmid",
fun.aggregate = length, drop = FALSE)
dfdata2 <- melt(dfdata2, measure.vars = patterns("^[0-9]+$"), variable.name = "month", value.name = "treated")
dfdata2 <- as.data.frame(dfdata2)
final <- as.data.frame(merge(new_DT2, dfdata2, all = TRUE))
view(final)
