Creating Missing Cases in Panel Data

Question

I am working on panel data with a unique case identifier and a column for time points of observations (long format). There are both time-constant variables and time-varying observations:

    id  time    tc1     obs1
1   101 1       male    4
2   101 2       male    5
3   101 3       male    3
4   102 1       female  6
5   102 3       female  2
6   103 1       male    2

For my model, I now need data with complete records for an identifier for each moment in time. In other words, if there is no observation, I still need to put a line with id, time, time-constant variables and NA for the observed variables (like the line (102, 2, "woman", NA) in the above example). So my question is:

• How do I know if there is a row in my dataset with a unique combination of id and time?
• If not, how can I add this line, transfer variables in time and fill in observations using NA?

It would be great if someone could shed light on this.

Many thanks!

---

EDIT

Thank you all for your answers. Here's what I finally did is a combination of several suggested approaches. The fact is that I have several variables in time (obs1-obsn) for each line, and I did not get a dcast to place for this - value.name takes no more arguments.

# create all possible permutations of id and year
iddat = expand.grid(id = unique(dataset$id), time = (c(1996,1999,2002,2005,2008,2011)))
iddat <- iddat[order(iddat$id, iddat$time), ]

# add permutations to existing data, combinations so far missing are NA
dataset_new <- merge(dataset, iddat, all.x=TRUE, all.y=TRUE, by=c("id", "time"))

# drop time-constant variables from data
dataset_new[c("tc1", "tc2", "tc3")] <- list(NULL)

# merge back time-constant variables from original data
temp <- dataset[c("tc1", "tc2", "tc3")]
dataset_new <- merge(dataset_new, temp, by=c("id"))

# sort
dataset_new <- dataset_new[order(dataset_new$id, dataset_new$time), ]
dataset_new <- unique(dataset_new) # some rows are duplicates after last merge, no idea why

rm(temp)
rm(iddat)

All the best and thanks again Matt

Answer 1

There are probably more elegant ways, but here is one option. I assume that you need all combinations of idand time, but not tc1(i.e. tc1attached to id).

# your data
df <- read.table(text = "    id  time    tc1     obs1
1   101 1       male    4
2   101 2       male    5
3   101 3       male    3
4   102 1       female  6
5   102 3       female  2
6   103 1       male    2", header = TRUE)

, NA, .

library('reshape2')

df_wide <- dcast(
  df, 
  id + tc1 ~ time,
  value.var = "obs1", 
  fill = NA
)

df_long <- melt(
  df_wide, 
  id.vars = c("id","tc1"), 
  variable.name = "time",
  value.name = "obs1"
)

# sort by id and then time
df_long[order(df_long$id, df_long$time), ]
   id    tc1 time obs1
1 101   male    1    4
4 101   male    2    5
7 101   male    3    3
2 102 female    1    6
5 102 female    2   NA
8 102 female    3    2
3 103   male    1    2
6 103   male    2   NA
9 103   male    3   NA

Answer 2

, .

 # Create dataset.  For you actual data ,you would replace c(1:3) with 
 # c(1:max(yourdata$id)) and adjust the number of time periods to match your data.
 id <- rep(c(1:3), each = 3)
 time <- rep(c(1:3), 3)
 df <- data.frame(id,time)


 test <- df[c(1,3,5,7,9),]
 test$tc1 <- c("male", "male", "female", "male", "male")
 test$obs1 <-c(4,5,3,6,2)

 merge(df, test, by.x = c("id","time"), by.y = c("id","time"), all.x = TRUE)

:

 id time    tc1 obs1
 1  1    1   male    4
 2  1    2   <NA>   NA
 3  1    3   male    5
 4  2    1   <NA>   NA
 5  2    2 female    3
 6  2    3   <NA>   NA
 7  3    1   male    6
 8  3    2   <NA>   NA
 9  3    3   male    2