PandasAI: Data Analysis with Natural Language in Python

Introduction

Imagine being able to ask your data questions in plain English, without writing complex SQL queries or remembering intricate Pandas syntax. That is exactly what PandasAI offers: a Python library that integrates Large Language Model (LLM) capabilities with Pandas DataFrames.

PandasAI enables data analysts, data scientists, and even non-technical stakeholders to perform data analysis simply by typing questions in natural language. The library automatically converts your questions into executable Python/Pandas code, runs it, and returns the results.

In this tutorial, we will explore how to use PandasAI comprehensively, from installation to building practical business analytics.

Prerequisites

Before getting started, make sure you have:

• Python 3.9 or later
• pip package manager
• OpenAI API key (or a local LLM like Ollama)
• Basic understanding of Pandas DataFrames

Installation

Basic Installation

pip install pandasai

Installation with Additional Dependencies

# With plotting support
pip install pandasai[plotting]

With Excel support
pip install pandasai[excel]

Full installation
pip install pandasai[all]

Verify Installation

import pandasai
print(pandasai.version)

Setup and LLM Configuration

Using OpenAI

import os
from pandasai import SmartDataframe
from pandasai.llm.openai import OpenAI

Set API key
os.environ["OPENAIAPIKEY"] = "sk-your-api-key-here"

Or directly during initialization
llm = OpenAI(apitoken="sk-your-api-key-here", model="gpt-4o")

# Install Ollama first
curl -fsSL https://ollama.ai/install.sh | sh

Download models
ollama pull llama3
ollama pull codellama

from pandasai.llm.localllm import LocalLLM

Configure local LLM via Ollama
llm = LocalLLM(
    apibase="http://localhost:11434/v1",

    model="llama3"
)

from pandasai.llm.azureopenai import AzureOpenAI

llm = AzureOpenAI(
    apitoken="your-azure-api-key",

    azureendpoint="https://your-resource.openai.azure.com/",
    apiversion="2024-02-15-preview",

    deploymentname="gpt-4o"
)

Basic Queries with SmartDataframe

Creating a SmartDataframe

import pandas as pd
from pandasai import SmartDataframe

Create sample sales data
data = {
    "product": ["Laptop", "Mouse", "Keyboard", "Monitor", "Headset",
                "Laptop", "Mouse", "Keyboard", "Monitor", "Headset"],
    "category": ["Electronics", "Accessories", "Accessories", "Electronics", "Accessories",
                  "Electronics", "Accessories", "Accessories", "Electronics", "Accessories"],
    "price": [1200, 25, 50, 400, 75,
              1300, 20, 45, 450, 80],
    "unitssold": [120, 500, 350, 80, 200,

                   150, 600, 400, 95, 250],
    "month": ["January", "January", "January", "January", "January",
              "February", "February", "February", "February", "February"]
}

df = pd.DataFrame(data)
sdf = SmartDataframe(df, config={"llm": llm})

# Simple query
result = sdf.chat("What is the total sales in January?")
print(result)

Aggregation query
result = sdf.chat("Which product sold the most units?")
print(result)

Filtered query
result = sdf.chat("Show products with price above 100")
print(result)

Statistical query
result = sdf.chat("What is the average price per category?")
print(result)

# Revenue calculation
result = sdf.chat(
    "Calculate total revenue (price x unitssold) for each product, "
    "sorted from highest to lowest"
)
print(result)

Month-over-month comparison
result = sdf.chat(
    "Compare total sales between January and February, "
    "which products experienced growth?"
)
print(result)

Percentage analysis
result = sdf.chat("What is the percentage contribution of each category to total revenue?")
print(result)

SmartDatalake: Multi-DataFrame Analysis

SmartDatalake allows you to analyze multiple DataFrames simultaneously, which is extremely useful when data is spread across multiple tables.

from pandasai import SmartDatalake

Customer DataFrame
dfcustomers = pd.DataFrame({

    "customerid": [1, 2, 3, 4, 5],
    "name": ["Alice", "Bob", "Charlie", "Diana", "Eve"],
    "city": ["New York", "San Francisco", "Chicago", "New York", "Austin"],
    "segment": ["Enterprise", "SMB", "Enterprise", "SMB", "Enterprise"]
})

Transactions DataFrame
dftransactions = pd.DataFrame({

    "transactionid": range(1, 11),
    "customerid": [1, 2, 3, 1, 4, 5, 2, 3, 4, 5],

    "product": ["Laptop", "Mouse", "Server", "Monitor", "Keyboard",
                "Laptop", "Headset", "Server", "Mouse", "Monitor"],
    "amount": [1200, 25, 5000, 400, 50,
               1300, 75, 5500, 20, 450],
    "date": pd.daterange("2026-01-01", periods=10, freq="W")
})

Combine in SmartDatalake
lake = SmartDatalake(
    [dfcustomers, dftransactions],
    config={"llm": llm}
)

Cross-table queries
result = lake.chat("Who is the customer with the highest total purchases?")
print(result)

result = lake.chat("What is the average transaction amount per city?")
print(result)

result = lake.chat(
    "Compare total purchases between Enterprise and SMB segments"
)
print(result)

Plotting and Visualization

PandasAI can generate visualizations directly from natural language queries.

import matplotlib
matplotlib.use("Agg")  # For environments without GUI

sdf = SmartDataframe(df, config={
    "llm": llm,
    "savecharts": True,

    "savechartspath": "./charts",

    "enablecache": True
})

Generate bar chart
result = sdf.chat("Create a bar chart of total sales per product")

Generate pie chart
result = sdf.chat("Create a pie chart of revenue distribution by category")

Generate line chart
result = sdf.chat(
    "Create a line chart comparing units sold per product "
    "between January and February"
)

Chart with customization
result = sdf.chat(
    "Create a horizontal bar chart of the top 5 products by revenue, "
    "use blue color and add value labels"
)

Saving and Exporting Charts

# Configure save path
sdf = SmartDataframe(df, config={
    "llm": llm,
    "savecharts": True,

    "savechartspath": "/path/to/charts/directory",

    "opencharts": False  # Don't auto-open
})

Charts are automatically saved to the specified directory
result = sdf.chat("Create a scatter plot of price vs units sold")
print(f"Chart saved to: {result}")

Custom Prompts and Advanced Configuration

Using Custom Prompts

from pandasai import SmartDataframe

sdf = SmartDataframe(df, config={
    "llm": llm,
    "customhead": df.head(3),  # Provide sample data to LLM

    "description": (
        "This dataset contains IT product sales data. "
        "The 'price' column is in USD. "
        "The 'unitssold' column represents units sold."
    )
})

LLM now has better context
result = sdf.chat("Analyze sales trends and provide insights")
print(result)

Response Configuration

sdf = SmartDataframe(df, config={
    "llm": llm,
    "conversational": True,      # Conversational format response
    "verbose": True,             # Show process in console
    "maxretries": 3,            # Retry on failure

    "customwhitelisteddependencies": ["scipy", "sklearn"]

})

from pandasai import Agent

agent = Agent([df], config={"llm": llm})

Add specific instructions
agent.addmessage(
    "Always format currency values with dollar signs and thousand separators. "
    "Use clear, professional language in all responses."
)

result = agent.chat("What is the total revenue?")
print(result)

Caching for Performance

PandasAI supports caching to avoid repeated API calls for the same queries.

# Enable caching
sdf = SmartDataframe(df, config={
    "llm": llm,
    "enablecache": True

})

First query will call the LLM
result1 = sdf.chat("What is the total sales?")  # API call

Same query will use cache
result2 = sdf.chat("What is the total sales?")  # From cache, much faster

To clear cache
sdf.clearcache()

Advanced Cache Configuration

import os

Cache is stored in the default directory
You can change it via environment variable
os.environ["PANDASAICACHEPATH"] = "/path/to/cache"

Or disable cache for development
sdf = SmartDataframe(df, config={
    "llm": llm,
    "enablecache": False

})

# PandasAI runs code in a restricted environment
sdf = SmartDataframe(df, config={
    "llm": llm,
    "enforceprivacy": True,  # Don't send actual data to LLM
    "customwhitelisteddependencies": [
        "numpy",
        "pandas",
        "matplotlib"
    ]  # Only allow specific libraries
})

Security Best Practices

# 1. Use enforceprivacy for sensitive data
sdf = SmartDataframe(sensitivedf, config={
    "llm": llm,
    "enforceprivacy": True  # Only send metadata, not actual data

})

2. Validate output before use
result = sdf.chat("Show customer data")
if isinstance(result, pd.DataFrame):
    # Validate returned columns
    allowedcolumns = ["name", "city", "segment"]
    result = result[[c for c in result.columns if c in allowedcolumns]]


3. Never expose API keys in code
Use environment variables
import os
os.environ["OPENAIAPIKEY"] = "your-key"  # Set in .env file, not in code


4. Restrict file system access
sdf = SmartDataframe(df, config={
    "llm": llm,
    "savecharts": True,
    "savechartspath": "./safedirectory"  # Restrict to specific directory

})

import pandas as pd
import numpy as np
from pandasai import SmartDatalake, Agent
from datetime import datetime, timedelta

Simulate e-commerce data
np.random.seed(42)

Product Data
products = pd.DataFrame({
    "productid": range(1, 21),
    "productname": [

        "Gaming Laptop", "Office Laptop", "Wireless Mouse", "Gaming Mouse",
        "Mechanical Keyboard", "Membrane Keyboard", "24-inch Monitor", "27-inch Monitor",
        "Gaming Headset", "Wireless Headset", "500GB SSD", "1TB SSD",
        "8GB RAM", "16GB RAM", "HD Webcam", "4K Webcam",
        "USB Hub", "Laptop Stand", "XL Mousepad", "Cable Management Kit"
    ],
    "category": [
        "Laptop", "Laptop", "Mouse", "Mouse",
        "Keyboard", "Keyboard", "Monitor", "Monitor",
        "Audio", "Audio", "Storage", "Storage",
        "Memory", "Memory", "Camera", "Camera",
        "Accessories", "Accessories", "Accessories", "Accessories"
    ],
    "price": [
        1500, 900, 35, 75,
        120, 30, 300, 450,
        150, 200, 80, 150,
        50, 100, 50, 150,
        15, 40, 20, 10
    ],
    "stock": np.random.randint(10, 200, 20)
})

Transaction Data (last 3 months)
ntransactions = 500
transactions = pd.DataFrame({
    "orderid": range(1, ntransactions + 1),
    "productid": np.random.randint(1, 21, ntransactions),
    "customerid": np.random.randint(1, 101, ntransactions),
    "quantity": np.random.randint(1, 5, ntransactions),

    "date": [
        datetime(2026, 1, 1) + timedelta(days=np.random.randint(0, 90))
        for  in range(ntransactions)

    ],
    "paymentmethod": np.random.choice(
        ["Bank Transfer", "E-Wallet", "Credit Card", "COD"],
        ntransactions,

        p=[0.35, 0.30, 0.20, 0.15]
    )
})

Customer Data
customers = pd.DataFrame({
    "customerid": range(1, 101),
    "name": [f"Customer{i}" for i in range(1, 101)],

    "city": np.random.choice(
        ["New York", "San Francisco", "Chicago", "Austin", "Seattle",
         "Boston", "Denver", "Miami", "Portland", "Atlanta"],
        100
    ),
    "tier": np.random.choice(["Regular", "Premium", "VIP"], 100, p=[0.6, 0.3, 0.1]),
    "joindate": [
        datetime(2025, 1, 1) + timedelta(days=np.random.randint(0, 365))
        for  in range(100)

    ]
})

Initialize Agent with all data
agent = Agent(
    [products, transactions, customers],
    config={
        "llm": llm,
        "verbose": True,
        "savecharts": True,
        "savechartspath": "./businesscharts"

    }
)

Analysis 1: Business Overview
print("="  50)

print("BUSINESS OVERVIEW ANALYSIS")
print("="  50)

result = agent.chat(
    "Provide a business summary: total revenue, number of transactions, "
    "average order value, and number of unique customers"
)
print(result)

Analysis 2: Top Products
print("\nTOP PRODUCTS")
result = agent.chat(
    "Show the top 5 products by total revenue (price x quantity), "
    "include units sold"
)
print(result)

Analysis 3: Monthly Trends
print("\nMONTHLY TRENDS")
result = agent.chat(
    "Analyze monthly sales trends (January, February, March). "
    "Is there growth or decline?"
)
print(result)

Analysis 4: Customer Segmentation
print("\nCUSTOMER SEGMENTATION")
result = agent.chat(
    "Compare average transaction value between Regular, Premium, "
    "and VIP customers. Which city has the most VIP customers?"
)
print(result)

Analysis 5: Payment Methods
print("\nPAYMENT METHOD ANALYSIS")
result = agent.chat(
    "Create a pie chart showing payment method distribution by transaction count"
)

Analysis 6: Stock Recommendations
print("\nSTOCK RECOMMENDATIONS")
result = agent.chat(
    "Which products need restocking? Compare average daily sales "
    "with current stock, identify products that will run out "
    "within the next 2 weeks"
)
print(result)

# Export results to Excel
result = agent.chat(
    "Create a summary table with columns: productname, totalunitssold, "
    "totalrevenue, avgmonthlysales"

)

if isinstance(result, pd.DataFrame):
    result.toexcel("businesssummary.xlsx", index=False)

    print("Summary exported to businesssummary.xlsx")

Export to CSV
result = agent.chat("Show all transactions with revenue above 5000")
if isinstance(result, pd.DataFrame):
    result.tocsv("highvaluetransactions.csv", index=False)

# Less effective
sdf.chat("total sales")

More effective
sdf.chat(
    "Calculate total sales (price x unitssold) "
    "for all products in January 2026"
)

2. Use DataFrame Descriptions

sdf = SmartDataframe(df, config={
    "llm": llm,
    "description": (
        "Monthly IT product sales data. "
        "Prices in USD. "
        "Period: Jan-Feb 2026."
    )
})

3. Handle Errors Gracefully

try:
    result = sdf.chat("Complex query here")
    if result is not None:
        print(result)
    else:
        print("No results for this query")
except Exception as e:
    print(f"Error: {e}")
    # Try with a simpler query
    result = sdf.chat("Show data summary")

4. Optimize API Costs

# Use cheaper models for simple queries
llmcheap = OpenAI(model="gpt-3.5-turbo")

llmsmart = OpenAI(model="gpt-4o")

Simple queries
sdfsimple = SmartDataframe(df, config={"llm": llmcheap})
result = sdfsimple.chat("How many rows in the data?")


Complex queries
sdfcomplex = SmartDataframe(df, config={"llm": llmsmart})

result = sdfcomplex.chat("Perform an in-depth correlation analysis")

Conclusion

PandasAI is a powerful tool for democratizing data analysis. With natural language query capabilities, anyone in an organization can extract insights from data without deep coding expertise.

Key takeaways:

• Start simple: Use basic queries before moving to complex ones
• Provide context: The clearer your query, the more accurate the results
• Mind security: Use enforce_privacy for sensitive data
• Leverage caching: Save API costs by enabling cache
• Use SmartDatalake: For analysis involving multiple DataFrames
• Validate output: Always verify results, especially for important business decisions

By combining PandasAI with your business data, you can build interactive analytics systems that are accessible to your entire team.

References

• Official PandasAI Documentation: https://docs.pandas-ai.com
• PandasAI GitHub Repository: https://github.com/Sinaptik-AI/pandas-ai
• OpenAI API Documentation: https://platform.openai.com/docs
• Ollama: https://ollama.ai