Wondering about Pandas Query Engine in Langchain

[mraguth]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

The reference from Llama Index: https://docs.llamaindex.ai/en/stable/examples/query_engine/pandas_query_engine/

Description

Is it Langchanin has any concepts which related to Llama Index Pandas query pipe line?
If yes, can you give me some code examples please?

The reference from Llama Index: https://docs.llamaindex.ai/en/stable/examples/query_engine/pandas_query_engine/

System Info

Using latest version of Llangchain and Langgraph.

[dosubot[bot]]

Yes, LangChain has concepts related to querying structured data, such as SQL databases, which can be analogous to the Llama Index Pandas query pipeline. Below are some code examples demonstrating how to build a Question/Answering system over SQL data using LangChain.

Setup

First, install the required packages and set environment variables:

%%capture --no-stderr
%pip install --upgrade --quiet langchain langchain-community langchain-openai faiss-cpu

Initialize Database

Set up a SQLite connection with the Chinook database:

from langchain_community.utilities import SQLDatabase

db = SQLDatabase.from_uri("sqlite:///Chinook.db")
print(db.dialect)
print(db.get_usable_table_names())
db.run("SELECT * FROM Artist LIMIT 10;")

Create SQL Query Chain

Create a chain that converts a question into a SQL query, executes it, and uses the result to answer the original question:

from langchain_openai import ChatOpenAI
from langchain.chains import create_sql_query_chain

llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)
chain = create_sql_query_chain(llm, db)
response = chain.invoke({"question": "How many employees are there"})
print(response)

Execute SQL Query

Use the QuerySQLDatabaseTool to add query execution to the chain:

from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool

execute_query = QuerySQLDataBaseTool(db=db)
write_query = create_sql_query_chain(llm, db)
chain = write_query | execute_query
response = chain.invoke({"question": "How many employees are there"})
print(response)

Answer the Question

Combine the original question and SQL query result to generate a final answer:

from operator import itemgetter
from langchain_core.output_parsers import StrOutputParser
from langchain_core.prompts import PromptTemplate
from langchain_core.runnables import RunnablePassthrough

answer_prompt = PromptTemplate.from_template(
    """Given the following user question, corresponding SQL query, and SQL result, answer the user question.

Question: {question}
SQL Query: {query}
SQL Result: {result}
Answer: """
)

chain = (
    RunnablePassthrough.assign(query=write_query).assign(
        result=itemgetter("query") | execute_query
    )
    | answer_prompt
    | llm
    | StrOutputParser()
)

response = chain.invoke({"question": "How many employees are there"})
print(response)

Using Agents

LangChain also provides a more flexible way of interacting with SQL databases using agents:

from langchain_community.agent_toolkits import SQLDatabaseToolkit

toolkit = SQLDatabaseToolkit(db=db, llm=llm)
tools = toolkit.get_tools()

from langchain_core.messages import SystemMessage

SQL_PREFIX = """You are an agent designed to interact with a SQL database.
Given an input question, create a syntactically correct SQLite query to run, then look at the results of the query and return the answer.
Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 5 results.
You can order the results by a relevant column to return the most interesting examples in the database.
Never query for all the columns from a specific table, only ask for the relevant columns given the question.
You have access to tools for interacting with the database.
Only use the below tools. Only use the information returned by the below tools to construct your final answer.
You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.

DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.

To start you should ALWAYS look at the tables in the database to see what you can query.
Do NOT skip this step.
Then you should query the schema of the most relevant tables."""

system_message = SystemMessage(content=SQL_PREFIX)

from langchain_core.messages import HumanMessage
from langgraph.prebuilt import create_react_agent

agent_executor = create_react_agent(llm, tools, messages_modifier=system_message)

for s in agent_executor.stream(
    {"messages": [HumanMessage(content="Which country's customers spent the most?")]}
):
    print(s)
    print("----")

These examples demonstrate how LangChain can be used to create a Q&A system over SQL data, similar to the Llama Index Pandas query pipeline ^[1].

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu Do you have any specific examples related to pandas pipe line? I am interested in Pandas more than SQL for my use case. Thank you.

[dosubot[bot]]

Yes, LangChain has built-in support for querying Pandas DataFrames using natural language. You can use the create_pandas_dataframe_agent function to construct an agent that interacts with Pandas DataFrames. Here is an example:

from langchain_openai import ChatOpenAI
from langchain_experimental.agents import create_pandas_dataframe_agent
import pandas as pd

# Load your DataFrame
df = pd.read_csv("titanic.csv")

# Initialize the language model
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)

# Create the Pandas DataFrame agent
agent_executor = create_pandas_dataframe_agent(
    llm,
    df,
    agent_type="tool-calling",
    verbose=True
)

# Example query
response = agent_executor.invoke({"input": "What is the average fare?"})
print(response)

This setup allows the agent to query the DataFrame using natural language. However, be cautious as this involves executing Python code, which carries security risks. For production use, consider loading the data into a SQL database and interacting with it via SQL to better manage permissions and sanitize queries ^[1].

Additionally, LangChain provides a DataFrameLoader class for loading Pandas DataFrames, which can be useful for document-based operations:

from langchain_community.document_loaders.dataframe import DataFrameLoader
import pandas as pd

# Load your DataFrame
df = pd.read_csv("titanic.csv")

# Initialize the DataFrameLoader
loader = DataFrameLoader(data_frame=df, page_content_column="text")

# Load documents
documents = list(loader.lazy_load())

This class ensures that the DataFrame is properly loaded and can be iterated over to yield documents with the specified page content and metadata ^[2].

For more detailed examples and tutorials, you can refer to the LangChain documentation ^[3].

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu... is that possible to combine Pandas query pipe lines with regular retrievers? I can do that in Lama Index. Like to know some examples if possible. Thank you.

[dosubot[bot]]

Yes, it is possible to combine Pandas query pipelines with regular retrievers in LangChain. Here is an example of how you can achieve this:

from langchain_core.runnables import chain
from langchain_core.pandas import PandasQueryPipeline
from langchain_community.document_loaders import WebBaseLoader
from langchain_text_splitters import RecursiveCharacterTextSplitter
from langchain_chroma import Chroma
from langchain_openai import OpenAIEmbeddings
from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder
from langchain.chains.combine_documents import create_stuff_documents_chain
from langchain_core.messages import HumanMessage, AIMessage
from langchain_core.output_parsers import StrOutputParser
from langchain_core.runnables import RunnableBranch, RunnablePassthrough
import pandas as pd

# Assuming you have already set up your retrievers
retrievers = {
    "HARRISON": retriever_harrison,
    "ANKUSH": retriever_ankush,
}

# Define a custom chain that includes both query analysis and retrieval
@chain
async def custom_chain(question):
    # Perform query analysis to determine which retriever to use
    response = await query_analyzer.ainvoke(question)
    retriever = retrievers[response.person]
    
    # Perform the retrieval
    retrieval_result = await retriever.ainvoke(response.query)
    
    # Assuming you have a Pandas DataFrame `df` that you want to query
    df = pd.DataFrame({
        'name': ['Harrison', 'Ankush'],
        'company': ['Kensho', 'Facebook']
    })
    
    # Create a Pandas query pipeline
    pandas_pipeline = PandasQueryPipeline(df)
    
    # Perform a Pandas query based on the retrieval result
    pandas_result = pandas_pipeline.query(f"name == '{response.person}'")
    
    return pandas_result

# Example usage
result_harrison = await custom_chain.ainvoke("where did Harrison Work")
result_ankush = await custom_chain.ainvoke("where did ankush Work")

print(result_harrison)
print(result_ankush)

In this example, the custom_chain function first performs query analysis to determine which retriever to use. It then performs the retrieval and subsequently uses a Pandas query pipeline to query a DataFrame based on the retrieval result.

Additionally, you can set up a more complex retrieval system using LangChain's capabilities. For instance, you can create a retriever from a vectorstore and combine it with a document chain to answer questions. Here's a brief overview of how you can set up such a system:

1. Create a retriever:

   loader = WebBaseLoader("https://docs.smith.langchain.com/overview")
   data = loader.load()
   text_splitter = RecursiveCharacterTextSplitter(chunk_size=500, chunk_overlap=0)
   all_splits = text_splitter.split_documents(data)
   vectorstore = Chroma.from_documents(documents=all_splits, embedding=OpenAIEmbeddings())
   retriever = vectorstore.as_retriever(k=4)

2. Create a document chain:

   SYSTEM_TEMPLATE = """
   Answer the user's questions based on the below context. 
   If the context doesn't contain any relevant information to the question, don't make something up and just say "I don't know":
   <context>
   {context}
   </context>
   """
   question_answering_prompt = ChatPromptTemplate.from_messages(
       [
           ("system", SYSTEM_TEMPLATE),
           MessagesPlaceholder(variable_name="messages"),
       ]
   )
   document_chain = create_stuff_documents_chain(chat, question_answering_prompt)

3. Combine the retriever and document chain:

   retrieval_chain = RunnablePassthrough.assign(
       context=parse_retriever_input | retriever,
   ).assign(
       answer=document_chain,
   )

4. Handle conversational queries:

   query_transform_prompt = ChatPromptTemplate.from_messages(
       [
           MessagesPlaceholder(variable_name="messages"),
           ("user", "Given the above conversation, generate a search query to look up in order to get information relevant to the conversation. Only respond with the query, nothing else."),
       ]
   )
   query_transformation_chain = query_transform_prompt | chat
   query_transforming_retriever_chain = RunnableBranch(
       (
           lambda x: len(x.get("messages", [])) == 1,
           (lambda x: x["messages"][-1].content) | retriever,
       ),
       query_transform_prompt | chat | StrOutputParser() | retriever,
   ).with_config(run_name="chat_retriever_chain")
   conversational_retrieval_chain = RunnablePassthrough.assign(
       context=query_transforming_retriever_chain,
   ).assign(
       answer=document_chain,
   )

By combining these steps, you can create a robust system that integrates Pandas query pipelines with regular retrievers, enhancing the capabilities of your LangChain-based applications ^[1][2][3].

To continue talking to Dosu, mention @dosu.