I currently have the dataset:
Time Var1 Var2 Cat
1 14 16 1
2 16 98 1
4 52 68 1
7 17 12 1
1 57 58 2
3 45 35 2
4 33 12 2
7 77 1 2
And wish to be able to create continuous time variable for each of category, like so:
Time Var1 Var2 Cat
1 14 16 1
2 16 98 1
3 NA NA 1
4 52 68 1
5 NA NA 1
6 NA NA 1
7 17 12 1
1 57 58 2
2 NA NA 2
3 45 35 2
4 33 12 2
5 NA NA 2
6 NA NA 2
7 77 1 2
I am struggling to be able to impute all the data rows for each category.
All help is greatly appreciated.
Base R solution:
df <- read.table(text = "Time Var1 Var2 Cat
1 14 16 1
2 16 98 1
4 52 68 1
7 17 12 1
1 57 58 2
3 45 35 2
4 33 12 2
7 77 1 2", header = T)
df2 <- expand.grid(Time = 1:7, Cat = 1:2)
df3 <- merge(df, df2, by = c("Time", "Cat"), all = T)
df3 <- df3[order(df3$Cat, df3$Time), c(1, 3, 4, 2)]
> df3
Time Var1 Var2 Cat
1 1 14 16 1
3 2 16 98 1
5 3 NA NA 1
7 4 52 68 1
9 5 NA NA 1
11 6 NA NA 1
13 7 17 12 1
2 1 57 58 2
4 2 NA NA 2
6 3 45 35 2
8 4 33 12 2
10 5 NA NA 2
12 6 NA NA 2
14 7 77 1 2