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 execution environment freezing, the agent is strictly prohibited from directly printing the full data or loading it into the Prompt context; it is only allowed to load the necessary data summary 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 from it and save it as a csv file
pdf: read using the \pdf tool, and extract the target data to be analyzed from it and save it as a csv file
md: read directly, and extract the target data to be analyzed from it and save it as a csv file
png, jpg: read using a vision tool, and extract the target data to be analyzed from it and save it as a csv file

2. Data Exploration

Before writing 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, and the specific association method should be saved 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 squeezed into one column, try sep=';' or sep='\t'。

Step 2: Data Quality Check

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

The following is a detailed checklist:

1. Completeness

Check whether there is missingness in the data, which 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 cases where -1, 0, 9999, or "Unknown" are used 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 a character length limit (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 completely identical rows exist.

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 due to differences in case or spaces (for example, "Apple" and "apple")?

3. Accuracy and Validity

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

Data types:

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

Value ranges:

Are the values within a reasonable range? (For example: age should not be negative or exceed 150; a 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 fen?
Enumeration value consistency: Does the same meaning have multiple representations? (For example: "Beijing", "BJ", "北京" should be standardized to the same one).
Cross-table consistency: Do fields with the same meaning in two tables have matching data? (For example: does the total amount in the order table equal the sum of the item amounts in the detail table).

5. Relatedness

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, and Point-biserial correlation coefficients
Distribution analysis: normality, skewness, kurtosis, Q-Q plots
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 plots
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 according to the user's requirements; if there is no explicit requirement, simply provide a data analysis report in md format.

Report types

Executive summary: Analysis process document, providing the user 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: Detailed, rigorous, and clearly worded, ensuring that all statistical data and facts are correct and error-free
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 the report is a PDF or DOCX file, appropriate charts need to be inserted, visual consistency with the text ensured, and a suitable layout designed
Readability: Do not use difficult-to-understand terms like "industry jargon"; avoid unnecessary details; the wording should be simple, easy to understand, and written in a formal style.

Step 4: Standardized 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)
Utility libraries: `tabulate` (pretty printing), `\xlsx`tools, `\pdf`tools, `\docx`tools

Code generation steps

Phase 1: Requirements analysis

Clarify the analysis goals and output requirements based on the analysis process determined in the previous step
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: handle large datasets efficiently

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 computed 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 the 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 can only be made after confirming that the quality gate has been passed.

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. Also, there seem to be 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)。 Findings: column contains date, amount, status, product_id。date in string format, needs conversion.
Step 2 (Quality Check): Completeness: Check amount for null values. Consistency: Check status field, found to contain "Refund" and "Completed". Action: Filter out rows with status == 'Refund' , keep only completed orders.
Steps 3 & 4 (Analysis and Execution): Processing: Convert date to datetime objects, aggregate by month amount。 Code: Use Pandas for resample('M').sum()。 Visualization: Generate a line chart sales_trend.png, annotate the peak month.
Step 5 (Delivery): Output a briefing and line chart: "In the second half of 2023, sales peaked in November (due to the major promotion). After excluding 45 refund records, total revenue was X yuan."

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

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