new_coder new_coder - 5 months ago 10
Python Question

finding annual averages using pandas in python

my data looks like this

unit year SURQ
10001 2012 89
10002 2012 83
10003 2012 88
10001 2013 75
10002 2013 69
10003 2013 59
10001 2014 36
10002 2014 59
10003 2014 84


I want to find the annual average values for SURQ by unit. For example average SURQ in 2012 for unit 10001 = ???

I tried using pivot tables

avgSURQ = pd.pivot_table(df,index=['year'])


and groupby

avgSURQ = SURQ.groupby('year')


but I think I am getting the syntax wrong so it doesn't work. I am new to python so I might be misunderstanding the examples online.

Answer
>>> df.reset_index().groupby(['unit','year'])['SURQ'].mean()
unit   year
10001  2012    89
       2013    75
       2014    36
10002  2012    83
       2013    69
       2014    59
10003  2012    88
       2013    59
       2014    84
Name: SURQ, dtype: int64
Comments