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.