62

I have a pandas dataframe with over 1000 timestamps (below) that I would like to loop through:

2016-02-22 14:59:44.561776

I'm having a hard time splitting this time stamp into 2 columns- 'date' and 'time'. The date format can stay the same, but the time needs to be converted to CST (including milliseconds).

Thanks for the help

FObersteiner
  • 22,500
  • 8
  • 42
  • 72
Tom
  • 685
  • 1
  • 6
  • 10

9 Answers9

69

Had same problem and this worked for me.

Suppose the date column in your dataset is called "date"

import pandas as pd
df = pd.read_csv(file_path)

df['Dates'] = pd.to_datetime(df['date']).dt.date
df['Time'] = pd.to_datetime(df['date']).dt.time

This will give you two columns "Dates" and "Time" with splited dates.

Okroshiashvili
  • 3,677
  • 2
  • 26
  • 40
50

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 a datetime object aware (not naive)

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)

EDIT 2023

This is how would do this now:

df = df.assign(
    new_date=df["my_timestamp"].dt.date, 
    new_time=df["my_timestamp"].dt.time
)

In fairness, the assign method was introduced in version 0.16.0 released March 22, 2015, now long before when I originally replied to this post back in 2016.

Alexander
  • 105,104
  • 32
  • 201
  • 196
  • 1
    This answer, though it has been accepted as the right one, does not take advantage of pandas' vector functions. You don't have to loop through items in a dataframe - that's one of the main reasons why people like using them. You can just do it to the whole series (read: column) all at once, as is shown in many examples on this page. – Nesha25 Jul 14 '23 at 21:56
  • @Nesha25 You are so right. This answer is quite old and I've learned a thing or two since I wrote it! I've edited my response. – Alexander Jul 15 '23 at 18:30
18

The easiest way is to use the pandas.Series dt accessor, which works on columns with a datetime dtype (see pd.to_datetime). For this case, pd.date_range creates an example column with a datetime dtype, therefore use .dt.date and .dt.time:

df = pd.DataFrame({'full_date': pd.date_range('2016-1-1 10:00:00.123', periods=10, freq='5H')})
df['date'] = df['full_date'].dt.date
df['time'] = df['full_date'].dt.time

In [166]: df
Out[166]:
                full_date        date             time
0 2016-01-01 10:00:00.123  2016-01-01  10:00:00.123000
1 2016-01-01 15:00:00.123  2016-01-01  15:00:00.123000
2 2016-01-01 20:00:00.123  2016-01-01  20:00:00.123000
3 2016-01-02 01:00:00.123  2016-01-02  01:00:00.123000
4 2016-01-02 06:00:00.123  2016-01-02  06:00:00.123000
5 2016-01-02 11:00:00.123  2016-01-02  11:00:00.123000
6 2016-01-02 16:00:00.123  2016-01-02  16:00:00.123000
7 2016-01-02 21:00:00.123  2016-01-02  21:00:00.123000
8 2016-01-03 02:00:00.123  2016-01-03  02:00:00.123000
9 2016-01-03 07:00:00.123  2016-01-03  07:00:00.123000
Trenton McKinney
  • 56,955
  • 33
  • 144
  • 158
Anton Protopopov
  • 30,354
  • 12
  • 88
  • 93
3

If your timestamps are already in pandas format (not string), then:

df["date"] = df["timestamp"].date
dt["time"] = dt["timestamp"].time

If your timestamp is a string, you can parse it using the datetime module:

from datetime import datetime
data1["timestamp"] = df["timestamp"].apply(lambda x: \
    datetime.strptime(x,"%Y-%m-%d %H:%M:%S.%f"))

Source: http://pandas.pydata.org/pandas-docs/stable/timeseries.html

Mathieu B
  • 451
  • 3
  • 12
3

If your timestamp is a string, you can convert it to a datetime object:

from datetime import datetime

timestamp = '2016-02-22 14:59:44.561776'
dt = datetime.strptime(timestamp, '%Y-%m-%d %H:%M:%S.%f')

From then on you can bring it to whatever format you like.

pp_
  • 3,435
  • 4
  • 19
  • 27
1

Try

s = '2016-02-22 14:59:44.561776'

date,time = s.split()

then convert time as needed.

If you want to further split the time,

hour, minute, second = time.split(':')
wrkyle
  • 529
  • 1
  • 13
  • 36
0

try this:

def time_date(datetime_obj):
    date_time = datetime_obj.split(' ')
    time = date_time[1].split('.')
    return date_time[0], time[0]
Fabio Lamanna
  • 20,504
  • 24
  • 90
  • 122
Nitin Sanghi
  • 32
  • 1
  • 1
0

In addition to @Alexander if you want a single liner

df['new_date'],df['new_time'] = zip(*[(d.date(), d.time()) for d in df['my_timestamp']])
arun
  • 461
  • 4
  • 7
0

If your timestamp is a string, you can convert it to pandas timestamp before splitting it.

#convert to pandas timestamp
data["old_date"] = pd.to_datetime(data.old_date)

#split columns
data["new_date"] = data["old_date"].dt.date
data["new_time"] = data["old_date"].dt.time
sule mohammed
  • 117
  • 5