include missing data

Question

include missing data

Navigation

#1 by (2 votes)

5

Good afternoon,

I have the following problem. I need to join two sets of data that have 2 variables in common, "uor" and "month". Only 1 variable does not have values for a given month. And what I wanted was that for the months that do not have information, this particular variable, would assume the value 0 and included those missing months. Below I send a code to exemplify. The idea is to include the value 0 in the variable x for months 4, 5 and 6 of the "B".

code example

library(data.table)
uor <- c(rep("A", 8), rep("B", 5), rep("C", 8), rep("D", 8))
uor2 <- c(rep("A", 8), rep("B", 8), rep("C", 8), rep("D", 8))
mes1 <- c(1:8, 1,2,3,7,8, 1:8, 1:8)
mes2 <- rep(1:8, 4)

DT <- data.table(uor, mes1, x = rnorm(length(uor)))
DT2 <- data.table(uor2, mes2, x1 = rnorm(length(uor2)), x2 = rpois(length(uor2), 4))

r

asked by anonymous 06.12.2016 / 20:09

1 answer

How do I save an undefined string in a structure? How to search for markers already created on a map - Google Maps

score 2 · Accepted Answer

Can be resolved with dplyr :

If you have a date frame completo with all possible combinations of uor and mes , you can join it with your data frame incompleto :

completo <- data.frame(uor=uor2, mes=mes2)
incompleto <- data.frame(uor=uor, mes=mes1, x= rnorm(length(mes1)))

The dplyr left_join function returns all rows of the first data frame and a combined column of the two data frames:

> set.seed(3)
> resultado <- left_join(completo, incompleto, by=c("uor","mes"))
> resultado
   uor mes           x
1    A   1  0.70290393
2    A   2  0.25700951
3    A   3  2.69017371
4    A   4  0.23524010
5    A   5 -2.75759062
6    A   6  1.03083168
7    A   7 -1.04026148
8    A   8 -0.72519990
9    B   1 -0.50549942
10   B   2  1.68207108
11   B   3  0.75289951
12   B   4          NA
13   B   5          NA
14   B   6          NA
15   B   7 -0.24574331
16   B   8  0.83446856
17   C   1 -1.01048869
18   C   2 -0.27242506
19   C   3  0.49329590
20   C   4  0.99004920
21   C   5  0.81256775
22   C   6 -0.75664209
23   C   7  0.41740965
24   C   8 -1.40809870
25   D   1 -0.81222459
26   D   2  0.73522139
27   D   3  0.43398983
28   D   4 -0.51572523
29   D   5  1.21112742
30   D   6  0.88230864
31   D   7 -1.71555588
32   D   8 -0.08829608

After that, just set all NA to 0:

resultado[is.na(resultado$x), ]$x <- 0

Or you can use mutate :

resultado <- left_join(completo, incompleto, by=c("uor","mes")) %>%
                 mutate(x=ifelse(is.na(x), 0, x))