复杂的加入 pandas [英] Complex Joins in Pandas
问题描述
如何在Pandas中编写此内容?使用SQL更好吗?
How can you write this in Pandas? Is it just better to use SQL?
我尝试了"where"，"isin"，"join"，"merge"，但我无法在Pandas中复制它.
I have tried "where", "isin", "join", "merge", and I am not able to replicate this in Pandas.
问题:
我基本上有两列(x和y)，其值是1到10.然后，我想使用如下所示的特定条件进行自我联接.
Problem:
I basically have two columns (x & y) with values one through 10. I then want to do a self join with a specific criteria shown below.
IF OBJECT_ID('tempdb..#t1','u') IS NOT NULL
BEGIN
DROP TABLE #t1
END
CREATE TABLE #t1
(
x int,
y int
)
INSERT #t1
select distinct number as x, number as y from Master..spt_values
where number between 1 and 10
order by number
select a.x as a_x,
a.y as a_y,
b.x as b_x,
b.y as b_y
from #t1 as a
join #t1 as b on (a.x <= b.x and a.x > b.x-4)
order by a.x,a.y
有什么建议吗?
以下是不使用SQL的查询的结果:
Here are the results of the query for those who don't have SQL:
a_x a_y b_x b_y
1 1 1 1
1 1 2 2
1 1 3 3
1 1 4 4
2 2 2 2
2 2 3 3
2 2 4 4
2 2 5 5
3 3 3 3
3 3 4 4
3 3 5 5
3 3 6 6
4 4 4 4
4 4 5 5
4 4 6 6
4 4 7 7
5 5 5 5
5 5 6 6
5 5 7 7
5 5 8 8
6 6 6 6
6 6 7 7
6 6 8 8
6 6 9 9
7 7 7 7
7 7 8 8
7 7 9 9
7 7 10 10
8 8 8 8
8 8 9 9
8 8 10 10
9 9 9 9
9 9 10 10
10 10 10 10
a_x a_y b_x b_y
1 1 1 1
1 1 2 2
1 1 3 3
1 1 4 4
2 2 2 2
2 2 3 3
2 2 4 4
2 2 5 5
3 3 3 3
3 3 4 4
3 3 5 5
3 3 6 6
4 4 4 4
4 4 5 5
4 4 6 6
4 4 7 7
5 5 5 5
5 5 6 6
5 5 7 7
5 5 8 8
6 6 6 6
6 6 7 7
6 6 8 8
6 6 9 9
7 7 7 7
7 7 8 8
7 7 9 9
7 7 10 10
8 8 8 8
8 8 9 9
8 8 10 10
9 9 9 9
9 9 10 10
10 10 10 10
以下是数据框
Here is the dataframe
df = pd.DataFrame({'x':range(1,11),
'y':range(1,11)})
推荐答案
以下是解决方案:
import pandas as pd
import numpy as np
df = pd.DataFrame({'x':range(1,11),
'y':range(1,11)})
df2 = pd.merge(df, df, on=np.ones(df.shape[0]), suffixes=("_a", "_b")).drop("key_0", axis=1)
print df2.query("x_a <= x_b & x_a > x_b - 4").reset_index(drop=True)
输出为:
x_a y_a x_b y_b
0 1 1 1 1
1 1 1 2 2
2 1 1 3 3
3 1 1 4 4
4 2 2 2 2
5 2 2 3 3
6 2 2 4 4
7 2 2 5 5
8 3 3 3 3
9 3 3 4 4
10 3 3 5 5
11 3 3 6 6
12 4 4 4 4
13 4 4 5 5
14 4 4 6 6
15 4 4 7 7
16 5 5 5 5
17 5 5 6 6
18 5 5 7 7
19 5 5 8 8
20 6 6 6 6
21 6 6 7 7
22 6 6 8 8
23 6 6 9 9
24 7 7 7 7
25 7 7 8 8
26 7 7 9 9
27 7 7 10 10
28 8 8 8 8
29 8 8 9 9
30 8 8 10 10
31 9 9 9 9
32 9 9 10 10
33 10 10 10 10
这篇关于复杂的加入 pandas 的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
