How to replace DataFrame.append with pd.concat to append a Series as row?

user_3074483 0
Viewed 225

I have a data frame with numeric values, such as

import pandas as pd

df = pd.DataFrame([[1, 2], [3, 4]], columns=['A', 'B'])

and I append a single row with all the column sums

totals = df.sum()
totals.name = 'totals'
df_append = df.append(totals)

Simple enough. Here are the values of df, totals, and df_append

>>> df
   A  B
0  1  2
1  3  4

>>> totals
A    4
B    6
Name: totals, dtype: int64

>>> df_append
        A  B
0       1  2
1       3  4
totals  4  6

Unfortunately, in newer versions of pandas the method DataFrame.append is deprecated, and will be removed in some future version of pandas. The advise is to replace it with pandas.concat.

Now, using pd.concat naively as follows

df_concat_bad = pd.concat([df, totals])

produces

>>> df_concat_bad
     A    B    0
0  1.0  2.0  NaN
1  3.0  4.0  NaN
A  NaN  NaN  4.0
B  NaN  NaN  6.0

Apparently, with df.append the Series object got interpreted as a row, but with pd.concat it got interpreted as a column.

You cannot fix this with something like calling pd.concat with axis=1, because that would add the totals as column:

>>> pd.concat([df, totals], axis=1)
     A    B  totals
0  1.0  2.0     NaN
1  3.0  4.0     NaN
A  NaN  NaN     4.0
B  NaN  NaN     6.0

(In this case, the result looks the same as using the default axis=0, because the indexes of df and totals are disjoint, as are their column names.)

How to handle this (elegantly and efficiently)?

pandas dataframe python
2 Answers

The solution is to convert totals (a Series object) to a DataFrame (which will then be a column) using to_frame() and next transpose it using T:

df_concat_good = pd.concat([df, totals.to_frame().T])

yields the desired

>>> df_concat_good
        A  B
0       1  2
1       3  4
totals  4  6
user_30744830

I prefer to use df.loc() to solve this problem than pd.concat()

df.loc["totals"]=df.sum()

user_197837700
Related