将日期添加到来自不同列的时间,如果时间跨越午夜,则添加额外的一天。

huangapple go评论87阅读模式
英文:

R - add date to time from different column or add extra day if the time goes across midnight

问题

以下是已翻译好的内容:

我在这里找不到任何东西,这就是我的问题所在。由于数据或缺失数据,我需要操作一列以将日期放入时间列中。

我有一个包含EVENT_START_DTTM的日期和时间,以及EVENT_END_TM的时间的数据框。
我的逻辑是:

  • (EVENT_START_DTTM <= EVENT_END_TM则为同一天)
  • (EVENT_START_DTTM > EVENT_END_TM则跨越午夜,我需要将日期加一天)

事件开始日期时间 EVENT_END_TM
2020-01-03 09:34:13 10:33:37
2020-01-03 07:57:24 23:04:38
2019-12-04 23:42:40 03:38:33
2019-12-04 22:33:33 00:07:13

此外,我想计算分钟差异,期望的结果是:

事件开始日期时间 事件结束日期时间 差异分钟
2020-01-03 09:34:13 2020-01-03 10:33:37 59
2020-01-03 07:57:24 2020-01-03 23:04:38 907
2019-12-04 23:42:40 2019-12-05 03:38:33 237
2019-12-04 22:33:33 2019-12-05 00:07:13 95

以下是我的代码

library(data.table)
library(lubridate)
EVENT_START_DTTM <- c("2020-01-03 09:34:13", "2020-01-03 07:57:24","2019-12-04 23:42:40", "2019-12-04 22:33:33")
EVENT_END_DTTM <- c("2020-01-03 10:33:37", "2020-01-03 23:04:38","2019-12-05 03:38:33", "2019-12-05 00:07:13")
df_dttm <- data.frame(as.POSIXct(EVENT_START_DTTM), as.POSIXct(EVENT_END_DTTM ))
setnames(df_dttm, c("EVENT_START_DTTM","EVENT_END_DTTM"))

英文:

I could not find anything here hence my question. Due to data or missing data, I need to manipulate a column to get dates into the time column.

I have a data frame with date & time for EVENT_START_DTTM and just time for EVENT_END_TM.
My logic is that if:

  • (EVENT_START_DTTM <= EVENT_END_TM then is the same day)

  • (EVENT_START_DTTM > EVENT_END_TM then is across midnight and I need to add one day to the date)

    EVENT_START_DTTM EVENT_END_TM
    2020-01-03 09:34:13 10:33:37
    2020-01-03 07:57:24 23:04:38
    2019-12-04 23:42:40 03:38:33
    2019-12-04 22:33:33 00:07:13

In addition, I want to calculate the difference in minutes and the desired results are:

  1. EVENT_START_DTTM EVENT_END_DTTM Difference_min
  2. 2020-01-03&#160;09:34:13 2020-01-03 10:33:37 59
  3. 2020-01-03&#160;07:57:24 2020-01-03 23:04:38 907
  4. 2019-12-04&#160;23:42:40 2019-12-05 03:38:33 237
  5. 2019-12-04&#160;22:33:33 2019-12-05 00:07:13 95

Below is my code

  1. library(data.table)
  2. library(lubridate)
  3. EVENT_START_DTTM &lt;- c(&quot;2020-01-03&#160;09:34:13&quot;, &quot;2020-01-03&#160;07:57:24&quot;,&quot;2019-12-04&#160;23:42:40&quot;, &quot;2019-12-04&#160;22:33:33&quot;)
  4. EVENT_END_DTTM &lt;- c(&quot;2020-01-03 10:33:3&quot;, &quot;2020-01-03 23:04:38&quot;,&quot;2019-12-05 03:38:33&quot;, &quot;2019-12-05 00:07:13&quot;)
  5. df_dttm &lt;- data.frame(as.POSIXct(EVENT_START_DTTM), as.POSIXct(EVENT_END_DTTM ))
  6. setnames(df_dttm, c(&quot;EVENT_START_DTTM&quot;,&quot;EVENT_END_DTTM&quot;) )

答案1

得分: 1

以下是您要翻译的内容:

  1. 您可以使用 `within()` 和数学函数。要比较小时,请使用 `substr()`
  2. d &lt;- within(d, {
  3. EVENT_START_DTTM=as.POSIXct(EVENT_START_DTTM)
  4. EVENT_END_TM=as.POSIXct(paste(substr(EVENT_START_DTTM, 1, 10), EVENT_END_TM)) +
  5. (as.numeric(substr(d[, 1], 12, 13)) &gt; as.numeric(substr(d[, 2], 1, 2)))^1*24*60*60
  6. Difference_min &lt;- EVENT_END_TM - EVENT_START_DTTM
  7. })
  8. d
  9. # EVENT_START_DTTM EVENT_END_TM Difference_min
  10. # 1 2020-01-03 09:34:13 2020-01-03 10:33:37 59.40000 mins
  11. # 2 2020-01-03 07:57:24 2020-01-03 23:04:38 907.23333 mins
  12. # 3 2019-12-04 23:42:40 2019-12-05 03:38:33 235.88333 mins
  13. # 4 2019-12-04 22:33:33 2019-12-05 00:07:13 93.66667 mins
  14. *数据:*
  15. d &lt;- structure(list(EVENT_START_DTTM = structure(4:1, .Label = c(&quot;2019-12-04 22:33:33&quot;,
  16. &quot;2019-12-04 23:42:40&quot;, &quot;2020-01-03 07:57:24&quot;, &quot;2020-01-03 09:34:13&quot;
  17. ), class = &quot;factor&quot;), EVENT_END_TM = structure(c(3L, 4L, 2L,
  18. 1L), .Label = c(&quot;00:07:13&quot;, &quot;03:38:33&quot;, &quot;10:33:37&quot;, &quot;23:04:38&quot;
  19. ), class = &quot;factor&quot;)), class = &quot;data.frame&quot;, row.names = c(NA,
  20. -4L))
英文:

You may use within() and mathematics. To compare the hours as proposed use substr().

  1. d &lt;- within(d, {
  2. EVENT_START_DTTM=as.POSIXct(EVENT_START_DTTM)
  3. EVENT_END_TM=as.POSIXct(paste(substr(EVENT_START_DTTM, 1, 10), EVENT_END_TM)) +
  4. (as.numeric(substr(d[, 1], 12, 13)) &gt; as.numeric(substr(d[, 2], 1, 2)))^1*24*60*60
  5. Difference_min &lt;- EVENT_END_TM - EVENT_START_DTTM
  6. })
  7. d
  8. # EVENT_START_DTTM EVENT_END_TM Difference_min
  9. # 1 2020-01-03 09:34:13 2020-01-03 10:33:37 59.40000 mins
  10. # 2 2020-01-03 07:57:24 2020-01-03 23:04:38 907.23333 mins
  11. # 3 2019-12-04 23:42:40 2019-12-05 03:38:33 235.88333 mins
  12. # 4 2019-12-04 22:33:33 2019-12-05 00:07:13 93.66667 mins

Data:

  1. d &lt;- structure(list(EVENT_START_DTTM = structure(4:1, .Label = c(&quot;2019-12-04 22:33:33&quot;,
  2. &quot;2019-12-04 23:42:40&quot;, &quot;2020-01-03 07:57:24&quot;, &quot;2020-01-03 09:34:13&quot;
  3. ), class = &quot;factor&quot;), EVENT_END_TM = structure(c(3L, 4L, 2L,
  4. 1L), .Label = c(&quot;00:07:13&quot;, &quot;03:38:33&quot;, &quot;10:33:37&quot;, &quot;23:04:38&quot;
  5. ), class = &quot;factor&quot;)), class = &quot;data.frame&quot;, row.names = c(NA,
  6. -4L))

答案2

得分: 1

你可以通过在dplyr中使用mutate函数和格式化日期时间来实现这一点。

  1. library(data.table)
  2. library(lubridate)
  3. library(dplyr)
  4. # 创建数据框
  5. EVENT_START_DTTM <- c("2020-01-03 09:34:13", "2020-01-03 07:57:24",
  6. "2019-12-04 23:42:40", "2019-12-04 22:33:33")
  7. EVENT_END_DTTM <- c("10:33:3", "23:04:38",
  8. "03:38:33", "00:07:13")
  9. df_dttm <- data.frame(as.POSIXct(EVENT_START_DTTM), EVENT_END_DTTM, stringsAsFactors = FALSE)
  10. setnames(df_dttm, c("EVENT_START_DTTM","EVENT_END_DTTM") )
  11. result <-
  12. df_dttm %>%
  13. mutate(start_date = ymd(as.Date(EVENT_START_DTTM)),
  14. start_time = format(strptime(EVENT_START_DTTM, "%Y-%m-%d %H:%M:%S"), "%H:%M:%S")) %>%
  15. rowwise() %>%
  16. mutate(end_date =
  17. if_else(difftime(as.POSIXct(start_time, format = "%H:%M:%S"),
  18. as.POSIXct(EVENT_END_DTTM, format = "%H:%M:%S"),
  19. tz = "", units = "mins") < 0,
  20. start_date, start_date + 1)) %>%
  21. ungroup() %>%
  22. mutate(EVENT_END_DTTM = as.POSIXct(paste(end_date, EVENT_END_DTTM)),
  23. Difference_min = round(difftime(EVENT_END_DTTM, EVENT_START_DTTM, units="mins"), 0))
  24. result
  25. # # A tibble: 4 x 6
  26. # EVENT_START_DTTM EVENT_END_DTTM start_date start_time end_date Difference_min
  27. # <dttm> <dttm> <date> <chr> <date> <drtn>
  28. # 2020-01-03 09:34:13 2020-01-03 10:33:03 2020-01-03 09:34:13 2020-01-03 59 mins
  29. # 2020-01-03 07:57:24 2020-01-03 23:04:38 2020-01-03 07:57:24 2020-01-03 907 mins
  30. # 2019-12-04 23:42:40 2019-12-05 03:38:33 2019-12-04 23:42:40 2019-12-05 236 mins
  31. # 2019-12-04 22:33:33 2019-12-05 00:07:13 2019-12-04 22:33:33 2019-12-05 94 mins
英文:

You can do that by using mutate function in dplyr and formatting datetime

  1. library(data.table)
  2. library(lubridate)
  3. library(dplyr)
  4. # Creating dataframe
  5. EVENT_START_DTTM &lt;- c(&quot;2020-01-03 09:34:13&quot;, &quot;2020-01-03 07:57:24&quot;,
  6. &quot;2019-12-04 23:42:40&quot;, &quot;2019-12-04 22:33:33&quot;)
  7. EVENT_END_DTTM &lt;- c(&quot;10:33:3&quot;, &quot;23:04:38&quot;,
  8. &quot;03:38:33&quot;, &quot;00:07:13&quot;)
  9. df_dttm &lt;- data.frame(as.POSIXct(EVENT_START_DTTM), EVENT_END_DTTM, stringsAsFactors = FALSE)
  10. setnames(df_dttm, c(&quot;EVENT_START_DTTM&quot;,&quot;EVENT_END_DTTM&quot;) )
  11. result &lt;-
  12. df_dttm %&gt;%
  13. mutate(start_date = ymd(as.Date(EVENT_START_DTTM)),
  14. start_time = format(strptime(EVENT_START_DTTM, &quot;%Y-%m-%d %H:%M:%S&quot;), &quot;%H:%M:%S&quot;)) %&gt;%
  15. rowwise() %&gt;%
  16. mutate(end_date =
  17. if_else(difftime(as.POSIXct(start_time, format = &quot;%H:%M:%S&quot;),
  18. as.POSIXct(EVENT_END_DTTM, format = &quot;%H:%M:%S&quot;),
  19. tz = &quot;&quot;, units = &quot;mins&quot;) &lt; 0,
  20. start_date, start_date + 1)) %&gt;%
  21. ungroup() %&gt;%
  22. mutate(EVENT_END_DTTM = as.POSIXct(paste(end_date, EVENT_END_DTTM)),
  23. Difference_min = round(difftime(EVENT_END_DTTM, EVENT_START_DTTM, units=&quot;mins&quot;), 0))
  24. result
  25. # # A tibble: 4 x 6
  26. # EVENT_START_DTTM EVENT_END_DTTM start_date start_time end_date Difference_min
  27. # &lt;dttm&gt; &lt;dttm&gt; &lt;date&gt; &lt;chr&gt; &lt;date&gt; &lt;drtn&gt;
  28. # 2020-01-03 09:34:13 2020-01-03 10:33:03 2020-01-03 09:34:13 2020-01-03 59 mins
  29. # 2020-01-03 07:57:24 2020-01-03 23:04:38 2020-01-03 07:57:24 2020-01-03 907 mins
  30. # 2019-12-04 23:42:40 2019-12-05 03:38:33 2019-12-04 23:42:40 2019-12-05 236 mins
  31. # 2019-12-04 22:33:33 2019-12-05 00:07:13 2019-12-04 22:33:33 2019-12-05 94 mins

huangapple
  • 本文由 发表于 2020年1月4日 00:11:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/59581763.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定