time series data; average per min/hour/day...; tscollap

[Christoph Winter]

Dear all,

I am currently working on my master thesis and I'm dealing with time series data. I am using Stata/IC 12.1 (tscollap and egenmore are installed).

I want to compute the average price per min/hour/day/week/month/year by id.
For example, if the price for id==3 is 1 for 20h of the day and 2 for 4h of the day, I want the day average to be (1*20+2*4)/24

I've tried tscollap:
...
gen double clock = clock(time,"YMDhms")
format clock %tc
sort id clock
tsset id clock, clocktime delta(1 sec)
tscollap price, to(y)

However, it doesn't work and Stata reports following error message:

The target frequency must be lower than the current frequency. r(198);

However, in my opinion target frequency is year and current freqency is seconds. So I don't understand the error message.


It would be great if some of you have ideas how to deal with that. Maybe there are other solutions than tscollap how to solve this problems....


Thank you for your help in advance!

Christoph



The structure of the data looks as follows:

id	case_id	clock	price
1	1	17.09.2014 11:10	1.508
2	1	17.09.2014 14:17	1.519
2	2	17.09.2014 21:00	1.529
2	3	18.09.2014 07:58	1.529
2	4	19.09.2014 09:00	1.529
2	5	19.09.2014 14:16	1.519
2	6	21.09.2014 20:56	1.529
3	1	17.09.2014 07:37	1.609
3	2	17.09.2014 10:12	1.589
3	3	17.09.2014 15:16	1.579
3	4	17.09.2014 16:26	1.569

[Christoph Winter]

In other words: I want to collapse my data by id and date, using the information of clock.

[Nick Cox]

I see no reason to suppose that tscollap (SSC; please indicate provenance as requested) supports aggregation of irregularly spaced time series, such as you have. However, so long as you define a time interval as a difference between times to use as weights and a year variable as target, this is just an orthodox application of collapse.

[Christoph Winter]

thanks a lot!