I have a large dataframe df with:
df = pd.DataFrame(
[
["2021-10-25", 123, 1, 444, 55, pd.NA, -6],
["2021-10-25", 123, 1, 444, 55, pd.NA, -20],
["2021-10-19", 123, 1, 444, 55, pd.NA, -8],
["2021-10-20", 123, 1, 444, 55, 628, -10],
["2021-10-21", 123, 1, 444, 55, 618, -2],
["2021-10-25", 123, 1, 444, 55, 616, -6],
["2021-10-25", 456, 1, 444, 66, pd.NA, -1],
["2021-10-25", 456, 1, 444, 66, pd.NA, -4],
["2021-10-19", 456, 1, 444, 66, pd.NA, -3],
["2021-10-20", 456, 1, 444, 66, 1372, -1],
["2021-10-21", 456, 1, 444, 66, 1371, -2],
["2021-10-25", 456, 1, 444, 66, 1369, -5],
],
columns=["DATE", "NUMBER", "QUAL", "FACTORY", "INV_LOC", "INV_TODAY", "INV_CHANGE"],
)
df.head(12)
| DATE | NUMBER | QUAL | FACTORY | INV_LOC | INV_TODAY | INV_CHANGE |
|---|---|---|---|---|---|---|
| 2021-10-25 | 123 | 1 | 444 | 55 | NaN | -6 |
| 2021-10-25 | 123 | 1 | 444 | 55 | NaN | -20 |
| 2021-10-19 | 123 | 1 | 444 | 55 | NaN | -8 |
| 2021-10-20 | 123 | 1 | 444 | 55 | 628 | -10 |
| 2021-10-21 | 123 | 1 | 444 | 55 | 618 | -2 |
| 2021-10-25 | 123 | 1 | 444 | 55 | 616 | -6 |
| 2021-10-25 | 456 | 1 | 444 | 66 | NaN | -1 |
| 2021-10-25 | 456 | 1 | 444 | 66 | NaN | -4 |
| 2021-10-19 | 456 | 1 | 444 | 66 | NaN | -3 |
| 2021-10-20 | 456 | 1 | 444 | 66 | 1372 | -1 |
| 2021-10-21 | 456 | 1 | 444 | 66 | 1371 | -2 |
| 2021-10-25 | 456 | 1 | 444 | 66 | 1369 | -5 |
The original df has ~10_000 different NUMBER + QUAL + FACTORY combinations with different dates, a few million rows.
INV_TODAY is the inventory amount at the beginning of that date. Then inventory comes in/out over the day. INV_CHANGE tells me the summed amount of products that went in/out over the day.
So INV_TODAY + INV_CHANGE gives me the inventory at the beginning of the next day.
I have only limited data for INV_TODAY but I have every INV_CHANGE when inventory changed many years into the past on a daily basis.
I need to fill the column INV_TODAY for each NUMBER + QUAL + FACTORY combination. INV_TODAY is INV_TODAY - (INV_CHANGE from the previous row)
The final dataframe should look like:
| DATE | NUMBER | QUAL | FACTORY | INV_LOC | INV_TODAY | INV_CHANGE |
|---|---|---|---|---|---|---|
| 2021-10-25 | 123 | 1 | 444 | 55 | 662 | -6 |
| 2021-10-25 | 123 | 1 | 444 | 55 | 656 | -20 |
| 2021-10-19 | 123 | 1 | 444 | 55 | 636 | -8 |
| 2021-10-20 | 123 | 1 | 444 | 55 | 628 | -10 |
| 2021-10-21 | 123 | 1 | 444 | 55 | 618 | -2 |
| 2021-10-25 | 123 | 1 | 444 | 55 | 616 | -6 |
| 2021-10-25 | 456 | 1 | 444 | 66 | 1380 | -1 |
| 2021-10-25 | 456 | 1 | 444 | 66 | 1379 | -4 |
| 2021-10-19 | 456 | 1 | 444 | 66 | 1375 | -3 |
| 2021-10-20 | 456 | 1 | 444 | 66 | 1372 | -1 |
| 2021-10-21 | 456 | 1 | 444 | 66 | 1371 | -2 |
| 2021-10-25 | 456 | 1 | 444 | 66 | 1369 | -5 |
My idea so far was to calculate the values for yesterday, shift(-1), then fillna().
The formula is:
df["INV_YESTERDAY"] = df["INV_TODAY"] - df["INV_CHANGE"].shift(1)
df["INV_YESTERDAY"] = df["INV_YESTERDAY"].shift(-1)
But I only get
| DATE | NUMBER | QUAL | FACTORY | INV_LOC | INV_TODAY | INV_CHANGE | INV_YESTERDAY |
|---|---|---|---|---|---|---|---|
| 2021-10-25 | 123 | 1 | 444 | 55 | NaN | -6 | NaN |
| 2021-10-25 | 123 | 1 | 444 | 55 | NaN | -20 | NaN |
| 2021-10-19 | 123 | 1 | 444 | 55 | NaN | -8 | 636 |
| 2021-10-20 | 123 | 1 | 444 | 55 | 628 | -10 | 628 |
| 2021-10-21 | 123 | 1 | 444 | 55 | 618 | -2 | 618 |
| 2021-10-25 | 123 | 1 | 444 | 55 | 616 | -6 | NaN |
| 2021-10-25 | 456 | 1 | 444 | 66 | NaN | -1 | NaN |
| 2021-10-25 | 456 | 1 | 444 | 66 | NaN | -4 | NaN |
| 2021-10-19 | 456 | 1 | 444 | 66 | NaN | -3 | 1375 |
| 2021-10-20 | 456 | 1 | 444 | 66 | 1372 | -1 | 1372 |
| 2021-10-21 | 456 | 1 | 444 | 66 | 1371 | -2 | 1371 |
| 2021-10-25 | 456 | 1 | 444 | 66 | 1369 | -5 | NaN |
How can I get the final dataframe in an efficient way?
Thanks in advance.