Compare the previous N rows to the current row in a pandas column

Question

I am trying to populate a new column in my pandas dataframe by considering the values of the previous n rows. If the current value is not equal to any of the past n values in that column, it should populate "N", else "Y".

Please let me know what would be a good way to achieve this.

Here's my input data :

testdata = {'col1' :['car','car','car','bus','bus','bus','car']}
df = pd.DataFrame.from_dict(testdata)

Input DF:

  col1
0  car
1  car
2  car
3  bus
4  bus
5  car  
6  car

Output DF (with n=2):

  col1   Result
0  car         
1  car         
2  car      Y  
3  bus      N  
4  bus      Y  
5  bus      Y  
6  car      N 

Answer 1

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

Answer 2

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