[Python] polars, combining sales and purchases, FIFO method

I have two dataframes:

One with buys

df_buy = pl.DataFrame(
{
"BuyId": [1, 2],
"Item": ["A", "A"],
"BuyDate": [date.fromisoformat("2023-01-01"), date.fromisoformat("2024-03-07")],
"Quantity": [40, 50],
}
)

BuyId Item BuyDate Quantity
1 A 2023-01-01 40
2 A 2024-03-07 50

And other with sells:

df_sell = pl.DataFrame(
{
"SellId": [3, 4],
"Item": ["A", "A"],
"SellDate": [date.fromisoformat("2024-04-01"), date.fromisoformat("2024-05-01")],
"Quantity": [10, 45],
}
)


SellId Item SellDate Quantity
3 A 2024-04-01 10
4 A 2024-05-01 45

I want to determine which sales came from which purchases using the FIFO method.

The result should be something like this.

BuyId​

Item​

BuyDate​

RemainingQuantity​

SellId​

SellDate​

SellQuantity​

QuantityAfterSell
1​

A​

2023-01-01​

40​

3​

2024-04-01​

10​

30
1​

A​

2023-01-01​

30​

4​

2024-05-01​

30​

0
2​

A​

2024-03-07​

50​

4​

2024-05-01​

15​

35​

I know that I can do it using a for loop but I wanted to know if there is a more vectorized way to do it.

Edit:

Added new example for testing:

df_buy = pl.DataFrame(
{
"BuyId": [5, 1, 2],
"Item": ["B", "A", "A"],
"BuyDate": [date.fromisoformat("2023-01-01"), date.fromisoformat("2023-01-01"), date.fromisoformat("2024-03-07")],
"Quantity": [10, 40, 50],
}
)

df_sell = pl.DataFrame(
{
"SellId": [6, 3, 4],
"Item": ["B", "A", "A"],
"SellDate": [
date.fromisoformat("2024-04-01"),
date.fromisoformat("2024-04-01"),
date.fromisoformat("2024-05-01"),
],
"Quantity": [5, 10, 45],
}
)

Continue reading...