data-transform

What this skill does

# 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
```python
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
```python
# 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
```python
# 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
```python
# 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
```python
# 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
```python
# 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
```python
# 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
```python
# 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['expressi