Comprehensive Study Notes: Data Wrangling, Missing Data, and Data Inspection

Overview of Data Wrangling and Data Management

• Data Wrangling Definition:

  • Data wrangling is formally defined as the comprehensive process of retrieving, cleansing, integrating, transforming, and adjusting data to prepare it for statistical analysis.

  • Colloquially, it represents the technical process of cleaning raw data, transforming variable formats, and organizing information into a usable structure.

  • In real-world applications, raw data downloaded from online databases or public repositories is rarely clean, organized, or immediately ready for empirical analysis.

  • Data analysts spend the vast majority of their time executing data wrangling tasks rather than running statistical models.

• Data Management Definition:

  • Data management refers specifically to the organizational architecture and systematic processes used to store, maintain, and retrieve data efficiently.

• Structured versus Unstructured Data:

  • Structured Data: Data organized in a standardized row-and-column table format. Observations (cases or records) occupy individual rows along the vertical axis, while variables (attributes or measures) occupy columns across the horizontal axis. This class relies exclusively on structured data.

  • Unstructured Data: Non-traditional data forms that do not fit standard tabular formats.

  • Textual Analysis: A prominent subfield of unstructured data analysis that has expanded significantly over the past 15 years. It applies quantitative methodologies to extract numerical patterns from raw text, contrasting with qualitative textual interpretations performed in humanities disciplines like English, Sociology, or Psychology.

• Dummy Variables:

  • Dummy variables are binary indicators represented numerically as either $1$ or $0$.

  • They are used to convert qualitative or categorical attributes into numerical values for regression analysis and econometric procedures.

Initial Data Inspection and Identifying Anomalies

• Data Inspection Goals:

  • Before performing analytical procedures, analysts must inspect raw variables to understand their measurement scales, values, missingness, and underlying distributional shapes.

  • Initial diagnostic checks include generating frequency tables, identifying minimum and maximum values, locating missing value counts, and assessing category sizes for variables such as race, geography, or income.

• Systemic Data Coding Anomalies (The Census Bureau Example):

  • The United States Census Bureau conducts the decennial census every 10 years to gather comprehensive demographic data across the country.

  • In several Census Bureau datasets, non-responses or missing observations for income are explicitly coded using nine nines: $999,999,999$.

  • An analyst who fails to inspect the data thoroughly might misinterpret this missing value code as true numeric income, erroneously treating missing values as individuals earning $900,000,000.

• Limitations of Automated Tools and AI:

  • Relying blindly on artificial intelligence tools (e.g., ChatGPT) to process Excel spreadsheets automatically prevents analysts from developing necessary domain-specific familiarity with their datasets.

  • Manual inspection is necessary to detect variable-level edge cases, subtle coding schemas, and missing value indicators.

Handling Missing Data: Omission versus Imputation

• Context and Causes of Missing Values:

  • Missing values are common in large-scale social science and government surveys.

  • Sensitive Questions and Non-Response: Respondents frequently refuse to answer sensitive items. For example, the 2020 Decennial Census added an explicit question inquiring about respondents' United States citizenship status. This addition caused non-response rates and incomplete survey submissions to rise sharply.

  • Identifiability in Niche Samples: In a statewide banking study conducted alongside Dr. Bockernie, thousands of banking survey responses were collected across South Dakota. In small rural towns with few bank employees, respondents omitted location and employer details out of fear that their identities could be inferred.

• Selection Bias:

  • When data is missing systematically rather than randomly, the remaining sample exhibits selection bias.

  • If specific subgroups systematically withhold responses due to underlying traits, inferences drawn from the observed sample will fail to generalize to the full population.

• Missing Data in Panel Datasets:

  • Panel data tracks identical entities (e.g., individuals, firms, or regions) across multiple consecutive time periods.

  • Missing observations for a single entity in even one time period break the balance of the panel, complicating longitudinal tracking.

• Strategy 1: Omission (Case Deletion):

  • Omission involves completely removing cases or rows that contain missing values.

  • Statistical packages and software tools like Excel require complete, fully populated matrices to execute advanced analytical operations without errors.

  • Proportional Thresholds: Omission is appropriate if missing values constitute a small proportion of the total sample—specifically between $5\%$ and $10\%$.

  • Warning Thresholds: If $25\%$ or more of the observations are missing, dropping rows causes severe data loss and risks introducing selection bias.

  • Documentation: Analysts must document an explicit paper trail detailing the exact logical sequence and rules used to omit incomplete cases.

• Strategy 2: Imputation:

  • Imputation is the process of replacing missing values with logically estimated or mathematically derived values.

• Quantitative Imputation Techniques:

  • Mean Imputation: Missing observations are filled using the arithmetic mean calculated from observed cases.

    • Advantage: Mean imputation is computationally simple and leaves the central tendency intact. Crucially, imputing the mean does not increase the overall variability (variance or standard deviation) of the dataset, as added points cluster at the exact center.

    • Limitation: Mean imputation distorts summary statistics when applied to skewed distributions.

  • Normal Distribution Equivalence: In a symmetric, bell-shaped normal distribution, the mean and median are identical:         $\text{mean} = \text{median}$         Imputing either metric preserves distribution width.

  • Positively (Right) Skewed Distributions: Right-skewed distributions feature a long tail extending toward high values along the positive x-axis. Extreme values pull the mean above the median:         $\text{mean} > \text{median}$         Using mean imputation on right-skewed numeric data (e.g., dollar-denominated variables like income, home values, or tuition) overestimates missing observations and further inflates skewness.

  • Negatively (Left) Skewed Distributions: Left-skewed distributions feature a long tail extending toward low values along the negative x-axis. Extreme low values pull the mean below the median:         $\text{mean} < \text{median}$

  • Median Imputation: When variables are measured in monetary units (dollars), median imputation is preferred over mean imputation because the median is resistant to extreme skewness.

  • Regression Imputation: An advanced quantitative technique where an econometric model estimates a dependent variable as a function of multiple independent variables ($X$):         $Y = f(X_1, X_2, \dots, X_k)$         The estimated parameters are then used to predict specific values for missing observations.

• Qualitative / Categorical Imputation Techniques:

  • Mode Imputation: Missing values are replaced with the single most frequent category in the dataset.

  • Creation of an "Unknown" or "Other" Category: Missing responses are assigned to a distinct, newly created categorical label. This enables analysts to run comparative models testing whether individuals with missing attributes differ structurally from those with complete data.

  • Proxy Variables: A proxy is an auxiliary variable that substitutes for an unobserved attribute. For instance, while the American Community Survey (ACS) conducted by the Census Bureau sometimes omits direct measures of homeownership, it records whether respondents make monthly mortgage payments or rent payments. Analysts use these related financial variables to deduce homeownership status accurately.

Subsetting Datasets and Managing Outliers

• Subsetting Data:

  • Subsetting is the process of filtering a dataset to isolate specific subgroups relevant to a study, while removing unneeded variables or observations.

  • Examples of Subsetting: Filtering demographic data to isolate the prime-age working population, separating college-educated individuals from non-college-educated individuals, or restricting economic geographic data strictly to the Midwest.

  • Column Elimination: Deleting unused variables simplifies large datasets (such as raw Census files containing 400,000 to 500,000 rows and 80 variables), preventing software lag and minimizing operational error.

  • Time-Series Truncation: Truncating excessively long historical time series (e.g., cutting off historical data from 1870 or 1950) reduces computational load when older data adds minimal analytical value.

• Computational Burden and Error Reduction:

  • High-dimensional datasets place severe processing demands on computing hardware.

  • Flight Data Example: Analyzing all commercial passenger flights in the United States over a 10-year period involves tens of millions of records, requiring random sampling or subsetting to execute routines.

  • Dissertation Data Example: Executing complex econometric commands on a panel dataset containing $2,000,000$ observations can take over an hour per run. Subsetting extra variables or unused regions speeds up execution times and reduces computational errors.

• Outlier Detection and Treatment:

  • Definition: Outliers are extreme values that deviate significantly from the rest of the sample distribution.

  • Empirical Rule Detection: For bell-shaped distributions, values that lie beyond plus or minus three standard deviations from the sample mean are flagged as potential outliers:         $\text{Outlier Boundary} = \mu \pm 3\,\sigma$

  • Interquartile Range (IQR) Method: A non-parametric threshold method based on the spread between the 25th and 75th percentiles.

  • Wealth/Income Examples: Including hyper-wealthy individuals such as Bill Gates or Warren Buffett in a general population income sample skews the mean drastically upward, making it unrepresentative of standard earning levels.

  • Sensitivity Analysis: Analysts should run empirical procedures both with outliers included and with outliers excluded to observe whether qualitative conclusions change.

Data Binned Structures and Numerical Transformation

• Binning (Grouping Numeric Data):

  • Binning is the numerical transformation technique of grouping fine continuous data points into discrete categories or numerical intervals.

  • Higher Education Research Example: In a graduate study evaluating return on investment across higher education institutions in the 2010s, binning was applied to classify four-year universities into distinct groups (such as for-profit vs. non-profit institutions) to evaluate poor outcomes among for-profit enrollments.

  • Outlier Mitigation: Binning reduces the distortive effect of extreme outliers. For instance, converting continuous income values into quintiles (five equal bins, each containing $20\%$ of the sample population) places individuals like Warren Buffett into the top 20th percentile bin without inflating summary stats.

• Required Criteria for Binning:

  1. Mutually Exclusive: Bin boundaries must not overlap. Every individual data point must fall into one, and only one, designated bin.

  2. Collectively Exhaustive: Bins must cover the entire numeric domain of the variable. Every single observation in the dataset must have a valid bin assignment.

• Interval Properties:

  • Properly constructed numerical bins maintain equal spacing across categories to preserve consistent interpretation.

Special Data Handling: Dates and High-Frequency Panel Data

• Date Formats in Software:

  • Spreadsheet software like Excel handles dates inconsistently, frequently reformatting text input into underlying serial numbers or altering calendar displays.

  • Analysts must manually verify date formats when importing external data files.

• High-Frequency Panel Data Risks:

  • Panel data combines cross-sectional spatial tracking with longitudinal temporal tracking.

  • When working with high-frequency financial data (such as hourly or daily stock closing prices), minor date-formatting errors in software corrupt panel alignments, resulting in severe data misalignment across time-series sequences.