CODEWITHSUNDEEP
rtime-seriesdplyrforecasting
emehex
emehex

Reputation: 10538

Inserting missing time observations in a dataframe

I have a data frame:

zz <- "Product    Quarter   Million
AAA 2013-Q3 81.1
AAA 2013-Q4 50.5
AAA 2014-Q1 81.9
AAA 2014-Q4 78.3
BBB 2013-Q3 29.9
BBB 2013-Q4 17
BBB 2014-Q3 87.4
BBB 2014-Q4 63
CCC 2013-Q4 41.1
CCC 2014-Q1 59.1
CCC 2014-Q2 110.7
CCC 2014-Q3 127"

df <- read.table(text = zz, header = TRUE); rm(zz)

With observations that span:

2013-Q3 2013-Q4 2014-Q1 2014-Q2 2014-Q3 2014-Q4

Except, most Products have missing time observations.

I need the missing periods to be inserted as zeros:

Product Quarter Million
AAA 2013-Q3 81.1
AAA 2013-Q4 50.5
AAA 2014-Q1 81.9
AAA 2014-Q2 0
AAA 2014-Q3 0
AAA 2014-Q4 78.3
BBB 2013-Q3 29.9
BBB 2013-Q4 0
BBB 2014-Q1 0
BBB 2014-Q2 0
BBB 2014-Q3 87.4
BBB 2014-Q4 63
CCC 2013-Q3 0
CCC 2013-Q4 41.1
CCC 2014-Q1 59.1
CCC 2014-Q2 110.7
CCC 2014-Q3 127
CCC 2014-Q4 0

Upvotes: 0

Views: 84

Answers (5)

G. Grothendieck
G. Grothendieck

Reputation: 269714

Both solutions below assume that each quarter appears in at least one product as is the case in the question:

1) xtabs This solution requires no packages:

 xt <- xtabs(Million ~ Quarter + Product, df)
 as.data.frame(xt, responseName = "Million")[c(2, 1, 3)]

   Product Quarter Million
1      AAA 2013-Q3    81.1
2      AAA 2013-Q4    50.5
3      AAA 2014-Q1    81.9
4      AAA 2014-Q2     0.0
5      AAA 2014-Q3     0.0
6      AAA 2014-Q4    78.3
7      BBB 2013-Q3    29.9
8      BBB 2013-Q4    17.0
9      BBB 2014-Q1     0.0
10     BBB 2014-Q2     0.0
11     BBB 2014-Q3    87.4
12     BBB 2014-Q4    63.0
13     CCC 2013-Q3     0.0
14     CCC 2013-Q4    41.1
15     CCC 2014-Q1    59.1
16     CCC 2014-Q2   110.7
17     CCC 2014-Q3   127.0
18     CCC 2014-Q4     0.0

If the column order and column names do not have to be exactly as in the question then it can be shortened to:

as.data.frame(xtabs(Million ~ Quarter + Product, df))

If wide form is OK then it can be shortened further to:

xtabs(Million ~ Quarter + Product, df)

giving:

         Product
Quarter     AAA   BBB   CCC
  2013-Q3  81.1  29.9   0.0
  2013-Q4  50.5  17.0  41.1
  2014-Q1  81.9   0.0  59.1
  2014-Q2   0.0   0.0 110.7
  2014-Q3   0.0  87.4 127.0
  2014-Q4  78.3  63.0   0.0

2) zoo Convert df to a zoo object z and then replace the each NA with zero and use fortify.zoo with the melt=TRUE argument to convert it back to long form. 

library(zoo)

z <- read.zoo(df, index = 2, FUN = identity, split = 1, header = TRUE)
z <- na.fill(z, 0)
df_full <- fortify.zoo(z, melt = TRUE, name = "Product")[, c(2, 1, 3)]
names(df_full) <- names(df)

giving:

> df_full
   Product Quarter Million
1      AAA 2013-Q3    81.1
2      AAA 2013-Q4    50.5
3      AAA 2014-Q1    81.9
4      AAA 2014-Q2      NA
5      AAA 2014-Q3      NA
6      AAA 2014-Q4    78.3
7      BBB 2013-Q3    29.9
8      BBB 2013-Q4    17.0
9      BBB 2014-Q1      NA
10     BBB 2014-Q2      NA
11     BBB 2014-Q3    87.4
12     BBB 2014-Q4    63.0
13     CCC 2013-Q3      NA
14     CCC 2013-Q4    41.1
15     CCC 2014-Q1    59.1
16     CCC 2014-Q2   110.7
17     CCC 2014-Q3   127.0
18     CCC 2014-Q4      NA

If a wide form "zoo" object is OK then omit the last two lines, i.e. the omit the lines that set df_full and its names and just use z.

> z
         AAA  BBB   CCC
2013-Q3 81.1 29.9   0.0
2013-Q4 50.5 17.0  41.1
2014-Q1 81.9  0.0  59.1
2014-Q2  0.0  0.0 110.7
2014-Q3  0.0 87.4 127.0
2014-Q4 78.3 63.0   0.0

Upvotes: 2

Eric Brooks
Eric Brooks

Reputation: 667

You can do it using the reshape2 package:

library(reshape2)    
df <- melt(dcast(df, Product ~ Quarter))

Then you can change the NA values to 0:

df[is.na(df)] <- 0

Upvotes: 1

mucio
mucio

Reputation: 7119

Maybe a solution a bit too verbose for R people, but it's using dplyr

# all products from your dataframe
product <- unique(df$Product) # all products from your dataframe
# all quarters you want
quarter <- c('2013-Q3', '2013-Q4', '2014-Q1', '2014-Q2', '2014-Q3', '2014-Q4')
# let's combine them
df2 <- expand.grid(Product=product, Quarter = quarter)

# and now let's do a join between your df and all possible 
# combinations of Products and Quarters
df %>%
  right_join(df2) %>%                               # here the join
  arrange(Product, Quarter) %>%                     # here the sorting
  mutate(Value = ifelse(is.na(Million),0, Million)) # replacing the NA with 0

Upvotes: 0

user2600629
user2600629

Reputation: 561

Try this

Values = as.data.frame(table(df$Product,df$Quarter))
Values = Values[with(Values, order(Var1, Var2)), ]
colnames(Values)[1] = 'Product'
colnames(Values)[2] = 'Quarter'

data = merge(x = Values, y = df, by =c("Product","Quarter"), all.x=TRUE)
data[is.na(data)] <- 0
data = data[,c(1,2,4)]

Upvotes: 0

Colonel Beauvel
Colonel Beauvel

Reputation: 31171

You can try:

library(data.table)
setkey(setDT(df), Product, Quarter)[CJ(unique(Product), unique(Quarter))][!df, Million:=0][]

#    Product Quarter Million
# 1:     AAA 2013-Q3    81.1
# 2:     AAA 2013-Q4    50.5
# 3:     AAA 2014-Q1    81.9
# 4:     AAA 2014-Q2     0.0
# 5:     AAA 2014-Q3     0.0
# 6:     AAA 2014-Q4    78.3
# 7:     BBB 2013-Q3    29.9
# 8:     BBB 2013-Q4    17.0
# 9:     BBB 2014-Q1     0.0
#10:     BBB 2014-Q2     0.0
#11:     BBB 2014-Q3    87.4
#12:     BBB 2014-Q4    63.0
#13:     CCC 2013-Q3     0.0
#14:     CCC 2013-Q4    41.1
#15:     CCC 2014-Q1    59.1
#16:     CCC 2014-Q2   110.7
#17:     CCC 2014-Q3   127.0
#18:     CCC 2014-Q4     0.0

Upvotes: 2

Related Questions