Combining date and time columns into dd/mm/yyyy hh

问题:

New to R and R studio. I have imported a dataset with the date and time in five seperate columns headed: YYYY MM DD hh mm How do I combine these into a single date column of the format dd/mm/yyyy hh:mm?

Dataset:

回答1:

Based on the new information and questions asked by @LauraKate in the comment.

Answers below are done with packages dplyr and ggplot2:

df <- read.table("http://www.ndbc.noaa.gov/data/realtime2/51206.txt")
names(df) <- c("YYYY", "MM", "DD", "hh", "mm", "WD", "WSPD", "V1", "GST", "WVHT", "DPD", "APD", "MWD", "BARO", "ATMP", "V2", "V3", "V4", "V5")


df2 <- df %>% 
  unite(date, YYYY, MM, DD, sep="-") %>% 
  unite(time, hh, mm, sep=":") %>% 
  mutate(timestamp=paste(date, time) %>% 
           as.POSIXct(., format="%Y-%m-%d %H:%M")) %>% 
  select(timestamp, ATMP) 

ggplot(df2, aes(timestamp, ATMP)) + geom_line()

To get warning points when daily max temperature is above 26 degree (C)?

df3 <- df2 %>% 
    mutate(date=as.Date(timestamp) %>% as.POSIXct) %>% 
    group_by(date) %>% summarise(temp=max(ATMP)) %>% 
    mutate(warnings = ifelse(temp > 26, "red", "black"))

ggplot(df2, aes(timestamp, ATMP)) + 
    geom_line() + 
    geom_point(data=df3 %>% filter(warnings=="red"), aes(date, y=27.5), color="red")

回答2:

Here is the answer which uses lubridate package ( a great package for working with dates). You need to paste or concatenate the various columns in one, wipe away the whitespace, then use lubridate to convert it from string to date format. Note I useed dmy_hms function ( you can use other functions as needed from https://cran.r-project.org/web/packages/lubridate/vignettes/lubridate.html

> df=NULL
> df$H=10
> df$M=54
> df$S=27
> df$DD=12
> df$MM=06
> df$YYYY=2007
> df=as.data.frame(df)
> df
   H  M  S DD MM YYYY
1 10 54 27 12  6 2007
> attach(df)

> df$dat1=gsub(" ","",(paste(DD,"/",MM,"/",YYYY)))
> df
   H  M  S DD MM YYYY      dat1
1 10 54 27 12  6 2007 12/6/2007
> df$tim1=gsub(" ","",(paste(H,":",M,":",S)))
> df
   H  M  S DD MM YYYY      dat1     tim1
1 10 54 27 12  6 2007 12/6/2007 10:54:27



> df$dat2=paste(df$dat1,df$tim1)
> df
   H  M  S DD MM YYYY      dat1     tim1               dat2
1 10 54 27 12  6 2007 12/6/2007 10:54:27 12/6/2007 10:54:27
> str(df)
'data.frame':   1 obs. of  9 variables:
 $ H   : num 10
 $ M   : num 54
 $ S   : num 27
 $ DD  : num 12
 $ MM  : num 6
 $ YYYY: num 2007
 $ dat1: chr "12/6/2007"
 $ tim1: chr "10:54:27"
 $ dat2: chr "12/6/2007 10:54:27"
> library(lubridate)

Attaching package: ‘lubridate’

The following object is masked from ‘package:base’:

    date

> df$dat3=dmy_hms(df$dat2)
> str(df)
'data.frame':   1 obs. of  10 variables:
 $ H   : num 10
 $ M   : num 54
 $ S   : num 27
 $ DD  : num 12
 $ MM  : num 6
 $ YYYY: num 2007
 $ dat1: chr "12/6/2007"
 $ tim1: chr "10:54:27"
 $ dat2: chr "12/6/2007 10:54:27"
 $ dat3: POSIXct, format: "2007-06-12 10:54:27"
> df
   H  M  S DD MM YYYY      dat1     tim1               dat2                dat3
1 10 54 27 12  6 2007 12/6/2007 10:54:27 12/6/2007 10:54:27 2007-06-12 10:54:27

回答3:

You can use lubridate::make_datetime for this.

library(dplyr)
library(lubridate)

df <- data_frame(
  YYYY = sample(2000:2017, 10),
  MM = sample(1:12, 10),
  DD = sample(1:28, 10),
  hh = sample(1:23, 10),
  mm = sample(1:59, 10)
)

mutate(df, 
  date_time = make_datetime(
    year = YYYY, month = MM, day = DD, hour = hh, min = mm
  )
)

#> # A tibble: 10 x 6
#>     YYYY    MM    DD    hh    mm           date_time
#>    <int> <int> <int> <int> <int>              <dttm>
#>  1  2007     8    11     4    27 2007-08-11 04:27:00
#>  2  2002     7     5    23    51 2002-07-05 23:51:00
#>  3  2006     2    25    12    45 2006-02-25 12:45:00
#>  4  2008    10     1     1     1 2008-10-01 01:01:00
#>  5  2000     1    28    10    42 2000-01-28 10:42:00
#>  6  2010     4    12    21    37 2010-04-12 21:37:00
#>  7  2013    12     8    16    58 2013-12-08 16:58:00
#>  8  2014     5    17    18    56 2014-05-17 18:56:00
#>  9  2009     9    27    20    36 2009-09-27 20:36:00
#> 10  2001     3    14    14    14 2001-03-14 14:14:00