2

I have a DataFrame with a column of type datetime64[ns]. I want to filter on both year and quarter. Any suggestions on how to do this?

I have tried this cumbersome solution

data = data[(pd.Series(pd.DatetimeIndex(data['MatCalID']).year).isin([2018]) & pd.Series(pd.DatetimeIndex(data['MatCalID']).quarter).isin([2,3]))]

Why this complicated solution?:

  • It is necessary to use pd.DatetimeIndex in order to access 'year' and 'quarter'
  • It is necessary to use pd.Series to use 'isin'

Unfortunately I get 'Unalignable boolean Series key provided' as an error.

Does someone know how to do this?

KieranPC
  • 8,525
  • 7
  • 22
  • 25
  • 1
    Is your version of pandas 0.15.0 or higher? if so this seems unnecessary to convert to a datetimeIndex as you can access `year` and `quarter` via `.dt` e.g. `price['MatCalID'].dt.year` also are you wanting something like `data[(price['MatCalID'].dt.year == 2008) & (price['MatCalID'].dt.quarter.isin([2,3]))]`? – EdChum Apr 05 '15 at 21:44
  • Also what's the relationship between `data` and `price`? are these views of the same df? of the df's? – EdChum Apr 05 '15 at 21:48
  • Thanks Ed, 'dt' works perfectly. And I meant data, not price. It has now been changed in the question. – KieranPC Apr 05 '15 at 22:25
  • So when you say working you mean I should post as an answer? – EdChum Apr 05 '15 at 22:36
  • Yeah, you should do that – KieranPC Apr 06 '15 at 20:58

1 Answers1

4

If you're running pandas version 0.15.0 or higher it is not necessary to cast the Series to a DateTimeIndex as there is now a new datetime attribute .dt. You can use this to access the year and quarter attributes of the datetime objects and use a boolean condition to filter the df:

data[(data['MatCalID'].dt.year == 2008) & (data['MatCalID'].dt.quarter.isin([2,3]))]

Normally you should be using isin when you want to test membership of multiple values rather than a single value

EdChum
  • 376,765
  • 198
  • 813
  • 562