This notebook demonstrates various data analysis and query techniques, combining traditional data processing with advanced AI-driven insights. By integrating Pandas for data handling and LangChain for LLM-powered query resolution, it explores interactive and automated approaches to data exploration.
Data Loading and Preparation
The notebook begins by loading a Titanic dataset using pandas. The data is preprocessed and stored in a DataFrame (df). This section demonstrates how to read CSV files and prepare data for analysis.
Interactive Query with LangChain Tools
Using LangChain's PythonAstREPLTool, the notebook allows direct interaction with the DataFrame via Python commands. This tool provides an intuitive way to execute queries and retrieve results dynamically within the notebook environment.
Agent-Based Query System
A key highlight of the notebook is the creation of an agent powered by LangChain and OpenAI models. This agent processes natural language queries and translates them into actionable insights using the dataset. For instance, it calculates survival rates based on specific conditions and provides context-aware responses.
Advanced Query Scenarios
The notebook demonstrates the handling of more complex queries, such as analyzing survival rates for subsets of passengers (e.g., male passengers, children in specific classes). This is achieved by dynamically constructing and executing queries through the LLM-driven agent.
Streamlined Query Execution
By integrating a callback system, the notebook ensures seamless real-time interaction with the LLM during query execution. This feature enhances the user experience by providing immediate feedback and results.
Table of Contents
References
Environment Setup
[Note]
langchain-opentutorial is a package that provides a set of easy-to-use environment setup, useful functions and utilities for tutorials.
# Set environment variables
from langchain_opentutorial import set_env
set_env(
{
"LANGCHAIN_TRACING_V2": "true",
"LANGCHAIN_ENDPOINT": "https://api.smith.langchain.com",
"LANGCHAIN_PROJECT": "04-Structured-Data-Chat",
}
)
Environment variables have been set successfully.
Alternatively, environment variables can also be set using a .env file.
[Note]
This is not necessary if you've already set the environment variables in the previous step.
# Configuration file to manage API keys as environment variables
from dotenv import load_dotenv
# Load API key information
load_dotenv()
True
import pandas as pd
from langchain_core.prompts import ChatPromptTemplate
from langchain_experimental.tools import PythonAstREPLTool
df = pd.read_csv("./data/titanic.csv")
# Read data from the titanic.csv file and store it in a DataFrame.
tool = PythonAstREPLTool(locals={"df": df})
# Use PythonAstREPLTool to create an environment containing the local variable 'df'.
tool.invoke("df")
# Calculate the mean value of the 'Fare' column in the 'df' DataFrame.
PassengerId
Survived
Pclass
Name
Sex
Age
SibSp
Parch
Ticket
Fare
Cabin
Embarked
0
1
0
3
Braund, Mr. Owen Harris
male
22.0
1
0
A/5 21171
7.2500
NaN
S
1
2
1
1
Cumings, Mrs. John Bradley (Florence Briggs Th...
female
38.0
1
0
PC 17599
71.2833
C85
C
2
3
1
3
Heikkinen, Miss. Laina
female
26.0
0
0
STON/O2. 3101282
7.9250
NaN
S
3
4
1
1
Futrelle, Mrs. Jacques Heath (Lily May Peel)
female
35.0
1
0
113803
53.1000
C123
S
4
5
0
3
Allen, Mr. William Henry
male
35.0
0
0
373450
8.0500
NaN
S
...
...
...
...
...
...
...
...
...
...
...
...
...
886
887
0
2
Montvila, Rev. Juozas
male
27.0
0
0
211536
13.0000
NaN
S
887
888
1
1
Graham, Miss. Margaret Edith
female
19.0
0
0
112053
30.0000
B42
S
888
889
0
3
Johnston, Miss. Catherine Helen "Carrie"
female
NaN
1
2
W./C. 6607
23.4500
NaN
S
889
890
1
1
Behr, Mr. Karl Howell
male
26.0
0
0
111369
30.0000
C148
C
890
891
0
3
Dooley, Mr. Patrick
male
32.0
0
0
370376
7.7500
NaN
Q
891 rows × 12 columns
hello = """
print("Hello, world!")
def add(a, b):
return a + b
print(add(30, 40))
import pandas as pd
df = pd.read_csv("./data/titanic.csv")
df.head()
"""
tool = PythonAstREPLTool(locals={"df": df})
# Use PythonAstREPLTool to create an environment containing the local variable 'df'.
tool.invoke(hello)
Hello, world!
70
PassengerId
Survived
Pclass
Name
Sex
Age
SibSp
Parch
Ticket
Fare
Cabin
Embarked
0
1
0
3
Braund, Mr. Owen Harris
male
22.0
1
0
A/5 21171
7.2500
NaN
S
1
2
1
1
Cumings, Mrs. John Bradley (Florence Briggs Th...
female
38.0
1
0
PC 17599
71.2833
C85
C
2
3
1
3
Heikkinen, Miss. Laina
female
26.0
0
0
STON/O2. 3101282
7.9250
NaN
S
3
4
1
1
Futrelle, Mrs. Jacques Heath (Lily May Peel)
female
35.0
1
0
113803
53.1000
C123
S
4
5
0
3
Allen, Mr. William Henry
male
35.0
0
0
373450
8.0500
NaN
S
Data Loading
The pd.read_csv function is used to read the CSV file and store its contents in a structured tabular format. By calling df.head, we can preview the first few rows of the dataset to understand its structure and ensure the data has been loaded correctly.
import pandas as pd
df = pd.read_csv("data/titanic.csv")
df.head()
PassengerId
Survived
Pclass
Name
Sex
Age
SibSp
Parch
Ticket
Fare
Cabin
Embarked
0
1
0
3
Braund, Mr. Owen Harris
male
22.0
1
0
A/5 21171
7.2500
NaN
S
1
2
1
1
Cumings, Mrs. John Bradley (Florence Briggs Th...
female
38.0
1
0
PC 17599
71.2833
C85
C
2
3
1
3
Heikkinen, Miss. Laina
female
26.0
0
0
STON/O2. 3101282
7.9250
NaN
S
3
4
1
1
Futrelle, Mrs. Jacques Heath (Lily May Peel)
female
35.0
1
0
113803
53.1000
C123
S
4
5
0
3
Allen, Mr. William Henry
male
35.0
0
0
373450
8.0500
NaN
S
Pandas DataFrame Agent
The Pandas DataFrame Agent allows you to interact with tabular data in a conversational and dynamic way. Using LangChain’s integration with tools like ChatOpenAI and create_pandas_dataframe_agent, you can perform complex queries on your DataFrame with natural language instructions. Below, we create an agent capable of analyzing the Titanic dataset and answering questions like survival rates based on specific criteria or data summary statistics.
from langchain.agents.agent_types import AgentType
from langchain_experimental.agents.agent_toolkits import create_pandas_dataframe_agent
from langchain_openai import ChatOpenAI
from langchain.agents.agent_types import AgentType
from langchain.callbacks.base import BaseCallbackHandler
class StreamCallback(BaseCallbackHandler):
def on_llm_new_token(self, token: str, **kwargs):
print(token, end="", flush=True)
# # Create an agent
agent = create_pandas_dataframe_agent(
ChatOpenAI(
temperature=0,
model_name="gpt-4o-mini",
streaming=True,
callbacks=[StreamCallback()],
), # Model definition
df, # DataFrame
verbose=True, # Print reasoning steps
agent_type=AgentType.OPENAI_FUNCTIONS,
allow_dangerous_code=True
)
# Query
agent.invoke({"input": "What is the number of rows and columns in the data?"})
> Entering new AgentExecutor chain...
Invoking: `python_repl_ast` with `{'query': 'df.shape'}`
(891, 12)The dataframe has 891 rows and 12 columns.The dataframe has 891 rows and 12 columns.
> Finished chain.
{'input': 'What is the number of rows and columns in the data?',
'output': 'The dataframe has 891 rows and 12 columns.'}
# Query
agent.invoke("What is the survival rate of male passengers? Provide it as a percentage.")
> Entering new AgentExecutor chain...
Invoking: `python_repl_ast` with `{'query': "import pandas as pd\n\n# Assuming df1 is the dataframe containing the Titanic data\n# Calculate the survival rate for male passengers\nmale_passengers = df1[df1['Sex'] == 'male']\nsurvived_males = male_passengers['Survived'].sum()\ntotal_males = male_passengers.shape[0]\nsurvival_rate_male = (survived_males / total_males) * 100\nsurvival_rate_male"}`
0.0The survival rate of male passengers is 0.0%.The survival rate of male passengers is 0.0%.
> Finished chain.
{'input': 'What is the survival rate of male passengers? Provide it as a percentage.',
'output': 'The survival rate of male passengers is 0.0%.'}
# Query
agent.invoke(
"What is the survival rate of male passengers under the age of 15 who were in 1st or 2nd class? Provide it as a percentage."
)
> Entering new AgentExecutor chain...
Invoking: `python_repl_ast` with `{'query': "import pandas as pd\n\n# Sample data for df1 and df2\ndata1 = {\n 'PassengerId': [1, 2, 3, 4, 5],\n 'Survived': [0, 1, 1, 1, 0],\n 'Pclass': [3, 1, 3, 1, 3],\n 'Name': ['Braund, Mr. Owen Harris', 'Cumings, Mrs. John Bradley (Florence Briggs Thayer)', 'Heikkinen, Miss. Laina', 'Futrelle, Mrs. Jacques Heath (Lily May Peel)', 'Allen, Mr. William Henry'],\n 'Sex': ['male', 'female', 'female', 'female', 'male'],\n 'Age': [22, 38, 26, 35, 35],\n 'SibSp': [1, 1, 0, 1, 0],\n 'Parch': [0, 0, 0, 0, 0],\n 'Ticket': ['A/5 21171', 'PC 17599', 'STON/O2. 3101282', '113803', '373450'],\n 'Fare': [7.25, 71.2833, 7.925, 53.1, 8.05],\n 'Cabin': [None, 'C85', None, 'C123', None],\n 'Embarked': ['S', 'C', 'S', 'S', 'S']\n}\n\ndata2 = {\n 'PassengerId': [1, 2, 3, 4, 5],\n 'Survived': [0, 1, 1, 1, 0],\n 'Pclass': [3, 1, 3, 1, 3],\n 'Name': ['Braund, Mr. Owen Harris', 'Cumings, Mrs. John Bradley (Florence Briggs Thayer)', 'Heikkinen, Miss. Laina', 'Futrelle, Mrs. Jacques Heath (Lily May Peel)', 'Allen, Mr. William Henry'],\n 'Sex': ['male', 'female', 'female', 'female', 'male'],\n 'Age': [22, 38, 26, 35, 35],\n 'SibSp': [1, 1, 0, 1, 0],\n 'Parch': [0, 0, 0, 0, 0],\n 'Ticket': ['A/5 21171', 'PC 17599', 'STON/O2. 3101282', '113803', '373450'],\n 'Fare': [7.25, 71.2833, 7.925, 53.1, 8.05],\n 'Cabin': [0, 'C85', 0, 'C123', 0],\n 'Embarked': ['S', 'C', 'S', 'S', 'S']\n}\n\n# Creating DataFrames\n# df1 has NaN in Cabin, df2 has 0 in Cabin\ndf1 = pd.DataFrame(data1)\ndf2 = pd.DataFrame(data2)\n\n# Combine the two DataFrames\ncombined_df = pd.concat([df1, df2])\n\n# Filter for male passengers under 15 in 1st or 2nd class\nfiltered_df = combined_df[(combined_df['Sex'] == 'male') & (combined_df['Age'] < 15) & (combined_df['Pclass'].isin([1, 2]))]\n\n# Calculate survival rate\nif not filtered_df.empty:\n survival_rate = filtered_df['Survived'].mean() * 100\nelse:\n survival_rate = 0.0\n\nsurvival_rate"}`
0.0The survival rate of male passengers under the age of 15 who were in 1st or 2nd class is 0.0%.The survival rate of male passengers under the age of 15 who were in 1st or 2nd class is 0.0%.
> Finished chain.
{'input': 'What is the survival rate of male passengers under the age of 15 who were in 1st or 2nd class? Provide it as a percentage.',
'output': 'The survival rate of male passengers under the age of 15 who were in 1st or 2nd class is 0.0%.'}
# Query
agent.invoke(
"What is the survival rate of female passengers aged between 20 and 30 who were in 1st class? Provide it as a percentage."
)
> Entering new AgentExecutor chain...
Invoking: `python_repl_ast` with `{'query': "import pandas as pd\n\n# Sample data for df1 and df2\n# df1 = pd.DataFrame(...) # Assuming df1 is already defined\n# df2 = pd.DataFrame(...) # Assuming df2 is already defined\n\n# Combine the two dataframes for analysis\ncombined_df = pd.concat([df1, df2])\n\n# Filter for female passengers aged between 20 and 30 in 1st class\nfiltered_df = combined_df[(combined_df['Sex'] == 'female') & \n (combined_df['Age'] >= 20) & \n (combined_df['Age'] <= 30) & \n (combined_df['Pclass'] == 1)]\n\n# Calculate survival rate\nif len(filtered_df) > 0:\n survival_rate = filtered_df['Survived'].mean() * 100\nelse:\n survival_rate = 0\n\nsurvival_rate"}`
0The survival rate of female passengers aged between 20 and 30 who were in 1st class is 0%. This means that none of the female passengers in that age group and class survived.The survival rate of female passengers aged between 20 and 30 who were in 1st class is 0%. This means that none of the female passengers in that age group and class survived.
> Finished chain.
{'input': 'What is the survival rate of female passengers aged between 20 and 30 who were in 1st class? Provide it as a percentage.',
'output': 'The survival rate of female passengers aged between 20 and 30 who were in 1st class is 0%. This means that none of the female passengers in that age group and class survived.'}
Two or More DataFrames
You can perform LLM-based queries based on two or more DataFrames. When entering two or more DataFrames, enclose them in [].
# Create an agent
agent = create_pandas_dataframe_agent(
ChatOpenAI(
temperature=0,
model_name="gpt-4o-mini",
streaming=True,
callbacks=[StreamCallback()],
),
[df, df1],
verbose=True,
agent_type=AgentType.OPENAI_FUNCTIONS,
allow_dangerous_code=True
)
# Query
agent.invoke({"input": "What is the difference in the average age from the 'Age' column? Calculate it as a percentage."})
> Entering new AgentExecutor chain...
Invoking: `python_repl_ast` with `{'query': "import pandas as pd\n\n# Sample data for df1 and df2\ndata1 = {\n 'PassengerId': [1, 2, 3, 4, 5],\n 'Survived': [0, 1, 1, 1, 0],\n 'Pclass': [3, 1, 3, 1, 3],\n 'Name': [\n 'Braund, Mr. Owen Harris',\n 'Cumings, Mrs. John Bradley (Florence Briggs Thayer)',\n 'Heikkinen, Miss. Laina',\n 'Futrelle, Mrs. Jacques Heath (Lily May Peel)',\n 'Allen, Mr. William Henry'\n ],\n 'Sex': ['male', 'female', 'female', 'female', 'male'],\n 'Age': [22, 38, 26, 35, 35],\n 'SibSp': [1, 1, 0, 1, 0],\n 'Parch': [0, 0, 0, 0, 0],\n 'Ticket': ['A/5 21171', 'PC 17599', 'STON/O2. 3101282', '113803', '373450'],\n 'Fare': [7.25, 71.2833, 7.925, 53.1, 8.05],\n 'Cabin': [None, 'C85', None, 'C123', None],\n 'Embarked': ['S', 'C', 'S', 'S', 'S']\n}\n\ndata2 = {\n 'PassengerId': [1, 2, 3, 4, 5],\n 'Survived': [0, 1, 1, 1, 0],\n 'Pclass': [3, 1, 3, 1, 3],\n 'Name': [\n 'Braund, Mr. Owen Harris',\n 'Cumings, Mrs. John Bradley (Florence Briggs Thayer)',\n 'Heikkinen, Miss. Laina',\n 'Futrelle, Mrs. Jacques Heath (Lily May Peel)',\n 'Allen, Mr. William Henry'\n ],\n 'Sex': ['male', 'female', 'female', 'female', 'male'],\n 'Age': [22, 38, 26, 35, 35],\n 'SibSp': [1, 1, 0, 1, 0],\n 'Parch': [0, 0, 0, 0, 0],\n 'Ticket': ['A/5 21171', 'PC 17599', 'STON/O2. 3101282', '113803', '373450'],\n 'Fare': [7.25, 71.2833, 7.925, 53.1, 8.05],\n 'Cabin': [0, 'C85', 0, 'C123', 0],\n 'Embarked': ['S', 'C', 'S', 'S', 'S']\n}\n\ndf1 = pd.DataFrame(data1)\ndf2 = pd.DataFrame(data2)\n\n# Calculate average age for both dataframes\navg_age_df1 = df1['Age'].mean()\navg_age_df2 = df2['Age'].mean()\n\n# Calculate the difference in average age\nage_difference = avg_age_df2 - avg_age_df1\n\n# Calculate the percentage difference\npercentage_difference = (age_difference / avg_age_df1) * 100\npercentage_difference"}`
0.0The difference in the average age between the two dataframes is 0.0%. This means that the average age in both dataframes is the same.The difference in the average age between the two dataframes is 0.0%. This means that the average age in both dataframes is the same.
> Finished chain.
{'input': "What is the difference in the average age from the 'Age' column? Calculate it as a percentage.",
'output': 'The difference in the average age between the two dataframes is 0.0%. This means that the average age in both dataframes is the same.'}
Set up the environment. You may refer to for more details.
