Mathew K AnalyticsLesson 10 · Market Research Analytics in Python
How to Load Survey and Market Data from CSV & Excel in Python | Market Research Analytics
We are learning how to load and explore real survey and market datasets using Python. Knowing how to correctly load data from CSV and Excel is critical for…
- CourseMarket Research Analytics in Python
- Lesson10 of 56
- Video25 min
- FormatJupyter notebook · 27 code cells
What you'll learn
Data
No separate download needed — the notebook creates or downloads everything it uses.
📓 Full notebook
Download .ipynbLoading Survey and Market Data from CSV and Excel#
- We are learning how to load and explore real survey and market datasets using Python.
- Knowing how to correctly load data from CSV and Excel is critical for market research and customer analytics.
- This lesson will show you practical techniques to read, clean, and understand survey and transactional customer data.
- At the end, you will be able to import and preview real-world data for essential analysis.
import pandas as pd
import numpy as np
import openml
import warnings
warnings.filterwarnings('ignore')
Understanding Market Research Data Structures#
- Survey datasets typically include columns for demographics (such as age, gender, location), customer responses (Likert scales, NPS, open text), and transaction details.
- Market data may include campaign responses, purchase history, or customer feedback text.
- Beginners often confuse codes (like NPS 0-10) or miss that text columns require different handling.
- It is important to understand what each column means before analysis.
# Beginner Example 1: Load Customer Satisfaction Survey data
dataset = openml.datasets.get_dataset(42178)
df, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df.shape)
print(df.head(3))
# Beginner Example 2: Load a marketing campaign dataset and fix columns
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.shape)
print(df_campaign.head(3))
# Beginner Example 3: Load online retail Excel data
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.head(3))
# Beginner Example 4: Generate synthetic NPS survey data
np.random.seed(42)
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.shape)
print(df_nps.head(3))
# Beginner Example 5: Create a small open-ended customer feedback table
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.shape)
print(df_feedback.head(3))
# Beginner Example 6: Simulate customer cohort signup activity over months
np.random.seed(0)
dates = pd.date_range('2021-01-01', periods=24, freq='ME')
df_cohort = pd.DataFrame({
'CustomerID': np.random.randint(1000, 2000, len(dates)),
'Signup_Month': dates,
'Active_Users': np.random.randint(50, 300, len(dates))
})
print(df_cohort.shape)
print(df_cohort.head(3))
Going Deeper: Intermediate Data Insights#
- Now that we know how to load survey and market datasets, we can explore structure, missing data, types, and response distribution.
- Real data often needs cleaning and preprocessing before we use it for dashboards or modeling.
# Intermediate Example 1: Examine column types and summary in customer survey
print(df.dtypes)
print(df.describe(include='all').T.head(8))
# Intermediate Example 2: Check for missing survey data
missing_perc = df.isna().mean().mul(100).sort_values(ascending=False)
print(missing_perc.head(10))
# Intermediate Example 3: Value counts for a categorical field (InternetService usage)
print(df['InternetService'].value_counts(dropna=False))
# Intermediate Example 4: Preview a campaign response rate
response_rate = df_campaign['response'].value_counts(normalize=True).mul(100)
print('Campaign response rate (%):')
print(response_rate)
# Intermediate Example 5: Filter survey data for senior citizens with internet service
senior_net = df[(df['SeniorCitizen'] == 1) & (df['InternetService'] != 'No')]
print(senior_net[['gender', 'SeniorCitizen', 'InternetService', 'Churn']].head(5))
# Advanced Example 1: Pivot retail data by country to summarize sales
sales_by_country = df_retail.groupby('Country')['Price'].sum().sort_values(ascending=False)
print(sales_by_country.head(5))
# Advanced Example 2: Calculate NPS (Net Promoter Score) from synthetic data
promoters = df_nps[df_nps['NPS_Score'] >= 9]
detractors = df_nps[df_nps['NPS_Score'] <= 6]
nps_score = (len(promoters) - len(detractors)) / len(df_nps) * 100
print(f'Net Promoter Score: {nps_score:.2f}')
# Advanced Example 3: Parse customer feedback for positive keyword frequency
positive_terms = ['great', 'excellent', 'good']
df_feedback['Feedback_Lower'] = df_feedback['Feedback'].str.lower()
for term in positive_terms:
count = df_feedback['Feedback_Lower'].str.contains(term).sum()
print(f"Feedbacks containing '{term}': {count}")
# Advanced Example 4: Build a simple segmentation of customers by cohort month
cohort_group = df_cohort.groupby(df_cohort['Signup_Month'].dt.to_period('M'))['Active_Users'].sum()
print(cohort_group.head(6))
Handling Errors in Market Research Data#
- Real survey files often have missing responses or incorrectly coded values.
- Let us demonstrate ways to deal with these common problems.
# Error Handling: Replace missing survey values with explicit label
df_missing = df.copy()
df_missing['PaymentMethod'] = df_missing['PaymentMethod'].fillna('Unknown')
print(df_missing['PaymentMethod'].value_counts())
# Error Handling: Identify and fix likely aggregation mistakes
wrong_sum = df['Churn'].sum() # Not meaningful for Yes/No text
print(f'Sum of Churn column (incorrect): {wrong_sum}')
churn_n = df['Churn'].value_counts()
print('Churn yes/no counts:')
print(churn_n)
# Error Handling: Convert Likert/NPS scale numerics to text categories
df_nps['NPS_Category'] = np.where(df_nps['NPS_Score'] >= 9, 'Promoter',
np.where(df_nps['NPS_Score'] <= 6, 'Detractor', 'Passive'))
print(df_nps[['NPS_Score', 'NPS_Category']].head(5))
Best Practices: Patterns for Market Research Analytics#
- Grouping and segmenting customers or responses multiplies insight.
- Cross-tabulating demographics and satisfaction can find key drivers.
- Building indices or trend lines makes reports easier to interpret.
# Best Practice: Segment NPS by region
nps_by_region = df_nps.groupby('Region')['NPS_Score'].mean()
print(nps_by_region)
# Best Practice: Cross-tabulate Churn by payment method
churn_by_payment = pd.crosstab(df['PaymentMethod'], df['Churn'])
print(churn_by_payment.head(6))
# Best Practice: Aggregate customer retention trend over time
df_cohort['YearMonth'] = df_cohort['Signup_Month'].dt.to_period('M')
trend = df_cohort.groupby('YearMonth')['Active_Users'].sum()
print(trend.tail(6))
End-to-End: Rapid Market Research Use Case#
- Let us do a quick end-to-end problem: load a survey, clean it, summarize key metrics, and recommend an action based on data.
# 1. Load and preview survey data
dataset = openml.datasets.get_dataset(42178)
df, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df.head(2))
# 2. Clean missing data in PaymentMethod
df['PaymentMethod'] = df['PaymentMethod'].fillna('Unknown')
# 3. Group and calculate churn rates by payment type
churn_rates = df.groupby('PaymentMethod')['Churn'].value_counts(normalize=True).unstack().fillna(0)
print(churn_rates)
# 4. Recommend a business action based on the result
highest_churn = churn_rates['Yes'].idxmax()
print(f'Recommend targeting {highest_churn} customers with loyalty offers.')
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 10
Loading Retail Transaction Data from CSV and Excel in Python
30 minLesson 17
Python Data Analytics #17: Real Cryptocurrency Market Analysis in Python
27 minLesson 18
Python Data Analytics #18: Company Fundamentals from Real Financial Statements
12 minLesson 19