Aggregating Weekly Data to Monthly

[Jack Hays]

Suppose I have a time series that has weekly realizations, but I want to average each week of a given month into a single realization for that month. How would I go about doing that?

[Tom Scott]

Jack Hays provide your data or a sample of it using dataex to receive proper help

[Jack Hays]

Unfortunately, I have not made many posts on this forum, so I do not quite know what dataex is. The series is MNCCLAIMS off of St. Louis' Federal Reserve of Economic Data website. I've attached the csv file too if that helps. Otherwise, as you probably already know, you can use the freduse command in STATA.

[Andrew Musau]

Unfortunately, I have not made many posts on this forum, so I do not quite know what dataex is.

Click on "FAQ" at the top-left hand of this page. Here, you will find information on how to present data examples using dataex.

[Nick Cox]

Being new is fine, and everybody started that way. But the forum home page and the prompt for every new post enjoin everyone to read the FAQ Advice before posting, which you clearly didn't do.

The FAQ Advice also explains about using search first, and the results might include

SJ-12-4 dm0065_1 . . . . . Stata tip 111: More on working with weeks, erratum
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q4/12 SJ 12(4):765 (no commands)
lists previously omitted key reference

SJ-12-3 dm0065 . . . . . . . . . . Stata tip 111: More on working with weeks
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q3/12 SJ 12(3):565--569 (no commands)
discusses how to convert data presented in yearly and weekly
form to daily dates and how to aggregate such data to months
or longer intervals

SJ-10-4 dm0052 . . . . . . . . . . . . . . . . Stata tip 68: Week assumptions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q4/10 SJ 10(4):682--685 (no commands)
tip on Stata's solution for weeks and on how to set up
your own alternatives given different definitions of the
week

Hence dm0065 focuses on this problem.

The key details we need include:

1. How you are holding the dates, whether as a daily date (better) or as a Stata week (don't).

2. If as daily dates, what do the daily dates mean: do they define the beginning of the week or the end of the week or what?

3. Are you content with a crude average of those weeks whose reference dates fall within a month, or do you want to split weeks at the beginning and end of each month and apply a weighted average? Clearly weeks map cleanly to months if and only if it is a February in a non-leap year AND (the beginning of each week is 1, 8, 15, 22 February or equivalently the end of each week is 7, 14, 21, 28 February) -- in short only rarely.

[Jack Hays]

My apologies about my lack of etiquette. I have read through your STATA tip 111, however, I did not really find what I wanted. To answer your questions:

1. I am holding the dates as a daily date

2. Each daily date is the end of the given week. That is, each daily date is a Saturday

3. I was advised to use an average, as using things like MIDAS, or something of the like, was claimed to be overly challenging for the given task. So, if the week falls within a month, I want that week's value to be included in the average for the month it is in.

[Nick Cox]

The week falling within a month is ambiguous as it says nothing about exactly what you want to do with part weeks. Despite Andrew Musau's clear prompt you don't yet use dataex.

Here is a fake example using the simplest procedure that may help.

clear 
set obs 13 

gen week = mdy(1,5,2020) + (_n- 1) * 7 
format week %td 

gen y = _n 

gen month = mofd(week)
format month %tm 

egen wanted = mean(y), by(month)

list, sepby(month)

     +----------------------------------+
     |      week    y    month   wanted |
     |----------------------------------|
  1. | 05jan2020    1   2020m1      2.5 |
  2. | 12jan2020    2   2020m1      2.5 |
  3. | 19jan2020    3   2020m1      2.5 |
  4. | 26jan2020    4   2020m1      2.5 |
     |----------------------------------|
  5. | 02feb2020    5   2020m2      6.5 |
  6. | 09feb2020    6   2020m2      6.5 |
  7. | 16feb2020    7   2020m2      6.5 |
  8. | 23feb2020    8   2020m2      6.5 |
     |----------------------------------|
  9. | 01mar2020    9   2020m3       11 |
 10. | 08mar2020   10   2020m3       11 |
 11. | 15mar2020   11   2020m3       11 |
 12. | 22mar2020   12   2020m3       11 |
 13. | 29mar2020   13   2020m3       11 |
     +----------------------------------+

[Jack Hays]

My apologies once again. Next time I post a question I will ensure that it is well specified and it has what is needed to duplicate my code. I will say, your example has helped me get to where I needed with my data, so thanks a bunch, professor!