Inserting missing dates into a data frame
I have this dataframe:
Date Company Region Units
1 1/1/2012 IBM America 10
2 1/1/2012 IBM Europe 4
3 1/1/2012 IBM Pacific 2
4 1/1/2012 HP America 10
5 1/1/2012 HP Europe 2
6 1/1/2012 Gateway Americas 2
7 1/2/2012 IBM Americas 10
8 1/2/2012 HP Europe 2
9 1/12/2012 Gateway Americas 10
dput(x)
structure(list(Date = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 3L,
3L, 2L), .Label = c("1/1/2012", "1/12/2012", "1/2/2012"), class = "factor"),
Company = structure(c(3L, 3L, 3L, 2L, 2L, 1L, 3L, 2L, 4L), .Label = c(" Gateway",
" HP", " IBM", " Gateway"), class = "factor"), Region = structure(c(3L,
5L, 6L, 1L, 2L, 7L, 4L, 2L, 7L), .Label = c(" America",
" Europe", " America", " Americas",
" Europe", " Pacific", " Americas"), class = "factor"),
Units = c(10L, 4L, 2L, 10L, 2L, 2L, 10L, 2L, 10L)), .Names = c("Date",
"Company", "Region", "Units"), class = "data.frame", row.names = c(NA,
-9L))
I would like to create a heatmap, but there are many missing dates to make it look not very good. I need to fill in) for units for each missing region and date.
I need to have 3 regions for each company and each date, and if Date and Region are missing, insert it and put 0 for units.
I can create this vector from 1/1/2012 to 12/12/2012 for all dates:
d<-seq(as.Date(c("1/1/2012"), format="%m/%d/%Y"), as.Date(c("12/12/2012"), format="%m/%d/%Y"), by="mon")
For each company, I have to check the dates in the d vector for all three regions, unless inserting them with 0 units.
Is there an easy way to do this? Any guidance would be greatly appreciated.
+3
source to share
1 answer
You can use expand.grid
if you are not familiar with the company area or date values before starting work.
> a <- structure(list(Date = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 3L,
+ 3L, 2L), .Label = c("1/1/2012", "1/12/2012", "1/2/2012"), class = "factor"),
+ Company = structure(c(3L, 3L, 3L, 2L, 2L, 1L, 3L, 2L, 4L), .Label = c(" Gateway",
+ " HP", " IBM", " Gateway"), class = "factor"), Region = structure(c(3L,
+ 5L, 6L, 1L, 2L, 7L, 4L, 2L, 7L), .Label = c(" America",
+ " Europe", " America", " Americas",
+ " Europe", " Pacific", " Americas"), class = "factor"),
+ Units = c(10L, 4L, 2L, 10L, 2L, 2L, 10L, 2L, 10L)), .Names = c("Date",
+ "Company", "Region", "Units"), class = "data.frame", row.names = c(NA,
+ -9L))
>
> b <- expand.grid(Date=unique(a$Date), Company=unique(a$Company), Region=unique(a$Region))
>
>
>
> z <- merge(x=b,y=a, all.x=T)
>
> z[is.na(z)] <- 0
> z
Date Company Region Units
1 1/1/2012 Gateway America 0
2 1/1/2012 Gateway Europe 0
3 1/1/2012 Gateway America 0
4 1/1/2012 Gateway Americas 0
5 1/1/2012 Gateway Europe 0
6 1/1/2012 Gateway Pacific 0
7 1/1/2012 Gateway Americas 2
8 1/1/2012 HP America 10
9 1/1/2012 HP Europe 2
10 1/1/2012 HP America 0
11 1/1/2012 HP Americas 0
12 1/1/2012 HP Europe 0
13 1/1/2012 HP Pacific 0
14 1/1/2012 HP Americas 0
15 1/1/2012 IBM America 0
16 1/1/2012 IBM Europe 0
17 1/1/2012 IBM America 10
18 1/1/2012 IBM Americas 0
19 1/1/2012 IBM Europe 4
20 1/1/2012 IBM Pacific 2
21 1/1/2012 IBM Americas 0
22 1/1/2012 Gateway America 0
23 1/1/2012 Gateway Europe 0
24 1/1/2012 Gateway America 0
25 1/1/2012 Gateway Americas 0
26 1/1/2012 Gateway Europe 0
27 1/1/2012 Gateway Pacific 0
28 1/1/2012 Gateway Americas 0
29 1/12/2012 Gateway America 0
30 1/12/2012 Gateway Europe 0
31 1/12/2012 Gateway America 0
32 1/12/2012 Gateway Americas 0
33 1/12/2012 Gateway Europe 0
34 1/12/2012 Gateway Pacific 0
35 1/12/2012 Gateway Americas 0
36 1/12/2012 HP America 0
37 1/12/2012 HP Europe 0
38 1/12/2012 HP America 0
39 1/12/2012 HP Americas 0
40 1/12/2012 HP Europe 0
41 1/12/2012 HP Pacific 0
42 1/12/2012 HP Americas 0
43 1/12/2012 IBM America 0
44 1/12/2012 IBM Europe 0
45 1/12/2012 IBM America 0
46 1/12/2012 IBM Americas 0
47 1/12/2012 IBM Europe 0
48 1/12/2012 IBM Pacific 0
49 1/12/2012 IBM Americas 0
50 1/12/2012 Gateway America 0
51 1/12/2012 Gateway Europe 0
52 1/12/2012 Gateway America 0
53 1/12/2012 Gateway Americas 0
54 1/12/2012 Gateway Europe 0
55 1/12/2012 Gateway Pacific 0
56 1/12/2012 Gateway Americas 10
57 1/2/2012 Gateway America 0
58 1/2/2012 Gateway Europe 0
59 1/2/2012 Gateway America 0
60 1/2/2012 Gateway Americas 0
61 1/2/2012 Gateway Europe 0
62 1/2/2012 Gateway Pacific 0
63 1/2/2012 Gateway Americas 0
64 1/2/2012 HP America 0
65 1/2/2012 HP Europe 2
66 1/2/2012 HP America 0
67 1/2/2012 HP Americas 0
68 1/2/2012 HP Europe 0
69 1/2/2012 HP Pacific 0
70 1/2/2012 HP Americas 0
71 1/2/2012 IBM America 0
72 1/2/2012 IBM Europe 0
73 1/2/2012 IBM America 0
74 1/2/2012 IBM Americas 10
75 1/2/2012 IBM Europe 0
76 1/2/2012 IBM Pacific 0
77 1/2/2012 IBM Americas 0
78 1/2/2012 Gateway America 0
79 1/2/2012 Gateway Europe 0
80 1/2/2012 Gateway America 0
81 1/2/2012 Gateway Americas 0
82 1/2/2012 Gateway Europe 0
83 1/2/2012 Gateway Pacific 0
84 1/2/2012 Gateway Americas 0
NOTE. It seems that your data has duplicate values America
and Gateway
etc. Therefore, they appear more than once when usedexpand.grid
+3
source to share