Is there a way to cumsum over a order by column

[carlsunderman]

lets say i have this table

import ibis from ibis import _ ibis.options.interactive = True t = ibis.memtable( { "fruit": ["apple", "apple", "banana", "orange"], "price": [0.5, 0.5, 0.25, 0.33], "order": [1, 2, 3, 4], } )

is there a way to output

┏━━━━━━━━┳━━━━━━━━━
┃ fruit ┃ price ┃ order ┃cumsum_price
┡━━━━━━━━╇━━━━━━━━━
│ string │ float64 │ int64 │ int64 │
├────────┼─────────━━━━━━
│ apple │ 0.50 │ 1 │ 0.50
│ apple │ 0.50 │ 2 │ 1.0
│ banana │ 0.25 │ 3 │ 1.25
│ orange │ 0.33 │ 4 │ 1.58
└────────┴─────────━━━━━━

I attempted something like this

t = t.mutate(cumsum_price = t['price']+t.cumsum_price.lag().over(order_by=[t.order])

Which does not work

[carlsunderman]

like this

# Example data
data = {
    'fruit' :['apple','apple','banana', 'orange','orange'],
    '_order': [1, 2, 3, 4, 5],
    'price': [10, 20, 30, 15, 25]
}

# Create a pandas DataFrame from the example data
df = pd.DataFrame(data)

# Register the DataFrame as a DuckDB table
con.register('my_table', df)

# Execute SQL query to calculate cumulative sum over time
query = """
SELECT
    fruit,
    _order,
    price,
    SUM(price) OVER (ORDER BY _order ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_over_time
FROM
    my_table
ORDER BY
    _order
"""

# Execute the query and fetch the result
result = con.execute(query).fetch_df()

# Print the result
print(result)

[cpcloud]

You can use ibis.cumulative_window or specify the ordering key and bounds by hand:

In [22]: from ibis.interactive import *

In [23]: data = {
    ...:     'fruit' :['apple','apple','banana', 'orange','orange'],
    ...:     '_order': [1, 2, 3, 4, 5],
    ...:     'price': [10, 20, 30, 15, 25]
    ...: }

In [24]: t = ibis.memtable(data)

In [25]: expr = t.mutate(
    ...:     over_time_manual=_.price.sum().over(order_by=_._order, rows=(None, 0)),
    ...:     over_time_cumulative=_.price.sum().over(ibis.cumulative_window(order_by=_._order))
    ...: )

In [26]: expr
Out[26]:
┏━━━━━━━━┳━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┓
┃ fruit  ┃ _order ┃ price ┃ over_time_manual ┃ over_time_cumulative ┃
┡━━━━━━━━╇━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━┩
│ string │ int64  │ int64 │ int64            │ int64                │
├────────┼────────┼───────┼──────────────────┼──────────────────────┤
│ apple  │      1 │    10 │               10 │                   10 │
│ apple  │      2 │    20 │               30 │                   30 │
│ banana │      3 │    30 │               60 │                   60 │
│ orange │      4 │    15 │               75 │                   75 │
│ orange │      5 │    25 │              100 │                  100 │
└────────┴────────┴───────┴──────────────────┴──────────────────────┘

In [27]: ibis.to_sql(expr)
Out[27]:
SELECT
  "t0"."fruit",
  "t0"."_order",
  "t0"."price",
  SUM("t0"."price") OVER (ORDER BY "t0"."_order" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CAST(0 AS TINYINT) following) AS "over_time_manual",
  SUM("t0"."price") OVER (ORDER BY "t0"."_order" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CAST(0 AS TINYINT) following) AS "over_time_cumulative"
FROM "ibis_pandas_memtable_chbaojbb7rcyrlfjp4d77nx3oq" AS "t0"