2

I'm having trouble creating a pivot table from a dataframe with a datetimeindex as the index. Editing to show complete code

The code in question is

unit1 = ["U1", "U1", "U1", "U1", "U1", "U1"]
name1 = ["fn ln", "fn ln2", "fn ln3", "fn ln4", "fn ln5", "fn ln6"]
count1 = [2,4,6,8,10,12]

df = pd.DataFrame( {'Date': pd.Timestamp('2016-01-01'),
                    'Unit': unit1,
                    'Name"': name1,
                    'Count': count1})
df2 = df.set_index(pd.DatetimeIndex(df.Date))
df2['Month'] = df2.index.month

# this line succeeds
pt = pd.pivot_table(df, index=df2.index.month, values='Count')

# this line fails with Series object has no attribute month
pt = pd.pivot_table(df, index=df2.Month.month, values='Count')

The internals for the dataframe (_stat_axis) show that the index field is DatetimeIndex. The month column also has the datetimeindex setting but creating the pivot table still gives Series error.

cryptoref
  • 413
  • 1
  • 7
  • 17
  • I think it is bug - only works create temporary column `df['m'] = df.index.month` and `print pd.pivot_table(df, index='m', aggfunc=np.sum)` – jezrael Jan 30 '16 at 09:03
  • Strangely, this doesn't work. Even adding the values keyword doesn't do the trick. Looks like the only solution is on the actual index. – cryptoref Jan 30 '16 at 21:08
  • If you call column `Month`, then you can use: `pt = pd.pivot_table(df2, index='Month', values='Count')` – jezrael Jan 30 '16 at 21:10

1 Answers1

1

Maybe you can add parameter values to pivot_table:

print df
           Unit   Name  Count
2013-01-01   U1  fn ln      2
2013-01-01   U1  fn ln    200
2013-01-01   U2  fn ln     55

print pd.pivot_table(df, index=df.index.month, values="Count", aggfunc=np.sum)
1    257
Name: Count, dtype: int64
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • This works. I was trying to get it as simple as possible to find the error, looks like i made it too simple. (See edited code above). – cryptoref Jan 30 '16 at 21:08