CODEWITHSUNDEEP
pythonpandas
Munichong
Munichong

Reputation: 4041

Add a TOTAL row to a pandas dataframe

I have a dataframe as follows:

                   POSITIVE  NEGATIVE  MASTER Net Positivity
Speaker                                                     
Mike                 36.0      29.0  2187.0          0.32%
Rob                  36.0      35.0  2413.0          0.04%
May                   2.0       4.0   369.0         -0.54%

I'd like to add a row at the end, which has the total positive (36+36+2), total negative (29+35+4), total MASTER (2187+2413+369). The index of the new row should be 'TOTAL'. The total net positivity is (total POSITIVE - total NEGATIVE) / total MASTER.

                   POSITIVE  NEGATIVE  MASTER Net Positivity
Speaker                                                     
Mike                 36.0      29.0  2187.0          0.32%
Rob                  36.0      35.0  2413.0          0.04%
May                   2.0       4.0   369.0         -0.54%
TOTAL                74.0      68.0  4969.0          0.12%

How should I add this total row in pandas?

Upvotes: 1

Views: 9098

Answers (4)

user-asterix
user-asterix

Reputation: 936

theDF is original dataframe without TOTAL row at bottom. Simply,

theDF.loc["TOTALS", :] = list(theDF.sum(axis=0))

Command above adds TOTALS row with only TOTALS as index with list of sums of each column (axis=0); The command works als0 for Multi-index as well.

Upvotes: 0

Ben.T
Ben.T

Reputation: 29635

you can use loc to add the row and sum on df. then calculate Net Positive with the right format

df.loc['total'] = df.iloc[:, :-1].sum()
df.loc['total', 'Net Positivity'] = f"""{(df.loc['total', 'POSITIVE']
                                            - df.loc['total', 'NEGATIVE'])
                                          /df.loc['total', 'MASTER']:.2%}"""

         POSITIVE  NEGATIVE  MASTER Net Positivity
Speaker                                           
Mike         36.0      29.0  2187.0          0.32%
Rob          36.0      35.0  2413.0          0.04%
May           2.0       4.0   369.0         -0.54%
total        74.0      68.0  4969.0          0.12%

Upvotes: 2

NYC Coder
NYC Coder

Reputation: 7604

Try this:

df = df.append(df.sum(numeric_only=True), ignore_index=True)
df['Net_Positivity'] = (df['POSITIVE']-df['NEGATIVE'])*100/df['MASTER']
df['Speaker'].fillna('Total', inplace=True)
df.set_index('Speaker', inplace=True)
print(df)

         POSITIVE  NEGATIVE  MASTER  Net_Positivity
Speaker
Mike         36.0      29.0  2187.0        0.320073
Rob          36.0      35.0  2413.0        0.041442
May           2.0       4.0   369.0       -0.542005
Total        74.0      68.0  4969.0        0.120749

Upvotes: 2

Igor Rivin
Igor Rivin

Reputation: 4864

You can simply 

newdf = pd.concat([df, df.sum()]
newdf.rename(index={index[-1]:"Total"}, inplace=True)

Upvotes: -1

Related Questions