0
$\begingroup$

I need to transform millisecond data of trades of a certain stock to (approximately) minute data. The data is of the form

data={
 {"DATE","TIME_M","SYM_ROOT","SYM_SUFFIX","PRICE"},
 {20140108,"9:00:00.028","SPY","",183.29},
 {20140108,"9:00:10.321","SPY","",183.29},
 {20140108,"9:00:11.728","SPY","",183.28},
 {20140108,"9:00:12.384","SPY","",183.28},
 {20140108,"9:00:13.168","SPY","",183.28},
 {20140108,"9:00:26.801","SPY","",183.3},
 {20140108,"9:00:26.805","SPY","",183.3},
 {20140108,"9:00:27.573","SPY","",183.3},
 {20140108,"9:00:27.573","SPY","",183.3},
 {20140108,"9:00:27.573","SPY","",183.3},
 {20140108,"9:00:27.573","SPY","",183.3},
 {20140108,"9:00:56.582","SPY","",183.3},
 {20140108,"9:01:20.300","SPY","",183.31}
 }

and so on. I think the best way to approach this problem is to select only the elements whose time stamp is closest to a whole minute for the given minute. For the list above, the output would hence be

{
{20140108,"9:00:00.028","SPY","",183.29},
{20140108,"9:00:56.582","SPY","",183.3}
}

, the first element roughly corresponding to 9:00 and the second one to 9:01. How could I do this most efficiently? This question might be somewhat related but it does not answer my question directly.

EDIT: To clarify, I originally wanted to select the observations closest to a whole minute for each minute, i.e. for each hour, I wanted to have 60 observations with the time interval interval between them equal to approximately one minute. But the idea suggested by @JohnMcGee is better so I will approach the problem in this way.

$\endgroup$
  • $\begingroup$ Yo need to transform (digest, process) all the lines or simply select the nearest to a round minute? Please edit your question to clarify. $\endgroup$ – rhermans Oct 9 '15 at 10:56
  • 3
    $\begingroup$ Statistically, it would be better to take the average (or median) over each minute. The idea would be to round the times to the nearest minute, then use GroupBy to separate the data into blocks, one for each minute. Then take the mean (or median) over each block. $\endgroup$ – John McGee Oct 9 '15 at 10:56
4
$\begingroup$

The following commands implement the idea in my comment above.

t2 = {Round@(3600*FromDMS[#[[2]]]), #[[5]]} & /@ Rest[t]

then

Median /@ GatherBy[t2, #[[1]] &]
$\endgroup$
3
$\begingroup$
dat = Rest@data;
time = (DateList /@ dat[[All, 2]])[[All, 4 ;;]];
datef[s_] := 
 FromDigits /@ Internal`PartitionRagged[IntegerDigits[s], {4, 2, 2}]
date = datef /@ dat[[All, 1]];
dt = MapThread[Join, {date, time}];

You can use TimeSeries functionality:

ts = TimeSeries[dat[[All, -1]], {dt}]
DateListPlot[{ts, TimeSeriesAggregate[ts, "Minute"], 
  TimeSeriesAggregate[ts, "Minute", Median]}, 
 PlotLegends -> {"Raw", "Mean", "Median"}]

enter image description here

$\endgroup$

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.