10.1 Data Preparation and Cleaning

Data quality and preprocessing are crucial steps in marketing research. They ensure your data is clean, consistent, and ready for analysis. Without proper preparation, your results could be skewed or misleading, leading to poor decision-making.

Transforming and restructuring data helps you extract more meaningful insights. By recoding variables, merging datasets, and reshaping your data, you can uncover hidden patterns and relationships that might otherwise go unnoticed. These techniques are essential for thorough analysis.

Data Quality and Preprocessing

Common issues in raw data

Top images from around the web for Common issues in raw data

• 

  Types of Outliers in Linear Regression | Introduction to Statistics View original

  Is this image relevant?

• 

  6.1: Reading- The Marketing Research Process - Business LibreTexts View original

  Is this image relevant?

• 

  The Marketing Research Process | Principles of Marketing View original

  Is this image relevant?

• 

  Types of Outliers in Linear Regression | Introduction to Statistics View original

  Is this image relevant?

• 

  6.1: Reading- The Marketing Research Process - Business LibreTexts View original

  Is this image relevant?

1 of 3

Top images from around the web for Common issues in raw data

• 

  Types of Outliers in Linear Regression | Introduction to Statistics View original

  Is this image relevant?

• 

  6.1: Reading- The Marketing Research Process - Business LibreTexts View original

  Is this image relevant?

• 

  The Marketing Research Process | Principles of Marketing View original

  Is this image relevant?

• 

  Types of Outliers in Linear Regression | Introduction to Statistics View original

  Is this image relevant?

• 

  6.1: Reading- The Marketing Research Process - Business LibreTexts View original

  Is this image relevant?

1 of 3

• Missing data occurs when values are not recorded for certain observations
  • Completely at random (MCAR): missingness is unrelated to any variables (survey participant accidentally skips a question)
  • Missing at random (MAR): missingness is related to observed variables but not the missing value itself (older participants less likely to report income)
  • Missing not at random (MNAR): missingness is related to the missing value itself (high-income individuals less likely to report income)
• Outliers are extreme values that deviate significantly from other observations
  • Univariate outliers: extreme values on a single variable (income of $1,000,000 in a sample with mean income of$50,000)
  • Multivariate outliers: unusual combinations of values across multiple variables (high income and low education level)
• Inconsistencies arise when data is recorded or formatted differently across observations
  • Inconsistent formatting: dates recorded as "YYYY-MM-DD" and "MM/DD/YYYY"
  • Inconsistent units of measurement: height recorded in inches and centimeters
  • Inconsistent coding schemes: gender coded as "M/F" and "0/1"
• Duplicate entries occur when the same observation is recorded multiple times
• Irrelevant or unnecessary variables are not relevant to the analysis (respondent's favorite color in a study of purchase behavior)
• Incorrect data types occur when variables are stored in the wrong format (age stored as a string instead of an integer)

Techniques for data cleaning

• Handling missing data:
  • Listwise deletion: remove observations with missing values (may result in loss of data)
  • Pairwise deletion: use available data for each analysis (retains more data but may introduce bias)
  • Mean/median/mode imputation: replace missing values with the mean, median, or mode of the variable (single imputation methods)
  • Regression imputation: predict missing values based on relationships with other variables
  • Multiple imputation: create multiple plausible imputed datasets and combine results (accounts for uncertainty in imputation)
• Handling outliers:
  1. Trimming: remove observations below a lower bound and above an upper bound (e.g., remove bottom and top 1%)
  2. Winsorizing: replace extreme values with a specified percentile value (e.g., replace values below 1st percentile with 1st percentile)
  3. Transformations: apply mathematical functions to reduce the impact of outliers (logarithmic, square root)
  4. Robust statistical methods: use methods less sensitive to outliers (median instead of mean)
• Handling inconsistencies:
  • Standardizing formatting: convert all values to a consistent format (convert all dates to "YYYY-MM-DD")
  • Converting units of measurement: convert all values to a consistent unit (convert all heights to inches)
  • Recoding variables consistently: recode all values to a consistent scheme (recode all gender values to "0/1")
• Removing duplicate entries: identify and remove duplicate observations
• Feature selection: remove irrelevant or unnecessary variables (remove "favorite color" variable)

Data Transformation and Restructuring

Variable transformation and recoding

• Variable transformations apply mathematical functions to create new variables or modify existing ones
  • Logarithmic transformation: $\log(x)$, often used for skewed variables (income, sales)
  • Square root transformation: $\sqrt{x}$, used for count variables or to reduce skewness
  • Reciprocal transformation: $\frac{1}{x}$, used for variables with extreme outliers
  • Box-Cox transformation: $\frac{x^{\lambda}-1}{\lambda}$ for $\lambda \neq 0$, $\log(x)$ for $\lambda = 0$, a family of transformations to normalize data
• Recoding variables:
  • Binning continuous variables into categories (age into "young", "middle-aged", "old")
  • Combining categories in categorical variables ("strongly agree" and "agree" into "agree")
  • Dummy coding categorical variables: create binary variables for each category (color: red=1/0, blue=1/0, green=1/0)
  • Effect coding categorical variables: similar to dummy coding, but uses -1/1 instead of 0/1
• Scaling variables:
  • Standardization (z-score): $\frac{x-\mu}{\sigma}$, rescales variables to have mean 0 and standard deviation 1
  • Min-max scaling: $\frac{x-\min(x)}{\max(x)-\min(x)}$, rescales variables to range from 0 to 1
  • Unit vector scaling: $\frac{x}{\sqrt{\sum_{i=1}^{n}x_i^2}}$, rescales variables to have Euclidean length 1
• Creating new variables:
  • Interaction terms: multiply two or more variables (price * discount)
  • Polynomial terms: raise a variable to a power (price^2)

Dataset merging and reshaping

• Merging datasets combines multiple datasets based on a common variable
  • Inner join: keep only observations that appear in both datasets
  • Left join: keep all observations from the left dataset and matching observations from the right dataset
  • Right join: keep all observations from the right dataset and matching observations from the left dataset
  • Full outer join: keep all observations from both datasets
• Reshaping datasets converts between wide and long formats
  • Wide to long format (melting): convert multiple columns into a single column (convert columns for each year into a single "year" column and a "value" column)
  • Long to wide format (casting): convert a single column into multiple columns (convert a "year" column and a "value" column into separate columns for each year)
• Aggregating data:
  • Grouping by variables: split the data into groups based on one or more variables (group by product category)
  • Calculating summary statistics: compute metrics for each group (mean sales per product category)
• Transposing data switches the rows and columns of a dataset
• Splitting and combining variables:
  • Splitting a single variable into multiple variables (split a "full name" variable into "first name" and "last name")
  • Combining multiple variables into a single variable (combine "first name" and "last name" into a "full name" variable)