CODEWITHSUNDEEP
pythondatetimepandassliceseries
Lars Larsson
Lars Larsson

Reputation: 585

How do I slice a pandas time series on dates not in the index?

I have a time series indexed by datetime.date. Here are the first knots of the series:

1999-12-31  0
2000-06-30  170382.118454
2000-12-29  -319260.443362

I want to slice from the beginning of the series until Dec 28th 2000, but this doesn't work since that date is not in the index (I get a KeyError when I try original_series[:datetime.date(2000,12,28)]. I've also tried converting the index to timestamps, but that gives very spurious results (it manufactures fake knots, see below), so I wondered if there's a good approach to this problem.

test = pd.Series(original_series.values, map(pd.Timestamp, original_series.index))

At a first glance, this looks alright:

1999-12-31         0.000000
2000-06-30    170382.118454
2000-12-29   -319260.443362

But then I try to do my slicing (where do those extra days in January 2000 come from?):

In [84]: test[:'2000-12-28']
Out[84]: 
1999-12-31         0.000000
2000-06-30    170382.118454
2000-01-03    -71073.979016
2000-01-04    100498.744748
2000-01-05     91104.743684
2000-01-06     82290.255459

Upvotes: 10

Views: 8772

Answers (2)

Žygimantas
Žygimantas

Reputation: 331

There is a simple way to do it without converting it into a time-series object.

Scenario when your index is not a date:

Your df:
index   date     data
0    2000-01-01 10
1    2000-01-02 20
2    2000-01-03 12

First, convert your date into date-time format:

df["date"] = pd.to_datetime(df["date"])

Second change index to date:

df = df.set_index("date")

Your df should now look like this:

date       data
2000-01-01 10
2000-01-02 20
2000-01-03 12

Lastly, you can simply manipulate the rows by using:

df = df['2000-01-02':'2000-01-03']

Your df will now look like this:

date       data
2000-01-02 20
2000-01-03 12

Upvotes: 0

Colonel Beauvel
Colonel Beauvel

Reputation: 31171

You can simply do, if ts is your time.serie:

In [77]: ts = pd.Series([99,65],index=pd.to_datetime(['2000-12-24','2000-12-30']))

In [78]: ts
Out[78]:
2000-12-24    99
2000-12-30    65
dtype: int64

In [79]: ts[ts.index<=pd.to_datetime('2000-12-28')]
Out[79]:
2000-12-24    99
dtype: int64

If you have index as string just proceed with:

ts[ts.index.map(pd.to_datetime)<=pd.to_datetime('2000-12-28')]

Upvotes: 7

Related Questions