Pandas Append a Total Row with pandas.concat

Question

I have tried to find how to append a Total Row, which sums the columns.

There is a elegant solution to this problem here: [SOLVED] Pandas dataframe total row

However, when using this method, I have noticed a warning message:

FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.

I have tried to use this alternative, in order to avoid using legacy code. When I tried to use concat method, it was appending the sum row to the last column vertically.

Code I used:

pd.concat( [df, df.sum(numeric_only=True)] )

Result:

         a       b     c         d         e
0   2200.0   14.30   NaN   2185.70       NaN
1   3200.0   20.80   NaN   3179.20       NaN
2   6400.0   41.60   NaN   6358.40       NaN
3      NaN     NaN   NaN       NaN  11800.00     <-- Appended using Concat
4      NaN     NaN   NaN       NaN     76.70     <-- Appended using Concat
5      NaN     NaN   NaN       NaN      0.00     <-- Appended using Concat
6      NaN     NaN   NaN       NaN  11723.30     <-- Appended using Concat

What I want:

           a       b      c          d
0     2200.0   14.30    NaN    2185.70
1     3200.0   20.80    NaN    3179.20
2     6400.0   41.60    NaN    6358.40
3   11800.00   76.70   0.00   11723.30     <-- Appended using Concat

Is there an elegant solution to this problem using concat method?

Try df.loc['Total'] = df.sum()

Jon Clements
– Jon Clements

2022-03-02 17:58:18 +00:00
Commented Mar 2, 2022 at 17:58 — Jon Clements
– Jon Clements, Commented Mar 2, 2022 at 17:58

not_speshal · Accepted Answer · 2022-03-02 18:08:08Z

4

Convert the sum (which is a pandas.Series) to a DataFrame and transpose before concat:

>>> pd.concat([df,df.sum().to_frame().T], ignore_index=True)

         a     b    c        d
0   2200.0  14.3  NaN   2185.7
1   3200.0  20.8  NaN   3179.2
2   6400.0  41.6  NaN   6358.4
3  11800.0  76.7  0.0  11723.3

answered Mar 2, 2022 at 18:08

not_speshal

23.2k2 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Evil Cheetah Over a year ago

Thank you very much for the solution. I have tried to Google how to transpose the array, but didn't know you have to use to_frame.

Tuhin Mitra Over a year ago

Thanks a lot for this solutions, in pandas documentations, pd.concat([df1, df1]), doesn't work the same as df1.append(df2) would

mouwsy · Accepted Answer · 2023-12-14 21:39:51Z

1

The elegant solution is

pd.concat([df, df.agg(["sum"])])

which is quite short. The [] around "sum" causes that df.agg(["sum"]) returns a row (i.e. DataFrame). Without the [], a Series would be returned (which would not be in the interest of this solution). The index label of the newly generated row is automatically given the name sum, see the output:

         a     b    c        d
0     2200  14.3  NaN   2185.7
1     3200  20.8  NaN   3179.2
2     6400  41.6  NaN   6358.4
sum  11800  76.7  0.0  11723.3

edited Dec 14, 2023 at 21:39

answered Dec 13, 2023 at 21:42

mouwsy

2,12718 silver badges28 bronze badges

Collectives™ on Stack Overflow

Pandas Append a Total Row with pandas.concat

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related