File size: 7,605 Bytes
a784e49 a11e9b5 a784e49 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 |
from typing import Dict, List, Any, Optional
import pandas as pd
from sklearn.metrics.pairwise import cosine_similarity
import numpy as np
class CSVQueryEngine:
"""Query engine for CSV data with multi-file support."""
def __init__(self, index_manager, llm):
"""Initialize with index manager and language model."""
self.index_manager = index_manager
self.llm = llm
def query(self, query_text: str) -> Dict[str, Any]:
"""Process a natural language query across CSV files."""
# Find relevant CSV files
relevant_csvs = self.index_manager.find_relevant_csvs(query_text)
if not relevant_csvs:
return {
"answer": "No relevant CSV files found for your query.",
"sources": []
}
# Prepare context from relevant CSVs
context = self._prepare_context(query_text, relevant_csvs)
# Generate prompt
prompt = self._generate_prompt(query_text, context)
# Get response from LLM
response = self.llm.complete(prompt)
# Return formatted response
return {
"answer": response.text,
"sources": self._get_sources(relevant_csvs)
}
def _prepare_context(self, query: str, csv_ids: List[str]) -> str:
"""Prepare context from relevant CSV files with pre-calculated statistics."""
context_parts = []
calculated_answers = {}
# Check for common statistical questions
query_lower = query.lower()
is_avg_question = "average" in query_lower or "mean" in query_lower
is_max_question = "maximum" in query_lower or "max" in query_lower
is_min_question = "minimum" in query_lower or "min" in query_lower
# Extract potential column names from query
query_words = set(query_lower.replace("?", "").replace(",", "").split())
for csv_id in csv_ids:
# Get metadata
if csv_id not in self.index_manager.indexes:
continue
metadata = self.index_manager.indexes[csv_id]["metadata"]
file_path = self.index_manager.indexes[csv_id]["path"]
# Add CSV metadata
context_parts.append(f"CSV File: {metadata['filename']}")
context_parts.append(f"Columns: {', '.join(metadata['columns'])}")
context_parts.append(f"Row Count: {metadata['row_count']}")
# Add sample data and calculate statistics
try:
df = pd.read_csv(file_path)
context_parts.append("\nSample Data:")
context_parts.append(df.head(3).to_string())
# Find relevant columns based on query
column_matches = []
for col in df.columns:
col_lower = col.lower()
# Check if column name appears in query or is similar to words in query
if col_lower in query_lower or any(word in col_lower for word in query_words):
column_matches.append(col)
# If no direct matches, include all numeric columns
if not column_matches:
column_matches = df.select_dtypes(include=['number']).columns.tolist()
# Calculate statistics for matched columns
for col in column_matches:
if pd.api.types.is_numeric_dtype(df[col]):
if is_avg_question:
avg_value = df[col].mean()
context_parts.append(f"\nThe average {col} is: {avg_value:.2f}")
calculated_answers[f"average_{col}"] = avg_value
if is_max_question:
max_value = df[col].max()
context_parts.append(f"\nThe maximum {col} is: {max_value}")
calculated_answers[f"max_{col}"] = max_value
if is_min_question:
min_value = df[col].min()
context_parts.append(f"\nThe minimum {col} is: {min_value}")
calculated_answers[f"min_{col}"] = min_value
except Exception as e:
context_parts.append(f"Error reading CSV: {str(e)}")
# Add direct answer if calculated
if calculated_answers:
context_parts.append("\nDirect Answer:")
for key, value in calculated_answers.items():
context_parts.append(f"{key.replace('_', ' ')}: {value}")
return "\n\n".join(context_parts)
def _prepare_context1(self, query: str, csv_ids: List[str]) -> str:
"""Prepare context from relevant CSV files."""
context_parts = []
for csv_id in csv_ids:
# Get metadata
if csv_id not in self.index_manager.indexes:
continue
metadata = self.index_manager.indexes[csv_id]["metadata"]
file_path = self.index_manager.indexes[csv_id]["path"]
# Add CSV metadata
context_parts.append(f"CSV File: {metadata['filename']}")
context_parts.append(f"Columns: {', '.join(metadata['columns'])}")
context_parts.append(f"Row Count: {metadata['row_count']}")
# Add sample data
try:
df = pd.read_csv(file_path)
context_parts.append("\nSample Data:")
context_parts.append(df.head(5).to_string())
# Add some basic statistics that might be relevant
context_parts.append("\nNumeric Column Statistics:")
numeric_cols = df.select_dtypes(include=['number']).columns
for col in numeric_cols:
stats = df[col].describe()
context_parts.append(f"{col} - mean: {stats['mean']:.2f}, min: {stats['min']:.2f}, max: {stats['max']:.2f}")
except Exception as e:
context_parts.append(f"Error reading CSV: {str(e)}")
return "\n\n".join(context_parts)
def _generate_prompt(self, query: str, context: str) -> str:
"""Generate a prompt for the LLM."""
return f"""You are an AI assistant specialized in analyzing CSV data.
Your goal is to help users understand their data and extract insights.
Below is information about CSV files that might help answer the query:
{context}
User Query: {query}
Please provide a comprehensive and accurate answer based on the data.
If calculations are needed, explain your process.
If the data doesn't contain information to answer the query, say so clearly.
Answer:"""
def _get_sources(self, csv_ids: List[str]) -> List[Dict[str, str]]:
"""Get source information for the response."""
sources = []
for csv_id in csv_ids:
if csv_id not in self.index_manager.indexes:
continue
metadata = self.index_manager.indexes[csv_id]["metadata"]
sources.append({
"csv": metadata["filename"],
"columns": ", ".join(metadata["columns"][:5]) + ("..." if len(metadata["columns"]) > 5 else "")
})
return sources
|