Split Date-Time Column into Date and Time Variables

Split date-time column into Date and time variables

df$Date <- as.Date(df$Start) # already got this one from the answers above
df$Time <- format(as.POSIXct(df$Start), format = "%H:%M:%S") 

Use as.Date to convert 'Start' to a variables of class Date. For the time variable, we first convert 'Start' to POSIXct. Then use format to extract the time component as a string.

Splitting timestamp column into separate date and time columns

I'm not sure why you would want to do this in the first place, but if you really must...

df = pd.DataFrame({'my_timestamp': pd.date_range('2016-1-1 15:00', periods=5)})

>>> df
         my_timestamp
0 2016-01-01 15:00:00
1 2016-01-02 15:00:00
2 2016-01-03 15:00:00
3 2016-01-04 15:00:00
4 2016-01-05 15:00:00

df['new_date'] = [d.date() for d in df['my_timestamp']]
df['new_time'] = [d.time() for d in df['my_timestamp']]

>>> df
         my_timestamp    new_date  new_time
0 2016-01-01 15:00:00  2016-01-01  15:00:00
1 2016-01-02 15:00:00  2016-01-02  15:00:00
2 2016-01-03 15:00:00  2016-01-03  15:00:00
3 2016-01-04 15:00:00  2016-01-04  15:00:00
4 2016-01-05 15:00:00  2016-01-05  15:00:00

The conversion to CST is more tricky. I assume that the current timestamps are 'unaware', i.e. they do not have a timezone attached? If not, how would you expect to convert them?

For more details:

https://docs.python.org/2/library/datetime.html

How to make an unaware datetime timezone aware in python

EDIT

An alternative method that only loops once across the timestamps instead of twice:

new_dates, new_times = zip(*[(d.date(), d.time()) for d in df['my_timestamp']])
df = df.assign(new_date=new_dates, new_time=new_times)

How to Split Time and Date in a Column in R

Try:

Hours <- format(as.POSIXct(INV$`Date Assigned`, "%Y-%m-%d %H:%M:%S", tz = ""), format = "%H:%M")

Dates <- format(as.Date(INV$`Date Assigned`,"%Y-%m-%d"), format = "%d/%m/%Y")

Output:

Hours
[1] "14:25"

Dates
[1] "02/09/2017"

Of course if you want them as columns in your dataframe, you need to assign them as such:

INV$Hours <- format(as.POSIXct(INV$`Date Assigned`, "%Y-%m-%d %H:%M:%S", tz = ""), format = "%H:%M")

INV$Dates <- format(as.Date(INV$`Date Assigned`,"%Y-%m-%d"), format = "%d/%m/%Y")

Output:

        Date Assigned Hours      Dates
1 2017-09-02 14:25:00 14:25 02/09/2017

Split datetime into date and time

Always use a date library, to avoid the many errors that can happen when you try to do it yourself. In this case, it looks like November the 31st, which is not a valid day.

https://docs.python.org/3/library/datetime.html#datetime.datetime.strptime

Of course, you'll have to help it with the format. We're just going to ignore the T, by making it a literal in the format string. See What exactly does the T and Z mean in timestamp?

import datetime
format_string = "%Y-%m-%dT%H:%M:%S.%f"
date_string = "2015-11-31T13:45:00.000"
datetime.datetime.strptime(date_string, format_string)

builtins.ValueError: day is out of range for month

Once you have the datetime, it will be relatively easy to print out whatever components you want with strftime. Of course, you could just split on 'T' if that literal is always there, but then you wouldn't really have reliable time objects. It wouldn't tell you that Nov 31 is not a valid day.

How to split the time from the date and time variable in R?

If your data is something like this :

df <- structure(list(Date_and_time = c("2018-09-21T13:00", "2019-12-24T09:07", 
"2020-01-24T12:28")), row.names = c(NA, -3L), class = "data.frame")
df
#     Date_and_time
#1 2018-09-21T13:00
#2 2019-12-24T09:07
#3 2020-01-24T12:28

You can get data in desired format by doing :

library(dplyr)

df %>%
   mutate(Date_and_time = lubridate::ymd_hm(Date_and_time), 
          Date = as.Date(Date_and_time), 
          Time = format(Date_and_time, "%H:%M:%S"))

#        Date_and_time       Date     Time
#1 2018-09-21 13:00:00 2018-09-21 13:00:00
#2 2019-12-24 09:07:00 2019-12-24 09:07:00
#3 2020-01-24 12:28:00 2020-01-24 12:28:00

---

This can also be done using base R :

df$Date_and_time <- as.POSIXct(df$Date_and_time, format = "%Y-%m-%dT%H:%M")

transform(df, Date = as.Date(Date_and_time), 
              Time = format(Date_and_time, "%H:%M:%S"))

Splitting a column into date/time in tidyverse

Since you tagged this with tidyverse, here's a simple approach with lubridate:

library(dplyr)
library(lubridate)
data %>%
  mutate(Date = as.Date(mdy_hms(`Trip Start Timestamp`)), 
         Time = format(mdy_hms(`Trip Start Timestamp`), "%I:%M:%S %p"))
#    Trip Start Timestamp       Date        Time
#1 12/01/2019 12:30:00 AM 2019-12-01 12:30:00 AM
#2 12/01/2019 12:31:00 AM 2019-12-01 12:31:00 AM
#3 12/01/2019 12:32:00 AM 2019-12-01 12:32:00 AM

Example Data

data <- structure(list(`Trip Start Timestamp` = c("12/01/2019 12:30:00 AM", 
"12/01/2019 12:31:00 AM", "12/01/2019 12:32:00 AM")), class = "data.frame", row.names = c(NA, 
-3L))

---

Related Topics