Shyama Sonti Shyama Sonti -3 years ago 100
Python Question

Computing sum of a dataframe and appending it to the top

Here's a sample dataframe. Set_index was used to set Constraint_ID as the index, which mis-aligns with the rest of the columns.

What I want is to put the sum of the TotalSP, Onpeak and Offpeak in the spaces just below (spaces that got created due to the misalignment). Is that possible? If not, can I create another row just below the columns labels that shows the totals of the columns?

Constraint Name TotalSP Onpeak Offpeak
77127 aaaaaaaaaaaaaaaaaa -2174.5 -2027.21 -147.29
98333 bbbbbbbbbbbbbbbbbb -1180.62 -1180.62 0
1049 cccccccccccccccccc -1036.53 -886.77 -149.76

Answer Source

First, you can compute the sum with df.sum:

In [189]: df2 = df.iloc[:, 1:].sum().to_frame().T.rename({0 : 'sum'}); df2
     TotalSP  Onpeak  Offpeak
sum -4391.65 -4094.6  -297.05

Next, you can concatenate it with your first dataframe using pd.concat:

In [191]: pd.concat([df2, df])
          Constraint Name  Offpeak   Onpeak  TotalSP
sum                   NaN  -297.05 -4094.60 -4391.65
77127  aaaaaaaaaaaaaaaaaa  -147.29 -2027.21 -2174.50
98333  bbbbbbbbbbbbbbbbbb     0.00 -1180.62 -1180.62
1049   cccccccccccccccccc  -149.76  -886.77 -1036.53

Disclaimer, I don't recommend this unless you just want to write this to a csv.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download