Sorting with date and minimum time in R

Question

This task is difficult for me. I need to find the temp value for every one hour (minimum recorded time) of 30/31 days of a month. But, the sensor is measured the temp value at irregular periods (input file is attached as image). I want to write R code for this. Eg output:

1/6/2016 0.00 90.45
1/6/2016 1.01 92.54
1/6/2016 2.12 94.95

1/6/2016 21.53 95.85

A similar sample data frame:

sample <- data.frame( date = c(rep("2016-06-01", 13), NA, NA, rep("2016-06-01", 3), NA, NA, rep("2016-06-01", 3), NA, rep("2016-06-02", 2)), time = c("0:00", "0:10", "0:20", "0:30", "1:01", "1:11", "1:21", "1:31", "1:41", "1:51", "2:12", "2:42", "2:52", NA, NA, "12:03", "12:13", "12:23", NA, NA, "21:53", "21:54", "23:14", NA, NA, NA), temp = c(90.45, 91.29, 90.88, 91.22, 92.54, 92.57, 93.18, 93.9, 94.51, 94.37, 95.96, 95.32, 95.2, NA, NA, 95.37, 95.52, 95.35, NA, NA, 95.85, 95.6, 96.14, NA, NA, NA) )

If anyone please help of How to do with R programming

You can use `cut.POSIXct` to cut the time into hourly intervals, use that as grouping variable to find the minimum value — akrun, Dec 27 '16 at 06:25
Welcome to StackOverflow! Please read the info about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). This will make it much easier for others to help you. — Axeman, Dec 27 '16 at 15:08

score 1 · Accepted Answer · answered Dec 27 '16 at 14:48

1

Building on akrun's suggestion, here's a potential implementation using cut.POSIXct and dplyr:

library(dplyr)
 output <- 
  sample %>% # Using reproducible dataset above
  # Filter to only observed records
  filter(!is.na(date) & !is.na(time)) %>% 
  mutate(
    # Create a date_time using the date and time variables
    date_time = as.POSIXct(paste(date, time), 
                format="%Y-%m-%d %H:%M"),
    # Create hour intervals to be used as grouping variable 
         hour    = cut.POSIXt(date_time, breaks = "hour")) %>%
  # Group by hour
  group_by(hour) %>%
  # Select only records where the date and time are the minimum
  # date and time in the group
  filter(date_time == min(date_time))

I annotated the code -- there are definitely ways to make the code more concise and/or handle edge-cases like the empty records better, but this should correctly select the minimum date-time per hour.

answered Dec 27 '16 at 14:48

Adam Bethke

1,028
2
19
35

Thanks Adam. But I am newbie to R programming. Can you please elaborate the code in full, so that I can get the complete result. library(dplyr) setwd("C:/Users/Desktop/june") data<-read.csv("test.csv",stringsAsFactors = FALSE) Do I need to write your code after input file path. How to save the output with the sensor value... Kindly reply me. – Teenu Ss Jan 17 '17 at 05:03
No problem. Assuming that's how you're loading the non-sample data (as 'data'), you'd need to run the code to read the file in and then run the code I wrote switching out line three ('sample %>%' becomes 'data %>%') – Adam Bethke Jan 17 '17 at 22:40
library(dplyr) data <- read.csv("test.csv",stringsAsFactors = FALSE) output <- data %>% filter(!is.na(date) & !is.na(time)) %>% mutate( date_time = as.POSIXct(paste(date, time), format="%Y-%m-%d %H:%M"), hour = cut.POSIXt(date_time, breaks = "hour")) %>% group_by(hour) %>% filter(date_time == min(date_time)) – Adam Bethke Jan 18 '17 at 01:52
Thanks Adam so much...awesome – Teenu Ss Jan 18 '17 at 06:19
> output <- + data %>% + filter(!is.na(Date) & !is.na(Time)) %>% + mutate( + date_time = as.POSIXct(paste(Date, Time), + format="%Y-%m-%d %H:%M"), + hour = cut.POSIXt(date_time, breaks = "hour")) %>% + group_by(hour) %>% + filter(date_time == min(date_time)) – Teenu Ss Jan 18 '17 at 06:36
Error in mutate_impl(.data, dots) : 'to' cannot be NA, NaN or infinite In addition: Warning messages: 1: In min.default(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, : no non-missing arguments to min; returning Inf 2: In max.default(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, : no non-missing arguments to max; returning -Inf – Teenu Ss Jan 18 '17 at 06:36
Dear Adam. When I execute the last line, I am getting the above mentioned error – Teenu Ss Jan 18 '17 at 06:37
Quick glance, it looks like you've got one of three things: - a missing pipe; - missing (NA) values that aren't being handled by the filters; or - an incorrectly typed variable (eg. something is a string where it should be numeric (ex. [this post](http://stackoverflow.com/questions/34709973/error-in-seq-defaultfrom-minx-na-rm-true-to-maxx-na-rm-true)) – Adam Bethke Jan 18 '17 at 12:05

Sorting with date and minimum time in R

1 Answers1