Mathew K AnalyticsLesson 17 · Market Research Analytics in Python
Cleaning and Standardizing Market Research Data Using Python: Step-by-Step Tutorial
In this lesson, we will focus on preparing real market research data for analysis. Dirty or inconsistent data can lead to the wrong business conclusions.…
- CourseMarket Research Analytics in Python
- Lesson17 of 56
- Video32 min
- FormatJupyter notebook · 34 code cells
What you'll learn
Data
No separate download needed — the notebook creates or downloads everything it uses.
📓 Full notebook
Download .ipynbCleaning and Standardizing Market Research Data#
- In this lesson, we will focus on preparing real market research data for analysis.
- Dirty or inconsistent data can lead to the wrong business conclusions.
- You will learn to spot and fix issues in surveys, customer characteristics, satisfaction scores, and campaign data.
- Clean data gives accurate insights on retention, satisfaction, and marketing performance.
import pandas as pd
import numpy as np
import openml
import warnings
warnings.filterwarnings('ignore')
Understanding Market Research Data#
- Market research data includes survey answers, customer traits, satisfaction scores, and open feedback.
- Responses can be numbers (ratings), text (feedback), or categories (gender, region).
- Demographic columns help slice insights by customer type.
- Beginners often assume the data is already clean and make mistakes like using wrong scales, missing hidden null values, or failing to standardize columns.
dataset = openml.datasets.get_dataset(42178)
df_satis, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df_satis.shape)
print(df_satis.head(3))
df_satis.info()
print(df_satis['TotalCharges'].sample(10, random_state=42))
df_satis['TotalCharges'] = pd.to_numeric(df_satis['TotalCharges'], errors='coerce')
print(df_satis['TotalCharges'].isnull().sum())
df_satis['gender'] = df_satis['gender'].str.strip().str.lower()
print(df_satis['gender'].unique())
df_satis['SeniorCitizen'] = df_satis['SeniorCitizen'].replace({1: 'Yes', 0: 'No'})
print(df_satis['SeniorCitizen'].value_counts())
dataset = openml.datasets.get_dataset(1461)
df_campaign, _, _, _ = dataset.get_data(dataset_format='dataframe')
df_campaign.columns = ['age','job','marital','education','default','balance','housing','loan','contact','day','month','duration','campaign','pdays','previous','poutcome','response']
print(df_campaign.head(3))
df_campaign['response'] = df_campaign['response'].str.lower().str.strip()
df_campaign['response'] = df_campaign['response'].replace({'yes':'Yes', 'no':'No'})
print(df_campaign['response'].value_counts())
print(df_campaign['job'].unique())
df_campaign['job'] = df_campaign['job'].replace({'admin.':'admin','management ':'management'})
print(df_campaign['job'].unique())
df_campaign['balance'] = pd.to_numeric(df_campaign['balance'], errors='coerce')
missing_balance = df_campaign['balance'].isnull().sum()
print(f'Missing or non-numeric balances: {missing_balance}')
n_missing = df_campaign.isnull().sum().sum()
print(f'Total missing entries in campaign data: {n_missing}')
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df_retail = pd.read_excel(url, sheet_name='Year 2010-2011')
df_retail['InvoiceDate'] = pd.to_datetime(df_retail['InvoiceDate'])
print(df_retail.shape)
print(df_retail[['Invoice','StockCode','Description','Quantity','Price']].head(3))
df_retail = df_retail.dropna(subset=['Description', 'Price'])
print('Rows after dropping missing Descriptions or Price:', len(df_retail))
df_retail = df_retail[df_retail['Quantity'] > 0]
df_retail = df_retail[df_retail['Price'] >= 0]
print(f'Shape after filtering for positive quantities and non-negative prices: {df_retail.shape}')
df_nps = pd.DataFrame({'CustomerID': range(1,501),
'Age': np.random.randint(18,70,500),
'Region': np.random.choice(['North','South','East','West'],500),
'NPS_Score': np.random.randint(0,11,500)})
print(df_nps.sample(5, random_state=42))
print(df_nps['NPS_Score'].unique())
invalid = df_nps[~df_nps['NPS_Score'].between(0,10)]
print('Number of invalid NPS scores:', len(invalid))
df_feedback = pd.DataFrame({'CustomerID':[1,2,3,4,5],
'Feedback':['Great service and friendly staff',
'Delivery was slow and packaging was poor',
'Excellent quality, will buy again',
'Customer support needs improvement',
'Good value for money']})
print(df_feedback)
df_feedback['Feedback'] = df_feedback['Feedback'].str.strip().str.replace(' +', ' ', regex=True).str.lower()
print(df_feedback)
df_satis_clean = df_satis.drop_duplicates()
print(df_satis.shape[0] - df_satis_clean.shape[0], 'duplicate rows removed')
n_missing = df_nps.isnull().sum().sum()
print(f'Total missing NPS entries: {n_missing}')
n_invalid = df_nps[~df_nps['NPS_Score'].between(0,10)].shape[0]
mean_nps = df_nps[df_nps['NPS_Score'].between(0,10)]['NPS_Score'].mean()
print(f'Mean NPS (excluding invalid): {mean_nps:.2f}')
Handling Missing and Mistyped Data#
- In market research, missing survey answers can distort business recommendations, so always check for and communicate nulls.
- Mistyped codes (like 'femaale' instead of 'female') cause wrong group totals; fix using string cleaning and mapping.
- Bad groupings (for example, by unsanitized region) ruin trend or segmentation analysis.
df_satis['gender'] = df_satis['gender'].replace({'femaale':'female','Femle':'female'})
print(df_satis['gender'].value_counts())
Best Practices: Analytics Patterns#
- Segment customers or responses before analyzingmarket segments often have unique satisfaction or churn rates.
- Use cross-tabulation to compare business metrics by two traits (like by contract or by age group).
- Construct customer indexes or scores after the data is cleannever before.
- Trend analysis requires clean and date-standardized time fields.
segment_counts = df_satis.groupby('Contract').size()
print(segment_counts)
cross = pd.crosstab(df_satis['gender'], df_satis['Churn'])
print(cross)
df_satis['TenureGroup'] = pd.cut(df_satis['tenure'], bins=[0,12,24,36,48,60,72], labels=['<1y','1-2y','2-3y','3-4y','4-5y','5-6y'])
grouped = df_satis.groupby('TenureGroup')['Churn'].value_counts().unstack().fillna(0)
print(grouped)
df_satis['MonthlyCharges'] = pd.to_numeric(df_satis['MonthlyCharges'], errors='coerce')
trend = df_satis.groupby('tenure')['MonthlyCharges'].mean()
print(trend.head(10))
df_cohort = pd.DataFrame({'CustomerID': np.random.randint(1000,2000,24),
'Signup_Month': pd.date_range('2021-01-01', periods=24, freq='ME'),
'Active_Users': np.random.randint(50,300,24)})
print(df_cohort.head(3))
monthly_active = df_cohort.groupby(df_cohort['Signup_Month'].dt.year).agg({'Active_Users':'mean'})
print(monthly_active)
End-to-End Example: Cleaning a Satisfaction Dataset#
- We will walk through cleaning, standardizing, and summarizing a real satisfaction survey dataset.
- The business goal is to deliver a clean input to the analytics team for NPS and churn analysis.
- Final insight: What percent of customers are likely to churn, and what is the mean monthly bill after cleaning?
df_satis_final = df_satis.copy()
df_satis_final['TotalCharges'] = pd.to_numeric(df_satis_final['TotalCharges'], errors='coerce')
df_satis_final['MonthlyCharges'] = pd.to_numeric(df_satis_final['MonthlyCharges'], errors='coerce')
df_satis_final = df_satis_final.dropna(subset=['TotalCharges','MonthlyCharges'])
df_satis_final['gender'] = df_satis_final['gender'].str.strip().str.lower().replace({'femaale':'female','Femle':'female'})
df_satis_final['SeniorCitizen'] = df_satis_final['SeniorCitizen'].replace({1:'Yes',0:'No'})
n_churn = df_satis_final[df_satis_final['Churn']=='Yes'].shape[0]
n_total = df_satis_final.shape[0]
mean_bill = df_satis_final['MonthlyCharges'].mean()
print(f'Churn rate: {100*n_churn/n_total:.1f}%, Mean monthly bill: ${mean_bill:.2f}')
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
23 minLesson 22
Customer Churn Prediction: Step-by-Step Python Tutorial Using Real Data
20 minLesson 11
Effective Methods for Cleaning and Managing Missing Data in Banking Datasets Using Python
19 minLesson 4
Retail Analytics Workflow Using Python: Step-by-Step Training for E-commerce Data
27 minLesson 51