r - group_by 并创建一系列每月日期-6ren

r - group_by 并创建一系列每月日期

转载作者：行者123 更新时间：2023-12-04 19:52:37

我有一些数据如下所示:

     cusip       date start_date   end_date
1 00036020 2011-01-31 2011-07-29 2012-06-30
2 00036020 2011-02-28 2011-07-29 2012-06-30
3 00036020 2011-03-31 2011-07-29 2012-06-30
4 00036020 2011-04-29 2011-07-29 2012-06-30
5 00036020 2011-05-31 2011-07-29 2012-06-30
6 00036020 2011-06-30 2011-07-29 2012-06-30

我想对 group_by 列进行 id 并计算 start_date 和 end_date 之间的月底日期。或者在 start_date 和 end_date 之间创建一系列每月日期，我可以将 date 列与之匹配。

我基本上想将分组数据过滤到开始日期和结束日期之间，只是执行 filter(date >= start_date & date <= end_date) 并不能得到结果。

执行以下操作:

  group_by(cusip, start_date, end_date) %>%
  filter(date >= start_date & date <= end_date)

> head(df2, 13)
# A tibble: 13 x 4
# Groups:   cusip, start_date, end_date [3]
   cusip    date       start_date end_date  
   <chr>    <date>     <date>     <date>    
 1 00036020 2011-07-29 2011-07-29 2012-06-30
 2 00036020 2011-08-31 2011-07-29 2012-06-30
 3 00036020 2011-09-30 2011-07-29 2012-06-30
 4 00036020 2011-10-31 2011-07-29 2012-06-30
 5 00036020 2011-11-30 2011-07-29 2012-06-30
 6 00036020 2011-12-30 2011-07-29 2012-06-30
 7 00036020 2012-07-31 2012-07-31 2013-06-30
 8 00036020 2012-08-31 2012-07-31 2013-06-30
 9 00036020 2012-09-28 2012-07-31 2013-06-30
10 00036020 2012-10-31 2012-07-31 2013-06-30
11 00036020 2012-11-30 2012-07-31 2013-06-30
12 00036020 2012-12-31 2012-07-31 2013-06-30
13 00036020 2013-07-31 2013-07-31 2014-06-30

这也不是我想要的结果。从第 6/7 行开始，我丢失了 6 个月的数据。

我有一个比这大得多的数据框，我想将数据过滤到 date 和 start_date 之间的 end_date 列之间。

只是在想我该怎么做。

数据:

    df <- structure(list(cusip = c("00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036020", "00036020", "00036020", 
"00036020", "00036020", "00036020", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110", 
"00036110", "00036110", "00036110"), date = structure(c(15005, 
15033, 15064, 15093, 15125, 15155, 15184, 15217, 15247, 15278, 
15308, 15338, 15370, 15399, 15429, 15460, 15491, 15520, 15552, 
15583, 15611, 15644, 15674, 15705, 15736, 15764, 15792, 15825, 
15856, 15884, 15917, 15947, 15978, 16009, 16038, 16070, 16101, 
16129, 16160, 16190, 16220, 16251, 16282, 16311, 16343, 16374, 
16402, 16435, 16465, 16493, 16525, 16555, 16584, 16616, 16647, 
16678, 16708, 16738, 16769, 16800, 16829, 16829, 16860, 16860, 
16891, 16891, 16920, 16920, 16952, 16952, 16982, 16982, 17011, 
17011, 17044, 17044, 17074, 17074, 17105, 17105, 17135, 17135, 
17165, 17165, 17197, 17225, 17256, 17284, 17317, 17347, 17378, 
17409, 17438, 17470, 17500, 17529, 17562, 17590, 17619, 17651, 
17682, 17711, 17743, 17774, 17802, 17835, 17865, 17896, 12814, 
12842, 12873, 12902, 12934, 12964, 12993, 13026, 13056, 13087, 
13117, 13147, 13179, 13207, 13238, 13266, 13299, 13329, 13360, 
13391, 13420, 13452, 13482, 13511, 13544, 13572, 13602, 13633, 
13664, 13693, 13725, 13756, 13784, 13817, 13847, 13878, 13909, 
13938, 13969, 13999, 14029, 14060, 14091, 14120, 14152, 14183, 
14211, 14244, 14274, 14302, 14334, 14364, 14393, 14425, 14456, 
14487, 14517, 14547, 14578, 14609, 14638, 14666, 14699, 14729, 
14757, 14790, 14820, 14852, 14882, 14911, 14943, 14974, 15005, 
15033, 15064, 15093, 15125, 15155, 15184, 15217, 15247, 15278, 
15308, 15338, 15370, 15399, 15429, 15460, 15491, 15520, 15552, 
15583, 15611, 15644, 15674, 15705, 15736, 15764, 15792, 15825, 
15856, 15884, 15917, 15947, 15978, 16009, 16038, 16070, 16101, 
16129, 16160, 16190, 16220, 16251, 16282, 16311, 16343, 16374, 
16402, 16435, 16465, 16493, 16525, 16555, 16584, 16616, 16647, 
16678, 16708, 16738, 16769, 16800, 16829, 16860, 16891, 16920, 
16952, 16982, 17011, 17044, 17074, 17105, 17135, 17165, 17197, 
17225, 17256, 17284, 17317, 17347, 17378, 17409, 17438, 17470, 
17500, 17529), class = "Date"), start_date = structure(c(15184, 
15184, 15184, 15184, 15184, 15184, 15184, 15184, 15184, 15184, 
15184, 15184, 15552, 15552, 15552, 15552, 15552, 15552, 15552, 
15552, 15552, 15552, 15552, 15552, 15917, 15917, 15917, 15917, 
15917, 15917, 15917, 15917, 15917, 15917, 15917, 15917, 16282, 
16282, 16282, 16282, 16282, 16282, 16282, 16282, 16282, 16282, 
16282, 16282, 16647, 16647, 16647, 16647, 16647, 16647, 16647, 
16647, 16647, 16647, 16647, 16647, 17011, 17011, 17011, 17011, 
17011, 17011, 17011, 17011, 17011, 17011, 17011, 17011, 17011, 
17011, 17011, 17011, 17011, 17011, 17011, 17011, 17011, 17011, 
17011, 17011, 17378, 17378, 17378, 17378, 17378, 17378, 17378, 
17378, 17378, 17378, 17378, 17378, 17743, 17743, 17743, 17743, 
17743, 17743, 17743, 17743, 17743, 17743, 17743, 17743, 13360, 
13360, 13360, 13360, 13360, 13360, 13360, 13360, 13360, 13360, 
13360, 13360, 13725, 13725, 13725, 13725, 13725, 13725, 13725, 
13725, 13725, 13725, 13725, 13725, 14091, 14091, 14091, 14091, 
14091, 14091, 14091, 14091, 14091, 14091, 14091, 14091, 14456, 
14456, 14456, 14456, 14456, 14456, 14456, 14456, 14456, 14456, 
14456, 14456, 14820, 14820, 14820, 14820, 14820, 14820, 14820, 
14820, 14820, 14820, 14820, 14820, 15184, 15184, 15184, 15184, 
15184, 15184, 15184, 15184, 15184, 15184, 15184, 15184, 15552, 
15552, 15552, 15552, 15552, 15552, 15552, 15552, 15552, 15552, 
15552, 15552, 15917, 15917, 15917, 15917, 15917, 15917, 15917, 
15917, 15917, 15917, 15917, 15917, 16282, 16282, 16282, 16282, 
16282, 16282, 16282, 16282, 16282, 16282, 16282, 16282, 16647, 
16647, 16647, 16647, 16647, 16647, 16647, 16647, 16647, 16647, 
16647, 16647, 17011, 17011, 17011, 17011, 17011, 17011, 17011, 
17011, 17011, 17011, 17011, 17011, 17378, 17378, 17378, 17378, 
17378, 17378, 17378, 17378, 17378, 17378, 17378, 17378, 17743, 
17743, 17743, 17743, 17743, 17743, 17743, 17743, 17743, 17743, 
17743, 17743), class = "Date"), end_date = structure(c(15521, 
15521, 15521, 15521, 15521, 15521, 15521, 15521, 15521, 15521, 
15521, 15521, 15886, 15886, 15886, 15886, 15886, 15886, 15886, 
15886, 15886, 15886, 15886, 15886, 16251, 16251, 16251, 16251, 
16251, 16251, 16251, 16251, 16251, 16251, 16251, 16251, 16616, 
16616, 16616, 16616, 16616, 16616, 16616, 16616, 16616, 16616, 
16616, 16616, 16982, 16982, 16982, 16982, 16982, 16982, 16982, 
16982, 16982, 16982, 16982, 16982, 17347, 17347, 17347, 17347, 
17347, 17347, 17347, 17347, 17347, 17347, 17347, 17347, 17347, 
17347, 17347, 17347, 17347, 17347, 17347, 17347, 17347, 17347, 
17347, 17347, 17712, 17712, 17712, 17712, 17712, 17712, 17712, 
17712, 17712, 17712, 17712, 17712, 18077, 18077, 18077, 18077, 
18077, 18077, 18077, 18077, 18077, 18077, 18077, 18077, 13694, 
13694, 13694, 13694, 13694, 13694, 13694, 13694, 13694, 13694, 
13694, 13694, 14060, 14060, 14060, 14060, 14060, 14060, 14060, 
14060, 14060, 14060, 14060, 14060, 14425, 14425, 14425, 14425, 
14425, 14425, 14425, 14425, 14425, 14425, 14425, 14425, 14790, 
14790, 14790, 14790, 14790, 14790, 14790, 14790, 14790, 14790, 
14790, 14790, 15155, 15155, 15155, 15155, 15155, 15155, 15155, 
15155, 15155, 15155, 15155, 15155, 15521, 15521, 15521, 15521, 
15521, 15521, 15521, 15521, 15521, 15521, 15521, 15521, 15886, 
15886, 15886, 15886, 15886, 15886, 15886, 15886, 15886, 15886, 
15886, 15886, 16251, 16251, 16251, 16251, 16251, 16251, 16251, 
16251, 16251, 16251, 16251, 16251, 16616, 16616, 16616, 16616, 
16616, 16616, 16616, 16616, 16616, 16616, 16616, 16616, 16982, 
16982, 16982, 16982, 16982, 16982, 16982, 16982, 16982, 16982, 
16982, 16982, 17347, 17347, 17347, 17347, 17347, 17347, 17347, 
17347, 17347, 17347, 17347, 17347, 17712, 17712, 17712, 17712, 
17712, 17712, 17712, 17712, 17712, 17712, 17712, 17712, 18077, 
18077, 18077, 18077, 18077, 18077, 18077, 18077, 18077, 18077, 
18077, 18077), class = "Date")), row.names = c(NA, -264L), class = "data.frame")

编辑:预期输出:

预期的输出基本上是“复制”date 列。所以创建一个日期序列如下:

前 24 个观察值:

第一个序列是从 2011-07-29 到 2012-06-30 ，因此将从第 7 行开始(所有带有 ** 的行将被丢弃)，因为它们小于 start_date 。该序列应持续 12 个月 seq(from = as.Date("2011-07-29"), to = as.Date("2012-06-30"), by = "months") 在第 18 行结束。新序列从第 19 行开始，因为 start_date 是 2012-07-31。

          cusip       date start_date   end_date
   ** 1  00036020 2011-01-31 2011-07-29 2012-06-30
   ** 2  00036020 2011-02-28 2011-07-29 2012-06-30
   ** 3  00036020 2011-03-31 2011-07-29 2012-06-30
   ** 4  00036020 2011-04-29 2011-07-29 2012-06-30
   ** 5  00036020 2011-05-31 2011-07-29 2012-06-30
   ** 6  00036020 2011-06-30 2011-07-29 2012-06-30
    7    00036020 2011-07-29 2011-07-29 2012-06-30
    8    00036020 2011-08-31 2011-07-29 2012-06-30
    9    00036020 2011-09-30 2011-07-29 2012-06-30
    10   00036020 2011-10-31 2011-07-29 2012-06-30
    11   00036020 2011-11-30 2011-07-29 2012-06-30
    12   00036020 2011-12-30 2011-07-29 2012-06-30
    13   00036020 2012-01-31 2012-07-31 2013-06-30
    14   00036020 2012-02-29 2012-07-31 2013-06-30
    15   00036020 2012-03-30 2012-07-31 2013-06-30
    16   00036020 2012-04-30 2012-07-31 2013-06-30
    17   00036020 2012-05-31 2012-07-31 2013-06-30
    18   00036020 2012-06-29 2012-07-31 2013-06-30
    19   00036020 2012-07-31 2012-07-31 2013-06-30
    20   00036020 2012-08-31 2012-07-31 2013-06-30
    21   00036020 2012-09-28 2012-07-31 2013-06-30
    22   00036020 2012-10-31 2012-07-31 2013-06-30
    23   00036020 2012-11-30 2012-07-31 2013-06-30
    24   00036020 2012-12-31 2012-07-31 2013-06-30

我在想我应该让 start_date 和 end_date 成为唯一值并从那里过滤。

最佳答案

如果我们需要为每个“start_date”及其相应的“end_date”创建一个日期序列，可以使用 map2 完成，这里它不需要任何分组，因为它获得了每个相应的“开始日期/结束日期”的序列

library(purrr)
df %>%
      mutate(Seq = map2(start_date, end_date, seq, by = '1 day'))

更新

基于OP的评论

df %>%  
   group_by(cusip) %>% 
   mutate(rn = row_number()) %>%       
   filter(cummax(date >= start_date & date <= end_date) > 0)
# A tibble: 102 x 5
# Groups:   cusip [1]
#   cusip    date       start_date end_date      rn
#   <chr>    <date>     <date>     <date>     <int>
# 1 00036020 2011-07-29 2011-07-29 2012-06-30     7
# 2 00036020 2011-08-31 2011-07-29 2012-06-30     8
# 3 00036020 2011-09-30 2011-07-29 2012-06-30     9
# 4 00036020 2011-10-31 2011-07-29 2012-06-30    10
# 5 00036020 2011-11-30 2011-07-29 2012-06-30    11
# 6 00036020 2011-12-30 2011-07-29 2012-06-30    12
# 7 00036020 2012-01-31 2012-07-31 2013-06-30    13
# 8 00036020 2012-02-29 2012-07-31 2013-06-30    14
# 9 00036020 2012-03-30 2012-07-31 2013-06-30    15
#10 00036020 2012-04-30 2012-07-31 2013-06-30    16
# … with 92 more rows

-检查前 24 行

关于r - group_by 并创建一系列每月日期，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/58050764/

文章推荐：当我添加 create_dir : true 时，R 包中的 rmarkdown 模板失败

文章推荐： r - 你如何在 ggplot 中将文本放在不同的行上

文章推荐： java - 将对象转换到列表会导致未检查的转换警告

文章推荐： r - 如何整理这个数据集？

MSSQL监控数据库的DDL操作(创建，修改，删除存储过程，创建，修改，删除表等)
前言：有时候，一个数据库有多个帐号，包括数据库管理员，开发人员，运维支撑人员等，可能有很多帐号都有比较大的权限，例如DDL操作权限(创建，修改，删除存储过程，创建，修改，删除表等），账户多了，管理
javascript - 使用 Storybook 创建 React App 创建 webpack 问题
所以我用 Create React App 创建并设置了一个大型 React 应用程序。最近我们开始使用 Storybook 来处理和创建组件。它很棒。但是，当我们尝试运行或构建应用程序时，我们不断遇
javascript - 创建 Angular Directive(指令)以重用代码 - 创建 html 时解析错误
遵循我正在创建的控件的代码片段。这个控件用在不同的地方，变量也不同。我正在尝试编写指令来清理代码，但在 {{}} 附近插入值时出现解析错误。刚接触 Angular ，无法确定我错过了什么。请帮忙。
java - 创建 JAX-RS 提供程序以从 InputStream 创建 Java Image
我正在尝试创建一个 image/jpeg jax-rs 提供程序类，它为我的基于 post rest 的 Web 服务创建一个图像。我无法制定请求来测试以下内容，最简单的测试方法是什么？ @POST
c - 当我使用 FILE 创建 txt 文件时，Dev C++ 创建 test.txt
我一直在 Windows 10 的模拟器中练习 c。后来我改用dev C++ IDE。当我在 C 中使用 FILE 时。创建的文件的名称为 test.txt ，而我给出了其他名称。请帮助解决它。下面
ios - 为什么我们不遵循使用 xib 创建 customTableViewCell 的相同过程，就像使用 xib 创建 customView 一样？
当我们创建自定义 View 时，我们将 View 文件的所有者设置为自定义类，并使用 initWithFrame 或 initWithCode 对其进行实例化。当我们创建 customUITable
创建 Pthreads
我正在尝试为函数 * Producer 创建一个线程，但用于创建线程的行显示错误。我为这句话加了星标，但我无法弄清楚它出了什么问题...... #include #include #include
创建、调用JavaScript对象的方法集锦
今天在做项目时，遇到了需要创建JavaScript对象的情况。所以Bing了一篇老外写的关于3种创建JavaScript对象的文章，看后跟着打了一遍代码。感觉方法挺好的，在这里与大家分享一下。 &
python - 创建 StringToSign
我正在阅读将查询字符串传递给 Amazon 的 S3 以进行身份验证的文档，但似乎无法理解 StringToSign 的创建和使用方式。我正在寻找一个具体示例来说明 (1) 如何构造 String
c# - 创建、不等待和确保任务完成的正确方法
前言:我对 C# 中任务的底层实现不太了解，只了解它们的用法。为我在下面屠宰的任何东西道歉: 对于“我怎样才能开始一项任务但不等待它？”这个问题，我找不到一个好的答案。在 C# 中。更具体地说，即使任
linq - 创建 ILookups
我有一个由一些复杂的表达式生成的 ILookup。假设这是按姓氏查找人。 (在我们简单的世界模型中，姓氏在家庭中是唯一的) ILookup families; 现在我有两个对如何构建感兴趣的查询。首
WIX bundle 创建
我试图创建一个 MSI，其中包含和 exe。在 WIX 中使用了捆绑选项。这样做时出错。有人可以帮我解决这个问题。下面是代码: 错误 error LGH
Yii 创建、更新具有不同字段的表单
在 Yii 中，Create 和 Update 通常使用相同的形式。因此，如果我在创建期间有电子邮件、密码、...other_fields...等字段，但我不想在更新期间专门显示电子邮件和密码字段，但
qt - 创建 QModelIndex
上周我一直在努力创建一个给定一行和一列的 QModelIndex。或者，我会满足于在已经存在的 QModelIndex 中更改 row() 的值。任何帮助，将不胜感激。编辑: QModelInd
C: 创建、传递和访问指向常量字符串的常量指针数组
出于某种原因，这不起作用: const char * str_reset_command = "\r\nReset"; const char * str_config_command = "\r\nC
r - 创建 "other"字段
现在，我有以下由 original.df %.% group_by(Category) %.% tally() %.% arrange(desc(n)) 创建的 data.frame。 DF 5),
vim - 创建〜/.vimrc后错误打开文件
在今天之前，我使用/etc/vim/vimrc来配置我的vim设置。今天，我想到了创建.vimrc文件。所以，我用 touch .vimrc cat /etc/vim/vimrc > .vimrc 所
iPhone:创建 MKAnnotation
我可以创建一个 MKAnnotation，还是只读的？我有坐标，但我发现使用 setCooperative 手动创建 MKAnnotation 并不容易。想法？最佳答案 MKAnnotation
iphone - 创建 NSDictionary
在以下代码中，第一个日志语句按预期显示小数，但第二个日志语句记录 NULL。我做错了什么？ NSDictionary *entry = [[NSDictionary alloc] initWithOb
php - 创建/添加多维数组值时的未定义偏移
我正在使用与此类似的代码动态添加到数组； $arrayF[$f+1][$y][$x+1] = $value+1; 但是我在错误报告中收到了这个: undefined offset :1 问题:尝试创

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

r - group_by 并创建一系列每月日期

更新