8. Pandas Data Handling Missing Values Multi Indexing and Dataset Combination Notes

Course Administration and Lab Logistics

• Lab 1 grading status: Lab 1 was designated as optional and ungraded. Any displayed grades for Lab 1 represent automated misgrading or Canvas system import errors and will be updated.

• Regrading policy: All submitted materials, including Lab 2 grades and feedback, will be comprehensively regraded. Official grade publications will be formally announced on the DLC platform to establish the official date of release.

• Lab weight recalculations: Initial syllabus displays showing individual lab weights at $6.25\%$ resulted from automated course transfer settings from a prior semester. Imported legacy labs were removed, and new labs are generated on a weekly basis. System weights recalculate dynamically as new lab assignments are added.

• Expected lab count: Within a standard $14$-week semester, excluding midterm week and final exam week (leaving approximately $12$ instruction weeks), the course structure anticipates approximately $9$ to $10$ (or up to $11$) total lab assignments.

Missing Value Sentinels in Pandas

• Data science pipelines encounter missing values represented primarily by two sentinel values: Python None and NumPy NaN. Pandas also provides its own native sentinel, pd.NA.

• None Sentinel:

  • Origin: Standard raw Python object.

  • Primary use case: Placeholders for missing object types or text strings.

  • Data type: NoneType.

  • Function execution behavior: Functions lacking an explicit return statement automatically return None. Calling operations expecting return values on NoneType objects raises a TypeError.

  • Error propagation: Does not propagate gracefully through numerical operations; it immediately halts execution with a TypeError.

  • Identity and comparison: None is a single, standardized singleton object in memory. Evaluating None == None returns True.

• NaN Sentinel (np.nan):

  • Origin: NumPy library (representing "Not a Number"), adhering to the IEEE 754 floating-point standard.

  • Primary use case: Replacement placeholder for missing numerical data.

  • Data type: float.

  • Error propagation: Propagates through numerical computations without raising execution errors. If an operand in a matrix multiplication or mathematical array operation is NaN, the final calculated result propagates as NaN.

  • Identity and comparison: Floating-point standards dictate that np.nan is not equal to itself. Evaluating np.nan == np.nan returns False.

• Pandas Native pd.NA Sentinel:

  • Origin: Introduced in Pandas to serve as a unified missing data indicator across standard data types (integers, booleans, strings).

  • Data type: NAType.

  • Propagation and logical behavior: Propagates through arithmetic and comparison operations (pd.NA == pd.NA yields pd.NA). Operates under three-valued logic rules:

  • True or pd.NA evaluates to True.

  • False and pd.NA evaluates to False.

Detection, Filtering, and Imputation of Missing Data

• Detecting missing values:

  • isna() / isnull(): Scans a Pandas Series or DataFrame and generates a Boolean mask of identical dimensions, returning True for missing sentinels (None, NaN, pd.NA) and False for valid observations.

  • notnull() / notna(): Performs the logical inverse of isnull(), returning True for valid non-missing data and False for missing sentinels.

• Filtering missing values (dropna()):

  • Generates a filtered dataset by removing records or features containing missing values.

  • Axis specification:

  • $\text{axis}=0$ (default): Removes entire rows (records) containing missing data.

  • $\text{axis}=1$: Removes entire columns (features) containing missing data.

  • Thresholding (thresh parameter): Retains rows or columns based on a minimum count of non-missing entries. For example, setting a threshold equivalent to $80\%$ of total record length drops features containing more than $20\%$ missing data.

• Imputing missing values (fillna()):

  • Replaces missing sentinels with explicitly declared constants, calculated metrics (e.g., mean, median), or spatial values.

  • Forward filling (method='ffill'): Propagates the last observed valid entry forward to overwrite subsequent missing values.

  • Backward filling (method='bfill'): Propagates the next observed valid entry backward to overwrite preceding missing values.

Hierarchical Indexing (MultiIndex)

• Conceptual overview: MultiIndex structures enable representation of higher-dimensional data within 1D Series or 2D DataFrames without forcing dataset unstacking into sparse tables.

• Dimensional levels: Allows multiple nested levels of indexing along the row axis ($\text{axis}=0$) or column axis ($\text{axis}=1$).

• MultiIndex construction methods:

  • pd.MultiIndex.from_tuples(): Constructs a hierarchical index from a list of tuple pairs.

  • Tuple array format: [('California', 2000), ('California', 2010), ('New York', 2000), ('New York', 2010), ('Texas', 2000), ('Texas', 2010)].

  • Index mapping: The first tuple element maps to the primary (outer) index level; the second element maps to the secondary (inner) index level.

  • pd.MultiIndex.from_product(): Constructs hierarchical levels via the Cartesian product of multiple lists.

  • Row product example: Cartesian product of years [2013, 2014] and visit numbers [1, 2] yields index levels (2013, 1), (2013, 2), (2014, 1), (2014, 2).

  • Column product example: Cartesian product of patient subjects ['Bob', 'Alice', 'Sue'] and clinical metrics ['HR', 'Temp'] yields outer column levels for subjects and inner column levels for metrics.

• Data restructuring and indexing operations:

  • reindex(): Re-aligns flat series data with a MultiIndex object (e.g., pop.reindex(index)).

  • unstack(): Pivots inner row index levels into columns, converting a multi-indexed Series into a 2D DataFrame where primary indices serve as row labels and secondary indices serve as column headers.

  • MultiIndex DataFrame creation syntax:

$\text{data} = \mathbf{np.random.rand}(4, 6) \times 10 + 37$

$\text{df} = \mathbf{pd.DataFrame}(\text{data}, \text{index}=\text{row\_multiindex}, \text{columns}=\text{col\_multiindex})$

• Data extraction and slicing:

  • Extracting outer column groups: df['Bob'] returns all inner column attributes (HR, Temp) across all rows for patient Bob.

  • Extracting specific columns: df['Bob', 'HR'] extracts the heart rate series specifically for Bob.

  • Row extraction via .loc:

  • df.loc[2013] retrieves all rows matching the primary row index 2013 (encompassing visit 1 and visit 2).

  • df.loc[(2013, 2)] retrieves specifically visit 2 of year 2013.

  • Point extraction: Explicitly combining column and row hierarchies in order extracts individual data points.

Combining Datasets: Concatenation (pd.concat)

• Conceptual overview: pd.concat is a top-level Pandas library function (pd.concat([df1, df2, ...])) designed to combine sequence objects (Series, DataFrames) along a designated axis.

• Structural execution:

  • Accepts lists or homogeneous dictionaries of DataFrames or Series.

  • Performs arbitrary multi-object combinations in a single pass.

• Memory behavior: pd.concat creates temporary memory copies of input objects before forming the final combined structure, resulting in temporary memory overhead for large operations.

• Axis alignment settings:

  • $\text{axis}=0$ (default): Stacks objects vertically (row-wise).

  • $\text{axis}=1$: Concatenates objects horizontally (column-wise).

• Set logic options:

  • Outer join (join='outer', default): Computes the union of indices/columns across objects, inserting NaN for non-aligned entries.

  • Inner join (join='inner'): Computes the intersection of indices/columns across objects, retaining only matching axes.

• Label management and duplicate handling:

  • Concatenation preserves duplicate row index numbers and duplicate column names.

  • Accessing a duplicated column label (e.g., result['B']) returns a DataFrame containing all matching column instances rather than a single Series.

  • Index resetting (ignore_index=True): Clears legacy axis labels along the target concatenation dimension, assigning a clean continuous sequential integer range ($0, 1, 2, \dots, N-1$).

• Converting Series for row concatenation:

  • Appending a 1D Series s2 as a row to DataFrame df1 requires transforming the Series into a 1-row DataFrame:

$\mathbf{pd.concat}([df1, s2.\mathbf{to\_frame}().\mathbf{T}], \text{ignore\_index}=\text{True})$

• Calling .to_frame().T (or pd.DataFrame(s2).T) turns a vertical 1-column Series into a horizontal 1-row DataFrame.

Combining Datasets: Relational Merges (pd.merge)

• Conceptual overview: pd.merge is a top-level Pandas function (pd.merge(left, right, on=..., how=...)) that executes SQL-style relational database joins on keys or columns.

• Relational multiplicity types:

  • One-to-one join: Key values exist uniquely ($1$ instance) in both left and right DataFrames.

  • One-to-many / Many-to-one join: Key values exist once in one DataFrame and multiple times in the target DataFrame. Matches replicate the single-instance record across every corresponding multi-instance record.

  • Many-to-many join: Key values exist multiple times across both DataFrames. The join constructs the full Cartesian product of matching rows.

• Join methods (how parameter):

  • how='left': Uses key alignment prioritize to the left DataFrame. Preserves all left rows; missing right matches populate as NaN.

  • how='right': Prioritizes alignment to the right DataFrame. Preserves all right rows; missing left matches populate as NaN.

  • how='inner' (default): Retains only records whose join keys exist in both input DataFrames (intersection).

  • how='outer': Preserves all key records across both input DataFrames (union), inserting NaN where matches are absent.

  • how='cross': Computes the Cartesian product of all rows between both DataFrames, ignoring key column alignment.

• Column suffix management:

  • When merging DataFrames that contain matching non-key column names, Pandas appends default suffixes _x and _y to distinguish left and right features.

  • Custom suffixes can be defined via a tuple (e.g., suffixes=('_left', '_right') or suffixes=('_l', '_r')).

Combining Datasets: Object Joins (DataFrame.join)

• Conceptual overview: DataFrame.join is an instance method defined directly on DataFrame objects (left.join(right)).

• Structural defaults:

  • Merges datasets on row indices by default.

  • Performs a left join by default (how='left'), preserving all row indices of the primary calling DataFrame.

• Parameter support: Accepts standard relational parameters including how='outer', how='inner', how='right', on, lsuffix, and rsuffix.

• Comparative selection guide for dataset combination:

  • pd.concat: Optimal for direct stacking (top-to-bottom or side-by-side) of arrays or DataFrames with uniform labels.

  • pd.merge: Optimal for relational, database-style merging based on key columns.

  • DataFrame.join: Optimal for index-aligned side-by-side joins directly invoked on DataFrame instances.

Questions & Discussion

• Question: If each lab is marked as worth $6.25\%$ on Canvas, does that mean the course contains only four labs total?

  • Response: No. Transferring course materials from the prior semester automatically copied existing lab entries and legacy weight distributions. Legacy labs were removed to allow new labs to be created weekly. Weights dynamically update as new assignments are introduced. Accounting for a $14$-week semester minus midterm and final exam weeks ($12$ instruction weeks), the course will include roughly $9$ to $10$ (or $10$ to $11$) total labs.

• Question: What happens if a backward fill (bfill) is executed on missing trailing values at the end of a dataset where no subsequent valid number exists?

  • Response: Execution does not raise an error. The trailing missing values remain un-filled (or retain default NaN states) because there is no trailing valid number to propagate backward.

• Question: If pd.concat follows set logic (union/intersection), why does horizontal concatenation retain duplicate column names?

  • Response: pd.concat along $\text{axis}=1$ executes an outer join across row indices by default while preserving column headers intact. Duplicate column names across concatenated DataFrames are retained. Accessing a duplicate column header (e.g., result['B']) returns a DataFrame containing all matching columns.

• Question: Can the axis parameter in pd.concat accept values like $-1$ or $2$?

  • Response: Concatenation dimensions for standard 2D DataFrames are strictly defined along $\text{axis}=0$ (rows) and $\text{axis}=1$ (columns).