如何按月汇总数据并将其存储在该月的第一天 [英] how to sum data by month and store them in the first day of the month

问题描述

让我们说我有如下数据框：

Lets say I have a data frame as follows:

gageID    date        flow_cms
1011000 1937-02-19   25.768334
1011000 1937-02-20   24.918828
1011000 1937-02-21   24.069322

<我想汇总具有相同月份的行的总和，并将
的结果存储到一个新的数据值中：每个月的第一天；以获得以下输出：

I want to aggregate the rows that have the same month summing the flow, and store the result into a new data value: the first day of every month; in order to obtain the following output:

gageID  date  sum_monthly_flow
1011000 1937-02-01  500.2222
1011000 1937-03-01  589.222

我正在使用此行：

>rowsum(qfile$flow_cms, format(qfile$date, '%Y-%m-01'))

，我获得了正确的总和，但我也想减少
a唯一天的记录天数：每月的第一天！有了上面显示的带，R不能
将左列识别为数据（或日期）。

and I obtain the right sum, but I want also to reduce the record days in a unique day: the first of every month! with the strip shows above, R cannot recognize the left coloumn as data (or date).

非常感谢您的帮助！

推荐答案

首先请确保您的日期列已正确设置为R中的日期对象：

First make sure your "date" column is properly formatted as a date object in R:

qfile$date <- as.Date(qfile$date, format = "%Y-%m-%d")

然后我们可以使用格式提取月份和年份，并 group_by 求和并取第一个日期：

Then we can use format to extract the month and year, and group_by that for a sum and take the first date:

library(dplyr)
qfile %>% mutate(monthyear = as.character(format(date, "%m-%Y"))) %>%
          arrange(date) %>% 
          group_by(monthyear) %>%
          summarise(date=date[1], flow = sum(flow_cms))

这将为您提供数据中每个月的第一个记录。

This will give you the first taken record for each month in the data.

这篇关于如何按月汇总数据并将其存储在该月的第一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！