CODEWITHSUNDEEP
rstatisticsdata.tablemedian
user11325078
user11325078

Reputation:

How to calculate the median for each minute of a dataframe?

I have a data frame (~15.000 lines), like:

time                   value
01-01-2019 08:09:25    5,3
01-01-2019 08:09:26    5,5
01-01-2019 08:09:27    6,1
...
01-01-2019 08:09:58    5,1
01-01-2019 08:09:59    5,4
01-01-2019 08:10:00    6,5
01-01-2019 08:10:01    5,2
01-01-2019 08:10:02    6,2
01-01-2019 08:10:03    5,4
...

In addition, there are missing ~ 10 lines. That means sometimes there are only 59 seconds in one minute. I do not know if that plays a role.

I found this online, but it didn't work:

library(lubridate)
dd[, c('Hour', 'Minute') := .(datastrom::hour(zeit), minute(zeit))
][, .(Avg = mean(strom)), .(Hour, Minute)]

I need the median for each minute. It would be really nice, if somebody could help me!

Expected result:

time                   value
01-01-2019 08:09       5,4 (=median of all values between 08:09:00 and 08:09:59)
01-01-2019 08:10       5,8 (=median of all values between 08:10:00 and 08:10:59)
...

Upvotes: 0

Views: 111

Answers (3)

user11325078
user11325078

Reputation:

How can I program a loop so that all eight tables are calculated one after the other?

The code:

dt_M1_I <- M1_I
dt_M1_I <- data.table(dt_M1_I)
dt_M1_I[,I:=as.numeric(gsub(",",".",I))]
dt_M1_I[,day:=substr(t,1,10)]
dt_M1_I[,hour:=substr(t,12,16)]
dt_M1_I_median <- dt_M1_I[,list(median_I=median(I,na.rm = TRUE)),by=.(day,hour)]

This should be calculated for:

M1_I
M2_I
M3_I
M4_I
M1_U
M2_U
M3_U
M4_U

Thank you very much for your help!

Upvotes: 0

sebastiann
sebastiann

Reputation: 163

I made a reproducible example...

library(data.table)
dd <- read.table(text="time                   value
'01-01-2019 08:09:25'    5,3
'01-01-2019 08:09:26'    5,5
'01-01-2019 08:10:00'    6,5
'01-01-2019 08:10:01'    5,2
'01-01-2019 08:10:02'    6,2
'01-01-2019 08:10:03'    5,4", header =TRUE)#
dd <- data.table(dd)
dd[,value:=as.numeric(gsub(",",".",value))]
dd[,day:=substr(time,1,10)]
dd[,hour:=substr(time,12,16)]
## only listing the median
dd[,list(median_value=median(value,na.rm = TRUE)),by=.(day,hour)]
## or an new data table
dd_median <- dd[,list(median_value=median(value,na.rm = TRUE)),by=.(day,hour)]

Just make a data.table (dd) of your data frame.

Upvotes: 0

lebatsnok
lebatsnok

Reputation: 6469

if your time is something like POSIXct then the following sould work:

with(mydata, tapply(value, cut(time, "mins"), median))

See ?cut.POSIXt

Upvotes: 1

Related Questions