Converting half hourly data into hourly

[Pippa Lange]

I am trying to convert data from half hourly to hourly. The trading periods occur every half hour hence there are 48 but I am trying to convert it to just hourly. I want 24 trading periods where the new trading period 1 would be the sum of 1 and 2, the new 2 would be the sum of 3 and 4 etc. The format is as follows:

Date tradingperiod_halfhourly TP
1/4/2023 1 2413
1/4/2023 2 3715
1/4/2023 3 1682
1/4/2023 4 3272
etc....

I aim aiming for
Date tradingperiod_hourly TP
1/4/2023 1 6128
1/4/2023 2 4954
etc....

[Andrew Musau]

The idea is to get a function that will map 1 and 2 to 1, 3 and 4 to 2, and so on for the trading period variable. The ceiling function will do exactly this.

help ceil()

Then you just collapse the data.

clear
input str10 Date float(tradingperiod_halfhourly TP)
"1/4/2023" 1 2413
"1/4/2023" 2 3715
"1/4/2023" 3 1682
"1/4/2023" 4 3272
end

gen tradingperiod = ceil(trad/2)
collapse (sum) TP, by(Date tradingperiod)

Res.:

. l

     +----------------------------+
     |     Date   tradin~d     TP |
     |----------------------------|
  1. | 1/4/2023          1   6128 |
  2. | 1/4/2023          2   4954 |
     +----------------------------+

If periods do not start at 1 and are not consecutive within Date, start with

bys Date: egen period= group(tradingperiod_half)

and use "period" in place of "tradingperiod_halfhourly" in the code above. This assumes that you just want to combine any 2 successive half-hour observations. If the values 1, 2, ..., mean something (e.g., 1 = 8:30-9:00, 2 = 9:00-9:30, and so on) and you want to preserve this information so that each hour represents a specific time period, then leave the values as they are.

[Nick Cox]

I don't know if floor and ceiling now appear in say high school or early college mathematics courses, but they are extraordinarily useful functions that are natural in coding.

Some further resources:

SJ-3-4 dm0002 . . . . . . . . Stata tip 2: Building with floors and ceilings
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q4/03 SJ 3(4):446--447 (no commands)
tips for using floor() and ceil()

https://journals.sagepub.com/doi/pdf...867X0400300413

SJ-11-3 dm0058 . . . . . . . . Speaking Stata: Fun and fluency with functions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q3/11 SJ 11(3):460--471 (no commands)
a tour of easily missed or underestimated Stata functions

https://journals.sagepub.com/doi/pdf...867X1101100308

SJ-18-3 dm0095 . . . . . . . . . . . Speaking Stata: From rounding to binning
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q3/18 SJ 18(3):741--754 (no commands)
basic review of how to bin variables in Stata, meaning how to
divide their range or support into disjoint intervals

https://journals.sagepub.com/doi/pdf...867X1801800311