data-transform

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.

• 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

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")

```

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['expression_lag1'] = df.groupby('gene')['expression'].shift(1)

df['expression_lag2'] = df.groupby('gene')['expression'].shift(2)

print("✅ New features created")

```

Grouping and Aggregation

```python

# 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

```python

# 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")

```

AnnData to DataFrame Conversion

```python

# 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

```python

# 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

```python

# 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

```python

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")

```

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

Issue: "SettingWithCopyWarning"

Solution: Use .copy() to create explicit copy

```python

df_subset = df[df['expression'] > 10].copy()

df_subset['new_col'] = values # No warning

```

Issue: "Memory error with large datasets"

Solution: Process in chunks

```python

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

```python

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

```python

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

```python

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

```

IMPORTANT: `.dtype` vs `.dtypes` - Common Pitfall!

CORRECT usage:

```python

# 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:

```python

# 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

```python

# 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

| Attribute/Method | Series | DataFrame |

|-----------------|--------|-----------|

| .dtype | ✅ Returns single dtype | ❌ AttributeError |

| .dtypes | ❌ AttributeError | ✅ Returns Series of dtypes |

| .shape | (n,) tuple | (n, m) tuple |

| .values | 1D array | 2D array |

• 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)

• 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