Tracking the position of element in rows using pandas

user_9561497 0
Viewed 126

I have been working on sport analysis especially soccer, I am calculating week rank of the teams. I want to track their position changes. Here is my sample data that replicate my problem.

df = pd.DataFrame()
df ['Season'] = ['1314','1314','1314','1314','1314','1314','1314','1314','1314','1415','1415','1415','1415','1415','1415','1415','1415','1415']
df ['Team'] = ['A','B','C','A','B','C','A','B','C','A','B','C','A','B','C','A','B','C']
df ['GW'] = [1,1,1,2,2,2,3,3,3,1,1,1,2,2,2,3,3,3]
df['Position'] = [1,2,3,3,1,2,2,3,1,2,1,3,2,1,3,3,2,1]

SO I have all the weekly position etc. To track the position changes, I will sort dataframe first as follow:

df = df.sort_values (['Season','Team'])

after sorting, I apply diff() function to get the change as follow:

v = df.Position.diff()
v[0] = 0
df ['Position_change']= v

I wanted to track the changes for each GW. In my data frame i have 3 GWs. I want all GW1 for each team to be 0 at position change. The second issue is the changes is taken between two teams (which i do not want) if I use diff() You can see in the attached picture: enter image description here

Can anyone advice on it?

pandas python
1 Answers

IIUC, you need:

df['Position_Change']=df.groupby(['Season','Team'])['Position'].apply(lambda x : x.diff().fillna(0))
print(df)

   Season Team  GW  Position  Position_Change
0    1314    A   1         1         0.0
3    1314    A   2         3         2.0
6    1314    A   3         2        -1.0
1    1314    B   1         2         0.0
4    1314    B   2         1        -1.0
7    1314    B   3         3         2.0
2    1314    C   1         3         0.0
5    1314    C   2         2        -1.0
8    1314    C   3         1        -1.0
9    1415    A   1         2         0.0
12   1415    A   2         2         0.0
15   1415    A   3         3         1.0
10   1415    B   1         1         0.0
13   1415    B   2         1         0.0
16   1415    B   3         2         1.0
11   1415    C   1         3         0.0
14   1415    C   2         3         0.0
17   1415    C   3         1        -2.0
user_98406370
Related