1

I have this dataframe:

Data = {'name':  ['a', 'b'],
        'number1': [5, 3],
        'number2': [3, 2]
        }
df = pd.DataFrame(Data, columns = ['name','number1', 'number2'])

I want to add another row which would contain sums of columns number1 and number2. So my desired result is this:

Data2 = {'name':  ['a', 'b', 'total'],
        'number1': [5, 3, 8],
        'number2': [3, 2, 5]
        }
df2 = pd.DataFrame(Data2, columns = ['name','number1', 'number2'])

I tried the solution offered in this thread: Pandas dataframe total row

But this line

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

also makes a sum of names so in the end I get Total as my index and in names column I get a value ab (because a+b). Is there a way to get a df that looks exactly the same as my desired result df?

Josh21
  • 506
  • 5
  • 15
milka1117
  • 521
  • 4
  • 8
  • 17

4 Answers4

4

I am using .loc 

df.loc[df.index.max()+1]=['total']+df.sum().tolist()[1:]
df
Out[80]: 
    name  number1  number2
0      a        5        3
1      b        3        2
2  total        8        5
BENY
  • 317,841
  • 20
  • 164
  • 234
  • After 4 hours of trying every other solution under the son, this is the only thing that worked for me in a jupyter notebook. This is insane. – Steve3p0 Nov 13 '19 at 02:38
3

Use:

m=df.set_index('name')
m.loc['total']=m.sum()
print(m)

       number1  number2
name                   
a            5        3
b            3        2
total        8        5

You can also reset_index() if you intend to have the name column as column:

print(m.reset_index())

    name  number1  number2
0      a        5        3
1      b        3        2
2  total        8        5
anky
  • 74,114
  • 11
  • 41
  • 70
3

Use Series.append by columns filtered by positions by DataFrame.iloc or only numeric columns by DataFrame.select_dtypes:

df.loc[len(df)] = df.iloc[:, 1:].sum().append(pd.Series({'name':'total'}))
print (df)
    name  number1  number2
0      a        5        3
1      b        3        2
2  total        8        5

Or:

df.loc[len(df)] = df.select_dtypes(np.number).sum().append(pd.Series({'name':'total'}))
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
3

Here's one way:

df.append(df.sum().rename('total').replace({'ab':'total'})).reset_index(drop=True)

     name  number1  number2
0      a        5        3
1      b        3        2
2  total        8        5
yatu
  • 86,083
  • 12
  • 84
  • 139