title: Python Pandas filtering dataframe on date
body: <p>I am trying to manipulate a CSV file on a certain date in a certain column.
  I am using pandas (total noob) for that and was pretty successful until i got to dates.
  The CSV looks something like this (with more columns and rows of course).
  These are the columns:</p>
  
  <blockquote>
    <p>Circuit<br>
    Status<br>
    Effective Date</p>
  </blockquote>
  
  <p>These are the values:</p>
  
  <blockquote>
    <p>XXXX001<br>
    Operational<br>
    31-DEC-2007</p>
  </blockquote>
  
  <p>I tried dataframe query (which i use for everything else) without success.
  I tried dataframe loc (which worked for everything else) without success.</p>
  
  <p>How can i get all rows that are older or newer from a given date? If i have other conditions to filter the dataframe, how do i combine them with the date filter?</p>
  
  <p>Here's my "raw" code:</p>
  
  <pre><code>import pandas as pd
  # parse_dates = ['Effective Date']
  # dtypes = {'Effective Date': 'str'}
  df = pd.read_csv("example.csv", dtype=object)
  # , parse_dates=parse_dates, infer_datetime_format=True
  # tried lot of suggestions found on SO
  cols = df.columns
  cols = cols.map(lambda x: x.replace(' ', '_'))
  df.columns = cols
  
  status1 = 'Suppressed'
  status2 = 'Order Aborted'
  pool = '2'
  region = 'EU'
  date1 = '31-DEC-2017'
  
  filt_df = df.query('Status != @status1 and Status != @status2 and Pool == @pool and Region_A == @region')
  filt_df.reset_index(drop=True, inplace=True)
  filt_df.to_csv('filtered.csv')
  # this is working pretty well
  
  supp_df = df.query('Status == @status1 and Effective_Date &lt; @date1')
  supp_df.reset_index(drop=True, inplace=True)
  supp_df.to_csv('supp.csv')
  # this is what is not working at all
  </code></pre>
  
  <p>I tried many approaches, but i was not able to put it together. This is just one of many approaches i tried.. so i know it is perhaps completely wrong, as no date parsing is used.
  supp.csv will be saved, but the dates present are all over the place, so there's no match with the "logic" in this code.
  Thanks for any help!</p>