所以我有三个表(A,A_to_B,B),其中一个是用于许多关系的联接表...我需要创建一个数据框,其中包含B中每个id的标志数组(是否存在联接表中的事务),对于A中的每一行...很难解释,但是下面是示例表...
A_to_B A B
+------+------+ +------+------+ +------+------+
| id_a | id_b | | id | val | | id | val |
+------+------+ +------+------+ +------+------+
| 1 | 2 | | 1 | foo | | 1 | foob |
+------+------+ +------+------+ +------+------+
| 1 | 3 | | 2 | bar | | 2 | barb |
+------+------+ +------+------+ +------+------+
| 2 | 3 | | 3 | baz | | 3 | bazb |
+------+------+ +------+------+ +------+------+
最后我要得到一个像这样的数据框
1 2 3
_______________________
1 0 1 1 # id 1 from table A contains entries for ids 2/3 in B
2 0 0 1 # id 2 from table A contains entry for id 3 in B
3 0 0 0 # id 3 contains no transactions in the join table
希望这是有道理的。另外,请记住,这必须是一个高效 sqlite查询,因为我正在处理每个表中可能成千上万的行。
我将每个表加载到一个单独的数据框中,如下所示(但是,这绝不是解决方案的约束)。
import pandas as pd
import sqlite3
conn = sqlite3.connect('database.sqlite3')
cur = conn.cursor()
df_A = pd.read_sql_query('SELECT * FROM A',conn)
df_B = pd.read_sql_query('SELECT * FROM B',conn)
df_A_to_B = pd.read_sql_query('SELECT * FROM A_to_B',conn)