Smithery is now a part of Arcade.dev! Read more in our announcement here
Automated CSV data analysis with statistical insights, pattern detection, visualization recommendations, and anomaly detection
Comprehensive CSV data analysis with automatic insight generation, statistical analysis, pattern detection, and visualization recommendations.
Activate this skill when the user:
1. Load CSV file
2. Detect encoding (UTF-8, Latin-1, etc.)
3. Infer data types for each column
4. Check for headers
5. Count rows and columns
6. Display sample data (first/last N rows)
7. Identify null/missing values
1. Generate column-by-column statistics
2. Detect data types (numeric, categorical, datetime, text)
3. Calculate cardinality (unique values)
4. Identify primary key candidates
5. Check for duplicates
6. Analyze value distributions
7. Detect potential relationships between columns
1. Descriptive statistics for numeric columns
2. Frequency analysis for categorical columns
3. Correlation matrix
4. Distribution fitting
5. Outlier detection
6. Missing data patterns
7. Time series decomposition (if applicable)
1. Trend analysis
2. Seasonality detection
3. Clustering analysis
4. Association rules
5. Anomaly detection
6. Segment identification
1. Summarize key findings
2. Identify significant patterns
3. Highlight anomalies
4. Compare segments
5. Generate recommendations
6. Create visualizations
# Preview first 10 rows
head -n 10 file.csv
# Preview last 10 rows
tail -n 10 file.csv
# Count rows
wc -l file.csv
# Count columns
head -n 1 file.csv | tr ',' '\n' | wc -l
# Display specific columns (column 1 and 3)
cut -d',' -f1,3 file.csv
# Sort by column
sort -t',' -k2 file.csv
# Remove duplicate rows
sort file.csv | uniq > clean.csv
# Remove empty lines
grep -v '^$' file.csv > clean.csv
# Convert to Unix line endings
dos2unix file.csv
# Remove BOM (Byte Order Mark)
sed '1s/^\xEF\xBB\xBF//' file.csv > clean.csv
# Sum of column 2
awk -F',' '{sum+=$2} END {print sum}' file.csv
# Average of column 2
awk -F',' '{sum+=$2; count++} END {print sum/count}' file.csv
# Min and max of column 2
awk -F',' 'NR==1{min=max=$2} {if($2<min) min=$2; if($2>max) max=$2} END {print "Min:", min, "Max:", max}' file.csv
# Count occurrences of values in column 1
awk -F',' '{count[$1]++} END {for(val in count) print val, count[val]}' file.csv
import pandas as pd
import numpy as np
# Load CSV
df = pd.read_csv('file.csv')
# Basic info
df.info()
df.describe()
# Check for missing values
df.isnull().sum()
# Detect duplicates
df.duplicated().sum()
# Correlation matrix
df.corr()
# Value counts
df['column'].value_counts()
# Groupby analysis
df.groupby('category').agg({'value': ['mean', 'sum', 'count']})
# Outlier detection (IQR method)
Q1 = df['column'].quantile(0.25)
Q3 = df['column'].quantile(0.75)
IQR = Q3 - Q1
outliers = df[(df['column'] < Q1 - 1.5*IQR) | (df['column'] > Q3 + 1.5*IQR)]
Columns: Date, Product, Category, Quantity, Revenue, Region
Analysis:
1. Revenue trends over time
2. Top products by revenue
3. Regional performance comparison
4. Seasonal patterns
5. Product category breakdown
6. Average order value
7. Growth rate calculation
Columns: CustomerID, Age, Gender, Location, SignupDate, LastPurchase, TotalSpent
Analysis:
1. Customer demographics (age, gender distribution)
2. Geographic distribution
3. Customer lifetime value
4. Churn analysis (last purchase date)
5. Cohort analysis by signup date
6. Spending patterns by segment
7. Customer retention rate
Columns: Date, PageViews, Sessions, Users, BounceRate, AvgSessionDuration
Analysis:
1. Traffic trends over time
2. User engagement metrics
3. Bounce rate analysis
4. Peak traffic periods
5. User retention patterns
6. Session duration distribution
7. Conversion funnel analysis
Columns: Date, Account, Type, Amount, Category, Description
Analysis:
1. Income vs expenses
2. Category breakdown (spending patterns)
3. Monthly trends
4. Budget vs actual
5. Unusual transactions (anomalies)
6. Cash flow analysis
7. Forecast future expenses
Trend Types:
- Upward: Values consistently increasing
- Downward: Values consistently decreasing
- Seasonal: Regular periodic patterns
- Cyclical: Irregular periodic patterns
- Random: No discernible pattern
Detection Methods:
- Moving averages
- Linear regression
- Seasonal decomposition
- Autocorrelation analysis
Pattern Types:
- Dominant category (80/20 rule)
- Balanced distribution
- Rare events
- Category correlations
Detection Methods:
- Frequency analysis
- Chi-square test
- Association rules
- Contingency tables
Pattern Types:
- Normal distribution
- Skewed distribution
- Bimodal distribution
- Uniform distribution
Detection Methods:
- Histogram analysis
- Q-Q plots
- Skewness and kurtosis
- Kolmogorov-Smirnov test
Z-Score Method:
- Calculate mean and std dev
- Z-score = (value - mean) / std dev
- Flag if |Z-score| > 3
IQR Method:
- Calculate Q1 (25th percentile) and Q3 (75th percentile)
- IQR = Q3 - Q1
- Lower bound = Q1 - 1.5 * IQR
- Upper bound = Q3 + 1.5 * IQR
- Flag if value < lower bound or value > upper bound
Methods:
- Moving average deviation
- ARIMA residuals
- Seasonal decomposition anomalies
- Change point detection
- Rate of change anomalies
Methods:
- Isolation Forest
- Local Outlier Factor (LOF)
- Mahalanobis distance
- One-Class SVM
- DBSCAN clustering
| Data Type | Recommended Chart | Use Case |
|---|---|---|
| Single numeric variable | Histogram, Box plot | Distribution analysis |
| Two numeric variables | Scatter plot, Line chart | Correlation, trends |
| Categorical variable | Bar chart, Pie chart | Frequency comparison |
| Time series | Line chart, Area chart | Trend over time |
| Multiple categories | Grouped bar chart, Stacked bar chart | Category comparison |
| Correlation matrix | Heatmap | Multiple variable relationships |
| Part-to-whole | Pie chart, Treemap | Composition analysis |
| Distribution comparison | Violin plot, Box plot | Group comparison |
| Geographic data | Choropleth map, Bubble map | Spatial patterns |
| Hierarchical data | Treemap, Sunburst | Hierarchical structure |
1. Data Overview
Columns: Date, Product, Category, Quantity, Price, Revenue, Region
Rows: 10,000
Date Range: 2024-01-01 to 2024-12-31
Missing Values: 0
Duplicates: 0
2. Descriptive Statistics
Revenue:
Mean: $1,245.67
Median: $987.50
Std Dev: $456.78
Min: $10.00
Max: $4,999.99
Quantity:
Mean: 12.5 units
Median: 10 units
Mode: 5 units
3. Key Findings
- Total Revenue: $12,456,700
- Top Product: "Widget Pro" ($2.5M, 20% of revenue)
- Top Region: "North America" (45% of sales)
- Growth Rate: +15% YoY
- Seasonal Peak: Q4 (November-December, 35% of annual revenue)
- Best Day: December 15 ($125,000)
4. Patterns Detected
- Strong upward trend (+15% growth)
- Clear seasonal pattern (Q4 spike)
- Weekly pattern (higher sales on weekends)
- Category correlation: Electronics products correlate with accessories
5. Anomalies
- 15 days with revenue >3 std dev above mean (promotional events)
- 3 days with zero sales (system downtime)
- 1 product with unusually high return rate (45% vs 5% average)
6. Recommendations
1. Increase inventory for Q4 (35% spike expected)
2. Investigate high return rate for "Product XYZ"
3. Replicate success of "Widget Pro" in other categories
4. Expand marketing in underperforming regions
5. Analyze weekend sales drivers for weekday application
7. Suggested Visualizations
1. Line chart: Revenue over time (show trend and seasonality)
2. Bar chart: Top 10 products by revenue
3. Pie chart: Revenue by region
4. Heatmap: Sales by day of week and hour
5. Scatter plot: Price vs quantity (demand curve)
Always provide:
