R从带有时间字段的日期开始按小时汇总数据帧 [英] R aggregate a dataframe by hours from a date with time field

问题描述

我对R比较陌生，但是我对Excel和T-SQL非常熟悉.

I'm relatively new to R but I am very familiar with Excel and T-SQL.

我有一个简单的数据集，该数据集带有一个带时间的日期和一个与之关联的数字值.我想做的是按小时汇总数字值.我发现了一些用于在R中使用时间类型的资源，但我希望找到一种与excel提供的解决方案类似的解决方案(在这里我可以调用函数并传入我的日期/时间数据，并使其返回小时数)一天).

I have a simple dataset that has a date with time and a numeric value associated it. What I'd like to do is summarize the numeric values by-hour of the day. I've found a couple resources for working with time-types in R but I was hoping to find a solution similar to is offered excel (where I can call a function and pass-in my date/time data and have it return the hour of the day).

任何建议将不胜感激-谢谢！

Any suggestions would be appreciated - thanks!

推荐答案

library(readr)
library(dplyr)
library(lubridate)


df <- read_delim('DateTime|Value
3/14/2015 12:00:00|23
3/14/2015 13:00:00|24
3/15/2015 12:00:00|22
3/15/2015 13:00:00|40',"|")

df %>% 
  mutate(hour_of_day = hour(as.POSIXct(strptime(DateTime, "%m/%d/%Y %H:%M:%S")))) %>% 
  group_by(hour_of_day) %>% 
  summarise(meanValue = mean(Value))

细分:

将DateTime(字符)列转换为格式化时间，然后使用lubridate中的hour()提取该小时值，并将其放入名为hour_of_day的新列中.

breakdown:

Convert column of DateTime (character) into formatted time then use hour() from lubridate to pull out just that hour value and put it into new column named hour_of_day.

> df %>% 
       mutate(hour_of_day = hour(as.POSIXct(strptime(DateTime, "%m/%d/%Y %H:%M:%S"))))
Source: local data frame [4 x 3]

            DateTime Value hour_of_day
1 3/14/2015 12:00:00    23          12
2 3/14/2015 13:00:00    24          13
3 3/15/2015 12:00:00    22          12
4 3/15/2015 13:00:00    40          13

group_by(hour_of_day)设置通过summarise(...)调用在其中计算mean(Value)的组.

The group_by(hour_of_day) sets the groups upon which mean(Value) is computed in the via the summarise(...) call.

这给出结果:

  hour_of_day meanValue
1          12      22.5
2          13      32.0

这篇关于R从带有时间字段的日期开始按小时汇总数据帧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！