Pandas 查询引擎¶

本指南将展示如何使用 PandasQueryEngine：通过大型语言模型（LLM）将自然语言转换为 Pandas Python 代码。

PandasQueryEngine 的输入是一个 Pandas 数据框，输出则是查询结果。LLM 会推断需要执行的数据框操作以获取结果。

警告： 该工具允许 LLM 访问 eval 函数。 在运行此工具的机器上可能存在任意代码执行风险。 虽然会对代码进行一定程度的过滤，但除非具备严格的沙盒环境或虚拟机保护，否则不建议在生产环境中使用此工具。

如果您在 Colab 上打开此 Notebook，可能需要安装 LlamaIndex 🦙。

In [ ]:

!pip install llama-index llama-index-experimental

!pip install llama-index llama-index-experimental

In [ ]:

import logging
import sys
from IPython.display import Markdown, display

import pandas as pd
from llama_index.experimental.query_engine import PandasQueryEngine


logging.basicConfig(stream=sys.stdout, level=logging.INFO)
logging.getLogger().addHandler(logging.StreamHandler(stream=sys.stdout))

import logging import sys from IPython.display import Markdown, display import pandas as pd from llama_index.experimental.query_engine import PandasQueryEngine logging.basicConfig(stream=sys.stdout, level=logging.INFO) logging.getLogger().addHandler(logging.StreamHandler(stream=sys.stdout))

从示例 DataFrame 开始¶

这里我们将加载一个包含城市与人口对应关系的简易数据框，并对其运行 PandasQueryEngine。

通过设置 verbose=True 参数，可以查看中间生成的指令过程。

In [ ]:

# Test on some sample data
df = pd.DataFrame(
    {
        "city": ["Toronto", "Tokyo", "Berlin"],
        "population": [2930000, 13960000, 3645000],
    }
)

# Test on some sample data df = pd.DataFrame( { "city": ["Toronto", "Tokyo", "Berlin"], "population": [2930000, 13960000, 3645000], } )

In [ ]:

query_engine = PandasQueryEngine(df=df, verbose=True)

query_engine = PandasQueryEngine(df=df, verbose=True)

In [ ]:

response = query_engine.query(
    "What is the city with the highest population?",
)

response = query_engine.query( "What is the city with the highest population?", )

INFO:httpx:HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
> Pandas Instructions:
```
df['city'][df['population'].idxmax()]
```
> Pandas Output: Tokyo

In [ ]:

display(Markdown(f"<b>{response}</b>"))

display(Markdown(f"{response}"))

Tokyo

In [ ]:

# get pandas python instructions
print(response.metadata["pandas_instruction_str"])

# get pandas python instructions print(response.metadata["pandas_instruction_str"])

df['city'][df['population'].idxmax()]

我们还可以采取使用大语言模型（LLM）来合成响应的步骤。

In [ ]:

query_engine = PandasQueryEngine(df=df, verbose=True, synthesize_response=True)
response = query_engine.query(
    "What is the city with the highest population? Give both the city and population",
)
print(str(response))

query_engine = PandasQueryEngine(df=df, verbose=True, synthesize_response=True) response = query_engine.query( "What is the city with the highest population? Give both the city and population", ) print(str(response))

INFO:httpx:HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
> Pandas Instructions:
```
df.loc[df['population'].idxmax()]
```
> Pandas Output: city             Tokyo
population    13960000
Name: 1, dtype: object
INFO:httpx:HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
The city with the highest population is Tokyo, with a population of 13,960,000.

泰坦尼克号数据集分析¶

泰坦尼克号数据集是机器学习入门阶段最受欢迎的表格数据集之一
数据来源：https://www.kaggle.com/c/titanic

下载数据¶

In [ ]:

!wget 'https://raw.githubusercontent.com/jerryjliu/llama_index/main/docs/docs/examples/data/csv/titanic_train.csv' -O 'titanic_train.csv'

!wget 'https://raw.githubusercontent.com/jerryjliu/llama_index/main/docs/docs/examples/data/csv/titanic_train.csv' -O 'titanic_train.csv'

--2024-01-13 17:45:15--  https://raw.githubusercontent.com/jerryjliu/llama_index/main/docs/docs/examples/data/csv/titanic_train.csv
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 2606:50c0:8003::154, 2606:50c0:8002::154, 2606:50c0:8001::154, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|2606:50c0:8003::154|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 57726 (56K) [text/plain]
Saving to: ‘titanic_train.csv’

titanic_train.csv   100%[===================>]  56.37K  --.-KB/s    in 0.009s  

2024-01-13 17:45:15 (6.45 MB/s) - ‘titanic_train.csv’ saved [57726/57726]

In [ ]:

df = pd.read_csv("./titanic_train.csv")

df = pd.read_csv("./titanic_train.csv")

In [ ]:

query_engine = PandasQueryEngine(df=df, verbose=True)

query_engine = PandasQueryEngine(df=df, verbose=True)

In [ ]:

response = query_engine.query(
    "What is the correlation between survival and age?",
)

response = query_engine.query( "What is the correlation between survival and age?", )

INFO:httpx:HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
HTTP Request: POST https://api.openai.com/v1/chat/completions "HTTP/1.1 200 OK"
> Pandas Instructions:
```
df['survived'].corr(df['age'])
```
> Pandas Output: -0.07722109457217755

In [ ]:

display(Markdown(f"<b>{response}</b>"))

display(Markdown(f"{response}"))

-0.07722109457217755

In [ ]:

# get pandas python instructions
print(response.metadata["pandas_instruction_str"])

# get pandas python instructions print(response.metadata["pandas_instruction_str"])

df['survived'].corr(df['age'])

额外步骤¶

分析/修改提示词¶

让我们来看看这些提示词！

In [ ]:

from llama_index.core import PromptTemplate

from llama_index.core import PromptTemplate

In [ ]:

query_engine = PandasQueryEngine(df=df, verbose=True)
prompts = query_engine.get_prompts()
print(prompts["pandas_prompt"].template)

query_engine = PandasQueryEngine(df=df, verbose=True) prompts = query_engine.get_prompts() print(prompts["pandas_prompt"].template)

You are working with a pandas dataframe in Python.
The name of the dataframe is `df`.
This is the result of `print(df.head())`:
{df_str}

Follow these instructions:
{instruction_str}
Query: {query_str}

Expression:

In [ ]:

print(prompts["response_synthesis_prompt"].template)

print(prompts["response_synthesis_prompt"].template)

Given an input question, synthesize a response from the query results.
Query: {query_str}

Pandas Instructions (optional):
{pandas_instructions}

Pandas Output: {pandas_output}

Response: 

您可以更新提示词：

In [ ]:

new_prompt = PromptTemplate(
    """\
You are working with a pandas dataframe in Python.
The name of the dataframe is `df`.
This is the result of `print(df.head())`:
{df_str}

Follow these instructions:
{instruction_str}
Query: {query_str}

Expression: """
)

query_engine.update_prompts({"pandas_prompt": new_prompt})

new_prompt = PromptTemplate( """\ You are working with a pandas dataframe in Python. The name of the dataframe is `df`. This is the result of `print(df.head())`: {df_str} Follow these instructions: {instruction_str} Query: {query_str} Expression: """ ) query_engine.update_prompts({"pandas_prompt": new_prompt})

这是初始化时可通过传入 instruction_str 自定义的指令字符串

In [ ]:

instruction_str = """\
1. Convert the query to executable Python code using Pandas.
2. The final line of code should be a Python expression that can be called with the `eval()` function.
3. The code should represent a solution to the query.
4. PRINT ONLY THE EXPRESSION.
5. Do not quote the expression.
"""

instruction_str = """\ 1. Convert the query to executable Python code using Pandas. 2. The final line of code should be a Python expression that can be called with the `eval()` function. 3. The code should represent a solution to the query. 4. PRINT ONLY THE EXPRESSION. 5. Do not quote the expression. """

使用查询管道语法实现查询引擎¶

若您希望学习如何利用上述查询管道语法和提示组件构建自己的 Pandas 查询引擎，请参阅以下教程：

使用查询管道设置 Pandas DataFrame 查询引擎