Pandasai Multiple Dataframe: sqlalchemy.exc.DatabaseError: (databricks.sql.exc.ServerOperationError) [UNRESOLVED_COLUMN.WITH_SUGGESTION]

[ssling0817]

Pandasai Multiple Dataframe: sqlalchemy.exc.DatabaseError: (databricks.sql.exc.ServerOperationError) [UNRESOLVED_COLUMN.WITH_SUGGESTION]

[dosubot]

To address the issue of unresolved columns when joining tables with different column names in PandasAI, you can follow these steps:

1. Ensure Column Names are Correct: Make sure that the column names used in the join operation are correct and exist in the respective tables.

2. Use Aliases for Columns: If the column names are different, you can use aliases to rename the columns before performing the join.

3. Validate Column Names: Use the _validate_column_name method to ensure that the column names conform to the expected pattern.

Here is an example of how you can modify your code to handle different column names:

import pandas as pd
from pandasai import Agent

# Example DataFrames
table_userinfo = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'LegalLastName': ['Smith', 'Jones', 'Brown'],
    'LegalFirstName': ['Alice', 'Bob', 'Charlie'],
    'Email': ['[email protected]', '[email protected]', '[email protected]']
})

table_ticketinfo = pd.DataFrame({
    'reporter': ['Alice', 'Bob', 'Charlie'],
    'issue_id': [1, 2, 3]
})

# Ensure column names are valid
def _validate_column_name(column_name):
    import re
    regex = r"^[a-zA-Z0-9_]+$"
    if not re.match(regex, column_name):
        raise ValueError(f"Invalid column name: {column_name}")

_validate_column_name('reporter')
_validate_column_name('name')

# Initialize the Agent
agent = Agent([table_userinfo, table_ticketinfo], config={"llm": "your_llm_here"})

# Define the prompt
prompt = "Get the user info of the ticket with issue_id 1."

# Execute the chat
answer = agent.chat(prompt)
print(answer)

In this example, the _validate_column_name method ensures that the column names are valid before performing the join operation. This should help resolve the UNRESOLVED_COLUMN.WITH_SUGGESTION error.

Additionally, make sure that the column names used in the generated code are correct and exist in the respective DataFrames. If the column names are different, you can rename them before performing the join operation.

For more details on handling SQL execution and common errors related to unresolved columns, you can refer to the relevant code snippets and explanations in the PandasAI repository ^[1][2].

Details

About Dosu
This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.