% Difference Pivot Table python

user_9646788 0
Viewed 37

I have a sample dataframe/table as below and I would like to do a simple pivot table in Python to calculate the % difference from the previous year.

DataFrame
Year Month Count Amount Retailer
2019  5      10    100    ABC
2019  3      8     80    XYZ
2020  3      8     80    ABC
2020  5      7     70    XYZ
...

Expected Output

    MONTH   %Diff 
ABC  7       -0.2
XYG  8       -0.125

Thanks,

EDIT: I would like to reiterate that I would like to create the following table below. Not to do a join with the two tables

pivot-table python
1 Answers

It looks like you need a groupby not pivot

gdf = df.groupby(['Retailer']).agg({'Amount': 'pct_change'})

Then rename and merge with original df

df = gdf.rename(columns={'Amount': '%Diff'}).dropna().merge(df, how='left', left_index=True, right_index=True)

   %Diff  Year  Month  Count  Amount Retailer
2 -0.200  2020      3      7      80      ABC
3 -0.125  2020      5      8      70      XYZ
user_60319950
Related