Troubles with weekly data: reconciling weeks with months and years

Question

I am working with weekly data and I am running into troubles when analyzing them because of their inconsistent nature. By inconsistent nature, I mean that not every month is made of the same number of weeks so that some weeks span over two subsequent months and the same holds for months over subsequent years.

Just to give you an example let's suppose I have a week beginning on March 28 ending on April 3. And let's suppose I have just a figure for the whole week, while I should have two figures, one for each month.

The purpose of this dataset is to run some descriptive analysis, thus I need to be able to analyze data by weeks, (aggregated) by months and (aggregated) by years. And I would like to aggregate them properly, e.g. deaths across two months should be (as much as possible) imputed to each month correctly.

How would you proceed? Any technique better than the others?

Answer 1

Weekly data that correspond to measurements or reports made or issued on one day of the week are in my experience best handled as daily data spaced 7 days apart. Woe betide you if your software doesn't support that! At worst take the daily date (as days from some origin) and divide by 7 to get weekly dates that are successive integers.

If you need for some unstated reason to aggregate to months (and do avoid that unless under compulsion) then averaging the weeks that notionally fall in a given month may be adequate when averages are needed but splitting weeks between months is likely to be required when totals are needed. The problem now is that variously 3, 4, or 5 weeks may be assigned to each month. That variability gets in the way of tracking variations from month to month, which are of interest if you are interested in months at all.

So, aggregation means getting monthly totals. You need to split totals for weeks to do that; otherwise results will depend sensitively on how many weekly reports fall within each month. I've published on how to do it in Stata.

See especially Section 4 of this paper.

The principles are naturally identical across programmable software. It's most unlikely that you're the first person using your software to have this problem. I can't see that any trickery is needed beyond using the fractions 1/7 to 6/7 and their complements according to the number of days belonging to each month.

Aggregating months to quarters, half-years or years is then simple.