使用2个日期获取不同月份的天数 [英] Obtain number of days by different months using 2 dates

问题描述

我有一个数据集 input ，我想将其转换为数据集 output 。基本上，我试图操纵数据集，以便能够将2个日期之间的天数分成不同的月份。想知道是否可以在R中完成吗？

I have a dataset input and I want to convert it into dataset output. Basically I am trying to manipulate the dataset so that I am able to split the number of days between 2 dates into different months. Wondering if this can be done in R?

R代码用于创建下面的数据集以便于参考。

R code for creating the datasets below for easy reference.

library('data.table')
input=data.table(SerialNum=c(1,2),StartDate=c('28/01/2015','28/01/2015'),EndDate=c('03/02/2015','03/03/2015'))

#       SerialNum  StartDate    EndDate
#    1:         1 28/01/2015 03/02/2015
#    2:         2 28/01/2015 03/03/2015


output=data.table(SerialNum=c(1,1,2,2,2),
                  StartDate=c('28/01/2015','28/01/2015','28/01/2015','28/01/2015','28/01/2015'),
                  EndDate=c('03/02/2015','03/02/2015','03/03/2015','03/03/2015','03/03/2015'),
                  MMMYY=c('Jan15','Feb15','Jan15','Feb15','Mar15'),
                  Days=c(4,3,4,28,3))

#       SerialNum  StartDate    EndDate MMMYY Days
#    1:         1 28/01/2015 03/02/2015 Jan15    4
#    2:         1 28/01/2015 03/02/2015 Feb15    3
#    3:         2 28/01/2015 03/03/2015 Jan15    4
#    4:         2 28/01/2015 03/03/2015 Feb15   28
#    5:         2 28/01/2015 03/03/2015 Mar15    3

推荐答案

您可以通过在<$ c $中创建一个序列来实现c> StartDate 到 EndDate 并从中提取一个月份年份变量（在 mnth 下面的示例）。接下来，您用 serialNum 和新创建的月-年变量（<$ c $ mn> ）进行总结：

You can do this by creating a sequence from StartDate to EndDate and extract a month-year variable from that (mnth in the example below). Next you summarise by serialNum and the newly created month-year variable (mnth):

input[, .(mnth = format(seq(StartDate,EndDate,"day"), "%b%y")),
      by = .(SerialNum, StartDate, EndDate)
      ][, .N, by = .(SerialNum, StartDate, EndDate, mnth)]

这将给您：

   SerialNum  StartDate    EndDate  mnth  N
1:         1 2015-01-28 2015-02-03 jan15  4
2:         1 2015-01-28 2015-02-03 feb15  3
3:         2 2015-01-28 2015-03-03 jan15  4
4:         2 2015-01-28 2015-03-03 feb15 28
5:         2 2015-01-28 2015-03-03 mrt15  3

如果您 StartDate 和 EndDate 列尚未格式化为日期，您可以使用以下命令将其转换为日期格式：

If you StartDate and EndDate columns are not formatted as dates yet, you can convert them to date format with:

input[, `:=` (StartDate = as.Date(StartDate,"%d/%m/%Y"),
              EndDate = as.Date(EndDate,"%d/%m/%Y"))]

# or with the 'lubridate' package like @Titolondon used
library(lubridate)
input[, `:=` (StartDate = dmy(StartDate), EndDate = dmy(EndDate))]

---

使用的数据：

---

Used data:

input <- data.table(SerialNum = c(1,2),
                    StartDate = as.Date(c('28/01/2015','28/01/2015'),"%d/%m/%Y"),
                    EndDate = as.Date(c('03/02/2015','03/03/2015'),"%d/%m/%Y"))

这篇关于使用2个日期获取不同月份的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！