Question Details

No question body available.

Tags

python dataframe datetime python-polars

Answers (3)

Accepted Answer Available
Accepted Answer
October 8, 2025 Score: 5 Rep: 1,125 Quality: High Completeness: 50%

The easiest way is calculating the amount each day contributes, exploding each day into a separate row, then grouping by month and aggregating.

You can use dateranges to get each date within the interval for each row, explode to separate into individual rows, then groupbydynamic to aggregate per month

dates = pl.dateranges('DATEPREV', 'DATE').alias('dates')
valueperday = pl.col('REVDIFF') / dates.list.len()
daily = df.select('ID', dates, valueperday).explode('dates')
result = daily.groupbydynamic('dates', every='1mo', groupby='ID').agg(pl.col('REVDIFF').sum())
print(result)
October 9, 2025 Score: 2 Rep: 2,123 Quality: Low Completeness: 60%

Here is a solution that doesn't involve exploding each day into a row and grouping the data up again. It generates a date range, truncates each date to the month, then counts values.

This gives you a list of struct like (for e.g., for ID 1)

[{"MONTH": 2025-07-01, "count": 1}, {"MONTH": 2025-08-01, "count": 10}]

Then the list is exploded and you can compute the revenue that belongs to each month

dateranges = pl.dateranges("DATEPREV", "DATE")
(
    df.withcolumns(
        months=dateranges.list.eval(pl.element().dt.truncate("1mo").alias("MONTH").valuecounts())
    )
    .explode("months")
    .select(
        "ID",
        pl.col("months").struct["MONTH"],
        # E.g. for ID 1, month 2025-08-01 -> REVENUE = 5000 / 11  10
        REVENUE=pl.col("REV_DIFF") / date_ranges.list.len()  pl.col("months").struct["count"],
    )
)

shape: (5, 3)
┌─────┬────────────┬──────────────┐
│ ID  ┆ MONTH      ┆ REVENUE      │
│ --- ┆ ---        ┆ ---          │
│ i64 ┆ date       ┆ f64          │
╞═════╪════════════╪══════════════╡
│ 1   ┆ 2025-07-01 ┆ 454.545455   │
│ 1   ┆ 2025-08-01 ┆ 4545.454545  │
│ 2   ┆ 2025-06-01 ┆ 2500.0       │
│ 3   ┆ 2025-01-01 ┆ 22666.666667 │
│ 3   ┆ 2025-02-01 ┆ 37333.333333 │
└─────┴────────────┴──────────────┘
October 9, 2025 Score: 1 Rep: 24,917 Quality: Medium Completeness: 80%

Out of interest, I was trying to explode only the months:

(
    df.withcolumns(
        (pl.col.DATE - pl.col.DATEPREV).dt.totaldays().alias("TOTALDAYS") + 1,
        pl.dateranges(pl.col.DATEPREV.dt.monthstart(), pl.col.DATE.dt.monthend(), interval="1mo").alias("MONTHSTART")        
    )
    .explode("MONTHSTART")
)

shape: (5, 6)
┌─────┬────────────┬────────────┬──────────┬────────────┬─────────────┐
│ ID  ┆ DATEPREV  ┆ DATE       ┆ REVDIFF ┆ TOTALDAYS ┆ MONTHSTART │
│ --- ┆ ---        ┆ ---        ┆ ---      ┆ ---        ┆ ---         │
│ i64 ┆ date       ┆ date       ┆ i64      ┆ i64        ┆ date        │
╞═════╪════════════╪════════════╪══════════╪════════════╪═════════════╡
│ 1   ┆ 2025-07-31 ┆ 2025-08-10 ┆ 5000     ┆ 11         ┆ 2025-07-01  │
│ 1   ┆ 2025-07-31 ┆ 2025-08-10 ┆ 5000     ┆ 11         ┆ 2025-08-01  │
│ 2   ┆ 2025-06-01 ┆ 2025-06-01 ┆ 2500     ┆ 1          ┆ 2025-06-01  │
│ 3   ┆ 2025-01-15 ┆ 2025-02-28 ┆ 60000    ┆ 45         ┆ 2025-01-01  │
│ 3   ┆ 2025-01-15 ┆ 2025-02-28 ┆ 60000    ┆ 45         ┆ 2025-02-01  │
└─────┴────────────┴────────────┴──────────┴────────────┴─────────────┘

It seems from here, there are 3 possible cases:

case
when DATEPREV > MONTHSTART            then  DAYPREVDAYS
when SAMEYEARMONTH(DATE, MONTHSTART) then  DATEDAYS
else                                          MONTHDAYS
end

.when() can be used to construct similar logic.

(
    df
    .lazy()
    .withcolumns(
        (pl.col.DATE - pl.col.DATEPREV).dt.totaldays().alias("TOTALDAYS") + 1,
        pl.dateranges(pl.col.DATEPREV.dt.monthstart(), pl.col.DATE.dt.monthend(), interval="1mo").alias("MONTHSTART")        
    )
    .explode("MONTHSTART")
    .withcolumns(
        pl.when(pl.col.DATEPREV > pl.col.MONTHSTART)
          .then(pl.col.DATEPREV.dt.daysinmonth() + 1 - pl.col.DATEPREV.dt.day())
          .when(
              pl.col.DATE.dt.year() == pl.col.MONTHSTART.dt.year(),  # .dt.tostring("%y%m") was a bit slower
              pl.col.DATE.dt.month() == pl.col.MONTHSTART.dt.month(),
          )
          .then(pl.col.DATE.dt.day())
          .otherwise(pl.col.MONTHSTART.dt.daysinmonth())
          .alias("MONTHDAYS")
    )
    .withcolumns(
        (pl.col.REVDIFF * (pl.col.MONTHDAYS / pl.col.TOTALDAYS)).alias("REVENUE")
    )
    .collect(engine="streaming")
)

shape: (5, 8)
┌─────┬────────────┬────────────┬──────────┬────────────┬─────────────┬────────────┬──────────────┐
│ ID  ┆ DATEPREV  ┆ DATE       ┆ REVDIFF ┆ TOTALDAYS ┆ MONTHSTART ┆ MONTHDAYS ┆ REVENUE      │
│ --- ┆ ---        ┆ ---        ┆ ---      ┆ ---        ┆ ---         ┆ ---        ┆ ---          │
│ i64 ┆ date       ┆ date       ┆ i64      ┆ i64        ┆ date        ┆ i8         ┆ f64          │
╞═════╪════════════╪════════════╪══════════╪════════════╪═════════════╪════════════╪══════════════╡
│ 1   ┆ 2025-07-31 ┆ 2025-08-10 ┆ 5000     ┆ 11         ┆ 2025-07-01  ┆ 1          ┆ 454.545455   │
│ 1   ┆ 2025-07-31 ┆ 2025-08-10 ┆ 5000     ┆ 11         ┆ 2025-08-01  ┆ 10         ┆ 4545.454545  │
│ 2   ┆ 2025-06-01 ┆ 2025-06-01 ┆ 2500     ┆ 1          ┆ 2025-06-01  ┆ 1          ┆ 2500.0       │
│ 3   ┆ 2025-01-15 ┆ 2025-02-28 ┆ 60000    ┆ 45         ┆ 2025-01-01  ┆ 17         ┆ 22666.666667 │
│ 3   ┆ 2025-01-15 ┆ 2025-02-28 ┆ 60000    ┆ 45         ┆ 2025-02-01  ┆ 28         ┆ 37333.333333 │
└─────┴────────────┴────────────┴──────────┴────────────┴─────────────┴────────────┴──────────────┘

Using the Streaming Engine yielded the fastest results in my testing, so I've added lazy and collect calls.