CODEWITHSUNDEEP
pythonpandaspivotpivot-table
Exi
Exi

Reputation: 320

Mean in Pandas Pivot table

I've got a dataset of positive values between zero and one. I wanted to get mean values for each month in each year. If I apply the pivot method with aggfunc='mean' I get negative values, which can't be right.

My code snippet:

pv = pd.pivot_table(df_mb, index=df_mb.index.month, columns=df_mb.index.year, aggfunc='mean')


    2012        2013
1   -0.106180   0.021999
2   -0.089774   0.054606
3   -0.174909   0.027096
4   -0.062836   0.089484
5   -0.041830   0.103343
6   -0.138582   0.078373
7   -0.256108   -0.139217
8   -0.425878   -0.037988
9   -0.046610   -0.029985
10  -0.066550   0.158140
11  -0.100758   NaN
12  -0.043051   NaN

Am I doing something wrong, or is it maybe a bug?

I'm using:

Upvotes: 1

Views: 3096

Answers (1)

jezrael
jezrael

Reputation: 862761

I think it is no bug, only negative values in data.

You can check them if df_mb is Series:

print (df_mb[df_mb < 0])

Sample:

rng = pd.date_range('2012-04-03', periods=10, freq='2M')
df_mb = pd.Series([1,-2,5,4,8,6,-3,2,4,5], index=rng)  
#print (df_mb)

print (df_mb[df_mb < 0])
2012-06-30   -2
2013-04-30   -3
dtype: int64

df = df_mb.to_frame('data')
print (df)
            data
2012-04-30     1
2012-06-30    -2
2012-08-31     5
2012-10-31     4
2012-12-31     8
2013-02-28     6
2013-04-30    -3
2013-06-30     2
2013-08-31     4
2013-10-31     5

Or check if column data:

print (df[df['data'] < 0])
            data
2012-06-30    -2
2013-04-30    -3

Solution with one column DataFrame, because your code not working in last pandas version:

pv = df.pivot_table(index=df.index.month, 
                       columns=df.index.year, 
                       values = 'data', 
                       aggfunc='mean')
print (pv)
    2012  2013
2    NaN   6.0
4    1.0  -3.0
6   -2.0   2.0
8    5.0   4.0
10   4.0   5.0
12   8.0   NaN

Upvotes: 1

Related Questions