Practical Tools & Real-World Data Analysis

TAKEAWAYS:

• Tools matter: Python (Matplotlib/Seaborn), R (ggplot2), and Tableau are industry standards for visualization; Excel Solver and LINGO handle OR models.
• Case studies bridge theory and practice: Use OR models (e.g., linear programming) to optimize inventory (Daraz) or traffic routes (Kathmandu).
• Data → Insight → Action: Visualization tools reveal patterns (e.g., NEPSE stock trends), while OR tools prescribe decisions (e.g., Pathao driver routing).
• Ethics and limits: Tools can mislead if misused (e.g., cherry-picked scales in political graphs) or fail with poor data (e.g., Ncell’s call-drop maps).
• Nepal-specific applications: eSewa’s fraud detection uses anomaly visualization; NTC’s fiber-optic network relies on spatial optimization.
• Exam focus: Know how to map problems to tools (e.g., "Use a decision tree for loan approval" or "Plot a heatmap for Kathmandu traffic").

---

1. Tools for Data Analysis and Visualization

A. Programming Tools

Core libraries for visualization:

# Example: Loading and plotting Nepal’s COVID-19 data (real-world dataset)
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Load data (simplified)
data = pd.read_csv("nepal_covid_2020.csv")
data['Date'] = pd.to_datetime(data['Date'])

# Plot daily cases with a moving average (smoothing)
plt.figure(figsize=(10, 5))
sns.lineplot(data=data, x='Date', y='Cases', label='Daily Cases')
sns.lineplot(data=data.rolling(7).mean(), x='Date', y='Cases', color='red', label='7-Day Avg')
plt.title("COVID-19 Cases in Nepal (2020)")
plt.xticks(rotation=45)
plt.show()

Why this matters:

• Python (Matplotlib/Seaborn): Dominates academia and startups (e.g., eSewa uses Python for fraud detection dashboards).
• R (ggplot2): Preferred in research (e.g., NTC uses R for network performance analysis).
• Advantages:
  • Customizable (e.g., animate NEPSE stock trends over time).
  • Reproducible (code + data = same result).
• Disadvantages:
  • Steep learning curve (e.g., debugging a failed ggplot2 layer).
  • Requires data cleaning (e.g., Pathao’s raw GPS data has noise).

B. Drag-and-Drop Tools

Real-world example:

• How it works: Drag Transaction_Time to columns, Amount to rows, and add a trend line.
• Nepal link: eSewa uses Tableau to detect fraud spikes (e.g., sudden transactions from a single IP).

C. Operations Research (OR) Tools

For solving optimization problems (Units 10–13):

Key tools:

• Excel Solver: Free, built-in (e.g., Nepal Rastra Bank uses it for loan portfolio optimization).
• LINGO/GAMS: Industry standard (e.g., NTC uses GAMS for network flow optimization).
• Python (PuLP/SciPy): Open-source alternative (e.g., Khalti uses PuLP for fraud detection rules).

Worked Example: Kathmandu Traffic Light Optimization Problem: Reduce congestion on Ring Road by optimizing traffic light cycles. Data:

• Peak hours: 8–10 AM (volume: 500 vehicles/hour).
• Current cycle: 30s green, 10s red.
• Goal: Minimize average wait time.

Steps:

1. Model: Assign variables to each light’s cycle time.
2. Constraints:
   • Total cycle ≤ 60s (safety).
   • Pedestrian crossing time ≥ 15s.
3. Objective: Minimize total wait time = Σ (arrival rate × wait time).
4. Solve in Excel Solver:
   • Input data as a table:

     Intersection	Current Green (s)	Current Wait Time (vehicles)
     Thapathali	30	120
     Putalisadak	25	90

   • Solver settings:
     • Set Objective: MIN(SUM(Wait_Time))
     • Variables: Green times for each light.
     • Constraints: SUM(Green_Time) ≤ 60, Pedestrian_Time ≥ 15.
   • Solution: Optimal cycles = Thapathali: 28s, Putalisadak: 22s (saves 30 vehicles/hour).

Real-world tie-in:

• NTC and Kathmandu Metropolitan City use similar models to adjust signals dynamically.

---

2. Case Studies: Tools in Action

A. Fraud Detection (eSewa/Khalti)

Tool: Python (Scikit-learn + Matplotlib) + Tableau. How it works:

1. Data: Transaction logs (amount, time, location, device).
2. Visualization:
   • Anomaly detection: Plot transaction amounts vs. time. Fraud appears as spikes.

     sns.boxplot(x=data['Amount'])
     

   • Geospatial: Heatmap of transactions by district (unusual clusters = fraud).
3. OR Model: Linear programming to flag transactions violating constraints (e.g., "No $500 transfer at 3 AM").

Example:

• Action: Freeze transactions in red zones until verified.

B. Inventory Management (Daraz)

Tool: Excel Solver / Python (PuLP). Problem: Order 10,000 masks for Daraz with:

• Lead time: 7 days.
• Demand: 1,500 masks/day (normal), 3,000/day during festivals.
• Holding cost: $0.50/mask/month.
• Order cost: $50/order.

Solution:

1. Model: Newsvendor problem (balance stockouts vs. excess).
2. Solver Input:
   • Demand distribution: Normal(μ=1500, σ=300).
   • Critical fractile = 0.7 (70% service level).
3. Output: Order 12,000 masks every 7 days (cost: $600 vs. $800 with naive ordering).

Real-world tie-in:

• Daraz uses this to avoid stockouts during Dashain.

C. Network Optimization (NTC)

Tool: GAMS / Python (NetworkX). Problem: Route fiber-optic cables from NTC’s Kathmandu hub to Pokhara with:

• 3 possible paths (costs: $10K, $8K, $12K; capacities: 100Mbps, 50Mbps, 200Mbps).
• Demand: 150Mbps.

Solution:

1. Model: Minimum-cost flow.
2. Solver: GAMS outputs Path 1 + Path 3 (total cost: $22K).
3. Visualization:

   import networkx as nx
   G = nx.Graph()
   G.add_edges_from([("Kathmandu", "Path1", {"capacity": 100, "cost": 10}),
                     ("Kathmandu", "Path3", {"capacity": 200, "cost": 12})])
   nx.draw(G, with_labels=True)
   

---

3. Workflow: From Data to Decision

Example: Pathao uses this to reduce driver wait times by 20% in Kathmandu.

---

4. Common Pitfalls and Ethics

A. Visualization Mistakes

Real-world example:

• Ethical issue: Obfuscates risk for investors.

B. OR Model Failures

• Garbage in, garbage out (GIGO): Ncell’s call-drop maps failed because input data had GPS errors.
• Ignoring constraints: A Daraz warehouse model ignored truck capacity, leading to delivery delays.
• Overfitting: A Khalti fraud model flagged legitimate transactions during Diwali (high-volume anomaly).

---

5. Exam Tip: How to Score Full Marks

Do:

• Map problems to tools:
  • "How would you visualize NEPSE stock trends?" → "Use a candlestick plot in Python (Matplotlib) with volume bars."
  • "Optimize Pathao’s driver routes." → "Use a minimum-cost flow model in PuLP."
• Show calculations: For OR problems, write the objective function and constraints (even if not solved).
• Link to Nepal: Always tie examples to local contexts (e.g., eSewa, NTC, Daraz).

Avoid:

• Vague answers: ❌ "Use Excel" → ✅ "Use Excel Solver with the GRG Nonlinear method to minimize total cost under the constraint that inventory ≥ demand 95% of the time."
• Ignoring assumptions: Always state them (e.g., "Assume demand is normally distributed").
• Memorizing code: Explain the logic (e.g., "We use a heatmap because human eyes detect spatial anomalies faster than tables").

Sample Exam Question & Answer: Q: "Explain how you would use data visualization and OR tools to detect fraud in eSewa transactions. Provide a step-by-step workflow." A:

1. Data Collection: Gather transaction logs (amount, time, location, device ID).

2. Visualization:

   • Plot amount vs. time (spikes = anomalies).

     sns.scatterplot(data=transactions, x='Time', y='Amount', hue='Device_ID')
     

   • Create a geospatial heatmap of transaction density.

3. OR Model:

   • Define constraints:
     • No transaction > $5,000 without OTP.
     • Max 3 transactions/minute from one device.
   • Use linear programming to flag violations.

4. Implementation:

   • Deploy a Tableau dashboard for real-time monitoring.
   • Automate alerts for transactions outside constraints.

5. Validation:

   • Test with historical fraud data (e.g., eSewa’s 2021 hack).
   • Adjust thresholds based on false positives.

---

Key Formula to Remember: For the Newsvendor Model (inventory optimization): $$Q^{\ast }=\mu +Z\cdot \sigma$$ Where:

• $Q^{\ast }$ = Optimal order quantity.
• $\mu$ = Mean demand.
• $\sigma$ = Standard deviation of demand.
• $Z$ = Critical fractile (e.g., 0.84 for 80% service level).

Example: For Daraz masks ($\mu =1500$, $\sigma =300$, $Z=0.84$): $$Q^{\ast }=1500+0.84\times 300=1,752\text{ masks}$$