Why can't python recognize my month column in a dataset?

Question

This is what the dataframe looks like:

Date  Time (HHMM)         Site  Plot  Replicate  Temperature  \
0   2002-05-01          600  Barre Woods    16          5          4.5
1   2002-05-01          600  Barre Woods    21          7          4.5
2   2002-05-01          600  Barre Woods    31          9          6.5
3   2002-05-01          600  Barre Woods    10          2          5.3
4   2002-05-01          600  Barre Woods     2          1          4.0
5   2002-05-01          600  Barre Woods    13          4          5.5
6   2002-05-01          600  Barre Woods    11          3          5.0
7   2002-05-01          600  Barre Woods    28          8          5.0
8   2002-05-01          600  Barre Woods    18          6          4.5
9   2002-05-01         1400  Barre Woods     2          1         10.3
10  2002-05-01         1400  Barre Woods    31          9          9.0
11  2002-05-01         1400  Barre Woods    13          4         11.0
import pandas as pd
import datetime as dt
from datetime import datetime
df=pd.read_csv('F:/data32.csv',parse_dates=['Date'])
df['Date']=pd.to_datetime(df['Date'],format='%m/%d/%y')

This is where i get the error

df2=df.groupby(pd.TimeGrouper(freq='M'))

The error reads:

Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex'

Please post your sample data as text - not images - it makes it very difficult for any to actually test and run stuff with sample to provide an answer. Anyway, what's the exact result you want? — Jon Clements, Aug 14 '16 at 00:17
@JonClements thanks. I fixed it. I want to separate the data into months so i can split my data frame into two: one part having the months: (Jan,Feb,Mar,Oct,Dec) and the other data set containing all the others — Sergio Espejo, Aug 14 '16 at 00:28
Possible duplicate of [pandas dataframe groupby datetime month](http://stackoverflow.com/questions/24082784/pandas-dataframe-groupby-datetime-month) — Merlin, Aug 14 '16 at 00:30
I tried using that as a reference but I keep on getting errors @Merlin — Sergio Espejo, Aug 14 '16 at 00:32

Joe T. Boka · Answer 1 · 2016-08-14T03:18:38.473

1

You can use set_index first:

dfx = df.set_index('Date')

Then, you can groupby:

dfx.groupby(lambda x : x.month).mean() #just for an example I am using .mean()

edited Aug 14 '16 at 03:18

answered Aug 14 '16 at 01:04

Joe T. Boka

6,554
6
29
48

There is no `Date Time` column, so `df.set_index('Date Time')` raises a `KeyError`. – Alicia Garcia-Raboso Aug 14 '16 at 03:15

score 1 · Accepted Answer · answered Aug 14 '16 at 03:14

Group by df['Date'].dt.month. For example, to compute the average temperature you can do the following.

import io
import pandas as pd

data = io.StringIO('''\
Date,Time (HHMM),Site,Plot,Replicate,Temperature
0,2002-05-01,600,Barre Woods,16,5,4.5
1,2002-05-01,600,Barre Woods,21,7,4.5
2,2002-05-01,600,Barre Woods,31,9,6.5
3,2002-05-01,600,Barre Woods,10,2,5.3
4,2002-05-01,600,Barre Woods,2,1,4.0
5,2002-05-01,600,Barre Woods,13,4,5.5
6,2002-05-01,600,Barre Woods,11,3,5.0
7,2002-05-01,600,Barre Woods,28,8,5.0
8,2002-05-01,600,Barre Woods,18,6,4.5
9,2002-05-01,1400,Barre Woods,2,1,10.3
10,2002-05-01,1400,Barre Woods,31,9,9.0
11,2002-05-01,1400,Barre Woods,13,4,11.0
''')

df = pd.read_csv(data)
df['Date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d')

df.groupby(df['Date'].dt.month)['Temperature'].mean()

Output:

Date
5    6.258333
Name: Temperature, dtype: float64

Why can't python recognize my month column in a dataset?

2 Answers2