从 excel 中读取 mdy_hms AM/PM 到 r [英] Reading mdy_hms AM/PM off excel into r

问题描述

我正在使用 dplyr 和 lubridate.

I am using dplyr and lubridate.

我正在使用 read_excel 将 data.frame 从 excel 导出到 R 中.在 Excel 中，我有一列由 mdy_hms AM 或 PM 组成.在 R 中，我的代码包括:

I am using read_excel to export a data.frame into R from excel. In Excel, I have a column that consists of mdy_hms AM or PM. In R, my code consists of:

df$dateTimeEtc

这是作为示例打印出来的:

And this prints out as an example:

"2017-03-07 11:10:37 UTC" "2017-03-22 10:04:42 UTC" "2017-03-08 09:36:49 UTC"

但是，我尝试过使用:

df <- df %>% 
  mutate(dateTimeEtc = mdy_hms(dateTimeEtc))

以便 R 以 mdy_hms(不确定如何包含 AM/PM)格式识别这些数据点.但是，这行代码将我所有的数据点都转换为 NA.理想情况下，我宁愿 R 将这些数据点作为 ONLY mdy 读取，以便与其他 mdy 数据点进行比较.

So that R recognizes these data points in a mdy_hms (not sure what to do to include the AM/PM) format. However, this line of code converts all my data points into NA. Ideally I'd rather R read these data points as ONLY mdy so I can make comparisons with other mdy data points.

另一个目标是找到该列中的最大和最小数据点.

Another goal is to find the max and min data points in this column.

提前致谢

推荐答案

我建议您尽可能将所有时间线数据保存为日期或日期时间格式，以便进行计算和排序.根据我的经验，mdy 作为最后一步的格式选择更有意义，因为 R 根本不将 mdy 数据视为日期，而是将其视为字符串.因此，如果您尝试计算 mdy 中的任何内容或将其可视化，您将得到意想不到的结果.

I would suggest you keep all your timeline data in a date or datetime format as long as possible, to enable calculations and sorting. In my experience, mdy makes more sense as a formatting choice at the last step, since R does not see mdy data as dates at all, but rather as character strings. So you will get unexpected results if you try to calculate anything in mdy or visualize it.

library(dplyr); library(lubridate)
df = data_frame(dateTimeEtc = c("2017-03-07 11:10:37 UTC", 
                    "2017-03-22 18:04:42 UTC", 
                    "2017-03-08 09:36:49 UTC"))

df
#> # A tibble: 3 x 1
#>   dateTimeEtc            
#>   <chr>                  
#> 1 2017-03-07 11:10:37 UTC
#> 2 2017-03-22 18:04:42 UTC
#> 3 2017-03-08 09:36:49 UTC


# Convert character to datetime POSIXct (if not already that format),
#  then make a character string in mdy format and 12 hour clock. 
df2 <- df %>% 
  mutate(dateTimeEtc2 = ymd_hms(dateTimeEtc),
         as_mdy = format(dateTimeEtc2, '%m/%d/%Y %I:%M:%S %p'))
df2
#> # A tibble: 3 x 3
#>   dateTimeEtc             dateTimeEtc2        as_mdy                
#>   <chr>                   <dttm>              <chr>                 
#> 1 2017-03-07 11:10:37 UTC 2017-03-07 11:10:37 03/07/2017 11:10:37 AM
#> 2 2017-03-22 18:04:42 UTC 2017-03-22 18:04:42 03/22/2017 06:04:42 PM
#> 3 2017-03-08 09:36:49 UTC 2017-03-08 09:36:49 03/08/2017 09:36:49 AM

这篇关于从 excel 中读取 mdy_hms AM/PM 到 r的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！