dplyr - Filter by minutes-CodePudding

I have a data as below and I want to filter every 30 minutes data

Date, V1, V2
1/1/2004 0:00, 0, 0
1/1/2004 0:30, 1, 1
1/1/2004 1:00, 2, 2
1/1/2004 1:30, 3, 3
1/1/2004 2:00, 4, 4
1/1/2004 2:30, 5, 5

so this my code

data$Date \<- as.POSIXlt(data$Date, format="%d/%m/%Y %H:%M")

bello <- data %>%
          mutate(month=format(Date,"%m"),year=format(Date,"%Y"),day=format(Date,"%d"), 
                 hour=format(Date, "%H"),minutes=format(Date,"M")) %>%
          filter(minutes == '~:30') %>%
          group_by(year,month,day) %>%
          summarise_at(vars(V1:V202),sum)

write.csv(bello,"Daily_Cmorph_2004.csv")

I want to filter every 30 minutes data

CodePudding user response：

You can use lubridate, like this:

library(lubridate)
data %>% 
  filter(minute(Date) == 30) %>% 
  group_by(year =year(Date), month=month(Date), day=day(Date)) %>% 
  summarize(across(V1:V202,sum))

Using the example data you provided, where there are only two V columns, and there is only one year/month/day group (so only one row), you will have something like:

   year month   day    V1    V2
  <dbl> <dbl> <int> <dbl> <dbl>
1  2004     1     1     9     9

CodePudding user response：

You should also show expected output for such small data. The other answer by langtang shows output of 1 row only filtered but in the data there are 3 such records 13 and 5 from V1 and V2.

Idea is correct to extract minutes from date data and simply filter: data recreated:

    df <- data.table(
  'Date' = c('1/1/2004 0:00','1/1/2004 0:30','1/1/2004 1:00','1/1/2004 1:30','1/1/2004 2:00','1/1/2004 2:30')
  ,V1 = c(0,1,2,3,4,5)
  ,V2 = c(0,1,2,3,4,5))

Now to get every 30 minutes data : either mutate a Minute column or directly in 1 line (using dmy_hm to format dates) :

df%>%
  filter(Date%>%lubridate::dmy_hm()%>%minute()==30)

yields

           Date V1 V2
1: 1/1/2004 0:30  1  1
2: 1/1/2004 1:30  3  3
3: 1/1/2004 2:30  5  5