filtering a pandas Dataframe based on date value

Question

I have the following data imported from a csv file using pandas read_csv:

 instrument         type   from_date  to_date   
0   96000001    W/D & V/L  19951227  19960102
1   96000002   DEED TRUST  19951227  19960102
2   96000003  WARNTY DEED  19951228  19960102
3   96000004   DEED TRUST  19951228  19960102
4   96000005    W/D & V/L  19951228  19960102

I would like to select those rows that fit a date or date range. For instance I want to select only those rows with the date 19951227 in the from_date column or select days that range from from_date of 19951227 to to_date 19960102.

How would I do this?

Andy Hayden · Accepted Answer · 2013-06-12 19:24:42Z

8

Select those with a specific column:

In [11]: df[df['from_date'] == 19951227]
Out[11]:
   instrument        type  from_date   to_date
0    96000001   W/D & V/L   19951227  19960102
1    96000002  DEED TRUST   19951227  19960102

Or combine several queries (you can use | for or)

In [12]: df[(19951227 <= df['from_date']) & (df['to_date'] <= 19960102)]
Out[12]:
   instrument         type  from_date   to_date
0    96000001    W/D & V/L   19951227  19960102
1    96000002   DEED TRUST   19951227  19960102
2    96000003  WARNTY DEED   19951228  19960102
3    96000004   DEED TRUST   19951228  19960102
4    96000005    W/D & V/L   19951228  19960102

Worth noting that these columns are not datetime/Timestamp objects...

To convert these columns to timestamps you could use:

In [21]: pd.to_datetime(df['from_date'].astype(str))
Out[21]:
0   1995-12-27 00:00:00
1   1995-12-27 00:00:00
2   1995-12-28 00:00:00
3   1995-12-28 00:00:00
4   1995-12-28 00:00:00
Name: from_date, dtype: datetime64[ns]

In [22]: df['from_date'] = pd.to_datetime(df['from_date'].astype(str))

In [23]: pd.to_datetime(df['from_date'].astype(str))  # do same for to_date

And query via string representation of the date:

In [24]: df['1995-12-27' == df['from_date']]
Out[24]:
   instrument        type           from_date   to_date
0    96000001   W/D & V/L 1995-12-27 00:00:00  19960102
1    96000002  DEED TRUST 1995-12-27 00:00:00  19960102

edited Jun 12, 2013 at 19:24

answered Jun 12, 2013 at 19:12

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Allan Over a year ago

Andy Hayden, fastest guns in the west.

Aidis Over a year ago

What about doing something like df['1995-12-27' >= df['from_date'] & df['1997-12-27' <= df['from_date'] ] ?

Collectives™ on Stack Overflow

filtering a pandas Dataframe based on date value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related