Ndtyjky

Hi I am working to find out repetitive position of the following data frame:

data = pd.DataFrame()

data ['league'] =['A','A','A','A','A','A','B','B','B']

data ['Team'] = ['X','X','X','Y','Y','Y','Z','Z','Z']

data ['week'] =[1,2,3,1,2,3,1,2,3]

data ['position']= [1,1,2,2,2,1,2,3,4]

I will compare the data for position from previous row, it is it the same, I will assign one. If it is different previous row, I will assign as 1

My expected outcome will be as follow:

It means I will group by (League, Team and week) and work out the frequency.
Can anyone advise how to do that in Pandas

Thanks,

Zep

Use diff, and compare against 0:

v = df.position.diff()

v[0] = 0

df['frequency'] = v.ne(0).astype(int)



print(df)

  league Team  week  position  frequency

0      A    X     1         1          0

1      A    X     2         1          0

2      A    X     3         2          1

3      A    Y     1         2          0

4      A    Y     2         2          0

5      A    Y     3         1          1

6      B    Z     1         2          1

7      B    Z     2         3          1

8      B    Z     3         4          1

For performance reasons, you should try to avoid a fillna call.

df = pd.concat([df] * 100000, ignore_index=True)



%timeit df['frequency'] = df['position'].diff().abs().fillna(0,downcast='infer')

%%timeit

v = df.position.diff()

v[0] = 0

df['frequency'] = v.ne(0).astype(int)



83.7 ms ± 1.55 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

10.9 ms ± 217 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

To extend this answer to work in a groupby, use

v = df.groupby(['league', 'Team', 'week']).position.diff()

v[np.isnan(v)] = 0



df['frequency'] = v.ne(0).astype(int)

Use diff and abs with fillna:

data['frequency'] = data['position'].diff().abs().fillna(0,downcast='infer')



print(data)

  league Team  week  position  frequency

0      A    X     1         1          0

1      A    X     2         1          0

2      A    X     3         2          1

3      A    Y     1         2          0

4      A    Y     2         2          0

5      A    Y     3         1          1

6      B    Z     1         2          1

7      B    Z     2         3          1

8      B    Z     3         4          1

Using groupby gives all zeros, since you are comparing within groups not on whole dataframe.

data.groupby(['league', 'Team', 'week'])['position'].diff().fillna(0,downcast='infer')



0    0

1    0

2    0

3    0

4    0

5    0

6    0

7    0

8    0

Name: position, dtype: int64