data-analysis

Data analysis skill

Supported input data types

Tabular data: csv, xlsx
Data contained in text: docx, pdf, md
Data contained in images: png, jpg

Step 1: Data reading and exploration

To prevent Token overflow and the execution environment from freezing, the agent is strictly prohibited from directly printing the full dataset or loading it into the Prompt context; only the necessary data summary is allowed to be loaded into the context.

1. Data reading and saving

Different reading methods should be used for different types of data:

csv: Read directly using the pandas library
xlsx: Read using the pandas library or the \xlsx tool
docx: Read using the \docx tool, and extract the target data to be analyzed and save it as a csv file
pdf: Read using the \pdf tool, and extract the target data to be analyzed and save it as a csv file
md: Read directly, and extract the target data to be analyzed and save it as a csv file
png, jpg: Read using a visual tool, and extract the target data to be analyzed and save it as a csv file

2. Data exploration

Before writing the formal analysis code, a lightweight exploration code must first be executed. Save the code to the workspace.

Principles:

Look only at the appearance, not the whole picture: Obtain only metadata and a small number of samples.

Metadata first: Column names (Columns) and data types (Dtypes) must be obtained.

Sample limit: Strictly limit the number of rows read (n=5 is recommended).

Explore all files: If there are multiple files to be analyzed, all files need to be explored one by one, and possible associations between files should be checked; save the specific association method as a csv document or md file for reference in subsequent analysis.

Standard exploration code pattern (Python example):


import pandas as pd

# 设定文件路径
file_path = 'target_data.csv'

try:
    # 1. 仅读取前5行，避免加载大文件占用内存
    # 注意：对于非CSV文件 (如 Parquet, Excel)，使用相应的 read_ function 并配合 head()
    if file_path.endswith('.csv'):
        df_preview = pd.read_csv(file_path, nrows=5)
    elif file_path.endswith('.parquet'):
        df_preview = pd.read_parquet(file_path).head(5)
    elif file_path.endswith(('.xls', '.xlsx')):
        df_preview = pd.read_excel(file_path, nrows=5)
    
    # 2. 输出关键元数据供智能体思考
    print("=== 数据预览 (前5行) ===")
    print(df_preview.to_markdown(index=False))
    
    print("\n=== 列信息与类型 ===")
    print(df_preview.info())
    
except Exception as e:
    print(f"摄入错误: {e}")

3. Encoding and format adaptation

Encoding fallback: If reading fails, try `encoding='gbk'` or `encoding='latin1'`。
Delimiter: If the data is crammed into one column, try sep=';' or sep='\t'。

Step 2: Data quality check

Before performing any analysis, a "health check" must be done first. This can avoid "garbage in, garbage out." The check results need to be included in the final report.

The following is the detailed checklist:

1. Completeness

Check whether the data has missing values, as this directly affects statistical bias.

Missing values:

Check the proportion of NULL, NaN, or empty strings in the fields.

Watch out for "pseudo-missing values", such as using -1, 0, 9999, or "Unknown" to replace missing values.

Record missingness:

Does the data volume meet expectations? (For example: yesterday's logs should have 1 million entries, but actually only 200,000).
Is the time series continuous? (For example: are the transaction data for certain days missing).

Truncation:

Is the text field truncated due to character length limits (for example, an incomplete JSON string).

2. Uniqueness

Check whether the data contains duplicates, which would cause metrics to be inflated.

Exact duplicates: Check whether there are completely identical rows.

Primary key duplicates: Is the ID field unique? (For example: the same UserID should not appear in two rows of the user table).

Business entity duplicates: Is the same business object recorded multiple times because of differences in case or spaces (for example, "Apple" and "apple").

3. Accuracy and Validity

Check whether the data conforms to the defined format and real-world physical rules.

Data type:

Are strings mixed into numeric fields?
Are date fields actually in date format?

Value range:

Are the values within a reasonable range? (For example: age should not be negative or exceed 150; discount rate should not be greater than 100%).
Are probability values between 0 and 1?

4. Consistency

Check whether the data is unified across different dimensions or sources.

Unit consistency: Is distance in meters or kilometers? Is the amount in yuan or cents?
Enum value consistency: Does the same meaning have multiple expressions? (For example: "Beijing", "BJ", "北京" should be standardized to the same one).
Cross-table consistency: Do fields with the same meaning in two tables match in data? (For example: does the total amount in the order table equal the sum of the amounts of each item in the detail table).

5. Relationships

If there are relationships among multiple files, check whether the relationships are broken.
Foreign key constraints: Can all IDs in the fact table be found in the dimension table? (For example: does the product_id in the order table exist in the product table? If not, it is "orphan data").

Code example:

import pandas as pd
# --- 1. 完整性检查 (Completeness) ---
for col in COLS_CHECK_MISSING:
    # 检查 NULL/NaN
    missing_count = df[col].isnull().sum()
    # 检查空字符串 (如果是字符串列)
    empty_str_count = (df[col] == '').sum() if df[col].dtype == 'object' else 0
    total_missing = missing_count + empty_str_count
    if total_missing > 0:
        print(f"  -> 警告: 列 '{col}' 存在 {total_missing} 条缺失数据")
    else:
        print(f"  -> 通过: 列 '{col}' 无缺失")
# --- 2. 唯一性检查 (Uniqueness) ---
if df[COL_PRIMARY_KEY].duplicated().any():
    duplicate_count = df[COL_PRIMARY_KEY].duplicated().sum()
    print(f"  -> 警告: 主键 '{COL_PRIMARY_KEY}' 发现 {duplicate_count} 条重复记录")
    # 可选：查看重复样本
    # print(df[df[COL_PRIMARY_KEY].duplicated(keep=False)])
else:
    print(f"  -> 通过: 主键唯一")
# --- 3. 准确性检查 (Accuracy) ---
# 过滤出不符合规则的行
invalid_rows = df[df[COL_NUMERIC] < MIN_VALUE_THRESHOLD]
if not invalid_rows.empty:
    print(f"  -> 警告: 发现 {len(invalid_rows)} 条数据小于最小值 {MIN_VALUE_THRESHOLD}")
else:
    print(f"  -> 通过: 数值范围正常")
# --- 4. 一致性检查 (Consistency) ---
# 找出不在标准列表中的值
# fillna(False) 是为了处理空值情况，避免报错
invalid_mask = ~df[COL_CATEGORY].isin(VALID_CATEGORIES) & df[COL_CATEGORY].notnull()
invalid_cats = df.loc[invalid_mask, COL_CATEGORY].unique()
if len(invalid_cats) > 0:
    print(f"  -> 警告: 列 '{COL_CATEGORY}' 包含非标准值: {invalid_cats}")
else:
    print(f"  -> 通过: 枚举值一致")
# --- 5. 关联性检查 (Integrity) ---
# 注意：此步需要有一个参考表 (ref_df)
try:
    # 找出在当前表中存在，但在参考表(ref_df)中找不到的 ID
    # 同样排除了当前表外键为空的情况
    orphan_mask = ~df[COL_FOREIGN_KEY].isin(ref_df[COL_REF_KEY]) & df[COL_FOREIGN_KEY].notnull()
    orphan_count = orphan_mask.sum()
    if orphan_count > 0:
        print(f"  -> 警告: 发现 {orphan_count} 条“孤儿数据” (在维表中找不到对应记录)")
    else:
        print(f"  -> 通过: 外键关联完整")
except NameError:
    print("  -> 跳过: 未定义 ref_df (维表)，无法进行关联检查")

Step 3: Determine the workflow

Determine the specific workflow according to the task requirements, prioritize the analysis methods specified by the user, and do not use analysis methods unrelated to the task. The workflow generally includes data processing, analysis, and visualization presentation.

Core analysis methods

1. Statistical analysis

Descriptive statistics: Mean, median, standard deviation, quartiles, percentiles
Inferential statistics: Hypothesis testing, confidence intervals, P-values
Correlation analysis: Pearson, Spearman, Kendall, Point-biserial correlation coefficients
Distribution analysis: Normality, skewness, kurtosis, Q-Q plot
Advanced statistical tests: Analysis of variance (ANOVA), t-test, chi-square test, non-parametric tests

2. Pattern discovery

Trend analysis and time series decomposition
Seasonal pattern detection and forecasting
Clustering and segmentation: K-means, hierarchical clustering, DBSCAN
Association rule mining and market basket analysis
Feature engineering and feature selection
Dimensionality reduction techniques: PCA (Principal Component Analysis), t-SNE, UMAP

3. Business intelligence

KPI analysis and dashboard design
Customer Segmentation and Profiling
Market Basket Analysis and Recommendation Systems
Churn Prediction and Customer Lifetime Value (CLV)
A/B Testing and Experimental Design
ROI (Return on Investment) Analysis and Business Impact Assessment
Summary and Actionable Recommendations

4. Exploratory Techniques

Univariate Analysis: Distributions, Statistics, Visualization
Bivariate Analysis: Correlation, Comparison, Relationships
Multivariate Analysis: Regression, Clustering, Classification
Time Series Analysis: Trends, Seasonality, Forecasting
Categorical Data Analysis: Frequencies, Contingency Tables, Association
Spatial Analysis and Geographic Patterns
Text Analysis and Natural Language Processing (NLP)

Core Visualization Methods

The goal is to make data easy to understand and insightful through thoughtful visual design. Every visualization should be clear and help users make better decisions.

For Numerical Data

Distribution: Histograms, box plots, violin plots, density plots
Comparison: Bar charts, line charts, scatter plots
Relationships: Scatter plots, correlation matrices, heatmaps
Composition: Stacked bar charts, pie charts, treemaps
Trends: Line charts, area charts, moving average charts

For Categorical Data

Frequency: Bar charts, pie charts, donut charts
Comparison: Grouped bar charts, stacked bar charts
Relationships: Heatmaps, mosaic plots, parallel sets
Composition: Treemaps, sunburst charts

For Time Series Data

Trends: Line charts, area charts, smoothed curves
Seasonality: Seasonal decomposition, heatmaps
Comparison: Multiple line charts, faceted plots
Distribution: Time-based box plots, violin plots

Advanced Techniques

Interactive Dashboards: Create multi-panel dashboards, add filters and controls
Animated Visualizations: Show changes over time, illustrate complex processes

Report Writing

Determine the required report type based on the user's request; if there is no explicit requirement, simply provide a data analysis report in md format.

Report Types

Executive Summary: A document of the analysis process, providing users with the entire process of task execution
Business Data Analysis Report: Data-driven business insights, summarizing and analyzing the results of the data analysis
Dashboard Documentation: Interactive chart descriptions

Report Format

markdown、pdf、docx

Report Style and Content

Accuracy: Meticulous, rigorous, and clear in language, ensuring that all statistics and facts are correct and accurate
Logic: The logical development from data to insights
Professionalism: Understand key findings and statistical significance, and identify patterns and trends in the data
Based on the data: Build an in-depth narrative around the data findings
Actionable insights: Clear recommendations and next steps
Visual integration: If it is a PDF or DOCX report, appropriate charts need to be inserted, and consistency between visuals and text must be ensured, with an appropriate layout designed
Readability: Do not use difficult-to-understand terms like "industry jargon"; avoid unnecessary details; use simple, easy-to-understand, and formal wording.

Step 4: Standardize code generation and execution

Programming language

Python: Pandas, NumPy, Scikit-learn, Matplotlib, Seaborn, Plotly, TensorFlow, PyTorch
R: Tidyverse, ggplot2, dplyr, caret, shiny, data.table
SQL: PostgreSQL, MySQL, SQLite, BigQuery, Redshift, Snowflake
JavaScript: D3.js, Plotly.js, Chart.js, TensorFlow.js, Node.js

Common technology stack (Python)

Core processing: `pandas`, `numpy
Statistics and mining: `scipy`, `sklearn` (if needed)
Visualization: `matplotlib`, `seaborn` (static); `plotly` (interactive)
Tool libraries: `tabulate` (pretty printing), `\xlsx`tool, `\pdf`tool, `\docx`tool

Code generation steps

Phase 1: Requirements analysis

Based on the analysis process determined in the previous step, clarify the analysis objectives and output requirements
Choose an appropriate programming language (if not explicitly specified, default to Python)
Choose libraries and frameworks
Consider performance and scalability requirements

Phase 2: Structural design

Design a modular code structure, breaking complex tasks into small, focused functions
Break complex tasks into manageable functions
Plan for reusability and maintainability
Pay attention to memory management: efficiently handle large datasets

Phase 3: Implementation

Write concise and efficient code
Add appropriate error handling
Add clear and concise comments
Execute the code to obtain results

Error handling

Data loading errors: handle missing files, format issues, and encoding issues.
Memory issues: implement chunking or data sampling.
Performance issues: optimize algorithms, use caching and parallel processing.
Dependency issues: check library versions and provide fallback options.

Step 5: Result validation and output

Before presenting the results to the user, the following self-check list must be executed:

Completeness check:

Have all result files (.png/.html/.xlsx/.pdf, etc.) been successfully generated and do they exist in the workspace?
Are the key calculation results (such as KPIs and statistics) non-empty and numerically reasonable?
Do the output files (such as CSV) contain data and have correct headers?
Have all result files required by the user been fully generated?

Format consistency:

Do the charts have titles, axis labels, and legends?
Is the report font consistent, and is the heading hierarchy clear?
Are the images in the report appropriately placed, with clear labels and explanations?

Delivery is allowed only after passing the quality gate.

Example

Example 1: Basic sales data trend analysis (structured data)

User instruction: "Help me analyze sales_2023.csv, I want to see the sales trend for the second half of the year, and it seems there are some refund orders in the data, remember to handle them."

Agent execution flow:

Step 1 (Data exploration): Execute pd.read_csv('sales_2023.csv', nrows=5)。 Found: columns contain date, amount, status, product_id。date is in string format and needs to be converted.
Step 2 (Quality check): Completeness: Check whether amount has null values. Consistency: Check the status field and found it contains "Refund" and "Completed". Action: Filter out the rows where status == 'Refund' , keeping only completed orders.
Step 3 & 4 (Analysis and execution): Processing: Convert date to datetime objects, aggregate amount。 by month. Code: Use Pandas for resample('M').sum()。 Visualization: Generate a line chart sales_trend.png, annotate the peak month.
Step 5 (Delivery): Output a brief and a line chart: "Sales in the second half of 2023 peaked in November (affected by the major promotion). After excluding 45 refund records, the total revenue is X yuan."

Example 2: Cross-file correlation analysis (multimodal/complex scenario)

User instruction: "I have a definition file for product categories categories.xlsx, and also a recent user feedback log feedback.pdf. Please analyze which product category has the most negative feedback."