I am trying to replicate the following logic from Pandas, but using Numpy vectorization.
Also, I feel there might be a more Pythonic way of adding the Actual Available column without creating two separate variables series_1 and series_2 first, and also that is not verbose.
The logic behind [Actual Available] is,
- if [Is First?] column is True then
[Actual Available] = [Stock] + [Requirements] + [Receipts], - if [Is First?] column is False then
[Actual Available] = [Prev row of Actual Available] + [Requirements] + [Receipts]
Any ideas?
import pandas as pd
import numpy as np
df = pd.DataFrame({
"Material": ["ABC", "ABC", "ABC", "ABC", "XYZ", "XYZ", "XYZ"],
"Plant": [2685, 2685, 2685, 2685, 2685, 2685, 2685],
"Year": ["2020", "2020", "2020", "2020", "2020", "2020", "2020"],
"Week": [1, 2, 3, 4, 1, 2, 3],
"Stock": [30, 30, 30, 30, 70, 70, 70],
"Requirements": [10, 15, 20, 25, 20, 30, 40],
"Receipts": [1, 2, 3, 4, 11, 12, 13]
})
print(df)
# Add [Is First?] column
df["Is First?"] = np.where(
(df["Material"] == df["Material"].shift(1)) &
(df["Plant"] == df["Plant"].shift(1)),
False,
True,
)
# Add [Actual Available] column
df["Actual Available"] = (df["Stock"] + df["Requirements"] +
df["Receipts"]).where(df["Is First?"].eq(True))
series_1 = df["Is First?"].eq(True).cumsum()
series_2 = (df["Actual Available"].ffill() +
(df["Receipts"] +
df["Requirements"]).shift(-1).groupby(series_1).cumsum().shift())
df["Actual Available"] = df["Actual Available"].fillna(series_2)
print(df)
.eq(True)does? Why theFalseandTrueinnumpy.where()?