[FIXED] calculation of a pandas dataframe based on its previous row of same column (please use numpy since dataset is huge)

Issue

x = {"INPUT" : ['A','B','B','B','B','B','A','B','B','C','B','B','C','B','B','A','B','C']}

df = pd.DataFrame(x)

df

Condition for calculation of “OUTPUT” column

condition(1)if INPUT value A then OUTPUT A

condition(2)if INPUT value C then OUTPUT C

condition(3.i)if INPUT value B and B present after C then OUTPUT C

condition(3.ii)if INPUT value B and B present after A then OUTPUT A

cond_output = ((df["INPUT"] == "A"),
               (df["INPUT"] == "C"),
               ((df["INPUT"] == "B") & (df["OUTPUT"].shift(1, fill_value="A") == "A")),
               ((df["INPUT"] == "B") & (df["OUTPUT"].shift(1, fill_value="A") == "C")))
               
choice_output = ("A", "C", "A", "C")                       
                       
                       
df["OUTPUT"] = np.select(cond_output, choice_output)
df

# KeyError: 'OUTPUT'

Solution

Use mask to hide rows with 'B' then ffill last valid value:

df['OUTPUT'] = df['INPUT'].mask(df['INPUT'] == 'B').ffill()
print(df)

# Output
   INPUT OUTPUT
0      A      A
1      B      A
2      B      A
3      B      A
4      B      A
5      B      A
6      A      A
7      B      A
8      B      A
9      C      C
10     B      C
11     B      C
12     C      C
13     B      C
14     B      C
15     A      A
16     B      A
17     C      C

Or use where to keep rows with 'A' or 'B':

df['OUTPUT'] = df['INPUT'].where(df['INPUT'].isin(['A', 'C'])).ffill()

Answered By - Corralien