CODEWITHSUNDEEP
pythonpandas
user1911092
user1911092

Reputation: 4241

subset a date-time df in pandas python

Basic question, but I keep running into issues here.

I have a df:

df:
            val
date
2012-01-01  4.2      
2012-01-02  3.7
2012-01-03  6.2
2012-01-04  1.2
2012-01-05  2.4
2012-01-06  2.3
2012-01-08  4.5

As you can see, 2012-01-07 does not exist. If I were to write: 

exDate = 20120107
df.ix[str(exDate)]

I get a Key Error.

In this case, I want to change my exDate to 20120106 (the max number below 20120107). What is the easiest way to check an index to see if a date exists and if the date does not exist, select the next highest below that number (and then return in YYYYmmdd format?)

Also, more generally, what is the easiest way to grab the subset of the index with dates below 20120107 for example? I seem to be doing well with ranges, but having a hard time selecting above or below a date.

Thanks.

Upvotes: 1

Views: 2650

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375845

To grab the sub-DataFrame with dates below 20120107 you could use:

In [11]: df[:'2012-01-07']
Out[11]: 
            val
date           
2012-01-01  4.2
2012-01-02  3.7
2012-01-03  6.2
2012-01-04  1.2
2012-01-05  2.4
2012-01-06  2.3

To pick the last row using irow:

In [12]: df[:'2012-01-07'].irow(-1)
Out[12]: 
val    2.3
Name: 2012-01-06

So the last valid date:

In [13]: df[:'2012-01-07'].irow(-1).name
Out[13]: '2012-01-06'

Upvotes: 1

Related Questions