I have a data.frame with datetimes and values (between 0 and 1) and i would like to find the first occurence of value=1 per day.
df <- read.table(header = TRUE, text = '
Datetime Value
"2016-12-01 23:45:00" 0
"2016-12-01 23:50:00" 1
"2016-12-02 00:05:00" 1
"2016-12-02 00:10:00" 0
"2016-12-03 04:10:00" 0
"2016-12-03 04:15:00" 0
"2016-12-04 12:10:00" 1
"2016-12-04 12:15:00" 1
')
df$Datetime <- as.POSIXct(df$Datetime, "%Y-%m-%d %H:%M:%S", tz="UTC")
View(df)
What i would like to have is:
2016-12-01 23:50:00 1
2016-12-02 00:05:00 1
2016-12-04 12:10:00 1
I tried to solve the problem with match() and aggregate() but had no luck so far. Furthermore i was able to solve the problem with a for loop but it is was a) very slow and b) probably not the way it is meant to be.
An alternative with dplyr
:
library(dplyr)
df %>%
#group
group_by(as.Date(Datetime)) %>%
#select only those where value equals 1
filter(Value == 1) %>%
#get only the first row
slice(1) %>%
#ungroup
ungroup %>%
#select columns
select(Datetime, Value)
Ouput:
# A tibble: 3 x 2
Datetime Value
<time> <int>
1 2016-12-01 23:50:00 1
2 2016-12-02 00:05:00 1
3 2016-12-04 12:10:00 1
Or as per @Akrun 's comment:
df %>%
group_by(Date = as.Date(Datetime)) %>%
slice(which(Value==1)[1])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With