汇总日期数据 [英] Summarize data across dates
本文介绍了汇总日期数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是对先前发布的一个问题的跟进问题，(此处):
This is a follow up question to one posted earlier (here):
数据如下:
# A tibble: 54 x 5
ID start_date end_date date val
<chr> <date> <date> <dttm> <dbl>
1 00036110 2005-04-30 2006-03-31 2005-04-30 00:00:00 14.7
2 00036110 2005-04-30 2006-03-31 2006-03-31 00:00:00 28.5
3 00036110 2006-04-30 2007-03-31 2006-04-30 00:00:00 26.7
4 00036110 2006-04-30 2007-03-31 2007-03-31 00:00:00 27.6
5 00036110 2007-04-30 2008-03-31 2007-04-30 00:00:00 30.5
6 00036110 2007-04-30 2008-03-31 2008-03-31 00:00:00 27.3
7 00036110 2008-04-30 2009-03-31 2008-04-30 00:00:00 23.4
8 00036110 2008-04-30 2009-03-31 2009-03-31 00:00:00 12.5
9 00036110 2008-04-30 2009-03-31 2008-04-30 00:00:00 23.4
10 00036110 2008-04-30 2009-03-31 2009-03-31 00:00:00 12.5
# … with 44 more rows
我可以早点应用建议的解决方案并使用:
I can apply the solution suggested to me earlier and use:
d %>%
group_by(ID) %>%
summarise(start_date = start_date[1],
end_date = end_date[1],
diff = (val[2] - val[1]) / val[1],
val_start = val[1],
val_end = val[2]) %>%
arrange(start_date, end_date)
哪个给了我以下输出:
# A tibble: 2 x 6
ID start_date end_date diff val_start val_end
<chr> <date> <date> <dbl> <dbl> <dbl>
1 00036110 2005-04-30 2006-03-31 0.933 14.7 28.5
2 00036020 2010-04-30 2011-03-31 0.362 24.1 32.9
即它只会给我它所看到的第一"观察结果.
I.e. it only gives me the "first" observation it sees.
我想获得所有年份组合的数据，其中起始日期 = 日期和 end_date = 日期:预期输出:
I would like to have the data for all year combinations where the start_date = date and end_date = date: Expected output:
# A tibble: 2 x 6
ID start_date end_date diff val_start val_end
<chr> <date> <date> <dbl> <dbl> <dbl>
1 00036110 2005-04-30 2006-03-31 0.933 14.7 28.5
2 00036110 2006-04-30 2007-03-31 0.034 26.7 27.6
3 00036110 2007-04-30 2008-03-31 -0.104 30.5 27.3
4 00036110 2008-04-30 2009-03-31 -0.466 23.4 12.5
5 00036110 2009-04-30 2010-03-31 -0.466 23.4 12.5
(有一些重复项-上面的参考线4和5)
(there are some duplicates - ref lines 4 and 5 above)
观察值1对应于 d 数据的第1行和第2行，其中 date = start_date 在第1行和 date = 结束日期在第2行中.观察值2对应于 d 数据的第3行和第4行，其中 date 对应于日期"对应于第4行中的结束日期.等等.
Where observation 1 corresponds to lines 1 and 2 of the d data, where date = start_date in line 1 and date = end_date in line 2. Observation 2 corresponds to lines 3 and 4 of the d data, where date corresponds to start_date in line 3 and date corresponds to end_date in line 4. etc.
如果有人能指出我正确的方向，那就太好了！
If anyone can point me in the right direction, that would be great!
数据:
d <- structure(list(ID = c("00036110", "00036110", "00036110", "00036110",
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110",
"00036110", "00036110", "00036110", "00036110", "00036110", "00036110",
"00036020", "00036020", "00036110", "00036110", "00036020", "00036020",
"00036110", "00036110", "00036020", "00036020", "00036110", "00036110",
"00036020", "00036020", "00036110", "00036110", "00036020", "00036020",
"00036020", "00036020", "00036110", "00036110", "00036020", "00036020",
"00036020", "00036020", "00036110", "00036110", "00036020", "00036020",
"00036020", "00036020", "00036110", "00036110", "00036020", "00036020",
"00036110", "00036020"), start_date = structure(c(12903, 12903,
13268, 13268, 13633, 13633, 13999, 13999, 13999, 13999, 14364,
14364, 14364, 14364, 14729, 14729, 14729, 14729, 15094, 15094,
15094, 15094, 15460, 15460, 15460, 15460, 15825, 15825, 15825,
15825, 16190, 16190, 16190, 16190, 16190, 16190, 16555, 16555,
16555, 16555, 16555, 16555, 16921, 16921, 16921, 16921, 16921,
16921, 17286, 17286, 17286, 17286, 17651, 17651), class = "Date"),
end_date = structure(c(13238, 13238, 13603, 13603, 13969,
13969, 14334, 14334, 14334, 14334, 14699, 14699, 14699, 14699,
15064, 15064, 15064, 15064, 15430, 15430, 15430, 15430, 15795,
15795, 15795, 15795, 16160, 16160, 16160, 16160, 16525, 16525,
16525, 16525, 16525, 16525, 16891, 16891, 16891, 16891, 16891,
16891, 17256, 17256, 17256, 17256, 17256, 17256, 17621, 17621,
17621, 17621, 17986, 17986), class = "Date"), date = structure(c(1114819200,
1143763200, 1146355200, 1175299200, 1177891200, 1206921600,
1209513600, 1238457600, 1209513600, 1238457600, 1241049600,
1269993600, 1241049600, 1269993600, 1272585600, 1301529600,
1272585600, 1301529600, 1304121600, 1333152000, 1304121600,
1333152000, 1335744000, 1364688000, 1335744000, 1364688000,
1367280000, 1396224000, 1367280000, 1396224000, 1398816000,
1427760000, 1398816000, 1427760000, 1398816000, 1427760000,
1430352000, 1459382400, 1430352000, 1459382400, 1430352000,
1459382400, 1461974400, 1490918400, 1461974400, 1490918400,
1461974400, 1490918400, 1493510400, 1522454400, 1493510400,
1522454400, 1525046400, 1525046400), class = c("POSIXct",
"POSIXt"), tzone = "UTC"), val = c(14.7299995422363, 28.4799995422363,
26.6800003051758, 27.5599994659424, 30.5400009155273, 27.2700004577637,
23.3999996185303, 12.539999961853, 23.3999996185303, 12.539999961853,
15.0699996948242, 24.8199996948242, 15.0699996948242, 24.8199996948242,
24.3799991607666, 27.7199993133545, 24.1499996185303, 32.9000015258789,
26.0400009155273, 18.25, 32.8499984741211, 20.1900005340576,
15.4499998092651, 18.3899993896484, 20.3999996185303, 27.5900001525879,
17.8600006103516, 25.9500007629395, 28.4099998474121, 27.8700008392334,
25.8999996185303, 30.7000007629395, 28.3500003814697, 24.5300006866455,
28.3500003814697, 24.5300006866455, 30.2399997711182, 23.2700004577637,
23.9699993133545, 28, 23.9699993133545, 28, 24.0400009155273,
33.6300010681152, 26.5200004577637, 35.3499984741211, 26.5200004577637,
35.3499984741211, 35.9900016784668, 44.1100006103516, 36.6500015258789,
39, 43.2999992370605, 34)), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -54L))
其他计算(用于构建预期的输出 diff 列):
Additional Calcualtions (for constructing the expected output diff column):
> (27.6 - 26.7) / 26.7
[1] 0.03370787
> (27.3 - 30.5) / 30.5
[1] -0.104918
> (12.5 - 23.4) / 23.4
[1] -0.465812
推荐答案
这是一个不太漂亮的解决方案，在另一个解决方案中结合了dplyr调用.或者，您可以将数据分为开始日期值和结束日期值，然后将它们合并.
Here's a not-too-pretty solution combining a dplyr call within another one. Or, you could separate the data into the start date values and end date values, then merge them.
d %>% filter(date==end_date) %>% mutate(val.end=val) %>%
select(-val,-date) %>%
left_join(d %>% filter(date==start_date) %>% mutate(val.start=val) %>%
select(-val,-date),.) %>% mutate(diff=(val.end-val.start)/val.start)
# A tibble: 38 x 6
ID start_date end_date val.start val.end diff
<chr> <date> <date> <dbl> <dbl> <dbl>
1 00036110 2005-04-30 2006-03-31 14.7 28.5 0.933
2 00036110 2006-04-30 2007-03-31 26.7 27.6 0.0330
3 00036110 2007-04-30 2008-03-31 30.5 27.3 -0.107
4 00036110 2008-04-30 2009-03-31 23.4 12.5 -0.464
5 00036110 2008-04-30 2009-03-31 23.4 12.5 -0.464
6 00036110 2008-04-30 2009-03-31 23.4 12.5 -0.464
7 00036110 2008-04-30 2009-03-31 23.4 12.5 -0.464
8 00036110 2009-04-30 2010-03-31 15.1 24.8 0.647
9 00036110 2009-04-30 2010-03-31 15.1 24.8 0.647
10 00036110 2009-04-30 2010-03-31 15.1 24.8 0.647
# ... with 28 more rows
这篇关于汇总日期数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
