Day 3: Tabular CSV to Array Conversion & Relational Merging

1

Why We Merge Data Tables in Real-World Research

In clinical neuroscience, datasets are rarely recorded as a single monolithic file. Electrophysiological hardware exports high-density signal features (spectral power, band ratios), whereas clinical Electronic Health Record (EHR / REDCap) platforms track participant medical charts (Age, Sex, BMI, clinical scores).

1. Separation of Concerns

Keeping raw signal acquisition decoupled from REDCap metadata prevents data corruption and streamlines multi-site clinical trials.

2. Memory & Storage Efficiency (M:1)

A single visit yields 192 feature rows (32 channels × 6 bands). Storing static demographics in every raw feature row bloats storage.

3. Controlling Confounders

Age, Sex, and BMI heavily modulate brain signals (e.g. age-related alpha slowing). Merging demographics is required for ANCOVA and LMM models.

REAL-WORLD RATIONALE: Merging data tables via programmatic joins enables researchers to isolate true neurological signals from demographic covariates without inflating file sizes during recording.

2

Non-Unique Subject Identifiers & Key Lookup

A common pitfall in multi-group studies is assuming subject_id is globally unique. In our dataset, each study group (Control, Patient, Treatment) labels participants as Sub_01 through Sub_30 across experimental timepoints (Baseline, Task, Rest).

WATCH OUT FOR COMPOSITE KEYS: Because Sub_01 appears 9 separate times across the study, joining tables requires the composite key: ['group', 'timepoint', 'subject_id'].

group	timepoint	subject_id	channel	feature	power_value	age	gender	bmi
Control	Baseline	Sub_01	Fp1	delta	0.397215	58	M	25.3
Patient	Baseline	Sub_01	Fp1	delta	0.489120	61	F	28.1
Treatment	Task	Sub_01	Cz	alpha	0.612450	44	M	22.9

3

Merging Demographics & Defensive Validation

When joining relational tables in Pandas, defensive programmers enforce validation parameters such as validate="many_to_one" (or "m:1") to guarantee that the right table contains exactly one unique row per composite key.

PYTHON / PANDAS

import pandas as pd

# 1. Load feature mastersheet and sociodemographics CSVs
df_features = pd.read_csv("data/eeg_mastersheet.csv")
df_demo     = pd.read_csv("data/sociodemographics.csv")

# 2. Defensive Sanity Check: Ensure right key has zero duplicates
composite_key = ["group", "timepoint", "subject_id"]
assert not df_demo.duplicated(subset=composite_key).any(), "Duplicate demographic keys detected!"

# 3. Relational Left Join with explicit many_to_one validation
df_enriched = pd.merge(
    df_features,
    df_demo,
    on=composite_key,
    how="left",
    validate="many_to_one"  # Validates that right table has unique keys per left row
)

# 4. Post-Merge Data Integrity Sanity Assertions
assert len(df_enriched) == len(df_features), "Row count changed during merge!"
assert df_enriched[["age", "gender", "bmi"]].isnull().sum().sum() == 0, "Unmatched demographics!"

df_enriched.to_csv("data/eeg_mastersheet_enriched.csv", index=False)

🛡️

MERGE VALIDATION RESULT

Enriched DataFrame: 51,840 rows × 10 columns • 0 Missing Demographics

4

Reshaping Tabular CSV into 2D Matrices & 5D Tensors

Downstream analytics require pivoting long tabular DataFrames into matrix and tensor formats suitable for Scikit-Learn or MNE indexing:

PYTHON / SCALARS & MATRICES

# A. Pivot frequency bands into 2D Feature Matrix (8,640 samples × 6 band features)
df_wide = df.pivot(
    index=["group", "timepoint", "subject_id", "channel", "region"],
    columns="feature",
    values="power_value"
).reset_index()

feature_cols = ["delta", "theta", "alpha", "beta", "gamma", "broadband"]
X_2d = df_wide[feature_cols].to_numpy()  # Shape: (8640, 6)

# B. Convert CSV to 5D Signal Tensor Array (3 × 3 × 30 × 32 × 6)
df["group"]      = pd.Categorical(df["group"], categories=groups, ordered=True)
df["timepoint"]  = pd.Categorical(df["timepoint"], categories=timepoints, ordered=True)
df["subject_id"] = pd.Categorical(df["subject_id"], categories=subjects, ordered=True)
df["channel"]    = pd.Categorical(df["channel"], categories=channels, ordered=True)
df["feature"]    = pd.Categorical(df["feature"], categories=features, ordered=True)

df_sorted = df.sort_values(by=["group", "timepoint", "subject_id", "channel", "feature"])
arr_5d = df_sorted["power_value"].to_numpy().reshape(3, 3, 30, 32, 6)

ORDERED CATEGORICAL TIP: Standard string sorting arranges timepoints alphabetically ('Baseline' before 'Rest', placing 'Task' last). Using pd.Categorical(..., ordered=True) forces sorting to follow your exact metadata dimension lists (['Baseline', 'Task', 'Rest']).

5

Feature Matrix Dynamics & Deletion Strategies

In machine learning and tabular EEG processing, data is represented as a feature matrix X ∈ ℝ^{N × P}, where rows (N = 8,640) represent sample observations and columns (P = 6) represent frequency band features (delta, theta, alpha, beta, gamma, broadband). Selecting the appropriate deletion strategy requires balancing sample retention (N) against feature richness (P).

1. Sample-wise (Listwise) Row Deletion

Drops an entire sample row x_i if any single feature x_ij = NaN. Preserves all P features, but reduces sample size N. Unbiased under MCAR, but vulnerable to sample loss cascades.

2. Feature-wise (Column) Deletion

Drops an entire feature column f_j if missingness in that feature exceeds a threshold (e.g. >5%). Preserves all N sample rows, but reduces feature dimension P.

3. Pairwise Feature Analysis

Calculates feature-feature summary statistics (means via np.nanmean, Pearson correlations via df.corr()) over non-null sample pairs without discarding whole rows or columns.

4. Subject / Visit-wise Deletion

Filters out an entire participant or timepoint visit when data completeness drops below critical operational thresholds (e.g. unrecorded visits).

PYTHON / NUMPY & PANDAS MATRIX OPERATIONS

# 1. Feature Matrix Construction: X in R^(N x P) (8,640 samples x 6 features)
X_2d = df_wide[feature_cols].to_numpy()

# 2. Sample-wise (Listwise) Row Deletion: Drops row i if ANY x_ij is NaN
valid_rows = ~np.isnan(X_2d).any(axis=1)
X_clean    = X_2d[valid_rows]  # Preserves P=6 features, reduces N from 8,640 to 7,860

# 3. Feature-wise (Column) Deletion: Drops column j if NaN rate > 5%
nan_pct_per_feature = np.isnan(X_2d).mean(axis=0)
X_kept_features     = X_2d[:, nan_pct_per_feature < 0.05]  # Preserves N=8,640 samples

# 4. Pairwise Feature Correlation Matrix
corr_pairwise = df_wide[feature_cols].corr()  # Pairwise Pearson correlation over valid pairs

THE MULTI-FEATURE DATA LOSS CASCADE:
When dealing with P feature columns, small missingness rates per feature compound exponentially during Listwise Deletion. Even if individual features have only ~1.5–1.9% missing values, their union across 6 features causes 9.03% of all sample rows (780 samples) to be deleted! Always check individual feature missingness before applying listwise row deletion.

THEORETICAL GROUNDING — MISSINGNESS MECHANISMS:
• MCAR (Missing Completely at Random): Missingness is independent of any feature values (e.g., random ADC glitch). Listwise deletion yields unbiased estimates.
• MAR (Missing at Random): Missingness depends on observed features (e.g., higher age participants missing gamma power). Requires model covariates.
• MNAR (Missing Not at Random): Missingness depends on unobserved values (e.g., severe artifact saturation in high-power states). Listwise deletion induces severe bias!

6

Pandas Data Integrity & Sanity Checklist

In scientific computing, silent failure is far more hazardous than explicit exceptions. Always check these invariants:

1. Invariant Row Count

Assert len(df_merged) == len(df_left) during left joins to catch unintended Cartesian row explosions.

2. Zero Null Merges

Assert df_merged[new_cols].isnull().sum().sum() == 0 to verify 100% key matching across demographic tables.

3. Key Uniqueness

Assert not df.duplicated(subset=keys).any() on metadata lookup tables prior to merging.

4. Shape Invariants

Assert X_2d.shape == (8640, 6) and track np.isnan(arr_5d).sum() before passing features downstream.