Data Processing & Analysis: Cleaning, Coding, Tabulation & Interpretation

Key Concepts and Process Flow

What is Data Processing and Analysis?

Data processing is the systematic transformation of raw data into a form suitable for analysis. It includes:

• Cleaning (handling missing values, outliers, inconsistencies)
• Coding (assigning numerical/alphanumeric values to categorical responses)
• Tabulation (organizing data into tables for clarity)
• Analysis (applying statistical techniques to derive insights)

flowchart TD
    A["Raw Data
(Surveys, Transactions, Observations)"] --> B["Data Cleaning
(Check for errors, missing values, duplicates)"]
    B --> C["Data Coding
(Convert qualitative to quantitative)"]
    C --> D["Tabulation
(Create frequency tables, cross-tabulations)"]
    D --> E["Statistical Analysis
(Descriptive & Inferential)"]
    E --> F["Interpretation
(Draw conclusions, recommend actions)"]
    F -->|"Report"| G["Final Report
(Visualizations, Tables, Recommendations)"]

Why is this critical?

• Dirty data leads to wrong conclusions (e.g., a bank misclassifying loan applicants due to incorrect coding).
• Poor tabulation hides trends (e.g., Daraz failing to spot regional demand shifts).
• Incorrect analysis misguides strategy (e.g., NTC overestimating customer satisfaction).

Step 1: Data Cleaning

What to Clean?

1. Missing Values: Unanswered survey questions, blank fields in transaction logs.
2. Outliers: Extreme values (e.g., a customer spending ₹100,000 in one Daraz order when the average is ₹5,000).
3. Inconsistencies: Typos (e.g., "Male" vs. "M"), conflicting entries (e.g., age 150 in a customer database).
4. Duplicates: Repeated entries (e.g., the same WhatsApp user ID appearing twice in a market research survey).

How to Clean?

Worked Example: Cleaning NEPSE Stock Data

Problem: A researcher collects daily stock prices for Himalayan Java from NEPSE but finds:

• 5 missing values in the "Close Price" column.
• One outlier: ₹1,000,000 (likely a typo; correct value should be ₹100,000).

Solution:

1. Impute missing values with the 7-day moving average.
2. Cap the outlier at ₹500,000 (beyond 3 standard deviations from the mean).

# Pseudocode for outlier treatment
import pandas as pd
data = pd.read_csv("nepse_himalayan_java.csv")
data = data[data['Close_Price'] < 500000]  # Remove extreme values

Step 2: Data Coding

Why Code?

• Converts qualitative data (text, opinions) into quantitative data (numbers) for analysis.
• Example: Survey question: "How satisfied are you with Pathao’s delivery?"
  • Responses: "Very Satisfied," "Satisfied," "Neutral," "Dissatisfied," "Very Dissatisfied."
  • Coded as: 5, 4, 3, 2, 1.

Coding Schemes

Worked Example: Coding Khalti User Feedback

Survey Question: "How likely are you to recommend Khalti to a friend?" Responses:

• "Extremely Likely" → 5
• "Likely" → 4
• "Neutral" → 3
• "Unlikely" → 2
• "Extremely Unlikely" → 1

Analysis Ready:

• Now, Khalti can calculate the Net Promoter Score (NPS):
  • Promoters (9-10, here 4-5) = 40%
  • Detractors (0-6, here 1-2) = 10%
  • NPS = 40% - 10% = 30% (Strong loyalty!)

Step 3: Tabulation

Types of Tables

1. Frequency Tables: Show how often each response occurs.
   • Example: Number of Daraz customers who bought electronics vs. groceries.
2. Cross-Tabulation: Shows relationships between two variables.
   • Example: Do Ncell prepaid users spend more than postpaid users?

Insight: Postpaid users spend 3x more on average than prepaid users.

Worked Example: Tabulating eSewa Transaction Data

Problem: eSewa wants to analyze transaction patterns by time of day. Data:

• Transactions from 6 AM to 12 AM.
• Categorized into: Morning (6-12 AM), Afternoon (12-6 PM), Evening (6-12 PM).

Frequency Table:

Insight:

• Peak hours: Afternoon (highest volume).
• Highest spending: Evening (likely bill payments, shopping).

Step 4: Statistical Analysis

Descriptive vs. Inferential Statistics

Key Measures

1. Central Tendency:
   • Mean: Average (affected by outliers).
   • Median: Middle value (robust to outliers).
   • Mode: Most frequent value.
2. Dispersion:
   • Range: Max - Min.
   • Standard Deviation: Spread of data.
   • Variance: Square of standard deviation.

Worked Example: Analyzing NTC Customer Complaints

Data: Number of complaints per month (last 12 months). Descriptive Stats:

• Mean = 450 complaints/month
• Median = 420 complaints/month
• Standard Deviation = 80

Inferential Insight:

• If NTC improves service, they predict a 20% reduction in complaints (new mean = 360).
• Hypothesis Test: Is the reduction statistically significant?
  • Null Hypothesis (H₀): Complaints remain ≥400.
  • Alternative Hypothesis (H₁): Complaints <400.
  • p-value < 0.05 → Reject H₀ (improvement is significant).

Step 5: Interpretation and Reporting

How to Interpret Results?

1. Compare with Benchmarks:
   • Example: Daraz’s NPS of 30 vs. industry average of 25.
2. Identify Trends:
   • Example: Khalti’s transaction volume grows 15% YoY.
3. Recommend Actions:
   • Example: NTC should prioritize evening call centers (highest complaints).

Reporting Structure

In the Real World

1. Khalti’s Fraud Detection:

   • Idea Used: Outlier detection in transaction data.
   • How: Khalti flags transactions exceeding 3 standard deviations from the user’s average spending (e.g., a ₹50,000 transfer when the user’s norm is ₹5,000).
   • Outcome: Reduces fraudulent transactions by 40%.

2. Daraz’s Demand Forecasting:

   • Idea Used: Cross-tabulation of purchase data by region and season.
   • How: Daraz analyzes which products (e.g., umbrellas, heaters) sell more in monsoon vs. winter in Kathmandu vs. Pokhara.
   • Outcome: Optimizes inventory, reducing stockouts by 25%.

3. Ncell’s Churn Prediction:

   • Idea Used: Logistic regression on customer behavior data.
   • How: Ncell codes customer data (call duration, data usage, complaints) to predict which users will switch to NTC.
   • Outcome: Retains 30% more high-value customers with targeted offers.

4. NEPSE’s Market Sentiment Analysis:

   • Idea Used: Text mining of news headlines and social media.
   • How: NEPSE codes sentiment (positive/negative/neutral) from articles about Himalayan Java and correlates it with stock price movements.
   • Outcome: Investors adjust portfolios based on sentiment scores (e.g., -0.7 = "Sell," +0.5 = "Hold").

Exam Tip

What Examiners Look For

1. Step-by-Step Processing:

   • Show cleaning → coding → tabulation → analysis in your answer.
   • Example: "First, I removed 5% missing values using mean imputation..."

2. Visual Evidence:

   • Always include tables, charts, or pseudocode (even if not required, it proves you understand).
   • Example: A cross-tabulation table for a marketing mix question.

3. Real-World Application:

   • Tie your answer to Nepali businesses (e.g., "Like Daraz, a retail company would...").
   • Avoid generic examples; examiners reward local relevance.

4. Critical Thinking:

   • Don’t just describe—interpret!
   • Weak: "The mean age is 30."
   • Strong: "The mean age of 30 suggests a young workforce, which aligns with Nabil Bank’s digital-first strategy targeting millennials."

5. Common Pitfalls to Avoid:

   • Ignoring outliers: Always state how you handled them.
   • Misinterpreting scales: Nominal ≠ ordinal! (e.g., coding gender as 1/2 is nominal; satisfaction as 1-5 is ordinal).
   • Overlooking units: Always specify currency (₹), time (months), or percentages (%).

Model Answer Structure for 10 Marks

Question: "A market research firm collected data on customer satisfaction with a new product. The data had missing values, outliers, and inconsistent responses. Explain how you would process and analyze this data to provide insights to the company."

Model Answer (10 Marks):

1. Data Cleaning (3 marks)

   • "First, I would handle missing values by imputing the mean for numerical data (e.g., age) and the mode for categorical data (e.g., gender). Outliers like a ₹500,000 transaction when the average is ₹5,000 would be capped at the 99th percentile. Inconsistent responses (e.g., 'Yes' vs. 'Y') would be standardized."

2. Data Coding (2 marks)

   • "Next, I would code qualitative responses using a Likert scale (e.g., 'Very Satisfied' = 5, 'Neutral' = 3). For nominal data like 'Region' (Kathmandu/Pokhara), I’d assign 1 and 2."

3. Tabulation (2 marks)

   • "I would create frequency tables for satisfaction levels and cross-tabulate satisfaction by region to identify patterns (e.g., Pokhara customers rating the product higher)."

4. Analysis (2 marks)

   • "Descriptive stats (mean satisfaction = 3.8) would summarize trends, while inferential tests (ANOVA) would check if regional differences are significant (p < 0.05)."

5. Interpretation (1 mark)

   • "The analysis shows that Pokhara’s satisfaction score (4.2) drives the overall mean, suggesting a regional preference that the company should investigate further."

Final Checklist Before Submission

• Did I clean data (missing values, outliers, duplicates)?
• Did I code variables correctly (nominal vs. ordinal)?
• Did I tabulate with clear headers and units?
• Did I analyze using both descriptive and inferential stats?
• Did I interpret results with business implications?
• Did I link to a real Nepali company (e.g., Daraz, Khalti)?