find first value of a day

Question

I am trying to find the first value of the day.

I have the following dataframe

time_str = c('2018-03-19 17:00:18', '2018-03-19 18:00:18', '2018-03-19 19:00:18', '2018-03-20 00:01:18', '2018-03-20 08:00:18', '2018-03-21 02:00:18', '2018-03-21 09:00:18')
value= c(1,2,1,3,4,5,9)

df=data.frame(time_str, value)

The result should be as dataframe that looks like this:

             time_str value
1 2018-03-19 17:00:18     1
2 2018-03-20 00:01:18     3
3 2018-03-21 02:00:18     5

Does this answer your question? [Extract row corresponding to minimum value of a variable by group](https://stackoverflow.com/questions/24070714/extract-row-corresponding-to-minimum-value-of-a-variable-by-group) — andschar, Apr 16 '21 at 08:52

score 2 · Answer 1 · answered Apr 16 '21 at 08:13

Change time_str to POSIXct type and for each date select the row with earliest time.

library(dplyr)

df %>%
  mutate(time_str = as.POSIXct(time_str, 'UTC'), 
         date = as.Date(time_str)) %>%
  group_by(date) %>%
  slice(which.min(time_str)) %>%
  ungroup %>%
  select(-date)

#  time_str            value
#  <dttm>              <dbl>
#1 2018-03-19 17:00:18     1
#2 2018-03-20 00:01:18     3
#3 2018-03-21 02:00:18     5

score 1 · Answer 2 · answered Apr 16 '21 at 08:50

1

With R data.table you could do:

require(data.table)

# set to data.table
setDT(df)
# IDate data type
df[ , date := as.IDate(time_str) ]
# select minimum
df[ , .SD[which.min(date)], date ]

answered Apr 16 '21 at 08:50

andschar

3,504
2
27
35

Chris Ruehlemann · Answer 3 · 2021-04-16T09:03:16.963

You can first create a temporary variable containing just the day, then group_by that variable, and finally use slice_head with n = 1 to subset the dataframe on the first value:

library(dplyr)
df %>%
  mutate(temp = sub(" .*", "", time_str)) %>%
  group_by(temp) %>%
  slice_head(., n = 1)
A tibble: 3 x 3
# Groups:   temp [3]
  time_str            value temp      
  <chr>               <dbl> <chr>     
1 2018-03-19 17:00:18     1 2018-03-19
2 2018-03-20 00:01:18     3 2018-03-20
3 2018-03-21 02:00:18     5 2018-03-21

find first value of a day

3 Answers3