5
\$\begingroup\$

I've implemented Excel's SUMIFS function in Pandas using the following code. Is there a better — more Pythonic — implementation?

from pandas import Series, DataFrame
import pandas as pd
df = pd.read_csv('data.csv')
# pandas equivalent of Excel's SUMIFS function
df.groupby('PROJECT').sum().ix['A001']

One concern I have with this implementation is that I'm not explicitly specifying the column to be summed.

Data File

Here's an example CSV data file (data.csv), although I'm displaying | instead of commas to improve the visual appearance.

DATE     | EMPLOYEE     | PROJECT | HOURS
02/01/14 | Smith, John  | A001    | 4.0
02/01/14 | Smith, John  | B002    | 4.0
02/01/14 | Doe, Jane    | A001    | 3.0
02/01/14 | Doe, Jane    | C003    | 5.0
02/02/14 | Smith, John  | B002    | 2.0
02/02/14 | Smith, John  | C003    | 6.0
02/02/14 | Doe, Jane    | A001    | 8.0

Equivalent Excel SUMIFS Function

If I were to open data.csv in Excel and wanted to determine how many hours were worked on project A001, I would use the SUMIFS formula as follows:

=SUMIFS($D2:$D8, $C2:$C8, "A001")

Where the SUMIFS function syntax is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
        criteria2], …)
\$\endgroup\$
5
\$\begingroup\$

The usual approach -- if you want all the projects -- would be

>>> df.groupby("PROJECT")["HOURS"].sum()
PROJECT
A001       15
B002        6
C003       11
Name: HOURS, dtype: float64

This only applies the sum on the desired column, as this constructs an intermediate SeriesGroupBy object:

>>> df.groupby("PROJECT")["HOURS"]
<pandas.core.groupby.SeriesGroupBy object at 0xa94f8cc>

If you're only interested in the total hours of a particular project, then I suppose you could do

>>> df.loc[df.PROJECT == "A001", "HOURS"].sum()
15.0

or if you dislike the repetition of df:

>>> df.query("PROJECT == 'A001'")["HOURS"].sum()
15.0

but I find that I almost always want to be able to access more than one sum, so these are pretty rare patterns in my code.

Aside: .ix has fallen out of favour as it has some confusing behaviour. These days it's recommended to use .loc or .iloc to be explicit.

\$\endgroup\$
2
\$\begingroup\$

If you want to do simple sum aggregation together with SUMIF, or multiple SUMIFS with different criteria simultaneously, I would suggest the following approach:

(
  df
  .assign(HOURS_A001 = lambda df: df.apply(lambda x: x.HOURS if x.PROJECT == "A001" else 0, axis=1))
  .agg({'HOURS': 'sum', 'HOURS_A001': 'sum'})
)

or without per-row apply (this version is much faster):

(
  df
  .assign(HOURS_A001 = lambda df: df.HOURS * np.where(df.PROJECT == "A001", 1, 0))
  .agg({'HOURS': 'sum', 'HOURS_A001': 'sum'})
)

So basically apply criteria and create a new row, then sum values in this row.

\$\endgroup\$

protected by Jamal Aug 3 at 4:57

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.