可以使用比较合并两个pandas数据框吗？
通过以下命令：
pandas.merge(df_1, df_2, left_on=['date'], right_on=['from_date'])
我将两个表中的行结合起来，前提是第一个表中的date列的值等于第二个表中的from_date列的值。
现在我想让这个操作稍微复杂一点。我需要将第一个表中的一行与第二个表中的一行结合起来，条件是第一个表中的date列的值大于或等于第二个表中的from_date列的值，并且小于第二个表中的upto_date列的值。
在SQL中，可以使用类似这样的语句：
select
*
from
table_1
join
table_2
on
table_1.date >= table_2.from_date
and
table_1.date < table_2.upto_date
那么在pandas中可以做到吗？
3 个回答
0
conditional_join 是来自 pyjanitor 的一个功能，它可以高效地处理不等式连接：
使用 @hernamesbarbara 的虚拟数据：
# pip install pyjanitor
import pandas as pd
import janitor
(df1.conditional_join(
df2,
('timepoint', 'from_date', '>='),
('timepoint', 'to_date', '<='))
)
left right
timepoint measure from_date to_date measure
0 2014-01-03 5 2014-01-02 2014-01-06 89
1 2014-01-03 5 2014-01-03 2014-01-07 80
2 2014-01-04 73 2014-01-02 2014-01-06 89
3 2014-01-04 73 2014-01-03 2014-01-07 80
4 2014-01-04 73 2014-01-04 2014-01-05 44
5 2014-01-05 40 2014-01-02 2014-01-06 89
6 2014-01-05 40 2014-01-03 2014-01-07 80
7 2014-01-05 40 2014-01-04 2014-01-05 44
8 2014-01-05 40 2014-01-05 2014-01-12 68
9 2014-01-06 45 2014-01-02 2014-01-06 89
10 2014-01-06 45 2014-01-03 2014-01-07 80
11 2014-01-06 45 2014-01-05 2014-01-12 68
12 2014-01-06 45 2014-01-06 2014-01-11 62
13 2014-01-08 2 2014-01-05 2014-01-12 68
14 2014-01-08 2 2014-01-06 2014-01-11 62
15 2014-01-08 2 2014-01-07 2014-01-14 5
16 2014-01-08 2 2014-01-08 2014-01-09 23
17 2014-01-09 96 2014-01-05 2014-01-12 68
18 2014-01-09 96 2014-01-06 2014-01-11 62
19 2014-01-09 96 2014-01-07 2014-01-14 5
20 2014-01-09 96 2014-01-08 2014-01-09 23
21 2014-01-10 82 2014-01-05 2014-01-12 68
22 2014-01-10 82 2014-01-06 2014-01-11 62
23 2014-01-10 82 2014-01-07 2014-01-14 5
24 2014-01-11 61 2014-01-05 2014-01-12 68
25 2014-01-11 61 2014-01-06 2014-01-11 62
26 2014-01-11 61 2014-01-07 2014-01-14 5
27 2014-01-12 68 2014-01-05 2014-01-12 68
28 2014-01-12 68 2014-01-07 2014-01-14 5
29 2014-01-13 8 2014-01-07 2014-01-14 5
30 2014-01-14 94 2014-01-07 2014-01-14 5
0
我好像找到了一个解决办法。不过，我不太确定这个办法是否优雅和最优：
df_1['A'] = 'A'
df_2['A'] = 'A'
df = pandas.merge(df_1, df_2, on=['A'])
df = df[(df['date'] >= df['from']) & (df['date'] < df['upto'])]
del df['A']
这是代表提问者发布的内容
2
pandasql 是一个非常实用的工具，可以用 SQLite 的查询语法来查询 pandas 的数据框（DataFrame）。
资源
- pandasql - PyPI 文档
- yhat/pandasql - GitHub 源代码
-
pip install -U pandasql
这里有一个和你描述的类似的例子。
导入库
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pandas as pd
from pandas.io.parsers import StringIO
from pandasql import sqldf
# helper func useful for saving keystrokes
# when running multiple queries
def dbGetQuery(q):
return sqldf(q, globals())
生成一些假数据
sample_a = """timepoint,measure
2014-01-01 00:00:00,78
2014-01-03 00:00:00,5
2014-01-04 00:00:00,73
2014-01-05 00:00:00,40
2014-01-06 00:00:00,45
2014-01-08 00:00:00,2
2014-01-09 00:00:00,96
2014-01-10 00:00:00,82
2014-01-11 00:00:00,61
2014-01-12 00:00:00,68
2014-01-13 00:00:00,8
2014-01-14 00:00:00,94
2014-01-15 00:00:00,16
2014-01-16 00:00:00,31
2014-01-17 00:00:00,10
2014-01-18 00:00:00,34
2014-01-19 00:00:00,27
2014-01-20 00:00:00,75
2014-01-21 00:00:00,49
2014-01-23 00:00:00,28
2014-01-24 00:00:00,91
2014-01-25 00:00:00,88
2014-01-27 00:00:00,98
2014-01-28 00:00:00,39
2014-01-29 00:00:00,90
2014-01-30 00:00:00,63
2014-01-31 00:00:00,77
"""
sample_b = """from_date,to_date,measure
2014-01-02 00:00:00,2014-01-06 00:00:00,89
2014-01-03 00:00:00,2014-01-07 00:00:00,80
2014-01-04 00:00:00,2014-01-05 00:00:00,44
2014-01-05 00:00:00,2014-01-12 00:00:00,68
2014-01-06 00:00:00,2014-01-11 00:00:00,62
2014-01-07 00:00:00,2014-01-14 00:00:00,5
2014-01-08 00:00:00,2014-01-09 00:00:00,23
"""
读取数据集以创建两个数据框
df1 = pd.read_csv(StringIO(sample_a), parse_dates=['timepoint'])
df2 = pd.read_csv(StringIO(sample_b), parse_dates=['from_date', 'to_date'])
编写 SQL 查询
注意，这里使用了 SQLite 的 BETWEEN 操作符。如果你更喜欢，也可以换成类似 ON timepoint >= from_date AND timepoint < to_date 的写法。
query = """
SELECT
DATE(df1.timepoint) AS timepoint
, DATE(df2.from_date) AS start
, DATE(df2.to_date) AS end
, df1.measure AS measure_a
, df2.measure AS measure_b
FROM
df1
INNER JOIN df2
ON df1.timepoint BETWEEN
df2.from_date AND df2.to_date
ORDER BY
df1.timepoint;
"""
使用辅助函数运行查询
df3 = dbGetQuery(query)
df3
timepoint start end measure_a measure_b
0 2014-01-03 2014-01-02 2014-01-06 5 89
1 2014-01-03 2014-01-03 2014-01-07 5 80
2 2014-01-04 2014-01-02 2014-01-06 73 89
3 2014-01-04 2014-01-03 2014-01-07 73 80
4 2014-01-04 2014-01-04 2014-01-05 73 44
5 2014-01-05 2014-01-02 2014-01-06 40 89
6 2014-01-05 2014-01-03 2014-01-07 40 80
7 2014-01-05 2014-01-04 2014-01-05 40 44
8 2014-01-05 2014-01-05 2014-01-12 40 68
9 2014-01-06 2014-01-02 2014-01-06 45 89
10 2014-01-06 2014-01-03 2014-01-07 45 80
11 2014-01-06 2014-01-05 2014-01-12 45 68
12 2014-01-06 2014-01-06 2014-01-11 45 62
13 2014-01-08 2014-01-05 2014-01-12 2 68
14 2014-01-08 2014-01-06 2014-01-11 2 62
15 2014-01-08 2014-01-07 2014-01-14 2 5
16 2014-01-08 2014-01-08 2014-01-09 2 23
17 2014-01-09 2014-01-05 2014-01-12 96 68
18 2014-01-09 2014-01-06 2014-01-11 96 62
19 2014-01-09 2014-01-07 2014-01-14 96 5
20 2014-01-09 2014-01-08 2014-01-09 96 23
21 2014-01-10 2014-01-05 2014-01-12 82 68
22 2014-01-10 2014-01-06 2014-01-11 82 62
23 2014-01-10 2014-01-07 2014-01-14 82 5
24 2014-01-11 2014-01-05 2014-01-12 61 68
25 2014-01-11 2014-01-06 2014-01-11 61 62
26 2014-01-11 2014-01-07 2014-01-14 61 5
27 2014-01-12 2014-01-05 2014-01-12 68 68
28 2014-01-12 2014-01-07 2014-01-14 68 5
29 2014-01-13 2014-01-07 2014-01-14 8 5
30 2014-01-14 2014-01-07 2014-01-14 94 5