CODEWITHSUNDEEP
rdatetimedataframetidyr
NBE
NBE

Reputation: 651

separating date and time column in data frame in r

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")

Upvotes: 1

Views: 861

Answers (4)

akrun
akrun

Reputation: 887138

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…

Upvotes: 3

SmitM
SmitM

Reputation: 1376

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 = " ")

Upvotes: 1

www
www

Reputation: 39154

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

Upvotes: 2

C-x C-c
C-x C-c

Reputation: 1311

Try using substr instead:

philly_weather_data$TIME <- substr(philly_weather_data$DATE, 12, 20)

Upvotes: 1

Related Questions