R aggregate time series data at fixed time period with different aggregation on different columns

Question

With a data frame that holds time series data and need to perform aggregations on it.

text <- "
Time,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13
05:17:55.703,,,,,,21,,3,    89,891,11,
05:17:55.703,,,,,,21,,3,   217,891,12,
05:17:55.703,,,,,,21,,3,   217,891,13,
05:17:55.703,,,,,,21,,3,   217,891,15,
05:17:55.703,,,,,,21,,3,   217,891,16,
05:17:55.703,,,,,,21,,3,   217,891,17,
05:17:55.703,,,,,,21,,3,   217,891,18,
05:17:55.707,,,,,,18,,3,   185,892,0,
05:17:55.707,,,,,,21,,3,   185,892,1,
05:17:55.707,,,,,,17,,3,    73,892,5,
05:17:55.707,,,,,,17,,3,   185,892,6,
05:17:55.707,,,,,,21,,3,    73,892,7,
05:17:55.708,268,4,28,-67.60,13,,2,,,,,2
05:17:55.711,,,,,,18,,3,    57,892,10,
05:17:55.711,,,,,,21,,3,   201,892,11,
05:17:55.711,,,,,,21,,3,    25,892,12,
05:17:55.723,,,,,,21,,3,   217,893,11,
05:17:55.723,,,,,,21,,3,   217,893,15,
05:17:55.723,,,,,,21,,3,   217,893,16,
05:17:55.726,268,4,,-67.80,,,,,,,,
05:17:55.728,,,28,,12,31,2,3,   185,894,0,1
05:17:55.728,,,,,,31,,3,   185,894,1,
05:17:55.731,,,,,,31,,3,   217,894,10,
05:17:55.731,,,,,,20,,3,   217,894,11,
05:17:55.731,,,,,,20,,3,   217,894,12,
05:17:55.731,,,,,,20,,3,   217,894,13,
05:17:55.743,,,,,,20,,3,   217,895,11,
05:17:55.743,,,,,,20,,3,   217,895,15,
05:17:55.743,,,,,,20,,3,   217,895,16,
05:17:55.746,268,4,,-67.82,,,,,,,,
05:17:55.747,,,28,,13,20,2,3,   185,896,1,2
05:17:55.747,,,,,,20,,3,   185,896,2,
05:17:55.747,,,,,,30,,3,   217,896,5,
05:17:55.751,,,,,,18,,3,   217,896,10,
05:17:55.751,,,,,,21,,3,   217,896,11,
05:17:55.751,,,,,,21,,3,   217,896,12,
05:17:55.751,,,,,,21,,3,   217,896,13,
05:17:55.763,,,,,,31,,3,   217,897,11,
05:17:55.763,,,,,,30,,3,   217,897,15,
05:17:55.763,,,,,,20,,3,   217,897,16,
05:17:55.763,,,,,,20,,3,   217,897,17,
05:17:55.766,268,4,,-67.13,,,,,,,,
05:17:55.768,,,28,,12,20,2,3,   185,898,3,2
05:17:55.768,,,,,,16,,3,   217,898,6,
05:17:55.771,,,,,,18,,3,   217,898,10,
05:17:55.771,,,,,,20,,3,   217,898,11,
05:17:55.771,,,,,,20,,3,   217,898,12,
05:17:55.784,,,,,,20,,3,   217,899,11,
05:17:55.784,,,,,,20,,3,    41,899,12,
05:17:55.784,,,,,,20,,3,    25,899,13,
05:17:55.784,,,,,,20,,3,   217,899,15,
05:17:55.784,,,,,,20,,3,   217,899,16,
05:17:55.784,,,,,,20,,3,   217,899,17,
05:17:55.784,,,,,,20,,3,   217,899,18,
05:17:55.786,268,4,,-67.66,,,,,,,,
05:17:55.788,,,28,,13,18,2,3,   185,900,0,2
05:17:55.788,,,,,,20,,3,   185,900,1,
05:17:55.788,,,,,,20,,3,   185,900,2,
05:17:55.788,,,,,,16,,3,    41,900,5,
05:17:55.788,,,,,,17,,3,   185,900,6,
05:17:55.791,,,,,,20,,3,   105,900,7,
05:17:55.791,,,,,,20,,3,    89,900,8,
05:17:55.791,,,,,,18,,3,   217,900,10,
05:17:55.791,,,,,,20,,3,   217,900,11,
05:17:55.791,,,,,,20,,3,    25,900,12,
05:17:55.806,268,4,,-67.50,,,,,,,,
05:17:55.808,,,28,,12,31,2,3,   185,902,0,1
05:17:55.808,,,,,,31,,3,   185,902,1,
05:17:55.808,,,,,,20,,3,    25,902,2,
05:17:55.808,,,,,,20,,3,    25,902,3,
05:17:55.808,,,,,,16,,3,   217,902,5,
05:17:55.808,,,,,,16,,3,   217,902,6,
05:17:55.811,,,,,,20,,3,    89,902,7,
05:17:55.811,,,,,,20,,3,   121,902,8,
05:17:55.811,,,,,,18,,3,   217,902,10,
05:17:55.811,,,,,,20,,3,   217,902,11,
05:17:55.811,,,,,,20,,3,    73,902,12,
05:17:55.811,,,,,,20,,3,     9,902,15,
05:17:55.815,,,,,,20,,3,   217,902,16,
05:17:55.815,,,,,,20,,3,    25,902,17,
05:17:55.815,,,,,,20,,3,   217,902,18,
05:17:55.815,,,,,,18,,3,   217,903,0,
05:17:55.815,,,,,,21,,3,   217,903,1,
05:17:55.815,,,,,,19,,3,   105,903,2,
05:17:55.815,,,,,,21,,3,    41,903,3,
05:17:55.823,,,,,,21,,3,   217,903,11,
05:17:55.823,,,,,,21,,3,     9,903,12,
05:17:55.823,,,,,,21,,3,   105,903,13,
05:17:55.823,,,,,,21,,3,   217,903,15,
05:17:55.823,,,,,,21,,3,   217,903,16,
05:17:55.823,,,,,,21,,3,   121,903,17,
05:17:55.823,,,,,,21,,3,    89,903,18,
05:17:55.826,268,4,,-67.51,,,,,,,,
05:17:55.828,,,28,,12,18,2,3,   185,904,0,1
05:17:55.828,,,,,,21,,3,   185,904,1,
05:17:55.828,,,,,,21,,3,   185,904,2,
05:17:55.828,,,,,,21,,3,   185,904,3,
05:17:55.828,,,,,,17,,3,   217,904,5,
05:17:55.828,,,,,,17,,3,   217,904,6,
05:17:55.831,,,,,,21,,3,   217,904,7,
05:17:55.831,,,,,,20,,3,   169,904,11,
05:17:55.831,,,,,,20,,3,   217,904,12,
05:17:55.831,,,,,,20,,3,   217,904,13,
05:17:55.846,268,4,,-67.01,,,,,,,,
05:17:55.848,,,28,,13,19,2,3,    57,906,1,2
05:17:55.848,,,,,,19,,3,    41,906,2,
05:17:55.848,,,,,,19,,3,    73,906,3,
05:17:55.848,,,,,,16,,3,   217,906,5,
05:17:55.848,,,,,,16,,3,   217,906,6,
05:17:55.848,,,,,,19,,3,     9,906,7,
05:17:55.851,,,,,,20,,3,   121,906,11,
05:17:55.851,,,,,,20,,3,    57,906,12,
05:17:55.851,,,,,,20,,3,   105,906,13,
05:17:55.855,,,,,,20,,3,   217,906,15,
05:17:55.855,,,,,,20,,3,   217,906,16,
05:17:55.855,,,,,,20,,3,   105,906,17,
05:17:55.855,,,,,,17,,3,   185,907,0,
05:17:55.855,,,,,,20,,3,   217,907,1,
05:17:55.855,,,,,,20,,3,     9,907,2,
"
df <- read.table(textConnection(text), sep=",", header = T)

The data has Time col

There could be multiple rows with the same time stamp
Not all rows will have all the column values as they come from different sources
Need to create an aggregate every 40ms for values in Col2 to Col13
Each Col may have a different aggregation rule ignoring NAs

Like below

Col2, Col3 - mode (most occurring, if there is a tie - either would be fine)
Col4, Col6, Col7, Col8, Col13 - median
Col5, Col9, Col10 - mean
Col11, Col12 - the value from nearest previous timestamp relative to the bin timestamp

Output expected

+--------------+------+------+------+---------+------+------+------+------+--------+-------+-------+-------+
|     Time     | Col2 | Col3 | Col4 |  Col5   | Col6 | Col7 | Col8 | Col9 | Col10  | Col11 | Col12 | Col13 |
+--------------+------+------+------+---------+------+------+------+------+--------+-------+-------+-------+
| 05:17:55.740 |  268 |    4 |   28 |   -67.7 | 12.5 |   21 |    2 |    3 | 177.67 |   894 |    13 |   1.5 |
| 05:17:55.780 |  268 |    4 |   28 | -67.475 | 12.5 |   20 |    2 |    3 |  212.2 |   898 |    12 |     2 |
| 05:17:55.820 |  268 |    4 |   28 |  -67.58 | 12.5 |   20 |    2 |    3 | 144.56 |   903 |    11 |   1.5 |
| 05:17:55.860 |  268 |    4 |   28 |  -67.26 | 12.5 |   20 |    2 |    3 | 155.06 |   907 |     2 |   1.5 |
+--------------+------+------+------+---------+------+------+------+------+--------+-------+-------+-------+

If you say every 40ms why is it then 740ms, 780ms ... and not 720ms, 760ms? Col11 and Col12: do you want the last value in the bin? Or the last from the previous bin? — domaeg
Its the right boundary from data domain perspective. For Col11 and Col12 the last value from the previous bin — user3206440

domaeg domaeg · Accepted Answer · 2019-09-19T17:52:44

You can do the following:

calculate the ms bins
calculate each of the aggragations
bring all results together

something like this:

# mode function from 
# https://www.tutorialspoint.com/r/r_mean_median_mode.htm
getmode <- function(v) {
  # added to omit NAs
  v <- na.omit(v) 
  uniqv <- unique(v)
  uniqv[which.max(tabulate(match(v, uniqv)))]
}


# data frame as basis for calculations
df2 <- df %>% 
  mutate(MS = as.numeric(str_extract(Time, "(?<=\\.).*")),
         time_HMS = str_extract(Time, ".*(?=\\.)")) %>%
  # calculate the 40ms bins with an offset of 20ms wherever the offset comes from
  mutate(MS_40 = (MS+20) %/% 40 * 40 + 20 ) %>% 
  group_by(time_HMS, MS_40)

# calculate the mode cols
df_mode <- df2  %>% 
  summarise_at(.vars = vars(Col2, Col3), .funs = getmode) %>% 
  ungroup()

# calculate the median cols
df_median <- df2 %>% 
  summarise_at(.vars = vars(Col4, Col6:Col8, Col13), .funs = ~median(., na.rm = T)) %>% 
  ungroup()

# calculate the mean cols
df_mean <- df2 %>% 
  summarise_at(.vars = vars(Col5, Col9, Col10), .funs = ~mean(., na.rm = T)) %>% 
  ungroup()

# calculation for col 11 and 12
df_mx_prev <- df2 %>% 
  summarise_at(.vars = vars(Col11, Col12), .funs = ~last(.)) %>% 
  ungroup() %>% 
  mutate(Col11_l = lag(Col11), Col12_l = lag(Col12))

# bring all together
df_res <- df2 %>%
  ungroup() %>% 
  select(time_HMS, MS_40) %>% 
  distinct()  %>% 
  mutate(Time = paste(time_HMS, MS_40, sep = ".")) %>% 
  left_join(df_mode) %>% 
  left_join(df_median) %>% 
  left_join(df_mean) %>% 
  left_join(df_mx_prev) %>% 
  select(Time, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13)

# 
#           Time Col2 Col3 Col4    Col5 Col6 Col7 Col8 Col9    Col10 Col11 Col12 Col13
# 1 05:17:55.740  268    4   28 -67.700 12.5   21    2    3 177.6667   894    13   1.5
# 2 05:17:55.780  268    4   28 -67.475 12.5   20    2    3 211.9474   898    12   2.0
# 3 05:17:55.820  268    4   28 -67.580 12.5   20    2    3 144.5556   903     3   1.5
# 4 05:17:55.860  268    4   28 -67.260 12.5   20    2    3 150.5000   907     2   1.5

Col10 seems different and Col11 and Col12 has the last value from the current bin.

R aggregate time series data at fixed time period with different aggregation on different columns

1 Answers