# how to make this time series reguarly spaced

in the picture below we have in the first coloumn the day of the month, in the second coloumn the time in millisecond in epoch time(elapsed from 1 january 1970), third coloumn the stock price and in the last coloumn the volume traded. I want to make this dataset equally spaced with a frequency of 5 minutes using previous tick interpolation. My problem is that the time is in epoch time and I don't know how to apply previous tick interpolation such that any 5 minutes we take the trading instants and the relative price . Moreover in the dataset as displayed in the picture below we have time repetition due to the fact that at the same instants there is more than one transaction. Can someone help to implement it in R or MATLAB?

Being tick data the picture displays only a small parts of the transactions in a trading day

Here is an example how you can do it in R (without any packages).

Create a regular grid to which to map your data.

grid <- seq(from = as.POSIXct("2014-01-31 10:00:00"),
to   = as.POSIXct("2014-01-31 23:55:00"),
by = "5 min")
## [1] "2014-01-31 10:00:00 CET" "2014-01-31 10:05:00 CET"
## [3] "2014-01-31 10:10:00 CET" "2014-01-31 10:15:00 CET"
## [5] "2014-01-31 10:20:00 CET" "2014-01-31 10:25:00 CET"
## ....


Some example timestamps, taken from your example. R's POSIXct datetimes are internally the number of seconds since 1 Jan 1970, so dividing by 1000 makes your timestamps compatible with R's.

t <- c(1391160600000/1000, ## "2014-01-31 10:30:00 CET"
1391160600129/1000, ## "2014-01-31 10:30:00 CET"
1391160637368/1000, ## "2014-01-31 10:30:37 CET"
1391160676851/1000, ## "2014-01-31 10:31:16 CET"
1391183999289/1000, ## "2014-01-31 16:59:59 CET"
1391184172441/1000) ## "2014-01-31 17:02:52 CET"


It remains to map the t to the grid.

i <- findInterval(grid, t)
good <- i > 0

x <- rep(NA, length(grid))
x[good]  <- .POSIXct(t[i[good]])
data.frame(your.time = .POSIXct(x), grid)
##               your.time                grid
## 1                  <NA> 2014-01-31 10:00:00
## 2                  <NA> 2014-01-31 10:05:00
## 3                  <NA> 2014-01-31 10:10:00
## 4                  <NA> 2014-01-31 10:15:00
## 5                  <NA> 2014-01-31 10:20:00
## 6                  <NA> 2014-01-31 10:25:00
## 7   2014-01-31 10:30:00 2014-01-31 10:30:00
## 8   2014-01-31 10:31:16 2014-01-31 10:35:00
## 9   2014-01-31 10:31:16 2014-01-31 10:40:00
## ....
## 83  2014-01-31 10:31:16 2014-01-31 16:50:00
## 84  2014-01-31 10:31:16 2014-01-31 16:55:00
## 85  2014-01-31 16:59:59 2014-01-31 17:00:00
## 86  2014-01-31 17:02:52 2014-01-31 17:05:00
## 87  2014-01-31 17:02:52 2014-01-31 17:10:00


Now additionally to timestamps, map price etc, by subsetting the original series with i[good]. Suppose you have the following (made-up) prices that go with your timestamps.

price <- seq_along(t)
data.frame(time = .POSIXct(t), price)
##                  time price
## 1 2014-01-31 10:30:00     1
## 2 2014-01-31 10:30:00     2
## 3 2014-01-31 10:30:37     3
## 4 2014-01-31 10:31:16     4
## 5 2014-01-31 16:59:59     5
## 6 2014-01-31 17:02:52     6


Mapped:

grid.price <- rep(NA, length(grid))
grid.price[good]  <- price[i[good]]
data.frame(grid, grid.price)
##                    grid grid.price
## 1   2014-01-31 10:00:00         NA
## 2   2014-01-31 10:05:00         NA
## 3   2014-01-31 10:10:00         NA
## 4   2014-01-31 10:15:00         NA
## 5   2014-01-31 10:20:00         NA
## 6   2014-01-31 10:25:00         NA
## 7   2014-01-31 10:30:00          1
## 8   2014-01-31 10:35:00          4
## 9   2014-01-31 10:40:00          4
## ....
## 83  2014-01-31 16:50:00          4
## 84  2014-01-31 16:55:00          4
## 85  2014-01-31 17:00:00          5
## 86  2014-01-31 17:05:00          6
## 87  2014-01-31 17:10:00          6


# Utilizing the highfrequency package:

If you are willing to perform most of your data pre-processing and modelling in R, I strongly suggest familiarizing yourself with the highfrequency package. The package offers versatile tools for both cleaning your raw high-frequency data and effectively modeling and managing it thereafter. The package comes with a guide on how to clean and model high-frequency data using the package. Here is a link to the package documentation.

I have presented a simple example where I utilize the aggregateTS function from the package to get a 10-second sampling scheme using previous-tick interpolation. The same function can be used for any type of calendar-based sampling scheme (seconds, minutes, hours).

library(highfrequency)
library(dplyr)
library(data.table)

# See dataframe (df) below.

# Convert raw times to posixct
df$$DT <- as.POSIXct(df$$DT/1000, format = '01-01-1970')

####### Simple aggregate function using dplyr. #######
#1. Grouping by IntradayTime eg. 10:30:01.
#2. Calculate simple Median over prices and volumes
#   (other aggregation methods are also welcome).
#3. Selecting only needed columns.
#4. Converting back to data.table.

df2 <- df %>% mutate(IntradayTime = format(DT, '%T')) %>%
summarize(DT = last(DT), PRICE = median(PRICE), SIZE = median(SIZE)) %>%
select(DT, PRICE, SIZE) %>% data.table()

# Using the aggregateTS from the highfrequency package.
# Using 10-second frequency as an example:
agg <- aggregateTS(df2, FUN = "previoustick",alignBy = "seconds",alignPeriod = 10)


Giving you the corresponding result:

> agg
DT PRICE    SIZE
<POSc> <num>   <num>
1: 2014-01-31 10:30:00 37395 17267.5
2: 2014-01-31 10:30:10 37380     2.0
3: 2014-01-31 10:30:20 37380     2.0
4: 2014-01-31 10:30:30 37210   230.0
5: 2014-01-31 10:30:40 37215   150.0
---
2337: 2014-01-31 16:59:20 37240   100.0
2338: 2014-01-31 16:59:30 37240   100.0
2339: 2014-01-31 16:59:40 37240   100.0
2340: 2014-01-31 16:59:50 37240   100.0
2341: 2014-01-31 17:00:00 37400   100.0


This answer aims to give you some exposure to the package, as it can be incredibly useful when working with high-frequency data.

### The dataframe used in the above example

# Convert df to data.table: No need for the day of the month.
# Column names follow the structure of the highfrequency package.
df <- data.table(
DT = c(
1391160600129, 1391160600243, 1391160601160, 1391160601160, 1391160601599, 1391160605189, 1391160622539,
1391160622539, 1391160623157, 1391160623450, 1391160623458, 1391160626173, 1391160634087, 1391160637108,
1391160637368, 1391160644507, 1391160644757, 1391160644757, 1391160650867, 1391160650867, 1391160667192,
1391160676851, 1391160676851, 1391160688360, 1391183999208, 1391183999278, 1391183999289
),
PRICE = c(
37400, 37390, 37350, 37340, 37290, 37380, 37300,
37290, 37300, 37290, 37250, 37210, 37190, 37210, 37220,
37240, 37200, 37180, 37170, 37210, 37210, 37230, 37230, 37240, 37400,
37400, 37390
),
SIZE = c(
34435, 100, 100, 100, 200, 2, 10, 90, 211, 10, 193, 230, 100, 200,
100, 100, 200, 100, 100, 100, 103, 250,
50, 100, 100, 114, 85
)
)