python - 如何使用 read_sql_query(pandas 数据帧)指示多索引列

Question

我有一个包含以下列的表格:

| Date | ProductId | SubProductId | Value |

我正在尝试从该表中检索数据并将其放入 pandas DataFrame 中。

我希望 DataFrame 具有以下结构:

• 索引:日期
• 列:产品
• 子列:子产品

(products)    1              2               ...          
(subproducts) 1    2    3    1    2    3     ...     
date
2015-01-02   val val val   ...
2015-01-03   val val val   ...
2015-01-04   ...  
2015-01-05     
... 

我已经有了包含产品、子产品和日期的数据框。

我知道我需要使用 MultiIndex，这是我尝试过的:

query ="SELECT Date, ProductId, SubProductId, Value " \
    " FROM table "\
    " WHERE SubProductId in (1,2,3)"\
    " AND ProductId in (1,2,3)"\
    " AND Date BETWEEN '2015-01-02' AND '2015-01-08' "\
    " GROUP BY Date, ProductId, SubProductId, Value "\
    " ORDER BY Date, ProductId, SubProductId "  



df = pd.read_sql_query(query, conn, index_col=pd.MultiIndex.from_product([df_products['products'].tolist(), df_subproducts['subproducts'].tolist()])

但它不起作用，因为查询返回一个“值”向量(形状是值 x 1 的 nb)，而我需要一个矩阵(形状:不同日期的 nb x(子产品的 nb * nb prodcuts)) 在数据框中。

如何实现:

• 直接通过读取 sql 查询？
• 或者在数据库值插入后通过“trandofrming”数据框？

注意:我使用的是 Microsoft SQL Server。

Answer 1

您可以使用 IIUC unstack()方法:

df = pd.read_sql_query(query, conn, index_col=['Date','ProductID','SubProductId']) \
       .unstack(['ProductID','SubProductId'])

演示:

In [413]: df
Out[413]:
          Date  ProductID  SubProductId  Value
0   2015-01-02          1             1     11
1   2015-01-02          1             2     12
2   2015-01-02          1             3     13
3   2015-01-02          2             1     14
4   2015-01-02          2             2     15
5   2015-01-02          2             3     16
6   2015-01-03          1             1     17
7   2015-01-03          1             2     18
8   2015-01-03          1             3     19
9   2015-01-03          2             1     20
10  2015-01-03          2             2     21

In [414]: df.set_index(['Date','ProductID','SubProductId']).unstack(['ProductID','SubProductId'])
Out[414]:
             Value
ProductID        1                 2
SubProductId     1     2     3     1     2     3
Date
2015-01-02    11.0  12.0  13.0  14.0  15.0  16.0
2015-01-03    17.0  18.0  19.0  20.0  21.0   NaN