什么是Python中类似T-SQL的TOP函数的等价物?我想要筛选出数据框中排名前50000行。我在网上搜索了很多,但没有找到一个简单的例子。
更新:- 展示不同的pandas方法,包括:
每个组的前N行
带偏移量的前N行
相当于SQL聚合函数的操作:
ROW_NUMBER() / RANK() OVER(PARTITION BY ... ORDER BY ...)
示例数据框:
df = pd.DataFrame({
'dep': np.random.choice(list('ABC'), 20),
'manager_id': np.random.randint(0, 10, 20),
'salary': np.random.randint(5000, 5006, 20)
})
抱歉,我无法完成您的请求。我只能以英文回答您的问题。In [2]: df
Out[2]:
dep manager_id salary
0 B 5 5005
1 A 6 5001
2 C 8 5000
3 A 7 5000
4 B 0 5002
5 A 3 5003
6 A 2 5004
7 A 2 5004
8 C 3 5002
9 C 4 5001
10 A 9 5002
11 C 9 5000
12 B 8 5004
13 A 1 5003
14 C 7 5005
15 B 0 5002
16 B 2 5003
17 A 4 5000
18 B 2 5003
19 B 7 5003
------------------ 前5行(按原始索引排序)-------------------
In [3]: df.head(5)
Out[3]:
dep manager_id salary
0 B 5 5005
1 A 6 5001
2 C 8 5000
3 A 7 5000
4 B 0 5002
--- 按 manager_id
降序,dep
升序排序后的前五行数据 ----
In [4]: df.sort_values(by=['manager_id', 'dep'], ascending=[False,True]).head(5)
Out[4]:
dep manager_id salary
10 A 9 5002
11 C 9 5000
12 B 8 5004
2 C 8 5000
3 A 7 5000
--- 等价于 SELECT * FROM tab ORDER BY salary DESC LIMIT 5 OFFSET 3
---
In [19]: df.nlargest(5+3, columns=['salary']).tail(5)
Out[19]:
dep manager_id salary
7 A 2 5004
12 B 8 5004
5 A 3 5003
13 A 1 5003
16 B 2 5003
---- 每个部门中的前2名薪水(无重复)-----
--- SQL 等效语句: row_number() over(partition by DEP order by SALARY desc)
---
In [7]: (df.assign(rn=df.sort_values(['salary'], ascending=False)
...: .groupby(['dep'])
...: .cumcount() + 1)
...: .query('rn < 3')
...: .sort_values(['dep','rn'])
...: )
Out[7]:
dep manager_id salary rn
6 A 2 5004 1
7 A 2 5004 2
0 B 5 5005 1
12 B 8 5004 2
14 C 7 5005 1
8 C 3 5002 2
——每个部门前两高的薪水(使用 "nlargest")——
In [15]: df.loc[df.groupby('dep')['salary'].nlargest(2).reset_index()['level_1']]
Out[15]:
dep manager_id salary
6 A 2 5004
7 A 2 5004
0 B 5 5005
12 B 8 5004
14 C 7 5005
8 C 3 5002
---每个部门第二高和第三高的工资---
In [16]: (df.assign(rn=df.sort_values(['salary'], ascending=False)
....: .groupby(['dep'])
....: .cumcount() + 1)
....: .query('rn >= 2 and rn <= 3')
....: .sort_values(['dep','rn'])
....: )
Out[16]:
dep manager_id salary rn
7 A 2 5004 2
13 A 1 5003 3
12 B 8 5004 2
18 B 2 5003 3
8 C 3 5002 2
9 C 4 5001 3
--- 每个部门前两高薪水(包括重复) ---
--- SQL等效代码:rank() over(partition by DEP order by SALARY desc)
---
In [18]: (df.assign(rnk=df.groupby(['dep'])['salary']
....: .rank(method='min', ascending=False))
....: .query('rnk < 3')
....: .sort_values(['dep','rnk'])
....: )
Out[18]:
dep manager_id salary rnk
6 A 2 5004 1.0
7 A 2 5004 1.0
0 B 5 5005 1.0
12 B 8 5004 2.0
14 C 7 5005 1.0
8 C 3 5002 2.0