Data Views - Pandas Ai

<Note title="Beta Notice"> The semantic data layer is an experimental feature, suggested to advanced users. </Note>

What are Views?

Views are a feature of SQL databases that allow you to define logical subsets of data that can be used in queries. In PandasAI, you can define views in your semantic layer schema to organize and structure your data. Views are particularly useful when you want to:

Combine data from multiple datasets
Create a simplified or filtered view of your data
Define relationships between different datasets

Creating Views

You can create views either through YAML configuration or programmatically using Python.

Python Code Example

python

import pandasai as pai

# Create source datasets for an e-commerce analytics system
# Orders dataset
orders_df = pai.read_csv("orders.csv")
orders_dataset = pai.create(
    "myorg/orders",
    orders_df,
    description="Customer orders and transaction data"
)

# Products dataset
products_df = pai.read_csv("products.csv")
products_dataset = pai.create(
    "myorg/products",
    products_df,
    description="Product catalog with categories and pricing"
)

# Customer dataset
customers_df = pai.read_csv("customers.csv")
customers_dataset = pai.create(
    "myorg/customers",
    customers_df,
    description="Customer demographics and preferences"
)

# Define relationships between datasets
view_relations = [
    {
        "name": "order_to_product",
        "description": "Links orders to their products",
        "from": "orders.product_id",
        "to": "products.id"
    },
    {
        "name": "order_to_customer",
        "description": "Links orders to customer profiles",
        "from": "orders.customer_id",
        "to": "customers.id"
    }
]

# Select relevant columns for the sales analytics view
view_columns = [
    # Order details
    {"name": "orders.id", "type": "integer"},
    {"name": "orders.order_date", "type": "date"},
    {"name": "orders.total_amount", "type": "float"},
    {"name": "orders.status", "type": "string"},

    # Product information
    {"name": "products.name", "type": "string"},
    {"name": "products.category", "type": "string"},
    {"name": "products.unit_price", "type": "float"},
    {"name": "products.stock_level", "type": "integer"},

    # Customer information
    {"name": "customers.segment", "type": "string"},
    {"name": "customers.country", "type": "string"},
    {"name": "customers.join_date", "type": "date"},
]

# Create a comprehensive sales analytics view
sales_view = pai.create(
    "myorg/sales-analytics",
    description="Unified view of sales data combining orders, products, and customer information",
    relations=view_relations,
    columns=view_columns,
    view=True
)

# This view enables powerful analytics queries like:
# - Sales trends by customer segment and product category
# - Customer purchase history and preferences
# - Inventory management based on order patterns
# - Geographic sales distribution

YAML Configuration

Example Configuration

yaml

name: table_heart
columns:
  - name: parents.id
  - name: parents.name
  - name: parents.age
  - name: children.name
  - name: children.age
relations:
  - name: parent_to_children
    description: Relation linking the parent to its children
    from: parents.id
    to: children.id

Constraints

Mutual Exclusivity:
- A schema cannot define both table and view simultaneously.
- If view is true, then the schema represents a view.
Column Format:
- For views:
  - All columns must follow the format [table].[column].
  - from and to fields in relations must follow the [table].[column] format.
  - Example: loans.payment_amount, heart.condition.
Relationships for Views:
- Each table referenced in columns must have at least one relationship defined in relations.
- Relationships must specify from and to attributes in the [table].[column] format.
- Relations define how different tables in your view are connected.
Dataset Requirements:
- All referenced datasets must exist before creating the view.
- The columns specified in the view must exist in their respective source datasets.
- The columns used in relations (from and to) must be compatible types.