CODEWITHSUNDEEP
rdataframe
h7681
h7681

Reputation: 355

Data Frame - Fill in Missing Data

I have a data frame with a date, a group (factor) and value column. 

   Date       Group       Value
01/01/2015      A           1
01/01/2015      B           5
01/01/2015      C           4
01/02/2015      A           10
01/02/2015      B           1
01/03/2015      C           5

Some of the groups do not have values entered for the dates. In the above example Group C does not have a value for 01/02/2015. A & B do not have values for 01/03/2015.

I would like to fill in these missing rows with values of zero, like this (order is not important):

Date           Group    Value
01/01/2015       A        1
01/01/2015       B        5
01/01/2015       C        4
01/02/2015       A        10
01/02/2015       B        1
01/03/2015       C        5
01/03/2015       A        0
01/03/2015       B        0
01/02/2015       C        0

Does anyone know of an easy way to achieve this? Thanks.

Upvotes: 0

Views: 221

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 270195

Try this one-liner. It converts the data from long form to wide form and then back to long form. No packages are used.

as.data.frame(xtabs(Value ~ Group + Date, DF), responseName = "Value")

giving:

  Group       Date Value
1     A 01/01/2015     1
2     B 01/01/2015     5
3     C 01/01/2015     4
4     A 01/02/2015    10
5     B 01/02/2015     1
6     C 01/02/2015     0
7     A 01/03/2015     0
8     B 01/03/2015     0
9     C 01/03/2015     5

Note: The input in reproducible form is:

Lines <- "Date   Group       Value
01/01/2015      A           1
01/01/2015      B           5
01/01/2015      C           4
01/02/2015      A           10
01/02/2015      B           1
01/03/2015      C           5"

DF <- read.table(text = Lines, header = TRUE)

Upvotes: 2

Related Questions