0

My data i get from excel like;

Invoice Cost centre Invoice Category Price DataFeed Reporting Fequency
RIM                  Retail QLD      22.25  WEB      DWM
R5M                  Retail SYD      22.25  BWH      M
.....

my pivot table is like;

df = pd.read_excel(file_path, sheet_name='Invoice Details', usecols="E:F,I,L:M")                       

df['Price'] = df['Price'].astype(float)                                                                
df1 = df.groupby(["Invoice Cost Centre", "Invoice Category"]).agg({'Price': 'sum'}).reset_index()      

df = pd.pivot_table(df, index=["Invoice Cost Centre", "Invoice Category"],                             
                    columns=['Price', 'Reporting Frequency', 'Data Feed'],                             
                    aggfunc=len,                                                                       
                    fill_value=0)                                                                      

df2 = df.merge(df1, left_on=["Invoice Cost Centre", "Invoice Category"],                               
               right_on=["Invoice Cost Centre", "Invoice Category"], how='left').fillna(0)

enter image description here

I want to add total value for price column only. How can I do that in pivot table?

Expected output shouild be like;

Invoice Cost Centre  Invoice Category (22.25,M,BWH) (40,DWM,WEB)...     Price
           D3TM        Retail QLD                                     1907.85
          EQUITYEMP     Retail SYD                                    104.00
           EQUITYEMP                                                   463.15
           EQUITYEMP       ...                                         62.40
           EQUITYEMP                                                   201.95

                                                               **Total=2800**
Ratha
  • 9,434
  • 17
  • 85
  • 163
  • Try to add some example data and expected output based on your example data. This way we can visually see what you want to do. Adding things like `pd.read_excel` or a picture of your data does not add any value to your question. Check out [this](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) thread. – Erfan Dec 17 '19 at 23:24
  • not really following your logic, in terms of having a price only column your first groupby should do the trick? – Umar.H Dec 17 '19 at 23:30
  • @Erfan added more data to my question – Ratha Dec 17 '19 at 23:33
  • @Datanovice I have provided more info. DOes that make sense? – Ratha Dec 17 '19 at 23:33
  • Not really it doesn't make much sense to me atmo – Umar.H Dec 18 '19 at 15:01

0 Answers0