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. 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 combine independently collected clinical metadata with electrophysiological feature tables for controlled statistical analysis.

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.

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

Tabular CSV datasets loaded directly from day_1/data/eeg_mastersheet.csv can be converted into 5D NumPy arrays (G × T × S × C × F = 3 × 3 × 30 × 32 × 6) using explicit nested loops and dictionary coordinate mapping:

# 1. Load CSV directly from day_1/data folder
df = pd.read_csv("day_1/data/eeg_mastersheet.csv")

# 2. Extract dimension ordering for array axes
groups     = list(df["group"].unique())
timepoints = list(df["timepoint"].unique())
subjects   = list(df["subject_id"].unique())
channels   = list(df["channel"].unique())
features   = list(df["feature"].unique())

# 3. Allocate empty 5D array initialized with NaNs
arr_5d = np.full((3, 3, 30, 32, 6), fill_value=np.nan, dtype=np.float32)

# 4. Build fast coordinate lookup dictionary
value_lookup = {}
for row in df.itertuples(index=False):
    key = (row.group, row.timepoint, row.subject_id, row.channel, row.feature)
    value_lookup[key] = row.power_value

# 5. Populate 5D array via explicit 5-level nested loops
for g_idx, g_name in enumerate(groups):
    for t_idx, t_name in enumerate(timepoints):
        for s_idx, s_name in enumerate(subjects):
            for c_idx, c_name in enumerate(channels):
                for f_idx, f_name in enumerate(features):
                    cell_key = (g_name, t_name, s_name, c_name, f_name)
                    arr_5d[g_idx, t_idx, s_idx, c_idx, f_idx] = value_lookup.get(cell_key, np.nan)

# 6. Save array as .npy file
np.save("day_3/data/eeg_5d_array.npy", arr_5d)

NESTED LOOP & DICTIONARY LOOKUP ADVANTAGE: Reconstructing multi-dimensional tensors using explicit nested loops over extracted dimension axes guarantees exact index alignment without complex sorting, pivoting, or categorical conversions, while preserving missing NaN values.

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

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