从SQLite数据库中检索和绘制有序的二维热图数据

Question

本周大部分时间我都在努力解决这个问题，我想我最适合在这里寻求帮助。我在SQLite数据库中存储了2D数组数据，我想获取它并将其可视化。对于如何获取和绘制数据有许多限制，但我不确定如何处理。在

因此，我有一个包含以下内容的表的数据库：

| 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 |

此数据集的一些特性需要注意：

• ID是密钥，并且是唯一的
• SourceID和{}值来自同一个集合。在本例中，它们都包含{21，34，46}。SourceID列构成热图的x轴数据，TargetID列构成y轴数据。在
• Values列包含必须在热图中绘制的数据。在
• Parameter列是我需要用来对数据排序的列，因为我需要创建一个有序的热图。在本例中，Parameter基于与每个SourceID和TargetID关联的值。在

下表显示了每个SourceId/TargetId与Parameter值的关联：

^{pr2}$

基于我提出的另一个question，我知道我可以使用如下SQL查询将此表简化为（SourceId，TargetId）的唯一组合，基于Values的最小值：

SELECT SourceID, TargetID, min(Values)
  FROM dataset
  GROUP BY SourceID, TargetID;

得出以下简化表：

| ID | SourceID | TargetID | SourceSort | Values  |
| 1  |    21    |    34    |  23.46513  | 0.12654 |
| 3  |    21    |    46    |  23.46513  | 0.43564 |
| 5  |    34    |    21    |  14.56319  | 0.01476 |
| 8  |    34    |    46    |  14.56319  | 0.13665 |
| 9  |    46    |    21    |  7.99581   | 0.04189 |
| 12 |    46    |    34    |  7.99581   | 0.24299 |

我正在努力解决的一点是，我现在需要根据Parameter中的值对这个表进行排序。如果我将ORDER BY Parameter添加到SQL查询中，它将成功地对SourceID列排序，但是对于具有相同SourceID的行，TargetID列不会根据此排序。简而言之，我想得到下表：

| ID | SourceID | TargetID | Values  |
| 12 |    46    |    34    | 0.24299 |
| 9  |    46    |    21    | 0.04189 |    
| 8  |    34    |    46    | 0.13665 |        
| 5  |    34    |    21    | 0.01476 |
| 3  |    21    |    46    | 0.43564 |
| 1  |    21    |    34    | 0.12654 |

这个排序的基础是什么的一个例子：首先使用Parameter值根据SourceID排序。随后，TargetID根据Parameter排序，对于SourceID值相等的行，例如，第1行（ID=8）在第2行（ID=5）之前排序，因为TargetID = 46应该根据相关的Parameter值在TargetID = 21之前排序。在

最后，使用此表，我需要构建一个热图：

(TargetID ordered by `Parameter ASC`)
 ^
 |
 | 
----------------------------------
21 | 0.04189 | 0.01476 |    0    |
----------------------------------
34 | 0.24299 |    0    | 0.12654 |
----------------------------------
46 |    0    | 0.13665 | 0.43564 |
----------------------------------
   |    46   |    34   |    21  --> (SourceID ordered by `Parameter ASC`)

为了实现这一点，我使用PythonPandas库，并使用read_sql()函数执行SQL查询。我发现我可以使用matplotlib的pcolor()函数来绘制热图。我想用colormap来绘制2D数组中的值，并且我希望数据集的顺序与上面完全相同。最后，我想把SourceID和TargetID值绘制为记号标签。在

这看起来应该是可行的，但是到目前为止，我只能够生成热图，而没有按照Parameter排序。我不知道我是否最适合直接在SQL语句中解决排序位，还是应该只获取数据然后对read_sql()返回的DataFrame进行排序。在

无论如何，我将非常感谢您的意见！如果有什么不清楚的地方，请告诉我，我会尽力澄清的。在

谢谢！在

Answer 1

将sqlite表转换为Matplotlib的pcolor绘制热图所需的格式的最简单方法（我明白了）是使用Pandas DataFrame的pivot方法。由于这个pivot将对列和行进行重新排序，因此不需要对SQL查询生成的顺序大惊小怪。相反，在熊猫身上更容易确定顺序：

import io
import sqlite3
import numpy as np
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:
    sql = '''
        SELECT SourceID, TargetID, min(`Values`) as min_value
          FROM dataset 
          GROUP BY SourceID, TargetID
    '''
    df = pd.read_sql(sql, con)
    table = df.pivot(index='SourceID', columns='TargetID', values='min_value')

    sql = 'SELECT DISTINCT SourceID FROM dataset ORDER BY Parameter ASC'
    order = pd.read_sql(sql, con)['SourceID']
    table = table.reindex(index=order, columns=order)

    fig, ax = plt.subplots()
    ax.pcolor(table.values, cmap=plt.get_cmap('jet'), 
              vmin=df['min_value'].min(), vmax=df['min_value'].max())
    ax.set_xticks(np.arange(table.shape[1] + 1)+0.5, minor=False)
    ax.set_xticklabels(table.columns, minor=False)
    ax.set_yticks(np.arange(table.shape[0] + 1)+0.5, minor=False)
    ax.set_yticklabels(table.index, minor=False)
    ax.set_xlim(0, table.shape[1])
    ax.set_ylim(0, table.shape[0])
    plt.show()

收益率

Answer 2

sqlite表的格式不正确，因为参数值只反映 参数与SourceID的关联，但不与TargetID关联。在

最好有两张桌子：

id参数：

id  parameter
21   23.46513
34   14.56319
46    7.99581

数据集注意这里没有参数列：

^{pr2}$

然后，您可以使用联接在两者之间形成所需的关联 参数/SourceID:

    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

例如

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)

收益率

   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