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.

Improve this question
$\endgroup$
2
  • $\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, 2015 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, 2015 at 10:56

2 Answers 2

Reset to default
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]] &]
Improve this answer
$\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

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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