Finding the mean of timestamp data in r for time series data

Question

I am dealing with time series data my data captured is in Milliseconds format i.e 02/04/2018 07:45:00.1239. I want the mean of the same time stamp which contains only seconds.

DF

ID Time_Stamp                  A           B                 C
1  02/02/2018 07:45:00.123    123         567               434     
2   02/02/2018 07:45:00.456   123         678               678
3   02/02/2018 07:45:00.687   121         121               121
4   02/02/2018 07:45:01.123   112         2323              2323

As shown in the sample i have milliseconds data . I want mean of all the data of time 02/02/2018 07:45:00

My result data Frame should be:

 ID Time_Stamp                  A           B                 C
1  02/02/2018 07:45:01      mean(R1A:R4A)   mean(R1B:R4B)    mean(R1C:R4C)     
2  02/02/2018 07:45:02      mean(R4A:R8A)   mean(R4B:R8B)    mean(R4C:R8C)

I.e mean of the rows of that particular column.

Output should be:

ID Time_Stamp                A       B           C
1  02/02/2018 07:45:01     122.33  455.33       411

i.e mean of column A ,Rows 1:3(123,123,121), column B ,Rows 1:3(567,678,121),column C ,Rows 1:3(434,678,121)

Please help!!

Change the `15 min` to `1 min` and you ll get the interval you want — Sotos, Jun 06 '19 at 08:24

score 0 · Accepted Answer · answered Jun 06 '19 at 09:40

Since you want to do the aggregation per second, then the only thing you have to do is to convert to proper datetime and use it as your group variable, i.e.

df$grp <- as.POSIXct(paste(as.character(df$ID), as.character(df$Time_Stamp)), format = "%d/%m/%Y %H:%M:%OS")

aggregate(list(mean1 = df$A, mean2 = df$B, mean3 = df$C), list(df$grp), mean)

#              Group.1    mean1     mean2 mean3
#1 2018-02-02 07:45:00 122.3333  455.3333   411
#2 2018-02-02 07:45:01 112.0000 2323.0000  2323

Finding the mean of timestamp data in r for time series data

1 Answers1