How to slice a Pandas Dataframe based on datetime index

0000 Source

This has been bothering me for ages now:

Given a simple pandas DataFrame

>>> df

Timestamp     Col1
2008-08-01    0.001373
2008-09-01    0.040192
2008-10-01    0.027794
2008-11-01    0.012590
2008-12-01    0.026394
2009-01-01    0.008564
2009-02-01    0.007714
2009-03-01   -0.019727
2009-04-01    0.008888
2009-05-01    0.039801
2009-06-01    0.010042
2009-07-01    0.020971
2009-08-01    0.011926
2009-09-01    0.024998
2009-10-01    0.005213
2009-11-01    0.016804
2009-12-01    0.020724
2010-01-01    0.006322
2010-02-01    0.008971
2010-03-01    0.003911
2010-04-01    0.013928
2010-05-01    0.004640
2010-06-01    0.000744
2010-07-01    0.004697
2010-08-01    0.002553
2010-09-01    0.002770
2010-10-01    0.002834
2010-11-01    0.002157
2010-12-01    0.001034

How do I separate it so that a new DataFrame equals the entries in df for the dates between 2009-05-01 and 2010-03-01

>>> df2

Timestamp     Col1
2009-05-01    0.039801
2009-06-01    0.010042
2009-07-01    0.020971
2009-08-01    0.011926
2009-09-01    0.024998
2009-10-01    0.005213
2009-11-01    0.016804
2009-12-01    0.020724
2010-01-01    0.006322
2010-02-01    0.008971
2010-03-01    0.003911
pythonpandasslice

Answers

answered 5 months ago RafaelC #1

IIUC, a simple slicing?

from datetime import datetime
df2 = df[(df.Timestamp >= datetime(2009, 05, 01)) &
         (df.Timestamp <= datetime(2010, 03, 01))]

answered 5 months ago user1319128 #2

If you have set the "Timestamp" column as the index , then you can simply use

df['2009-05-01' :'2010-03-01']

answered 5 months ago Kunal Vats #3

You can do something like:

df2 = df.set_index('Timestamp')['2009-05-01' :'2010-03-01'] print(df2)

answered 5 months ago sacul #4

For the sake of giving you options, here is a solution using pd.Series.between:

df2 = df[df['Timestamp'].between('2009-05-01', '2010-03-01')]

This works regardless of whether your Timestamp column is a pd.Timestamp or just a regular string dtype, but in any case, for date functionality in a pd.DataFrame, it's always a good idea to make sure your column is a proper Timestamp: if you haven't already done so, consider using df['Timestamp'] = pd.to_datetime(df['Timestamp']) before using the solution above.

comments powered by Disqus