0
$\begingroup$

I have an xls file with sales data from 12 shops, each selling two types of goods.

If I read in the xls file and sum up sales for each month (ignoring the two types of goods and just looking at the overall total), and make it into a time series like this:

myTS <- makeTS(myDF, "myDateVar", "myVar")

makeTS <- function(dataObject, dateName, varName){ 
dataNameString <- deparse(substitute(dataObject))   
countsStr <- paste0("with(", dataNameString,", tapply(", varName, 
", substr(", dateName,     ", 1, 7), sum))")
counts <- eval(parse(text = countsStr))
dates <- eval(parse(text = paste0(dataNameString, "$", dateName)))
startYear <- format(min(dates), "%Y")
endYear <- format(max(dates), "%Y")
lastYearVect <- subset(dates, format(dates, "%Y") <= endYear)
lastDate <- (format(max(lastYearVect)))
endMonth <- as.numeric(format(as.yearmon(lastDate), "%m"))
commandLine <- paste0("ts(counts, start = c(", startYear,
 ",1), end = c(", endYear, ",",     endMonth,"), frequency = 12)")
tsIm <- eval(parse(text = commandLine))
return(tsIm)
}

I get one result. I'll put it at the end for easy comparison.

If I instead use the time series for each product in each shop, creating a time series matrix with 24 columns, and then convert it into a hts object. I get a slightly different result. The data at shop level is very sparse and I had to replace a lot of NAs with 0's in order to do any analysis on it.

countsMatrix <- createCountsMatrix(c(shop1.item1, ..., shop24.item2), "myDateVar", "myVar", 36)

Where

createCountsMatrix <- function(dfList, dateName, varName, tsLength){
clength <- length(dfList)
matrixIm <- c()
for (i in 1:clength){
iDFname <- dfList[i]
iVectString <- paste0("getCounts(",iDFname, ", \"", dateName, " \", \"", varName, "\")")
iVect <- eval(parse(text = iVectString))
iDF <- data.frame(Year = substr(names(iVect), 1, 4),
             Month = factor(month.abb[as.numeric(substr(names(iVect), 6, 7))], 
                     levels = month.abb),
             Value = iVect)
iVectWithNAs <- as.numeric(as.matrix(spread(iDF, Year, Value)[-1]))             
missingAfter <- tsLength - length(iVectWithNAs)
iVectNew <- append(iVectWithNAs, rep(NA, missingAfter))
matrixIm <- rbind(matrixIm, iVectNew)
}
rownames(matrixIm) <- dfList
return(t(matrixIm))
}

and

getCounts <- function(dataObject, dateName, varName){ 
dataNameString <- deparse(substitute(dataObject))   
countsStr <- paste0("with(", dataNameString,", tapply(", varName,
 ", substr(", dateName,     ", 1, 7), sum))")
counts <- eval(parse(text = countsStr))
return(counts)
}

I make the countsMatrix into a hierarchical time series matrix using

myMTS <- ts(countsMatrix, start = c(2011, 1), frequency = 12) 
myHTS <- hts(myMTS, nodes = list(12, c(rep(2,12))))

and now for the output:

With direct sums:

> myTS
      Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
2011  186  229  193  437 1122 1298 1223  808  277  145  162  306
2012  224  247  306  494  896  895 1019  843  213  154  157  245
2013  128  121  202  264  846  646  570  492  183   78   77  184

As hierarchical time series:

> myHTS
Hierarchical Time Series 
3 Levels 
Number of nodes at each level: 1 12 24 
Total number of series: 37 
Number of observations per series: 36 
Top level series: 
      Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
2011  186  232  214  480 1144 1295 1182  781  313  162  226  365
2012  235  259  330  531  993  956 1030  852  230  165  153  237
2013  117  108  178  228  720  574  494  438  161   71   72  158

Shouldn't myTS and the top level series of myHTS be identical?

Why don't they agree?

I hope somebody can help me understand why they're not. I can't provide the original data, but I hope that by providing the code I can still get an answer.

Thanks in advance.

$\endgroup$
  • $\begingroup$ Interesting. But we probably can't help you without the actual data. Try cutting your problem down to isolate the issue. Does the problem persist if you cut your XLS down to just two shops? Just one kind of product? Just two time points? If you still have an inconsistency there, you could put in random data and then post a minimal example. Good luck. $\endgroup$ – Stephan Kolassa Feb 5 '15 at 9:53
  • $\begingroup$ If I take rowsums of my countsMatrix, I get the same total as the top level series, so the disagreement must be between how the monthly counts are created in the two approaches. $\endgroup$ – SiKiHe Feb 5 '15 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.