Pandas: sum up multiple columns into one column without last column

Question

If I have a dataframe similar to this one

Apples   Bananas   Grapes   Kiwis
2        3         nan      1
1        3         7        nan
nan      nan       2        3

I would like to add a column like this

Apples   Bananas   Grapes   Kiwis   Fruit Total
2        3         nan      1        6
1        3         7        nan      11
nan      nan       2        3        5

I guess you could use df['Apples'] + df['Bananas'] and so on, but my actual dataframe is much larger than this. I was hoping a formula like df['Fruit Total']=df[-4:-1].sum could do the trick in one line of code. That didn't work however. Is there any way to do it without explicitly summing up all columns?

Look there. http://stackoverflow.com/questions/25748683/pandas-sum-dataframe-rows-for-given-columns — konstov, Feb 06 '17 at 08:57

jezrael · Accepted Answer · 2019-09-14T11:33:21.187

133

You can first select by iloc and then sum:

df['Fruit Total']= df.iloc[:, -4:-1].sum(axis=1)
print (df)
   Apples  Bananas  Grapes  Kiwis  Fruit Total
0     2.0      3.0     NaN    1.0          5.0
1     1.0      3.0     7.0    NaN         11.0
2     NaN      NaN     2.0    3.0          2.0

For sum all columns use:

df['Fruit Total']= df.sum(axis=1)

edited Sep 14 '19 at 11:33

answered Feb 06 '17 at 08:56

jezrael

822,522
95
1,334
1,252

Excellent. iloc is the thing I was looking for. – Tuutsrednas Feb 06 '17 at 09:04
1

This answer doesn't add the last column, and therefore is a little confusing. – Jinhua Wang Sep 14 '19 at 11:31
1

@JinhuaWang - title was changed. – jezrael Sep 14 '19 at 11:32
1

Oh ok sorry about that – Jinhua Wang Sep 14 '19 at 11:34

score 43 · Answer 2 · answered Jul 28 '21 at 14:36

This may be helpful for beginners, so for the sake of completeness, if you know the column names (e.g. they are in a list), you can use:

column_names = ['Apples', 'Bananas', 'Grapes', 'Kiwis']
df['Fruit Total']= df[column_names].sum(axis=1)

This gives you flexibility about which columns you use as you simply have to manipulate the list column_names and you can do things like pick only columns with the letter 'a' in their name. Another benefit of this is that it's easier for humans to understand what they are doing through column names. Combine this with list(df.columns) to get the column names in a list format. Thus, if you want to drop the last column, all you have to do is:

column_names = list(df.columns)
df['Fruit Total']= df[column_names[:-1]].sum(axis=1)

Ramon · Answer 3 · 2022-05-14T09:04:30.153

18

It is possible to do it without knowing the number of columns and even without iloc:

print(df)
   Apples  Bananas  Grapes  Kiwis
0     2.0      3.0     NaN    1.0
1     1.0      3.0     7.0    NaN
2     NaN      NaN     2.0    3.0

cols_to_sum = df.columns[ : df.shape[1]-1]

df['Fruit Total'] = df[cols_to_sum].sum(axis=1)

print(df)
   Apples   Bananas Grapes  Kiwis   Fruit Total
0  2.0      3.0     NaN     1.0     5.0
1  1.0      3.0     7.0     NaN     11.0
2  NaN      NaN     2.0     3.0     5.0

edited May 14 '22 at 09:04

answered May 03 '20 at 04:07

Ramon

518
6
16

I like this one as it doesn't require recoding if I decide to expand my dataframe – ReneBt Sep 25 '20 at 11:03

score 12 · Answer 4 · answered Aug 23 '18 at 07:42

12

Using df['Fruit Total']= df.iloc[:, -4:-1].sum(axis=1) over your original df won't add the last column ('Kiwis'), you should use df.iloc[:, -4:] instead to select all columns:

print(df)
   Apples  Bananas  Grapes  Kiwis
0     2.0      3.0     NaN    1.0
1     1.0      3.0     7.0    NaN
2     NaN      NaN     2.0    3.0

df['Fruit Total']=df.iloc[:,-4:].sum(axis=1)

print(df)
   Apples  Bananas  Grapes  Kiwis  Fruit Total
0     2.0      3.0     NaN    1.0          6.0
1     1.0      3.0     7.0    NaN         11.0
2     NaN      NaN     2.0    3.0          5.0

answered Aug 23 '18 at 07:42

Francisco Dura

121
1
3

1

Thanks for the answer. I, however, did not understand what is the benefit of having the negative sign in the iloc statement. iloc[:,1,5] seems to be a simpler and less confusing way. I am learning Python and Pandas. By trial and error, I realized that iloc[1:4] just sums the first 3 columns while iloc[:,1,5] sums the first 4 – RAVI D PARIKH Oct 20 '18 at 13:47
1

Using iloc[:,-4] you are telling it to take the last 4 columns. In this case iloc[:,-4] = iloc[:,1,5]. Which one you use depends on how specific or open you want to be in your statement. – Francisco Dura Feb 28 '19 at 10:38

score 4 · Answer 5 · answered Feb 05 '21 at 22:17

I want to build on Ramon's answer if you want to come up with the total without knowing the shape/size of the dataframe. I will use his answer below but fix one item that didn't include the last column for the total. I have removed the -1 from the shape:

cols_to_sum = df.columns[ : df.shape[1]-1]

To this:

cols_to_sum = df.columns[ : df.shape[1]]

print(df)
   Apples  Bananas  Grapes  Kiwis
0     2.0      3.0     NaN    1.0
1     1.0      3.0     7.0    NaN
2     NaN      NaN     2.0    3.0

cols_to_sum = df.columns[ : df.shape[1]]

df['Fruit Total'] = df[cols_to_sum].sum(axis=1)

print(df)
   Apples   Bananas Grapes  Kiwis   Fruit Total
0  2.0      3.0     NaN     1.0     6.0
1  1.0      3.0     7.0     NaN     11.0
2  NaN      NaN     2.0     3.0     5.0

Which then gives you the correct total without skipping the last column.

score 1 · Answer 6 · edited Mar 04 '23 at 16:26

1

This might be a much easier method to solve, and it will take care of the other datatypes too, which are not required:

df['Fruit Total'] = df.sum(axis=1, numeric_only= True)

print(df)
   Apples   Bananas Grapes  Kiwis   Fruit Total
0  2.0      3.0     NaN     1.0     6.0
1  1.0      3.0     7.0     NaN     11.0
2  NaN      NaN     2.0     3.0     5.0

edited Mar 04 '23 at 16:26

Adrian Mole

49,934
160
51
83

answered Feb 28 '23 at 16:55

iffy

11
1

Pandas: sum up multiple columns into one column without last column

6 Answers6

Linked