Python Percentage Change by Group

user_13863127 0
Viewed 71

I would like to get the year on year quarterly change in Value1 and Value 2

df =\
pd.DataFrame({'Year':[2010,2010,2010,2010,2009,2009,2009,2009],
              'Quarter':[1,1,2,2,1,1,2,2],
              'Section':['A', 'B', 'A', 'B','A', 'B','A', 'B'],
              'Value1': [1,2,3,4,5,6,7,8],
              'Value2':[10,20,30,40,50,60,70,80]
             })
df.set_index(['Year', 'Quarter', 'Section'], inplace=True)
df

Currently I am doing this:

##Not ideal
df_2009 =\
(df
 .reset_index()
 .where(lambda x: x.Year == 2009)
 .dropna()
 .astype({'Quarter':'int16'})
 .set_index(['Quarter', 'Section'])
 .drop('Year', axis=1)
)

df_2010 =\
(df
 .reset_index()
 .where(lambda x: x.Year == 2010)
 .dropna()
 .astype({'Quarter':'int16'})
 .set_index(['Quarter', 'Section'])
 .drop('Year', axis=1)
)
 
df_2010/df_2009

However, it is not scalable. I wonder it there's better way to do this. e.g. pandas functions or UDF

p.s. the result is created by

(somedata
.groupby(['Year', 'Quarter', 'Section'])
.agg({'Value1':'sum',
      'Value2':'sum'})
)
pandas-groupby pandas python
2 Answers

Are you looking for something like this:

df.groupby(['Quarter','Section']).pct_change(-1)

Output:

                        Value1    Value2
Year Quarter Section                    
2010 1       A       -0.800000 -0.800000
             B       -0.666667 -0.666667
     2       A       -0.571429 -0.571429
             B       -0.500000 -0.500000
2009 1       A             NaN       NaN
             B             NaN       NaN
     2       A             NaN       NaN
             B             NaN       NaN
user_42384080

Another way using pct_change that is not as elegant as @QuangHoang's answer. Adding +1 and .dropna() at the end matches your expected output from running your code. However, I kept the Year column as that will be required if you have more years (besides that it is the same as your ouput from running your code):

a = df.sort_values(['Section', 'Quarter', 'Year']). \
groupby(['Section', 'Quarter']). \
agg({'Value1' : 'pct_change', 'Value2' : 'pct_change'}). \
dropna().sort_values('Quarter') + 1
a

output:

                        Value1      Value2
Year    Quarter Section     
2010    1       A       0.200000    0.200000
                B       0.333333    0.333333
        2       A       0.428571    0.428571
                B       0.500000    0.500000
user_63667700
Related