Slice a Pandas Dataframe based on the results of a function on a column

Question

I want to slice a dataframe using a condition based on a DateTime column's month, element by element:

Met_Monthly_DF = Metsite_DF.iloc[Metsite_DF['DateTime'].month == Month]

I get the error:

builtins.AttributeError: 'Series' object has no attribute 'month'

It works on an element by element basis if I do say:

Months = [DT.month for DT in Metsite_DF['DateTime'].tolist()]

How do I apply this condition?

Answer 1

There are 2 problems:

• for get datetimes like atributes need dt - here dt.month
• for boolean indexing change iloc to loc

Met_Monthly_DF = Metsite_DF.loc[Metsite_DF['DateTime'].dt.month == Month]

Sample:

Metsite_DF = pd.DataFrame({'col':list('abcd'), 
                           'DateTime':pd.date_range('2017-01-01', periods=4, freq='16D')})
print (Metsite_DF)
    DateTime col
0 2017-01-01   a
1 2017-01-17   b
2 2017-02-02   c
3 2017-02-18   d

Month = 2
Met_Monthly_DF = Metsite_DF.loc[Metsite_DF['DateTime'].dt.month == Month]
print (Met_Monthly_DF)
    DateTime col
2 2017-02-02   c
3 2017-02-18   d

---

Or remove iloc, but then obviously need copy for avoid SettingWithCopyWarning:

Met_Monthly_DF = Metsite_DF[Metsite_DF['DateTime'].dt.month == Month].copy()

If you modify values in Met_Monthly_DF later you will find that the modifications do not propagate back to the original data (Metsite_DF), and that Pandas does warning.

Month = 2
Met_Monthly_DF = Metsite_DF[Metsite_DF['DateTime'].dt.month == Month]
#filter dataframe is modified - e.g. added new column for years
Met_Monthly_DF['new'] = Met_Monthly_DF['DateTime'].dt.year
print (Met_Monthly_DF)
    DateTime col   new
2 2017-02-02   c  2017
3 2017-02-18   d  2017

SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

---

Month = 2
Met_Monthly_DF = Metsite_DF[Metsite_DF['DateTime'].dt.month == Month].copy()

Met_Monthly_DF['new'] = Met_Monthly_DF['DateTime'].dt.year
print (Met_Monthly_DF)
    DateTime col   new
2 2017-02-02   c  2017
3 2017-02-18   d  2017