CSV

%pip install -qU langchain langchain-openai langchain-community langchain-experimental pandas

import getpass
import os
 
os.environ["OPENAI_API_KEY"] = getpass.getpass()
 
# Using LangSmith is recommended but not required. Uncomment below lines to use.
# os.environ["LANGCHAIN_TRACING_V2"] = "true"
# os.environ["LANGCHAIN_API_KEY"] = getpass.getpass()

!wget https://web.stanford.edu/class/archive/cs/cs109/cs109.1166/stuff/titanic.csv -O titanic.csv

import pandas as pd
 
df = pd.read_csv("titanic.csv")
print(df.shape)
print(df.columns.tolist())

(887, 8)
['Survived', 'Pclass', 'Name', 'Sex', 'Age', 'Siblings/Spouses Aboard', 'Parents/Children Aboard', 'Fare']

from langchain_community.utilities import SQLDatabase
from sqlalchemy import create_engine
 
engine = create_engine("sqlite:///titanic.db")
df.to_sql("titanic", engine, index=False)

887

db = SQLDatabase(engine=engine)
print(db.dialect)
print(db.get_usable_table_names())
db.run("SELECT * FROM titanic WHERE Age < 2;")

sqlite
['titanic']





"[(1, 2, 'Master. Alden Gates Caldwell', 'male', 0.83, 0, 2, 29.0), (0, 3, 'Master. Eino Viljami Panula', 'male', 1.0, 4, 1, 39.6875), (1, 3, 'Miss. Eleanor Ileen Johnson', 'female', 1.0, 1, 1, 11.1333), (1, 2, 'Master. Richard F Becker', 'male', 1.0, 2, 1, 39.0), (1, 1, 'Master. Hudson Trevor Allison', 'male', 0.92, 1, 2, 151.55), (1, 3, 'Miss. Maria Nakid', 'female', 1.0, 0, 2, 15.7417), (0, 3, 'Master. Sidney Leonard Goodwin', 'male', 1.0, 5, 2, 46.9), (1, 3, 'Miss. Helene Barbara Baclini', 'female', 0.75, 2, 1, 19.2583), (1, 3, 'Miss. Eugenie Baclini', 'female', 0.75, 2, 1, 19.2583), (1, 2, 'Master. Viljo Hamalainen', 'male', 0.67, 1, 1, 14.5), (1, 3, 'Master. Bertram Vere Dean', 'male', 1.0, 1, 2, 20.575), (1, 3, 'Master. Assad Alexander Thomas', 'male', 0.42, 0, 1, 8.5167), (1, 2, 'Master. Andre Mallet', 'male', 1.0, 0, 2, 37.0042), (1, 2, 'Master. George Sibley Richards', 'male', 0.83, 1, 1, 18.75)]"

from langchain_community.agent_toolkits import create_sql_agent
from langchain_openai import ChatOpenAI
 
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)
agent_executor = create_sql_agent(llm, db=db, agent_type="openai-tools", verbose=True)

agent_executor.invoke({"input": "what's the average age of survivors"})

> 进入新的AgentExecutor链...

Invoking: `sql_db_list_tables` with `{}`


titanic
Invoking: `sql_db_schema` with `{'table_names': 'titanic'}`



CREATE TABLE titanic (
	"Survived" BIGINT, 
	"Pclass" BIGINT, 
	"Name" TEXT, 
	"Sex" TEXT, 
	"Age" FLOAT, 
	"Siblings/Spouses Aboard" BIGINT, 
	"Parents/Children Aboard" BIGINT, 
	"Fare" FLOAT
)

/*
3 rows from titanic table:
Survived	Pclass	Name	Sex	Age	Siblings/Spouses Aboard	Parents/Children Aboard	Fare
0	3	Mr. Owen Harris Braund	male	22.0	1	0	7.25
1	1	Mrs. John Bradley (Florence Briggs Thayer) Cumings	female	38.0	1	0	71.2833
1	3	Miss. Laina Heikkinen	female	26.0	0	0	7.925
*/
Invoking: `sql_db_query` with `{'query': 'SELECT AVG(Age) AS AverageAge FROM titanic WHERE Survived = 1'}`
responded: 要找到幸存者的平均年龄，我将查询“titanic”表，并计算“Survived”等于1的行的“Age”列的平均值。

 以下是SQL查询语句：

```sql
SELECT AVG(Age) AS AverageAge
FROM titanic
WHERE Survived = 1
```

执行此查询将给出幸存者的平均年龄。

[(28.408391812865496,)]幸存者的平均年龄约为28.41岁。

> 链结束。





{'input': "what's the average age of survivors",
 'output': '幸存者的平均年龄约为28.41岁。'}

ai_msg = llm.invoke(
    "我有一个名为'df'的pandas DataFrame，具有'Age'和'Fare'列。编写代码计算两列之间的相关性。只返回Python代码的Markdown片段，不返回其他任何内容。"
)
print(ai_msg.content)

```python
correlation = df['Age'].corr(df['Fare'])
correlation
```

import pandas as pd
from langchain_core.prompts import ChatPromptTemplate
from langchain_experimental.tools import PythonAstREPLTool
 
df = pd.read_csv("titanic.csv")
tool = PythonAstREPLTool(locals={"df": df})
tool.invoke("df['Fare'].mean()")

32.30542018038331

llm_with_tools = llm.bind_tools([tool], tool_choice=tool.name)
llm_with_tools.invoke(
    "我有一个名为'df'的数据帧，想要知道'Age'和'Fare'列之间的相关性"
)

AIMessage(content='', additional_kwargs={'tool_calls': [{'id': 'call_6TZsNaCqOcbP7lqWudosQTd6', 'function': {'arguments': '{\n  "query": "df[[\'Age\', \'Fare\']].corr()"\n}', 'name': 'python_repl_ast'}, 'type': 'function'}]})

from langchain.output_parsers.openai_tools import JsonOutputKeyToolsParser
 
parser = JsonOutputKeyToolsParser(tool.name, first_tool_only=True)
(llm_with_tools | parser).invoke(
    "我有一个名为'df'的数据帧，想要知道'Age'和'Fare'列之间的相关性"
)

{'query': "df[['Age', 'Fare']].corr()"}

system = f"""您可以访问一个名为'df'的pandas数据帧。\
这是'df.head().to_markdown()'的输出：
 

给定一个用户问题，编写回答问题的Python代码。\
只返回有效的Python代码，不返回其他任何内容。\
不要假设你可以访问除内置的Python库和pandas之外的任何库。"""
prompt = ChatPromptTemplate.from_messages([("system", system), ("human", "{question}")])
code_chain = prompt | llm_with_tools | parser
code_chain.invoke({"question": "年龄和费用之间的相关性是多少"})

{'query': "df[['Age', 'Fare']].corr()"}

chain = prompt | llm_with_tools | parser | tool  # noqa
chain.invoke({"question": "年龄和费用之间的相关性是多少"})

0.11232863699941621

from operator import itemgetter
 
from langchain_core.messages import ToolMessage
from langchain_core.output_parsers import StrOutputParser
from langchain_core.prompts import MessagesPlaceholder
from langchain_core.runnables import RunnablePassthrough
 
system = f"""您可以访问一个名为'df'的pandas数据帧。\
这是'df.head().to_markdown()'的输出：
 

给定一个用户问题，编写回答问题的Python代码。\
不要假设你可以访问除内置的Python库和pandas之外的任何库。\
一旦您有足够的信息来回答问题，请直接回答问题。"""
prompt = ChatPromptTemplate.from_messages(
    [
        (
            "system",
            system,
        ),
        ("human", "{question}"),
>>>>>>> fix typo
    ]
)
code_chain = prompt | llm_with_tools | parser | tool  # noqa
chain = code_chain | MessagesPlaceholder()
conversation = [("human", "What's the correlation between age and fare?")]
for index, (role, message) in enumerate(conversation):
    if index == len(conversation) - 1:
        output = chain.invoke({"question": message})
    else:
        output = RunnablePassthrough(role=role, content=message)
    chain = chain | output | StrOutputParser() | itemgetter("output")
    print(f"{role}: {message}")
    print("Bot:", chain.invoke({}))
    print()

# This MessagesPlaceholder allows us to optionally append an arbitrary number of messages
# at the end of the prompt using the 'chat_history' arg.
MessagesPlaceholder("chat_history", optional=True),
]
)
 
 
def _get_chat_history(x: dict) -> list:
"""Parse the chain output up to this point into a list of chat history messages to insert in the prompt."""
ai_msg = x["ai_msg"]
tool_call_id = x["ai_msg"].additional_kwargs["tool_calls"][0]["id"]
tool_msg = ToolMessage(tool_call_id=tool_call_id, content=str(x["tool_output"]))
return [ai_msg, tool_msg]
 
 
chain = (
RunnablePassthrough.assign(ai_msg=prompt | llm_with_tools)
.assign(tool_output=itemgetter("ai_msg") | parser | tool)
.assign(chat_history=_get_chat_history)
.assign(response=prompt | llm | StrOutputParser())
.pick(["tool_output", "response"])
)

chain.invoke({"question": "What's the correlation between age and fare"})

{'tool_output': 0.11232863699941621,
'response': 'The correlation between age and fare is approximately 0.112.'}

from langchain_experimental.agents import create_pandas_dataframe_agent
 
agent = create_pandas_dataframe_agent(llm, df, agent_type="openai-tools", verbose=True)
agent.invoke(
{
"input": "What's the correlation between age and fare? is that greater than the correlation between fare and survival?"
}
)

df_1 = df[["Age", "Fare"]]
df_2 = df[["Fare", "Survived"]]
 
tool = PythonAstREPLTool(locals={"df_1": df_1, "df_2": df_2})
llm_with_tool = llm.bind_tools(tools=[tool], tool_choice=tool.name)
df_template = "```python\n{df_name}.head().to_markdown()\n>>> {df_head}\n```"
df_context = "\n\n".join(
df_template.format(df_head=_df.head().to_markdown(), df_name=df_name)
for _df, df_name in [(df_1, "df_1"), (df_2, "df_2")]
)
 
system = f"You have access to a number of pandas dataframes. \
Here is a sample of rows from each dataframe and the python code that was used to generate the sample:\n\n{df_context}\n\nGiven a user question about the dataframes, write the Python code to answer it. \
Don't assume you have access to any libraries other than built-in Python ones and pandas. \
Make sure to refer only to the variables mentioned above."
prompt = ChatPromptTemplate.from_messages([("system", system), ("human", "{question}")])
 
chain = prompt | llm_with_tool | parser | tool
chain.invoke(
{
"question": "return the difference in the correlation between age and fare and the correlation between fare and survival"
}
)