1

I am trying to separate my DATE column in my data frame which has the date stored as %y%m%d %h%m%s , however when I try to using the separate function, as well as following the instructions in the links below, I get NAs... How can I resolve this problem?

r how to separate date time data types

splitting date and time in data frame

Sample Data

structure(list(STATION_NAME = c("PHILADELPHIA INTERNATIONAL AIRPORT PA US", 
"PHILADELPHIA INTERNATIONAL AIRPORT PA US", "PHILADELPHIA INTERNATIONAL AIRPORT PA US", 
"PHILADELPHIA INTERNATIONAL AIRPORT PA US", "PHILADELPHIA INTERNATIONAL AIRPORT PA US", 
"PHILADELPHIA INTERNATIONAL AIRPORT PA US", "PHILADELPHIA INTERNATIONAL AIRPORT PA US", 
"PHILADELPHIA INTERNATIONAL AIRPORT PA US", "PHILADELPHIA INTERNATIONAL AIRPORT PA US", 
"PHILADELPHIA INTERNATIONAL AIRPORT PA US"), DATE = structure(c(1262305260, 
1262305620, 1262307240, 1262314440, 1262328840, 1262332440, 1262336040, 
1262339640, 1262343240, 1262346840), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), HOURLYWETBULBTEMPF = c(33L, 33L, 33L, 34L, 
34L, 34L, 34L, 34L, 35L, 37L), HOURLYPrecip = c("0.01", "0.01", 
"0.02", "0.00", "0.00", "0.00", "0.00", "0.00", "0.00", "0.00"
)), .Names = c("STATION_NAME", "DATE", "HOURLYWETBULBTEMPF", 
"HOURLYPrecip"), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame"))

My attempts

    philly_weather_data<-philly_weather_data%>%
      separate(DATE,into=c('DATE','TIME'),sep = '')

philly_weather_data$DATE <- as.POSIXct(as.character(philly_weather_data$DATE),
                                           format =  "%Y%m%d%H%M")

    philly_weather_data$DATE <- as.character(as.Date(philly_weather_data$DATE))
    philly_weather_data$TIME <- format(philly_weather_data$DATE, "%T")
Anonymous coward
  • 2,061
  • 1
  • 16
  • 29
NBE
  • 641
  • 2
  • 11
  • 33
  • Why not create a copy of the datetime column and then format the two separately - one to retain date part, while the other for the time part – SmitM Aug 31 '18 at 15:37
  • 1
    `philly_weather_data %>% mutate(time = format(DATE, "%T"), date = as.Date(DATE))`? – phiver Aug 31 '18 at 15:43
  • @phiver That is exactly what I was looking for thanks! Question though.. Do you have any idea why the separate command didn't work? – NBE Aug 31 '18 at 15:46
  • 1
    @KWANGER - the `sep = ''` is looking for no space. Your code would work like this: `philly_weather_data %>% separate(as.character(DATE), into = c("Datepart", "Timepart"), sep = " ")` – SmitM Aug 31 '18 at 15:47

4 Answers4

3

We can use as.Date with format

library(dplyr)
df1 %>% 
  mutate(Date = as.Date(DATE), times = format(DATE, "%H:%M:%S"))
# A tibble: 10 x 6
#   STATION_NAME             DATE                HOURLYWETBULBTE… HOURLYPrecip Date       times 
#   <chr>                    <dttm>                         <int> <chr>        <date>     <chr> 
# 1 PHILADELPHIA INTERNATIO… 2010-01-01 00:21:00               33 0.01         2010-01-01 00:21…
# 2 PHILADELPHIA INTERNATIO… 2010-01-01 00:27:00               33 0.01         2010-01-01 00:27…
# 3 PHILADELPHIA INTERNATIO… 2010-01-01 00:54:00               33 0.02         2010-01-01 00:54…
# 4 PHILADELPHIA INTERNATIO… 2010-01-01 02:54:00               34 0.00         2010-01-01 02:54…
# 5 PHILADELPHIA INTERNATIO… 2010-01-01 06:54:00               34 0.00         2010-01-01 06:54…
# 6 PHILADELPHIA INTERNATIO… 2010-01-01 07:54:00               34 0.00         2010-01-01 07:54…
# 7 PHILADELPHIA INTERNATIO… 2010-01-01 08:54:00               34 0.00         2010-01-01 08:54…
# 8 PHILADELPHIA INTERNATIO… 2010-01-01 09:54:00               34 0.00         2010-01-01 09:54…
# 9 PHILADELPHIA INTERNATIO… 2010-01-01 10:54:00               35 0.00         2010-01-01 10:54…
#10 PHILADELPHIA INTERNATIO… 2010-01-01 11:54:00               37 0.00         2010-01-01 11:54…
akrun
  • 874,273
  • 37
  • 540
  • 662
2

Here is one option. Convert the DATE column and then use the separate function. You can convert the DATE to date class later. 

library(dplyr)
library(tidyr)

dat2 <- dat %>%
  mutate(DATE = as.character(DATE)) %>%
  separate(DATE, into = c("DATE", "Time"), sep = " ") %>%
  mutate(DATE = as.Date(DATE))
dat2
# # A tibble: 10 x 5
#   STATION_NAME                             DATE       Time     HOURLYWETBULBTEMPF HOURLYPrecip
#   <chr>                                    <date>     <chr>                 <int> <chr>       
# 1 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 00:21:00                 33 0.01        
# 2 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 00:27:00                 33 0.01        
# 3 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 00:54:00                 33 0.02        
# 4 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 02:54:00                 34 0.00        
# 5 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 06:54:00                 34 0.00        
# 6 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 07:54:00                 34 0.00        
# 7 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 08:54:00                 34 0.00        
# 8 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 09:54:00                 34 0.00        
# 9 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 10:54:00                 35 0.00        
# 10 PHILADELPHIA INTERNATIONAL AIRPORT PA US 2010-01-01 11:54:00                 37 0.00
www
  • 38,575
  • 12
  • 48
  • 84
1

Try using substr instead:

philly_weather_data$TIME <- substr(philly_weather_data$DATE, 12, 20)
C-x C-c
  • 1,261
  • 8
  • 20
  • That works to create a new column for time but I also want to get ride of the time from the DATE column as well.. – NBE Aug 31 '18 at 15:43
  • Then follow that with: `philly_weather_data$DATE <- substr(philly_weather_data$DATE, 1, 10)` – C-x C-c Aug 31 '18 at 15:49
1

As per my comment above and drawing from your own attempt, this line of code works:

philly_weather_data <- philly_weather_data %>% 
                       separate(as.character(DATE), into = c("Datepart", "Timepart"), sep = " ")
SmitM
  • 1,366
  • 1
  • 8
  • 14