data-transform（数据转换）

Data Transformation (Universal)

Overview

This skill enables you to perform comprehensive data transformations including cleaning, normalization, reshaping, filtering, and feature engineering. Unlike cloud-hosted solutions, this skill uses standard Python data manipulation libraries (pandas, numpy, sklearn) and executes locally in your environment, making it compatible with ALL LLM providers including GPT, Gemini, Claude, DeepSeek, and Qwen.

When to Use This Skill

• Clean and preprocess raw data
• Normalize or scale numeric features
• Reshape data between wide and long formats
• Handle missing values
• Filter and subset datasets
• Merge multiple datasets
• Create new features from existing ones
• Convert data types and formats

How to Use

Step 1: Import Required Libraries

import pandas as pd
import numpy as np
from sklearn.preprocessing import StandardScaler, MinMaxScaler, RobustScaler
from sklearn.preprocessing import LabelEncoder, OneHotEncoder
import warnings
warnings.filterwarnings('ignore')

Step 2: Data Cleaning

# Load data
df = pd.read_csv('data.csv')

# Check for missing values
print("Missing values per column:")
print(df.isnull().sum())

# Remove duplicates
df_clean = df.drop_duplicates()
print(f"Removed {len(df) - len(df_clean)} duplicate rows")

# Remove rows with any missing values
df_clean = df_clean.dropna()

# Or fill missing values
df_clean = df.copy()
df_clean['numeric_col'] = df_clean['numeric_col'].fillna(df_clean['numeric_col'].median())
df_clean['categorical_col'] = df_clean['categorical_col'].fillna('Unknown')

# Remove outliers using IQR method
def remove_outliers(df, column, multiplier=1.5):
    Q1 = df[column].quantile(0.25)
    Q3 = df[column].quantile(0.75)
    IQR = Q3 - Q1
    lower_bound = Q1 - multiplier * IQR
    upper_bound = Q3 + multiplier * IQR
    return df[(df[column] >= lower_bound) & (df[column] <= upper_bound)]

df_clean = remove_outliers(df_clean, 'expression_level')
print(f"✅ Data cleaned: {len(df_clean)} rows remaining")

Step 3: Normalization and Scaling

# Select numeric columns
numeric_cols = df.select_dtypes(include=[np.number]).columns

# Method 1: Z-score normalization (StandardScaler)
scaler = StandardScaler()
df_normalized = df.copy()
df_normalized[numeric_cols] = scaler.fit_transform(df[numeric_cols])

print("Z-score normalized (mean=0, std=1)")
print(df_normalized[numeric_cols].describe())

# Method 2: Min-Max scaling (0-1 range)
scaler_minmax = MinMaxScaler()
df_scaled = df.copy()
df_scaled[numeric_cols] = scaler_minmax.fit_transform(df[numeric_cols])

print("\nMin-Max scaled (range 0-1)")
print(df_scaled[numeric_cols].describe())

# Method 3: Robust scaling (resistant to outliers)
scaler_robust = RobustScaler()
df_robust = df.copy()
df_robust[numeric_cols] = scaler_robust.fit_transform(df[numeric_cols])

print("\nRobust scaled (median=0, IQR=1)")
print(df_robust[numeric_cols].describe())

# Method 4: Log transformation
df_log = df.copy()
df_log['log_expression'] = np.log1p(df_log['expression'])  # log1p(x) = log(1+x)

print("✅ Data normalized and scaled")

Step 4: Data Reshaping

# Convert wide format to long format (melt)
# Wide format: columns are different conditions/samples
# Long format: one column for variable, one for value

df_wide = pd.DataFrame({
    'gene': ['GENE1', 'GENE2', 'GENE3'],
    'sample_A': [10, 20, 15],
    'sample_B': [12, 18, 14],
    'sample_C': [11, 22, 16]
})

df_long = df_wide.melt(
    id_vars=['gene'],
    var_name='sample',
    value_name='expression'
)

print("Long format:")
print(df_long)

# Convert long format to wide format (pivot)
df_wide_reconstructed = df_long.pivot(
    index='gene',
    columns='sample',
    values='expression'
)

print("\nWide format (reconstructed):")
print(df_wide_reconstructed)

# Pivot table with aggregation
df_pivot = df_long.pivot_table(
    index='gene',
    columns='sample',
    values='expression',
    aggfunc='mean'  # Can use sum, median, etc.
)

print("✅ Data reshaped")

Step 5: Filtering and Subsetting

# Filter rows by condition
high_expression = df[df['expression'] > 100]

# Multiple conditions (AND)
filtered = df[(df['expression'] > 50) & (df['qvalue'] < 0.05)]

# Multiple conditions (OR)
filtered = df[(df['celltype'] == 'T cell') | (df['celltype'] == 'B cell')]

# Filter by list of values
selected_genes = ['GENE1', 'GENE2', 'GENE3']
filtered = df[df['gene'].isin(selected_genes)]

# Filter by string pattern
filtered = df[df['gene'].str.startswith('MT-')]  # Mitochondrial genes

# Select specific columns
selected_cols = df[['gene', 'log2FC', 'pvalue', 'qvalue']]

# Select columns by pattern
numeric_cols = df.select_dtypes(include=[np.number])
categorical_cols = df.select_dtypes(include=['object', 'category'])

# Sample random rows
df_sample = df.sample(n=1000, random_state=42)  # 1000 random rows
df_sample_frac = df.sample(frac=0.1, random_state=42)  # 10% of rows

# Top N rows
top_genes = df.nlargest(10, 'expression')
bottom_genes = df.nsmallest(10, 'pvalue')

print(f"✅ Filtered dataset: {len(filtered)} rows")

Step 6: Merging and Joining Datasets

# Inner join (only matching rows)
merged = pd.merge(df1, df2, on='gene', how='inner')

# Left join (all rows from df1)
merged = pd.merge(df1, df2, on='gene', how='left')

# Outer join (all rows from both)
merged = pd.merge(df1, df2, on='gene', how='outer')

# Join on multiple columns
merged = pd.merge(df1, df2, on=['gene', 'sample'], how='inner')

# Join on different column names
merged = pd.merge(
    df1, df2,
    left_on='gene_name',
    right_on='gene_id',
    how='inner'
)

# Concatenate vertically (stack DataFrames)
combined = pd.concat([df1, df2], axis=0, ignore_index=True)

# Concatenate horizontally (side-by-side)
combined = pd.concat([df1, df2], axis=1)

print(f"✅ Merged datasets: {len(merged)} rows")

Advanced Features

Handling Missing Values

# Check missing value patterns
missing_summary = pd.DataFrame({
    'column': df.columns,
    'missing_count': df.isnull().sum(),
    'missing_percent': (df.isnull().sum() / len(df) * 100).round(2)
})

print("Missing value summary:")
print(missing_summary[missing_summary['missing_count'] > 0])

# Strategy 1: Fill with statistical measures
df_filled = df.copy()
df_filled['numeric_col'].fillna(df_filled['numeric_col'].median(), inplace=True)
df_filled['categorical_col'].fillna(df_filled['categorical_col'].mode()[0], inplace=True)

# Strategy 2: Forward fill (use previous value)
df_filled = df.fillna(method='ffill')

# Strategy 3: Interpolation (for time-series)
df_filled = df.copy()
df_filled['expression'] = df_filled['expression'].interpolate(method='linear')

# Strategy 4: Drop columns with too many missing values
threshold = 0.5  # Drop if >50% missing
df_cleaned = df.dropna(thresh=len(df) * threshold, axis=1)

print("✅ Missing values handled")

Feature Engineering

# Create new features from existing ones

# 1. Binning continuous variables
df['expression_category'] = pd.cut(
    df['expression'],
    bins=[0, 10, 50, 100, np.inf],
    labels=['Very Low', 'Low', 'Medium', 'High']
)

# 2. Create ratio features
df['gene_to_umi_ratio'] = df['n_genes'] / df['n_counts']

# 3. Create interaction features
df['interaction'] = df['feature1'] * df['feature2']

# 4. Extract datetime features
df['date'] = pd.to_datetime(df['timestamp'])
df['year'] = df['date'].dt.year
df['month'] = df['date'].dt.month
df['day_of_week'] = df['date'].dt.dayofweek

# 5. One-hot encoding for categorical variables
df_encoded = pd.get_dummies(df, columns=['celltype', 'condition'], prefix=['cell', 'cond'])

# 6. Label encoding (ordinal)
le = LabelEncoder()
df['celltype_encoded'] = le.fit_transform(df['celltype'])

# 7. Create polynomial features
df['expression_squared'] = df['expression'] ** 2
df['expression_cubed'] = df['expression'] ** 3

# 8. Create lag features (time-series)
df['expression_lag1'] = df.groupby('gene')['expression'].shift(1)
df['expression_lag2'] = df.groupby('gene')['expression'].shift(2)

print("✅ New features created")

Grouping and Aggregation

# Group by single column and aggregate
cluster_stats = df.groupby('cluster').agg({
    'expression': ['mean', 'median', 'std', 'count'],
    'n_genes': 'mean',
    'n_counts': 'sum'
})

print("Cluster statistics:")
print(cluster_stats)

# Group by multiple columns
stats = df.groupby(['cluster', 'celltype']).agg({
    'expression': 'mean',
    'qvalue': lambda x: (x < 0.05).sum()  # Count significant
})

# Apply custom function
def custom_stats(group):
    return pd.Series({
        'mean_expr': group['expression'].mean(),
        'cv': group['expression'].std() / group['expression'].mean(),  # Coefficient of variation
        'n_cells': len(group)
    })

cluster_custom = df.groupby('cluster').apply(custom_stats)

print("✅ Data aggregated")

Data Type Conversions

# Convert column to different type
df['cluster'] = df['cluster'].astype(str)
df['expression'] = df['expression'].astype(float)
df['significant'] = df['significant'].astype(bool)

# Convert to categorical (saves memory)
df['celltype'] = df['celltype'].astype('category')

# Parse dates
df['date'] = pd.to_datetime(df['date_string'], format='%Y-%m-%d')

# Convert numeric to categorical
df['expression_level'] = pd.cut(df['expression'], bins=3, labels=['Low', 'Medium', 'High'])

# String operations
df['gene_upper'] = df['gene'].str.upper()
df['is_mitochondrial'] = df['gene'].str.startswith('MT-')

print("✅ Data types converted")

Common Use Cases

AnnData to DataFrame Conversion

# Convert AnnData .obs (cell metadata) to DataFrame
df_cells = adata.obs.copy()

# Convert .var (gene metadata) to DataFrame
df_genes = adata.var.copy()

# Extract expression matrix to DataFrame
# Warning: This can be memory-intensive for large datasets
df_expression = pd.DataFrame(
    adata.X.toarray() if hasattr(adata.X, 'toarray') else adata.X,
    index=adata.obs_names,
    columns=adata.var_names
)

# Extract specific layer
if 'normalized' in adata.layers:
    df_normalized = pd.DataFrame(
        adata.layers['normalized'],
        index=adata.obs_names,
        columns=adata.var_names
    )

print("✅ AnnData converted to DataFrames")

Gene Expression Matrix Transformation

# Transpose: genes as rows, cells as columns → cells as rows, genes as columns
df_transposed = df.T

# Log-transform gene expression
df_log = np.log1p(df)  # log1p(x) = log(1+x), avoids log(0)

# Z-score normalize per gene (across cells)
df_zscore = df.apply(lambda x: (x - x.mean()) / x.std(), axis=1)

# Scale per cell (divide by library size)
library_sizes = df.sum(axis=1)
df_normalized = df.div(library_sizes, axis=0) * 1e6  # CPM normalization

# Filter low-expressed genes
min_cells = 10  # Gene must be expressed in at least 10 cells
gene_mask = (df > 0).sum(axis=0) >= min_cells
df_filtered = df.loc[:, gene_mask]

print(f"✅ Filtered to {df_filtered.shape[1]} genes")

Differential Expression Results Processing

# Assuming deg_df has columns: gene, log2FC, pvalue, qvalue

# Add significance labels
deg_df['regulation'] = 'Not Significant'
deg_df.loc[(deg_df['log2FC'] > 1) & (deg_df['qvalue'] < 0.05), 'regulation'] = 'Up-regulated'
deg_df.loc[(deg_df['log2FC'] < -1) & (deg_df['qvalue'] < 0.05), 'regulation'] = 'Down-regulated'

# Sort by significance
deg_df_sorted = deg_df.sort_values('qvalue')

# Top upregulated genes
top_up = deg_df[deg_df['regulation'] == 'Up-regulated'].nlargest(20, 'log2FC')

# Top downregulated genes
top_down = deg_df[deg_df['regulation'] == 'Down-regulated'].nsmallest(20, 'log2FC')

# Create summary table
summary = deg_df.groupby('regulation').agg({
    'gene': 'count',
    'log2FC': ['mean', 'median'],
    'qvalue': 'min'
})

print("DEG Summary:")
print(summary)

# Export results
deg_df_sorted.to_csv('deg_results_processed.csv', index=False)
print("✅ DEG results processed and saved")

Batch Processing Multiple Files

import glob

# Find all CSV files
file_paths = glob.glob('data/*.csv')

# Read and combine
dfs = []
for file_path in file_paths:
    df = pd.read_csv(file_path)
    # Add source file as column
    df['source_file'] = file_path.split('/')[-1]
    dfs.append(df)

# Combine all
df_combined = pd.concat(dfs, ignore_index=True)

print(f"✅ Processed {len(file_paths)} files, total {len(df_combined)} rows")

Best Practices

1. Check Data First: Always use df.head(), df.info(), df.describe() to understand data
2. Copy Before Modify: Use df.copy() to avoid modifying original data
3. Chain Operations: Use method chaining for readability: df.dropna().drop_duplicates().reset_index(drop=True)
4. Index Management: Reset index after filtering: df.reset_index(drop=True)
5. Memory Efficiency: Use categorical dtype for low-cardinality string columns
6. Vectorization: Avoid loops; use vectorized operations (numpy, pandas built-ins)
7. Documentation: Comment complex transformations
8. Validation: Check data after each major transformation

Troubleshooting

Issue: "SettingWithCopyWarning"

Solution: Use .copy() to create explicit copy

df_subset = df[df['expression'] > 10].copy()
df_subset['new_col'] = values  # No warning

Issue: "Memory error with large datasets"

Solution: Process in chunks

chunk_size = 10000
chunks = []
for chunk in pd.read_csv('large_file.csv', chunksize=chunk_size):
    # Process chunk
    processed = chunk[chunk['expression'] > 0]
    chunks.append(processed)

df = pd.concat(chunks, ignore_index=True)

Issue: "Key error when merging"

Solution: Check column names and presence

print("Columns in df1:", df1.columns.tolist())
print("Columns in df2:", df2.columns.tolist())

# Use left_on/right_on if names differ
merged = pd.merge(df1, df2, left_on='gene_name', right_on='gene_id')

Issue: "Data types mismatch in merge"

Solution: Ensure consistent types

df1['gene'] = df1['gene'].astype(str)
df2['gene'] = df2['gene'].astype(str)
merged = pd.merge(df1, df2, on='gene')

Issue: "Index alignment errors"

Solution: Reset index or specify ignore_index=True

df_combined = pd.concat([df1, df2], ignore_index=True)

Critical API Reference - DataFrame vs Series Attributes

IMPORTANT: .dtype vs .dtypes - Common Pitfall!

CORRECT usage:

# For DataFrame - use .dtypes (PLURAL) to get all column types
df.dtypes  # Returns Series with column names as index, dtypes as values

# For a single column (Series) - use .dtype (SINGULAR)
df['column_name'].dtype  # Returns single dtype object

# Check specific column type
if df['expression'].dtype == 'float64':
    print("Expression is float64")

# Check all column types
print(df.dtypes)  # Shows dtype for each column

WRONG - DO NOT USE:

# WRONG! DataFrame does NOT have .dtype (singular)
# df.dtype  # AttributeError: 'DataFrame' object has no attribute 'dtype'

# WRONG! This will fail
# if df.dtype == 'float64':  # ERROR!

DataFrame Type Inspection Methods

# Get dtypes for all columns
df.dtypes

# Get detailed info including dtypes
df.info()

# Check if column is numeric
pd.api.types.is_numeric_dtype(df['column'])

# Check if column is categorical
pd.api.types.is_categorical_dtype(df['column'])

# Select columns by dtype
numeric_cols = df.select_dtypes(include=['number'])
string_cols = df.select_dtypes(include=['object', 'string'])

Series vs DataFrame - Key Differences

Technical Notes

• Libraries: Uses pandas (1.x+), numpy, scikit-learn (widely supported)
• Execution: Runs locally in the agent's sandbox
• Compatibility: Works with ALL LLM providers (GPT, Gemini, Claude, DeepSeek, Qwen, etc.)
• Performance: Pandas is optimized with C backend; most operations are fast for <1M rows
• Memory: Pandas DataFrames store data in memory; use chunking for very large files
• Precision: Numeric operations use float64 by default (can use float32 to save memory)

References

• pandas documentation: https://pandas.pydata.org/docs/
• pandas user guide: https://pandas.pydata.org/docs/user_guide/index.html
• scikit-learn preprocessing: https://scikit-learn.org/stable/modules/preprocessing.html
• pandas cheat sheet: https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf