将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

这将作为示例输出:

"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以仅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屋！