SumIf / SumIfs

Excel SUMIF / SUMIFS via pre-aggregation and join.

fabrictools.excel.aggregate.SumIf(left: pyspark.sql.DataFrame, criteria_col: str, right: pyspark.sql.DataFrame, match_col: str, sum_col: str, *, output_name: str | None = None) → pyspark.sql.DataFrame[source]

Conditional sum keyed on one column (Excel SUMIF / SOMME.SI).

Pre-aggregates right with groupBy(match_col).sum(sum_col) then left-joins to left[criteria_col]. This matches Excel row-wise SUMIF semantics without row loops.

Parameters:

  • left (DataFrame) – Driving dataframe.

  • criteria_col (str) – Column on left used as the criteria value.

  • right (DataFrame) – Source table to aggregate.

  • match_col (str) – Column on right compared to criteria_col.

  • sum_col (str) – Column on right to sum.

  • output_name (str | None) – Name of the added column (defaults to sum_col).

Returns:

left with the conditional sum column appended.

Return type:

DataFrame

Raises:

ValueError – If a required column does not resolve.

Example

>>> from fabrictools import Excel
>>> df = Excel.SumIf(df, "RAO CODE", invoicing, "Project Number", "Total Invoice Amount",
...                  output_name="Total Invoicing")
fabrictools.excel.aggregate.SumIfs(left: pyspark.sql.DataFrame, sum_col: str, right: pyspark.sql.DataFrame, criteria: Mapping[str, str | int | float | bool | None], *, output_name: str | None = None) → pyspark.sql.DataFrame[source]

Multi-criteria conditional sum (Excel SUMIFS / SOMME.SI.ENS).

Each entry in criteria maps a right column to either:

  • a left column name (joined after aggregation), or

  • a literal value (filters right before aggregation).

Parameters:

  • left (DataFrame) – Driving dataframe.

  • sum_col (str) – Column on right to sum.

  • right (DataFrame) – Source table to aggregate.

  • criteria (collections.abc.Mapping[str, str | int | float | bool | None]) – {right_col: left_col_name | literal} mapping.

  • output_name (str | None) – Name of the added column (defaults to sum_col).

Returns:

left with the conditional sum column appended.

Return type:

DataFrame

Raises:

ValueError – If sum_col or join criteria do not resolve.

Example

>>> from fabrictools import Excel
>>> df = Excel.SumIfs(df, "Turnover", turnover_follow,
...     {"Project No.": "RAO CODE", "Year": 2022},
...     output_name="Turnover Follow 2022")