将 sql select 解压到 Pandas 数据框中 [英] unpacking a sql select into a pandas dataframe

问题描述

假设我有一个大致像这样的选择:

Suppose I have a select roughly like this:

select instrument, price, date from my_prices;

如何将返回的价格解包到单个数据框中，其中包含每个工具的系列并按日期编制索引?

How can I unpack the prices returned into a single dataframe with a series for each instrument and indexed on date?

要明确:我正在寻找:

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: ...
Data columns (total 2 columns):
inst_1    ...
inst_2    ...
dtypes: float64(1), object(1) 

我不是在寻找:

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: ...
Data columns (total 2 columns):
instrument    ...
price         ...
dtypes: float64(1), object(1)

...这很容易;-)

推荐答案

更新:最近的pandas有以下功能:read_sql_table和read_sql_query.

Update: recent pandas have the following functions: read_sql_table and read_sql_query.

首先创建一个数据库引擎(连接也可以在这里工作):

First create a db engine (a connection can also work here):

from sqlalchemy import create_engine
# see sqlalchemy docs for how to write this url for your database type:
engine = create_engine('mysql://scott:tiger@localhost/foo')

请参阅sqlalchemy 数据库网址.

table_name = 'my_prices'
df = pd.read_sql_table(table_name, engine)

pandas_read_sql_query

df = pd.read_sql_query("SELECT instrument, price, date FROM my_prices;", engine)

<小时>

旧答案引用了已弃用的 read_frame(有关该答案，请参阅此问题的版本历史).

先阅读通常是有意义的，然后然后根据您的要求执行转换(因为这些在 Pandas 中通常是高效和可读的).在您的示例中，您可以 pivot 结果:

It's often makes sense to read first, and then perform transformations to your requirements (as these are usually efficient and readable in pandas). In your example, you can pivot the result:

df.reset_index().pivot('date', 'instrument', 'price')

注意:您可能会错过 reset_index 如果没有在 read_frame 中指定 index_col.

Note: You could miss out the reset_index you don't specify an index_col in the read_frame.

这篇关于将 sql select 解压到 Pandas 数据框中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！