it cannot answer to the questions once 3 data frames are merged and provided

[mesanjeewax]

System Info

OS version: Windows
Python version: 3.10.11
The current version of pandasai being used: 2.0.42

🐛 Describe the bug

as per the below program it gives me wrong answer
import os
import pandas as pd
from pandasai import Agent
from langchain_groq.chat_models import ChatGroq
from pandasai import SmartDataframe

Initialize the language model

llm = ChatGroq(model_name="llama3-70b-8192", api_key=os.environ.get("GROQ_API_KEY"))

Data

employees_data = {
'EmployeeID': [1, 2, 3, 4, 5],
'Name': ['John', 'Emma', 'Liam', 'Olivia', 'William'],
'Department': ['HR', 'Sales', 'IT', 'Marketing', 'Finance']
}

card_data = {
'EmployeeID': [1, 2, 3, 4, 5],
'CardNo': ['123456', '23456', '34567', '45678', '56789'],
'status_code': ['1', '6', '1', '6', '3']
}

trxn_data = {
'CardNo': ['123456', '23456', '34567', '45678', '56789', '123456'],
'trxn_status': ['success', 'success', 'success', 'success', 'success', 'fail'],
'Merchant_Id': ['3', '6', '1', '6', '3', '3'],
'Device_Type': ['1', '6', '1', '6', '3', '1'],
'Trxn_type': ['1', '6', '1', '6', '3', '2'],
'Trxn_amount': [100, 200, 300, 400, 500, 500]
}

Create DataFrames

employees_df = pd.DataFrame(employees_data)
card_data_df = pd.DataFrame(card_data)
trxn_data_df = pd.DataFrame(trxn_data)

Merge DataFrames

merged_card_employee_df = pd.merge(employees_df, card_data_df, on='EmployeeID')
merged_df = pd.merge(merged_card_employee_df, trxn_data_df, on='CardNo')

Inspect the merged DataFrame

print("Merged DataFrame:")
print(merged_df)

Filter for successful transactions with Merchant ID 1

successful_transactions = merged_df[(merged_df['trxn_status'] == 'success') & (merged_df['Merchant_Id'] == '1')]

Print the filtered DataFrame

print("Filtered DataFrame for successful transactions with Merchant ID 1:")
print(successful_transactions)

Count the number of successful transactions

num_successful_transactions = successful_transactions.shape[0]

print(f"Number of successful transactions with Merchant ID 1: {num_successful_transactions}")

Create SmartDataframe with the merged DataFrame

agent = SmartDataframe(merged_df, config={"llm": llm})

Query the merged DataFrame

response = agent.chat("find the number of successful transactions done by employee 1 with merchant ID 1.")
print(response)

[mesanjeewax]

as per the above code provided response is 0