Compare the previous N rows to the current row in a pandas column
Here is my way
n=2
l=[False]*n+[df.iloc[x,0] in df.iloc[x-n:x,0].tolist() for x in np.arange(n,len(df))]
df['New']=l
df
col1 New
0 car False
1 car False
2 car True
3 bus False
4 bus True
5 bus True
6 car False
You can do this with a Rolling.apply
call.
n = 2
res = (df['col1'].astype('category')
.cat.codes
.rolling(n+1)
.apply(lambda x: x[-1] in x[:-1], raw=True))
df['Result'] = np.where(res == 1, 'Y', 'N')
df
col1 Result
0 car N
1 car N
2 car Y
3 bus N
4 bus Y
5 bus Y
6 car N
Rolling only works with numeric data, so the initial step is to factorise it. This can be done in many ways, I've used astype('category')
and then extracted the codes.
Another option is using pd.Categorical
for the conversion,
res = (df.assign(col1=pd.Categorical(df['col1']).codes)['col1']
.rolling(n+1)
.apply(lambda x: x[-1] in x[:-1], raw=True))
df['Result'] = res.map({1: 'Y', 0: 'N'})
df
col1 Result
0 car NaN
1 car NaN
2 car Y
3 bus N
4 bus Y
5 bus Y
6 car N