I have a dataframe, d
:
<code> Position Operation Side Price Size
9 9 0 1 0.7289 -16
8 8 0 1 0.729 -427
7 7 0 1 0.7291 -267
6 6 0 1 0.7292 -15
5 5 0 1 0.7293 -16
4 4 0 1 0.7294 -16
3 3 0 1 0.7295 -426
2 2 0 1 0.7296 -8
1 1 0 1 0.7297 -14
0 0 0 1 0.7298 -37
10 0 0 0 0.7299 6
11 1 0 0 0.73 34
12 2 0 0 0.7301 7
13 3 0 0 0.7302 9
14 4 0 0 0.7303 16
15 5 0 0 0.7304 15
16 6 0 0 0.7305 429
17 7 0 0 0.7306 16
18 8 0 0 0.7307 265
19 9 0 0 0.7308 18
</code>
<code> Position Operation Side Price Size
9 9 0 1 0.7289 -16
8 8 0 1 0.729 -427
7 7 0 1 0.7291 -267
6 6 0 1 0.7292 -15
5 5 0 1 0.7293 -16
4 4 0 1 0.7294 -16
3 3 0 1 0.7295 -426
2 2 0 1 0.7296 -8
1 1 0 1 0.7297 -14
0 0 0 1 0.7298 -37
10 0 0 0 0.7299 6
11 1 0 0 0.73 34
12 2 0 0 0.7301 7
13 3 0 0 0.7302 9
14 4 0 0 0.7303 16
15 5 0 0 0.7304 15
16 6 0 0 0.7305 429
17 7 0 0 0.7306 16
18 8 0 0 0.7307 265
19 9 0 0 0.7308 18
</code>
Position Operation Side Price Size
9 9 0 1 0.7289 -16
8 8 0 1 0.729 -427
7 7 0 1 0.7291 -267
6 6 0 1 0.7292 -15
5 5 0 1 0.7293 -16
4 4 0 1 0.7294 -16
3 3 0 1 0.7295 -426
2 2 0 1 0.7296 -8
1 1 0 1 0.7297 -14
0 0 0 1 0.7298 -37
10 0 0 0 0.7299 6
11 1 0 0 0.73 34
12 2 0 0 0.7301 7
13 3 0 0 0.7302 9
14 4 0 0 0.7303 16
15 5 0 0 0.7304 15
16 6 0 0 0.7305 429
17 7 0 0 0.7306 16
18 8 0 0 0.7307 265
19 9 0 0 0.7308 18
Using the below for updates to d
to recalculate Position
:
d['Position'] = d.groupby('Side')['Price'].rank().astype('int').sub(1)
But as the order of the sort is different for each Side
grouping, is there a way to sort ascending
for one group and descending
for another?
Code
I think simple solution is to rank the groups by multiplying their Price by -1, where the ranking order should be reversed.
<code>cond = d['Side'].eq(1) # you can use isin when apply to multiple group
d['rank'] = (
d['Price']
.mask(cond, d['Price'].mul(-1))
.groupby(d['Side']).rank().astype('int').sub(1)
)
</code>
<code>cond = d['Side'].eq(1) # you can use isin when apply to multiple group
d['rank'] = (
d['Price']
.mask(cond, d['Price'].mul(-1))
.groupby(d['Side']).rank().astype('int').sub(1)
)
</code>
cond = d['Side'].eq(1) # you can use isin when apply to multiple group
d['rank'] = (
d['Price']
.mask(cond, d['Price'].mul(-1))
.groupby(d['Side']).rank().astype('int').sub(1)
)
5
You could use groupby.apply
to access the group.name
:
<code>d['rank'] = (d.groupby('Side')['Price']
.apply(lambda x: x.rank(ascending=x.name==0).astype('int').sub(1))
.droplevel(0)
)
</code>
<code>d['rank'] = (d.groupby('Side')['Price']
.apply(lambda x: x.rank(ascending=x.name==0).astype('int').sub(1))
.droplevel(0)
)
</code>
d['rank'] = (d.groupby('Side')['Price']
.apply(lambda x: x.rank(ascending=x.name==0).astype('int').sub(1))
.droplevel(0)
)
Output:
<code> Position Operation Side Price Size rank
9 9 0 1 0.7289 -16 9
8 8 0 1 0.7290 -427 8
7 7 0 1 0.7291 -267 7
6 6 0 1 0.7292 -15 6
5 5 0 1 0.7293 -16 5
4 4 0 1 0.7294 -16 4
3 3 0 1 0.7295 -426 3
2 2 0 1 0.7296 -8 2
1 1 0 1 0.7297 -14 1
0 0 0 1 0.7298 -37 0
10 0 0 0 0.7299 6 0
11 1 0 0 0.7300 34 1
12 2 0 0 0.7301 7 2
13 3 0 0 0.7302 9 3
14 4 0 0 0.7303 16 4
15 5 0 0 0.7304 15 5
16 6 0 0 0.7305 429 6
17 7 0 0 0.7306 16 7
18 8 0 0 0.7307 265 8
19 9 0 0 0.7308 18 9
</code>
<code> Position Operation Side Price Size rank
9 9 0 1 0.7289 -16 9
8 8 0 1 0.7290 -427 8
7 7 0 1 0.7291 -267 7
6 6 0 1 0.7292 -15 6
5 5 0 1 0.7293 -16 5
4 4 0 1 0.7294 -16 4
3 3 0 1 0.7295 -426 3
2 2 0 1 0.7296 -8 2
1 1 0 1 0.7297 -14 1
0 0 0 1 0.7298 -37 0
10 0 0 0 0.7299 6 0
11 1 0 0 0.7300 34 1
12 2 0 0 0.7301 7 2
13 3 0 0 0.7302 9 3
14 4 0 0 0.7303 16 4
15 5 0 0 0.7304 15 5
16 6 0 0 0.7305 429 6
17 7 0 0 0.7306 16 7
18 8 0 0 0.7307 265 8
19 9 0 0 0.7308 18 9
</code>
Position Operation Side Price Size rank
9 9 0 1 0.7289 -16 9
8 8 0 1 0.7290 -427 8
7 7 0 1 0.7291 -267 7
6 6 0 1 0.7292 -15 6
5 5 0 1 0.7293 -16 5
4 4 0 1 0.7294 -16 4
3 3 0 1 0.7295 -426 3
2 2 0 1 0.7296 -8 2
1 1 0 1 0.7297 -14 1
0 0 0 1 0.7298 -37 0
10 0 0 0 0.7299 6 0
11 1 0 0 0.7300 34 1
12 2 0 0 0.7301 7 2
13 3 0 0 0.7302 9 3
14 4 0 0 0.7303 16 4
15 5 0 0 0.7304 15 5
16 6 0 0 0.7305 429 6
17 7 0 0 0.7306 16 7
18 8 0 0 0.7307 265 8
19 9 0 0 0.7308 18 9
Most Efficient :
<code>import pandas as pd
data = {
'Position': [9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
'Operation': [0]*20,
'Side': [1]*10 + [0]*10,
'Price': [0.7289, 0.729, 0.7291, 0.7292, 0.7293, 0.7294, 0.7295, 0.7296, 0.7297, 0.7298,
0.7299, 0.73, 0.7301, 0.7302, 0.7303, 0.7304, 0.7305, 0.7306, 0.7307, 0.7308],
'Size': [-16, -427, -267, -15, -16, -16, -426, -8, -14, -37, 6, 34, 7, 9, 16, 15, 429, 16, 265, 18]
}
df = pd.DataFrame(data)
print(df)
#Fastest and most efficient
# Add a new 'rank' column with ascending ranking for Side=1 and descending for Side=0
df['rank'] = df.groupby('Side')['Price']
.transform(lambda x: x.rank(ascending=(x.name == 1)).astype('int').sub(1))
df['rank1'] = df.groupby('Side')['Price']
.transform(lambda x: x.rank(ascending=(x.name == 0)).astype('int').sub(1))
print(df)
</code>
<code>import pandas as pd
data = {
'Position': [9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
'Operation': [0]*20,
'Side': [1]*10 + [0]*10,
'Price': [0.7289, 0.729, 0.7291, 0.7292, 0.7293, 0.7294, 0.7295, 0.7296, 0.7297, 0.7298,
0.7299, 0.73, 0.7301, 0.7302, 0.7303, 0.7304, 0.7305, 0.7306, 0.7307, 0.7308],
'Size': [-16, -427, -267, -15, -16, -16, -426, -8, -14, -37, 6, 34, 7, 9, 16, 15, 429, 16, 265, 18]
}
df = pd.DataFrame(data)
print(df)
#Fastest and most efficient
# Add a new 'rank' column with ascending ranking for Side=1 and descending for Side=0
df['rank'] = df.groupby('Side')['Price']
.transform(lambda x: x.rank(ascending=(x.name == 1)).astype('int').sub(1))
df['rank1'] = df.groupby('Side')['Price']
.transform(lambda x: x.rank(ascending=(x.name == 0)).astype('int').sub(1))
print(df)
</code>
import pandas as pd
data = {
'Position': [9, 8, 7, 6, 5, 4, 3, 2, 1, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9],
'Operation': [0]*20,
'Side': [1]*10 + [0]*10,
'Price': [0.7289, 0.729, 0.7291, 0.7292, 0.7293, 0.7294, 0.7295, 0.7296, 0.7297, 0.7298,
0.7299, 0.73, 0.7301, 0.7302, 0.7303, 0.7304, 0.7305, 0.7306, 0.7307, 0.7308],
'Size': [-16, -427, -267, -15, -16, -16, -426, -8, -14, -37, 6, 34, 7, 9, 16, 15, 429, 16, 265, 18]
}
df = pd.DataFrame(data)
print(df)
#Fastest and most efficient
# Add a new 'rank' column with ascending ranking for Side=1 and descending for Side=0
df['rank'] = df.groupby('Side')['Price']
.transform(lambda x: x.rank(ascending=(x.name == 1)).astype('int').sub(1))
df['rank1'] = df.groupby('Side')['Price']
.transform(lambda x: x.rank(ascending=(x.name == 0)).astype('int').sub(1))
print(df)
Less Efficient :
<code>df['rank'] = df.groupby('Side')['Price'].transform(lambda x: np.argsort(np.argsort(x.values)))
df['rank1'] = df.groupby('Side')['Price'].transform(lambda x: np.argsort(- np.argsort(x.values)))
</code>
<code>df['rank'] = df.groupby('Side')['Price'].transform(lambda x: np.argsort(np.argsort(x.values)))
df['rank1'] = df.groupby('Side')['Price'].transform(lambda x: np.argsort(- np.argsort(x.values)))
</code>
df['rank'] = df.groupby('Side')['Price'].transform(lambda x: np.argsort(np.argsort(x.values)))
df['rank1'] = df.groupby('Side')['Price'].transform(lambda x: np.argsort(- np.argsort(x.values)))
Output :
<code>'''
Position Operation Side Price Size rank rank1
0 9 0 1 0.7289 -16 0 9
1 8 0 1 0.7290 -427 1 8
2 7 0 1 0.7291 -267 2 7
3 6 0 1 0.7292 -15 3 6
4 5 0 1 0.7293 -16 4 5
5 4 0 1 0.7294 -16 5 4
6 3 0 1 0.7295 -426 6 3
7 2 0 1 0.7296 -8 7 2
8 1 0 1 0.7297 -14 8 1
9 0 0 1 0.7298 -37 9 0
10 0 0 0 0.7299 6 0 9
11 1 0 0 0.7300 34 1 8
12 2 0 0 0.7301 7 2 7
13 3 0 0 0.7302 9 3 6
14 4 0 0 0.7303 16 4 5
15 5 0 0 0.7304 15 5 4
16 6 0 0 0.7305 429 6 3
17 7 0 0 0.7306 16 7 2
18 8 0 0 0.7307 265 8 1
19 9 0 0 0.7308 18 9 0
'''
</code>
<code>'''
Position Operation Side Price Size rank rank1
0 9 0 1 0.7289 -16 0 9
1 8 0 1 0.7290 -427 1 8
2 7 0 1 0.7291 -267 2 7
3 6 0 1 0.7292 -15 3 6
4 5 0 1 0.7293 -16 4 5
5 4 0 1 0.7294 -16 5 4
6 3 0 1 0.7295 -426 6 3
7 2 0 1 0.7296 -8 7 2
8 1 0 1 0.7297 -14 8 1
9 0 0 1 0.7298 -37 9 0
10 0 0 0 0.7299 6 0 9
11 1 0 0 0.7300 34 1 8
12 2 0 0 0.7301 7 2 7
13 3 0 0 0.7302 9 3 6
14 4 0 0 0.7303 16 4 5
15 5 0 0 0.7304 15 5 4
16 6 0 0 0.7305 429 6 3
17 7 0 0 0.7306 16 7 2
18 8 0 0 0.7307 265 8 1
19 9 0 0 0.7308 18 9 0
'''
</code>
'''
Position Operation Side Price Size rank rank1
0 9 0 1 0.7289 -16 0 9
1 8 0 1 0.7290 -427 1 8
2 7 0 1 0.7291 -267 2 7
3 6 0 1 0.7292 -15 3 6
4 5 0 1 0.7293 -16 4 5
5 4 0 1 0.7294 -16 5 4
6 3 0 1 0.7295 -426 6 3
7 2 0 1 0.7296 -8 7 2
8 1 0 1 0.7297 -14 8 1
9 0 0 1 0.7298 -37 9 0
10 0 0 0 0.7299 6 0 9
11 1 0 0 0.7300 34 1 8
12 2 0 0 0.7301 7 2 7
13 3 0 0 0.7302 9 3 6
14 4 0 0 0.7303 16 4 5
15 5 0 0 0.7304 15 5 4
16 6 0 0 0.7305 429 6 3
17 7 0 0 0.7306 16 7 2
18 8 0 0 0.7307 265 8 1
19 9 0 0 0.7308 18 9 0
'''