R: find missing data and add it with a zero

Question

I have the following set of data:

The rows in Yellow is an example of a good situation, because for Vaer=B both Var2=F and Var2=G a freq value is present.

But, rows in red are example of a bad situation, because row 13 where Var2=F have freq value at date 07.02.2018 but I do not have a value for Var2=G at the same date.

On the other hand, row 27 in red, I have freq value for Var2=G at date 04:02:2018 but I do not have freq value for Var2=F at the same date.

What I need is: For all type of Var1 (A, B, ..) and For each Var2 (F, G) If freq is present in Var2=F but not in Var2=G create for Var2=G freq=0 at the same date.

The same have to be mabe for Var2=G where Var2=F is not present.

Could you give me some idea how to do that in R?

Please don't provide data as an image!! Read [how-to-make-a-great-r-reproducible-example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) — kath, Apr 27 '18 at 09:37

score 0 · Answer 1 · answered Apr 27 '18 at 10:26

0

Your explanation is a bit cryptic to me, but I guess you have counts for all groups but you need to have them as well for all permutations, even if there are no observations (therefore fill freq with 0). You can use tidyr::complete:

df <- data.frame(Var1 = c("A", "A", "B", "B", "B"),
                 Var2 = c("F", "G", "F", "G", "F"),
                 freq = c(2L, 3L, 1L, 5L, 3L),
                 date = as.Date(c("2018-04-27", "2018-04-28", "2018-04-27", "2018-04-28", "2018-04-29")))
tidyr::complete(data = df, Var1, Var2, date, fill = list(freq = 0L))

answered Apr 27 '18 at 10:26

Tino

2,091
13
15

Hi Tino, really thanks. I have to start to study a bit od tidyr. – user1010441 Apr 27 '18 at 13:46
Just another question: considering the same problem using the df your created, is it possible to have all permutation but separately for Var1=A and Var2=B? – user1010441 Apr 27 '18 at 13:48
You could use `subset()` like `subset(df, Var1=="A" & Var2=="B")`. – Tino Apr 27 '18 at 13:54
of course, thank you! – user1010441 Apr 27 '18 at 14:06

R: find missing data and add it with a zero

1 Answers1