我正在与R一起工作，我有这些时间表，为特定的一天提供3个资源(在实际数据中，一周中的每一天都有一个这个表)。
| Resource | start | end | status |
| -------- | -------- | -------- | ----------- |
| C1 | 01:00:00 | 06:20:00 | unavailable |
| C1 | 06:20:00 | 23:34:00 | available |
| C1 | 23:34:00 | 00:59:59 | unavailable |
| C2 | 01:00:00 | 03:33:00 | unavailable |
| C2 | 03:33:00 | 22:39:00 | available |
| C2 | 22:39:00 | 00:59:59 | unavailable |
| C3 | 01:00:00 | 05:10:00 | unavailable |
| C3 | 05:10:00 | 00:59:59 | available |
我想在一个资源容量表中转换这个调度。这意味着我想表示每个时间间隔有多少可用资源。结果应该如下所示:
实际数据是:但在上面的例子中，我只是包括了资源SolverC1和day Wednesday。
更新:在上面的例子中，我特意只包含求解器C1、C2和C3。因为可用性周期应该适用于处理相同任务的资源。因此，对于这个示例，前43行数据应该足够了。还应该考虑不可用的时间段，最后，一整天应该表示为可用(至少有一个资源可用)或不可用(没有资源可用)的时间段。在我想要得到的结果表中，您可以看到从01:00:00到00:59:59的一天被表示出来。这些日子的时间段是不连贯的，没有时间段的缺失。
| start | end | # of resources | Resources |
| | | available | available |
| -------- | -------- | -------------- | ---------- |
| 01:00:00 | 03:33:00 | 0 | |
| 03:33:00 | 05:10:00 | 1 | C2 |
| 05:10:00 | 06:20:00 | 2 | C2, C3 |
| 06:20:00 | 22:39:00 | 3 | C2, C3, C1 |
| 22:39:00 | 23:34:00 | 2 | C3, C1 |
| 23:34:00 | 00:59:59 | 1 | C3 |
structure(list(resource = c("SolverC1", "SolverC1", "SolverC1",
"SolverC1", "SolverC1", "SolverC1", "SolverC1", "SolverC1", "SolverC1",
"SolverC1", "SolverC1", "SolverC1", "SolverC2", "SolverC2", "SolverC2",
"SolverC2", "SolverC2", "SolverC2", "SolverC2", "SolverC2", "SolverC2",
"SolverC2", "SolverC2", "SolverC2", "SolverC2", "SolverC2", "SolverC2",
"SolverC2", "SolverC3", "SolverC3", "SolverC3", "SolverC3", "SolverC3",
"SolverC3", "SolverC3", "SolverC3", "SolverC3", "SolverC3", "SolverC3",
"SolverC3", "SolverC3", "SolverC3", "SolverS1", "SolverS1", "SolverS1",
"SolverS1", "SolverS1", "SolverS1", "SolverS1", "SolverS1", "SolverS1",
"SolverS1", "SolverS1", "SolverS1", "SolverS1", "SolverS1", "SolverS1",
"SolverS1", "SolverS2", "SolverS2", "SolverS2", "SolverS2", "SolverS2",
"SolverS2", "SolverS2", "SolverS2", "SolverS2", "SolverS2", "SolverS2",
"SolverS2", "SolverS2", "SolverS2", "SolverS3", "SolverS3", "SolverS3",
"SolverS3", "SolverS3", "SolverS3", "SolverS3", "SolverS3", "SolverS3",
"SolverS3", "SolverS3", "SolverS3", "SolverS3", "SolverS3", "SolverS3",
"SolverS3", "SolverS3", "SolverS3", "SolverS3", "System", "System",
"System", "System", "System", "System", "System", "System", "System",
"Tester1", "Tester1", "Tester1", "Tester1", "Tester1", "Tester1",
"Tester1", "Tester1", "Tester1", "Tester1", "Tester1", "Tester1",
"Tester1", "Tester2", "Tester2", "Tester2", "Tester2", "Tester2",
"Tester2", "Tester2", "Tester2", "Tester2", "Tester2", "Tester2",
"Tester2", "Tester2", "Tester2", "Tester3", "Tester3", "Tester3",
"Tester3", "Tester3", "Tester3", "Tester3", "Tester3", "Tester3",
"Tester3", "Tester3", "Tester3", "Tester3", "Tester3", "Tester3",
"Tester3", "Tester4", "Tester4", "Tester4", "Tester4", "Tester4",
"Tester4", "Tester4", "Tester4", "Tester4", "Tester4", "Tester4",
"Tester4", "Tester4", "Tester4", "Tester4", "Tester4", "Tester4",
"Tester5", "Tester5", "Tester5", "Tester5", "Tester5", "Tester5",
"Tester5", "Tester5", "Tester5", "Tester5", "Tester5", "Tester5",
"Tester5", "Tester5", "Tester5", "Tester5", "Tester6", "Tester6",
"Tester6", "Tester6", "Tester6", "Tester6", "Tester6", "Tester6",
"Tester6", "Tester6", "Tester6"), period_start = c("01:00:00",
"01:00:00", "01:00:00", "06:20:00", "23:34:00", "01:00:00", "03:35:00",
"01:00:00", "04:26:00", "01:00:00", "09:16:00", "01:00:00", "01:00:00",
"02:16:00", "01:00:00", "01:00:00", "03:33:00", "22:39:00", "01:00:00",
"03:07:00", "20:29:00", "01:00:00", "02:34:00", "01:00:00", "04:01:00",
"21:35:00", "01:00:00", "04:01:00", "01:00:00", "01:00:00", "07:38:00",
"23:38:00", "01:00:00", "05:10:00", "01:00:00", "03:24:00", "01:00:00",
"01:00:00", "04:18:00", "20:20:00", "01:00:00", "03:33:00", "01:00:00",
"03:11:00", "01:00:00", "03:57:00", "01:00:00", "02:26:00", "21:34:00",
"01:00:00", "03:56:00", "20:57:00", "01:00:00", "03:29:00", "01:00:00",
"23:27:00", "01:00:00", "21:07:00", "01:00:00", "03:32:00", "22:30:00",
"01:00:00", "02:12:00", "01:00:00", "02:25:00", "01:00:00", "04:35:00",
"21:49:00", "01:00:00", "01:00:00", "01:00:00", "02:07:00", "01:00:00",
"02:51:00", "22:28:00", "01:00:00", "04:37:00", "23:40:00", "01:00:00",
"03:07:00", "22:14:00", "01:00:00", "06:07:00", "01:00:00", "23:30:00",
"01:00:00", "03:15:00", "06:27:00", "01:00:00", "02:06:00", "23:11:00",
"01:00:00", "01:00:00", "01:00:00", "01:00:00", "01:00:00", "01:00:00",
"01:00:00", "08:56:00", "01:00:00", "01:00:00", "02:46:00", "22:38:00",
"01:00:00", "01:00:00", "02:32:00", "01:00:00", "04:02:00", "01:00:00",
"03:23:00", "01:00:00", "02:16:00", "01:00:00", "01:00:00", "01:00:00",
"02:26:00", "01:00:00", "01:00:00", "04:08:00", "22:42:00", "01:00:00",
"01:00:00", "02:44:00", "23:32:00", "01:00:00", "03:38:00", "21:17:00",
"01:00:00", "05:21:00", "01:00:00", "02:16:00", "23:46:00", "01:00:00",
"01:00:00", "03:24:00", "01:00:00", "03:36:00", "01:00:00", "01:00:01",
"01:08:00", "01:50:00", "09:04:00", "01:00:00", "01:00:00", "22:50:00",
"01:00:00", "03:13:00", "23:27:00", "01:00:00", "03:11:00", "22:14:00",
"01:00:00", "02:21:00", "01:00:00", "01:00:00", "02:50:00", "08:46:00",
"01:00:00", "04:44:00", "22:52:00", "01:00:00", "01:00:00", "02:46:00",
"22:05:00", "01:00:00", "02:34:00", "23:56:00", "01:00:00", "03:20:00",
"01:00:00", "04:10:00", "23:09:00", "01:00:00", "02:26:00", "08:38:00",
"01:00:00", "01:00:00", "06:44:00", "01:00:00", "01:00:00", "02:07:00",
"01:00:00", "23:11:00", "01:00:00", "03:53:00", "23:30:00", "01:00:00"
), period_end = c("00:59:59", "00:59:59", "06:20:00", "23:34:00",
"00:59:59", "03:35:00", "00:59:59", "04:26:00", "00:59:59", "09:16:00",
"00:59:59", "00:59:59", "02:16:00", "00:59:59", "00:59:59", "03:33:00",
"22:39:00", "00:59:59", "03:07:00", "20:29:00", "00:59:59", "02:34:00",
"01:08:00", "04:01:00", "21:35:00", "00:59:59", "04:01:00", "01:04:00",
"02:24:00", "07:38:00", "23:38:00", "00:59:59", "05:10:00", "00:59:59",
"03:24:00", "23:59:00", "00:59:59", "04:18:00", "20:20:00", "00:59:59",
"03:33:00", "00:59:59", "03:11:00", "00:59:59", "03:57:00", "00:59:59",
"02:26:00", "21:34:00", "00:59:59", "03:56:00", "20:57:00", "00:59:59",
"03:29:00", "00:59:59", "23:27:00", "00:59:59", "21:07:00", "00:59:59",
"03:32:00", "22:30:00", "00:59:59", "02:12:00", "00:59:59", "02:25:00",
"00:59:59", "04:35:00", "21:49:00", "00:59:59", "00:59:59", "00:59:59",
"02:07:00", "00:59:59", "02:51:00", "22:28:00", "00:59:59", "04:37:00",
"23:40:00", "00:59:59", "03:07:00", "22:14:00", "00:59:59", "06:07:00",
"01:07:00", "23:30:00", "00:59:59", "03:15:00", "06:27:00", "00:59:59",
"02:06:00", "23:11:00", "00:59:59", "00:59:59", "00:59:59", "00:59:59",
"00:59:59", "00:59:59", "00:59:59", "08:56:00", "00:59:59", "00:59:59",
"02:46:00", "22:38:00", "00:59:59", "00:59:59", "02:32:00", "00:59:59",
"04:02:00", "00:59:59", "03:23:00", "00:59:59", "02:16:00", "00:59:59",
"00:59:59", "00:59:59", "02:26:00", "00:59:59", "00:59:59", "04:08:00",
"22:42:00", "00:59:59", "00:59:59", "02:44:00", "23:32:00", "00:59:59",
"03:38:00", "21:17:00", "00:59:59", "05:21:00", "00:59:59", "02:16:00",
"23:46:00", "00:59:59", "00:59:59", "03:24:00", "00:59:59", "03:36:00",
"00:59:59", "01:08:00", "01:00:00", "01:50:00", "09:04:00", "00:59:59",
"00:59:59", "22:50:00", "00:59:59", "03:13:00", "23:27:00", "00:59:59",
"03:11:00", "22:14:00", "00:59:59", "02:21:00", "00:59:59", "00:59:59",
"02:50:00", "08:46:00", "00:59:59", "04:44:00", "22:52:00", "00:59:59",
"00:59:59", "02:46:00", "22:05:00", "00:59:59", "02:34:00", "23:56:00",
"00:59:59", "03:20:00", "00:59:59", "04:10:00", "23:09:00", "00:59:59",
"02:26:00", "08:38:00", "00:59:59", "00:59:59", "06:44:00", "00:59:59",
"01:06:00", "02:07:00", "00:59:59", "00:59:59", "00:59:59", "03:53:00",
"23:30:00", "00:59:59", "01:01:00"), status = c("available",
"available", "unavailable", "available", "unavailable", "unavailable",
"available", "unavailable", "available", "available", "unavailable",
"available", "unavailable", "available", "available", "unavailable",
"available", "unavailable", "unavailable", "available", "unavailable",
"unavailable", "available", "unavailable", "available", "unavailable",
"unavailable", "available", "available", "unavailable", "available",
"unavailable", "unavailable", "available", "unavailable", "available",
"available", "unavailable", "available", "unavailable", "unavailable",
"available", "unavailable", "available", "unavailable", "available",
"unavailable", "available", "unavailable", "unavailable", "available",
"unavailable", "unavailable", "available", "available", "unavailable",
"available", "unavailable", "unavailable", "available", "unavailable",
"unavailable", "available", "unavailable", "available", "unavailable",
"available", "unavailable", "available", "available", "unavailable",
"available", "unavailable", "available", "unavailable", "unavailable",
"available", "unavailable", "unavailable", "available", "unavailable",
"unavailable", "available", "available", "unavailable", "unavailable",
"available", "unavailable", "unavailable", "available", "unavailable",
"available", "available", "available", "available", "available",
"available", "available", "unavailable", "available", "unavailable",
"available", "unavailable", "available", "unavailable", "available",
"unavailable", "available", "unavailable", "available", "unavailable",
"available", "available", "available", "unavailable", "available",
"available", "unavailable", "available", "unavailable", "available",
"unavailable", "available", "unavailable", "unavailable", "available",
"unavailable", "unavailable", "available", "unavailable", "available",
"unavailable", "available", "unavailable", "available", "unavailable",
"available", "available", "unknown", "unknown", "available",
"unavailable", "available", "available", "unavailable", "unavailable",
"available", "unavailable", "unavailable", "available", "unavailable",
"unavailable", "available", "available", "unavailable", "available",
"unavailable", "unavailable", "available", "unavailable", "available",
"unavailable", "available", "unavailable", "unavailable", "available",
"unavailable", "unavailable", "available", "unavailable", "available",
"unavailable", "unavailable", "available", "unavailable", "available",
"unavailable", "available", "available", "unavailable", "available",
"available", "unavailable", "unavailable", "available", "unavailable",
"available"), day = structure(c(1L, 2L, 3L, 3L, 3L, 4L, 4L, 5L,
5L, 6L, 6L, 7L, 1L, 1L, 2L, 3L, 3L, 3L, 4L, 4L, 4L, 5L, 5L, 6L,
6L, 6L, 7L, 7L, 1L, 2L, 2L, 2L, 3L, 3L, 4L, 4L, 5L, 6L, 6L, 6L,
7L, 7L, 1L, 1L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 4L, 5L, 5L, 6L, 6L,
7L, 7L, 1L, 1L, 1L, 2L, 2L, 3L, 3L, 4L, 4L, 4L, 5L, 6L, 7L, 7L,
1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 5L, 5L, 6L, 6L, 6L,
7L, 7L, 7L, 1L, 2L, 3L, 4L, 5L, 6L, 6L, 6L, 7L, 1L, 1L, 1L, 2L,
3L, 3L, 4L, 4L, 5L, 5L, 6L, 6L, 7L, 1L, 2L, 2L, 3L, 4L, 4L, 4L,
5L, 6L, 6L, 6L, 7L, 7L, 7L, 1L, 1L, 2L, 2L, 2L, 3L, 4L, 4L, 5L,
5L, 6L, 6L, 6L, 6L, 6L, 7L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L,
4L, 5L, 6L, 6L, 6L, 7L, 7L, 7L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L,
4L, 5L, 5L, 5L, 6L, 6L, 6L, 7L, 1L, 1L, 2L, 3L, 3L, 4L, 5L, 6L,
6L, 6L, 7L), .Label = c("Mon", "Tue", "Wed", "Thu", "Fri", "Sat",
"Sun"), class = c("ordered", "factor")), week = c(1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L)), row.names = c("19", "6", "1310",
"7", "1410", "1510", "8", "2910", "16", "24", "4510", "18", "611",
"34", "42", "5310", "29", "5410", "5510", "30", "5610", "691",
"38", "701", "39", "711", "4910", "2710", "50", "941", "51",
"951", "1081", "59", "1231", "66", "54", "1141", "62", "115",
"911", "49", "1501", "78", "1381", "73", "1681", "87", "1691",
"1701", "88", "1711", "1581", "82", "83", "161", "71", "1351",
"1981", "102", "1991", "2141", "110", "1881", "97", "2181", "112",
"2191", "113", "93", "210", "108", "2311", "119", "2321", "2331",
"120", "2341", "2351", "121", "2361", "2621", "135", "1161",
"2271", "2651", "137", "2661", "229", "118", "2301", "149", "143",
"144", "152", "146", "1611", "162", "315", "155", "324", "167",
"325", "168", "355", "182", "357", "183", "345", "177", "320",
"165", "166", "200", "382", "194", "189", "399", "203", "400",
"204", "389", "198", "390", "378", "192", "379", "413", "2101",
"415", "211", "416", "218", "419", "213", "434", "220", "2281",
"12372", "12373", "2291", "451", "222", "249", "487", "474",
"243", "475", "464", "236", "465", "478", "245", "253", "496",
"254", "497", "470", "241", "471", "259", "522", "267", "523",
"5101", "261", "5111", "512", "262", "500", "256", "501", "543",
"277", "544", "272", "552", "281", "282", "581", "297", "291",
"547", "575", "293", "576", "294"), class = "data.frame")
如果我理解的话，这可能对你有帮助:
library(dplyr)
df %>%
filter(status == "available") %>%
group_by(week,day,period_start,period_end) %>%
summarise(
n = n(),
resources = paste0(resource,collapse = ", ")
)
# A tibble: 74 x 6
# Groups: week, day, period_start [63]
week day period_start period_end n resources
<int> <ord> <chr> <chr> <int> <chr>
1 1 Mon 01:00:00 00:59:59 4 SolverC1, System, Tester2, Tester5
2 1 Mon 01:00:00 02:24:00 1 SolverC3
3 1 Mon 01:00:00 22:50:00 1 Tester4
4 1 Mon 02:16:00 00:59:59 1 SolverC2
5 1 Mon 02:46:00 22:38:00 1 Tester1
6 1 Mon 02:51:00 22:28:00 1 SolverS3
7 1 Mon 03:11:00 00:59:59 1 SolverS1
8 1 Mon 03:32:00 22:30:00 1 SolverS2
9 1 Mon 05:21:00 00:59:59 1 Tester3
10 1 Mon 06:44:00 00:59:59 1 Tester6