<p>sqlite表的格式不正确,因为参数值只反映
参数与SourceID的关联,但不与TargetID关联。在</p>
<p>最好有两张桌子:</p>
<p><strong>id参数</strong>:</p>
<pre><code>id parameter
21 23.46513
34 14.56319
46 7.99581
</code></pre>
<p><strong>数据集</strong>注意这里没有参数列:</p>
^{pr2}$
<p>然后,您可以使用联接在两者之间形成所需的关联
参数/SourceID:</p>
<pre><code> SELECT d.SourceID, d.TargetID, min(d.`Values`) as min_value
FROM dataset d
JOIN id_param as ip1
ON d.SourceID = ip1.id
JOIN id_param as ip2
ON d.TargetID = ip2.id
GROUP BY SourceID, TargetID
ORDER BY ip1.parameter ASC, ip2.parameter ASC
</code></pre>
<p>例如</p>
<pre><code>import io
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
text = '''\
| ID | SourceID | TargetID | Parameter | Values |
| 1 | 21 | 34 | 23.46513 | 0.12654 |
| 2 | 21 | 34 | 23.46513 | 0.25478 |
| 3 | 21 | 46 | 23.46513 | 0.43564 |
| 4 | 21 | 46 | 23.46513 | 1.02487 |
| 5 | 34 | 21 | 14.56319 | 0.01476 |
| 6 | 34 | 21 | 14.56319 | 0.87265 |
| 7 | 34 | 46 | 14.56319 | 0.46478 |
| 8 | 34 | 46 | 14.56319 | 0.13665 |
| 9 | 46 | 21 | 7.99581 | 0.04189 |
| 10 | 46 | 21 | 7.99581 | 0.91754 |
| 11 | 46 | 34 | 7.99581 | 0.73688 |
| 12 | 46 | 34 | 7.99581 | 0.24299 |'''
def make_table(filename):
# make sqlite table
with sqlite3.connect(filename) as con:
df = pd.read_table(io.BytesIO(text), sep=r'\s*[|]\s*').iloc[:, 1:-1]
df.to_sql('dataset', con=con, if_exists='replace')
filename = '/tmp/data.sqlite'
make_table(filename)
with sqlite3.connect(filename) as con:
con.execute('DROP TABLE id_param')
sql = '''CREATE TABLE id_param
(id INTEGER PRIMARY KEY, parameter INTEGER)'''
con.execute(sql)
sql = '''INSERT INTO id_param
SELECT SourceID, Parameter
FROM dataset
GROUP BY SourceID'''
con.execute(sql)
sql = '''
SELECT d.SourceID, d.TargetID, min(d.`Values`) as min_value
FROM dataset d
JOIN id_param as ip1
ON d.SourceID = ip1.id
JOIN id_param as ip2
ON d.TargetID = ip2.id
GROUP BY SourceID, TargetID
ORDER BY ip1.parameter ASC, ip2.parameter ASC
'''
df = pd.read_sql(sql, con)
print(df)
</code></pre>
<p>收益率</p>
<pre><code> SourceID TargetID min_value
0 46 34 0.24299
1 46 21 0.04189
2 34 46 0.13665
3 34 21 0.01476
4 21 46 0.43564
5 21 34 0.12654
</code></pre>