给定本地时间fromLocalTime
和state
,我想创建一个名为hourUTC
的列,该列只返回小时(UTC时间(
因此,给定数据:
> dput(head(smart1,10))
structure(list(fromLocalTime = structure(c(1567086360, 1567604820,
1568037120, 1571344680, 1565797140, 1568641740, 1565983980, 1567100520,
1569963060, 1563888180), class = c("POSIXct", "POSIXt"), tzone = ""),
state = c("GA", "GA", "GA", "GA", "TX", "TX", "TX", "TX",
"TX", "FL")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA,
-10L))
> data.frame(smart1)
fromLocalTime state
1 2019-08-29 09:46:00 GA
2 2019-09-04 09:47:00 GA
3 2019-09-09 09:52:00 GA
4 2019-10-17 16:38:00 GA
5 2019-08-14 11:39:00 TX
6 2019-09-16 09:49:00 TX
7 2019-08-16 15:33:00 TX
8 2019-08-29 13:42:00 TX
9 2019-10-01 16:51:00 TX
10 2019-07-23 09:23:00 FL
我想要这样的输出:
> data.frame(smart1)
fromLocalTime state hourUTC
1 2019-08-29 09:46:00 GA 13
2 2019-09-04 09:47:00 GA 13
3 2019-09-09 09:52:00 GA 13
4 2019-10-17 16:38:00 GA 20
5 2019-08-14 11:39:00 TX 16
6 2019-09-16 09:49:00 TX 14
7 2019-08-16 15:33:00 TX 20
8 2019-08-29 13:42:00 TX 18
9 2019-10-01 16:51:00 TX 21
10 2019-07-23 09:23:00 FL 13
在excel中,我使用这个公式来获取基于状态的时区。我知道这并不是包罗万象的,但就我的数据而言,这就足够了:=如果(ISNUMBER(搜索(A1,"WA,OR,CA,NV"新泽西州,DE,MD,WV,VA,NC,SC,GA,FL,DC(,"东部",如果(ISNUMBER(SEARCH(A1,"AK"((,"阿拉斯加",如果
使用您提供的表格,如果您只是尝试近似(而不担心夏令时(,您可以执行类似的操作
smart1 %>% mutate(
approxUTC = lubridate::hour(fromLocalTime) + case_when(
state %in% c("WA","OR","CA","NV")~8,
state %in% c("MT","ID","WY","UT","CO","AZ","NM")~7,
state %in% c("ND","SD","NE","KS","OK","TX","MN","IA","MO","AR","LA","WI","IL","TN","MS","AL")~6,
state %in% c("MI","IN","OH","PA","NY","VT","ME","NH","MA","RI","CT","KY","NJ","DE","MD","WV","VA","NC","SC","GA","FL","DC")~5,
state %in% c("AK")~9,
state %in% c("HI")~10,
TRUE ~ NA_real_))