Pandas查询引擎¶
本指南将向您展示如何使用我们的PandasQueryEngine：通过LLM将自然语言转换为Pandas Python代码。
PandasQueryEngine的输入是一个Pandas数据框，输出是一个响应结果。LLM会推断需要执行的数据框操作以获取结果。
警告：该工具允许LLM访问eval函数。在运行此工具的机器上可能执行任意代码。虽然对代码进行了一定程度的过滤，但除非有严格的沙盒或虚拟机环境，否则不建议在生产环境中使用此工具。
如果你在Colab上打开这个Notebook，你可能需要安装LlamaIndex 🦙。
In [ ]:
Copied!
!pip install llama-index llama-index-experimental
!pip install llama-index llama-index-experimental
In [ ]:
Copied!
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))
In [ ]:
Copied!
# Test on some sample data
df = pd.DataFrame(
{
"city": ["Toronto", "Tokyo", "Berlin"],
"population": [2930000, 13960000, 3645000],
}
)
# 测试一些样本数据
df = pd.DataFrame(
{
"city": ["Toronto", "Tokyo", "Berlin"],
"population": [2930000, 13960000, 3645000],
}
)
In [ ]:
Copied!
query_engine = PandasQueryEngine(df=df, verbose=True)
query_engine = PandasQueryEngine(df=df, verbose=True)
In [ ]:
Copied!
response = query_engine.query(
"What is the city with the highest population?",
)
response = query_engine.query(
"人口最多的城市是哪个？",
)
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 [ ]:
Copied!
display(Markdown(f"<b>{response}</b>"))
display(Markdown(f"{response}"))
东京
In [ ]:
Copied!
# get pandas python instructions
print(response.metadata["pandas_instruction_str"])
# 获取pandas python指令
print(response.metadata["pandas_instruction_str"])
df['city'][df['population'].idxmax()]
我们还可以利用LLM来合成响应。
In [ ]:
Copied!
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(
"人口最多的城市是哪个？请同时给出城市名称和人口数量",
)
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 [ ]:
Copied!
!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 [ ]:
Copied!
df = pd.read_csv("./titanic_train.csv")
df = pd.read_csv("./titanic_train.csv")
In [ ]:
Copied!
query_engine = PandasQueryEngine(df=df, verbose=True)
query_engine = PandasQueryEngine(df=df, verbose=True)
In [ ]:
Copied!
response = query_engine.query(
"What is the correlation between survival and age?",
)
response = query_engine.query(
"生存率与年龄之间的相关性是什么？",
)
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 [ ]:
Copied!
display(Markdown(f"<b>{response}</b>"))
display(Markdown(f"{response}"))
-0.07722109457217755
In [ ]:
Copied!
# get pandas python instructions
print(response.metadata["pandas_instruction_str"])
# 获取pandas python指令
print(response.metadata["pandas_instruction_str"])
df['survived'].corr(df['age'])
In [ ]:
Copied!
from llama_index.core import PromptTemplate
从llamaindex.core导入PromptTemplate
In [ ]:
Copied!
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 [ ]:
Copied!
print(prompts["response_synthesis_prompt"].template)
打印(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 [ ]:
Copied!
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(
"""\
您正在使用Python中的pandas数据框。
该数据框的名称为`df`。
这是`print(df.head())`的结果：
{df_str}
请遵循以下指令：
{instruction_str}
查询：{query_str}
表达式："""
)
query_engine.update_prompts({"pandas_prompt": new_prompt})
这是指令字符串（您可以通过在初始化时传入instruction_str来自定义）
In [ ]:
Copied!
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. 使用Pandas将查询转换为可执行的Python代码。
2. 代码的最后一行应该是一个可以通过`eval()`函数调用的Python表达式。
3. 该代码应代表查询的解决方案。
4. 仅打印表达式。
5. 不要对表达式加引号。
"""
