Take previous row's value in dataframe when condition meets-CodePudding

I have a datafarme let's say something like this:

| ColumnX        |
| -------------- |
| Hi             |
|   Open         |
| How are you    |
| NAN            |
| Something      |
| something      |
| HEY            |
|   Open         |

now I need to go through the rows checking their values. If the row value is " Open" then select the previous row value and put it into a list. so far this is what I've done but I couldn't figure out how to take the previous value-

ilist=[]
for i in df["ColumnX"]:
    if i == '  Open':
        ilist.append(i)

CodePudding user response：

Create the mask for values, shift the mask passing -1, then fill NA by False which is basically the last row, then use this mask to get the values and finally create list out of the values:

>>> df.loc[df['ColumnX'].eq('  Open').shift(-1).fillna(False) ,'ColumnX'].to_list()

# Output:
['Hi', 'HEY']

CodePudding user response：

Maybe something like this:

ilist=[]
for i in range(0,len(df["ColumnX"])):
    if df["ColumnX"][i] == '  Open':
        ilist.append(df["ColumnX"][i-1])

CodePudding user response：

You could use enumerate and acces the previous value by index:

import pandas as pd

df = pd.DataFrame(["Hi", "  Open", "How are you", "NAN", "Something", "Something", "HEY","  Open"], columns=["ColumnX"])

ilist = []

for i, value in enumerate(df["ColumnX"]):
    if value == "  Open" and i>0: # check if i>0 to prevent out of index error
        ilist.append(df.at[i-1, "ColumnX"])
        
print(ilist)

Out:

['Hi', 'HEY']