CODEWITHSUNDEEP
pandaspivot-table
Benjamin TAN
Benjamin TAN

Reputation: 67

How to get total in pandas pivot table

   AssetLifecycleStatus                             Balance  Deployed
       Type       Environment OS     Model                         
   SupplierA      Network 1   Win 10 Model 1                4.0      10.0   
                                     Model 2                4.0       7.0   
                  Network 2   Win 10 Model 1               10.0       5.0   
                              Win 7  Model 2                NaN       1.0   
                                     Model 3                NaN       6.0  
   SupplierB      Network 1   Win 10 Model 1                4.0       8.0   
                                     Model 2                4.0       5.0   
                  Network 2   Win 10 Model 3               10.0       5.0   
                              Win 7  Model 2                NaN       1.0   
                                     Model 3                NaN       6.0

Trying to get the below result with total:

   AssetLifecycleStatus                             Balance  Deployed
       Type       Environment OS     Model                         
   SupplierA      Network 1   Win 10 Model 1                4.0      10.0   
                                     Model 2                4.0       7.0   
                  Network 2   Win 10 Model 1               10.0       5.0   
                              Win 7  Model 2                NaN       1.0   
                                     Model 3                NaN       6.0  
   SupplierB      Network 1   Win 10 Model 1                4.0       8.0   
                                     Model 2                4.0       5.0   
                  Network 2   Win 10 Model 3               10.0       5.0   
                              Win 7  Model 2                NaN       1.0   
                                     Model 3                NaN       6.0
                                       Total               36.0      54.0  

    filter1 = df[(df["Type"]!="")]
    table1 = pd.pivot_table(filter1,index=                
    ["Type","Env","OperSys","Model"],columns=
    ["AssetLifecycleStatus"],values="Serial         
    Number",aggfunc='count',margins=True,dropna=True)
    table1 = table1.reindex(['Network 1','Network 2'], level=1)
    table1 = table1.reindex_axis(['Balance','Deployed], axis=1)
    table1.index = table1.index.set_names('Environment', level=1)
    table1.index = table1.index.set_names('OS', level=2)

Pretty new to pandas and python. How do I get the total at the bottom of the pivot table. Tried concat but result is not as expected. Index 1, 2, 3 become a single line item instead.

Upvotes: 1

Views: 936

Answers (1)

jezrael
jezrael

Reputation: 862801

You can convert tuple to list because tuples are immutable for change 4.th element to Total and then add new row by setting with enlargement:

print (df.index[-1])
('SupplierB', 'Network 2', 'Win 7', 'Model 3')

idx = list(df.index[-1])
idx[3] = 'Total' 
print (idx)
['SupplierB', 'Network 2', 'Win 7', 'Total']

df.loc[tuple(idx)] = df.sum()

print (df)
                                      Balance  Deployed
Type      Environment OS     Model                     
SupplierA Network 1   Win 10 Model 1      4.0      10.0
                             Model 2      4.0       7.0
          Network 2   Win 10 Model 1     10.0       5.0
                      Win 7  Model 2      NaN       1.0
                             Model 3      NaN       6.0
SupplierB Network 1   Win 10 Model 1      4.0       8.0
                             Model 2      4.0       5.0
          Network 2   Win 10 Model 3     10.0       5.0
                      Win 7  Model 2      NaN       1.0
                             Model 3      NaN       6.0
                             Total       36.0      54.0

You can also use:

a,b,c,d = df.index[-1]
df.loc[(a,b,c,'Total')] = df.sum()

Upvotes: 1

Related Questions