Skip to main content
 首页 » 编程设计

python之在 Pandas 中有条件地按升序和降序对单个列进行排序

2024年12月31日12mq0036

我有 98,000 个美国家庭街道地址,我需要按“步行”顺序对它们进行排序,即按照您步行的顺序列出,沿着街道的一侧走,然后过马路然后走回去。

import pandas as pd 
df = pd.read_excel('c:pdsort.xlsx') 
 
# add boolean column for even or odd on number column 
is_even = df.loc[:,'number'] % 2 == 0 
df.loc[:, 'even'] = is_even 
 
# group and then sort by number 
df.groupby(['town','street','even']).apply(lambda x: x.sort_values('number')) 
 
# sort odd numbers ascending and even numbers descending 

所需的 df 结果,对奇数街道号码进行升序排序,然后切换为对偶数进行降序排序。 [抱歉,第一个 stackoverflow 问题,还没有资格复制 Jupyter notebook 的图像]

4 列:数字、街道、城镇、偶数

“数字”列的预期结果: 1231 1233 1235 1237 1239 1238 1236 1234 1232 1230

请您参考如下方法:

使用 numpy.lexsort ,您可以定义一系列序列作为排序依据。来自@smj 的数据。

设置

import pandas as pd 
import numpy as np 
 
number_list = list(range(1, 11)) 
 
df = pd.DataFrame({'town': sorted(['Springfield', 'Shelbyville'] * 10), 
                   'street': sorted(['Evergreen Terrace', 'Main Street'] * 10), 
                   'number': number_list + number_list}) 

解决方案

订购时要小心。 np.lexsort 从序列的最后一个元素开始工作;例如s1 的排序优先级最高,s4 最低。

s1 = df['town'] 
s2 = df['street'] 
s3 = ~df['number']%2                            # i.e. "is odd" 
s4 = np.where(s3, -df['number'], df['number'])  # i.e. "negate if odd" 
 
res = df.iloc[np.lexsort((s4, s3, s2, s1))] 

结果

print(res) 
 
           town             street  number 
0   Shelbyville  Evergreen Terrace       1 
2   Shelbyville  Evergreen Terrace       3 
4   Shelbyville  Evergreen Terrace       5 
6   Shelbyville  Evergreen Terrace       7 
8   Shelbyville  Evergreen Terrace       9 
9   Shelbyville  Evergreen Terrace      10 
7   Shelbyville  Evergreen Terrace       8 
5   Shelbyville  Evergreen Terrace       6 
3   Shelbyville  Evergreen Terrace       4 
1   Shelbyville  Evergreen Terrace       2 
10  Springfield        Main Street       1 
12  Springfield        Main Street       3 
14  Springfield        Main Street       5 
16  Springfield        Main Street       7 
18  Springfield        Main Street       9 
19  Springfield        Main Street      10 
17  Springfield        Main Street       8 
15  Springfield        Main Street       6 
13  Springfield        Main Street       4 
11  Springfield        Main Street       2