The essential idea here is to select the data you want to sum, and then sum them. This selection of data can be done in several different ways, a few of which are shown below.
Boolean indexing
Arguably the most common way to select the values is to use Boolean indexing.
With this method, you find out where column 'a' is equal to 1 and then sum the corresponding rows of column 'b'. You can use loc to handle the indexing of rows and columns:
>>> df.loc[df['a'] == 1, 'b'].sum()
15
The Boolean indexing can be extended to other columns. For example if df also contained a column 'c' and we wanted to sum the rows in 'b' where 'a' was 1 and 'c' was 2, we'd write:
df.loc[(df['a'] == 1) & (df['c'] == 2), 'b'].sum()
Query
Another way to select the data is to use query to filter the rows you're interested in, select column 'b' and then sum:
>>> df.query("a == 1")['b'].sum()
15
Again, the method can be extended to make more complicated selections of the data:
df.query("a == 1 and c == 2")['b'].sum()
Note this is a little more concise than the Boolean indexing approach.
Groupby
The alternative approach is to use groupby to split the DataFrame into parts according to the value in column 'a'. You can then sum each part and pull out the value that the 1s added up to:
>>> df.groupby('a')['b'].sum()[1]
15
This approach is likely to be slower than using Boolean indexing, but it is useful if you want check the sums for other values in column a:
>>> df.groupby('a')['b'].sum()
a
1 15
2 8
The essential idea here is to select the data you want to sum, and then sum them. This selection of data can be done in several different ways, a few of which are shown below.
Boolean indexing
Arguably the most common way to select the values is to use Boolean indexing.
With this method, you find out where column 'a' is equal to 1 and then sum the corresponding rows of column 'b'. You can use loc to handle the indexing of rows and columns:
>>> df.loc[df['a'] == 1, 'b'].sum()
15
The Boolean indexing can be extended to other columns. For example if df also contained a column 'c' and we wanted to sum the rows in 'b' where 'a' was 1 and 'c' was 2, we'd write:
df.loc[(df['a'] == 1) & (df['c'] == 2), 'b'].sum()
Query
Another way to select the data is to use query to filter the rows you're interested in, select column 'b' and then sum:
>>> df.query("a == 1")['b'].sum()
15
Again, the method can be extended to make more complicated selections of the data:
df.query("a == 1 and c == 2")['b'].sum()
Note this is a little more concise than the Boolean indexing approach.
Groupby
The alternative approach is to use groupby to split the DataFrame into parts according to the value in column 'a'. You can then sum each part and pull out the value that the 1s added up to:
>>> df.groupby('a')['b'].sum()[1]
15
This approach is likely to be slower than using Boolean indexing, but it is useful if you want check the sums for other values in column a:
>>> df.groupby('a')['b'].sum()
a
1 15
2 8
You can also do this without using groupby or loc. By simply including the condition in code. Let the name of dataframe be df. Then you can try :
df[df['a']==1]['b'].sum()
or you can also try :
sum(df[df['a']==1]['b'])
Another way could be to use the numpy library of python :
import numpy as np
print(np.where(df['a']==1, df['b'],0).sum())
python - Pandas: sum DataFrame rows for given columns - Stack Overflow
Pandas - how to get sum of column for specific/certain rows
Summing Rows based on column lookup
Sum across multiple columns by column name
There are a few concepts here:
-
If you're doing rowwise operations you're looking for the
rowwise()function -
With rowwise data frames you use
c_across()insidemutate()to select the columns you're operating on -
And if you're trying to use a character vector like
firstSumto select columns you wrap it in the select helperany_of() -
Afterwards you need to "ungroup" the data frame so that it no longer tries to do operations rowwise
library(tidyverse)
df <- data.frame(a = 1:2, b = 2:3, c = 3:4, d = 4:5)
firstSum <- c("a", "b")
secondSum <- c("c", "d")
df %>%
rowwise() %>%
mutate(first = sum(c_across(any_of(firstSum))),
second = sum(c_across(any_of(secondSum)))) %>%
ungroup()
#> # A tibble: 2 x 6
#> a b c d first second
#> <int> <int> <int> <int> <int> <int>
#> 1 1 2 3 4 3 7
#> 2 2 3 4 5 5 9Hope this helps! If you have any questions let me know
More on reddit.comYou can just sum and set axis=1 to sum the rows, which will ignore non-numeric columns; from pandas 2.0+ you also need to specify numeric_only=True.
In [91]:
df = pd.DataFrame({'a': [1,2,3], 'b': [2,3,4], 'c':['dd','ee','ff'], 'd':[5,9,1]})
df['e'] = df.sum(axis=1, numeric_only=True)
df
Out[91]:
a b c d e
0 1 2 dd 5 8
1 2 3 ee 9 14
2 3 4 ff 1 8
If you want to just sum specific columns then you can create a list of the columns and remove the ones you are not interested in:
In [98]:
col_list= list(df)
col_list.remove('d')
col_list
Out[98]:
['a', 'b', 'c']
In [99]:
df['e'] = df[col_list].sum(axis=1)
df
Out[99]:
a b c d e
0 1 2 dd 5 3
1 2 3 ee 9 5
2 3 4 ff 1 7
sum docs
If you have just a few columns to sum, you can write:
df['e'] = df['a'] + df['b'] + df['d']
This creates new column e with the values:
a b c d e
0 1 2 dd 5 8
1 2 3 ee 9 14
2 3 4 ff 1 8
For longer lists of columns, EdChum's answer is preferred.
The data is stored like so with driver & miles as two columns. I want to find the driver that has the most miles
driver miles Driver A 200 Driver B 410 Driver A 350
I put the data into a dataframe & sort by miles like so
df = pd.read_sql_query('SELECT * FROM "autos"', con=engine)
df = df.sort_values(by=['miles'], ascending=False)& the output is that Driver B is first (410) but I want Driver A to be first (550 = 200 + 350). At some point I guess I need to sum all drivers individually & then sort? First idea was to slice but that would remove many drivers & next I tried the axis but have yet to get it right. When I sum drivers individually & place them in a new column the number of rows didn't match & I got an error? All feedback is welcome...