Step 1: Import and Explore the Data

Before analysis, load your dataset and understand its structure. Identify the number of rows, columns, and data types.

In Excel: Open the CSV or Excel file and examine the first few rows.

In Python (Pandas):

import pandas as pd
df = pd.read_csv("data.csv")
print(df.shape)        # Number of rows and columns
print(df.columns)      # Column names
print(df.info())       # Data types and missing values
print(df.head())       # First five rows

Step 2: Data Preparation and Cleaning

Real-world data often contains errors, missing values, and duplicates. Cleaning ensures reliable results.

Handling missing values:

• Deletion: Remove rows or columns with missing values
• Imputation: Replace with mean, median, or mode

# Fill missing values with column mean
df.fillna(df.mean(), inplace=True)

# Or drop rows with missing values
df.dropna(inplace=True)

Removing duplicates:

df = df.drop_duplicates()

Step 3: Data Transformation

Normalize or standardize numerical data when variables have different scales.

Min-Max Normalization (0 to 1 range): $X_{norm} = \frac{X - X_{min}}{X_{max} - X_{min}}$

Z-Score Standardization (mean = 0, std = 1): $X_{std} = \frac{X - Mean}{Standard Deviation}$

Step 4: Exploratory Data Analysis (EDA)

Calculate descriptive statistics to summarize data:

# Mean, median, mode, standard deviation
print(df.describe())

# Count by category
print(df.groupby("Category")["Value"].sum())

In Excel: Use functions like =AVERAGE(), =MEDIAN(), =STDEV(), or the Analysis ToolPak.

Step 5: Data Visualization

Create charts to identify patterns and trends.

In Python (Matplotlib):

import matplotlib.pyplot as plt

# Bar chart
df.groupby("Region")["Sales"].sum().plot(kind="bar")
plt.title("Sales by Region")
plt.ylabel("Total Sales")
plt.show()

# Scatter plot for relationships
plt.scatter(df["StudyHours"], df["ExamScore"])
plt.xlabel("Study Hours")
plt.ylabel("Exam Score")
plt.title("Relationship between Study Hours and Exam Score")
plt.show()

Step 6: Interpretation and Decision-Making

Draw conclusions from your analysis. For example, if scatter plot shows a positive relationship between study hours and exam scores, recommend structured study programs.

A school collected attendance data. Let's perform advanced analysis using Python.

Dataset (CSV format):

Complete Analysis Workflow:

import pandas as pd
import matplotlib.pyplot as plt

# Step 1: Load data
df = pd.read_csv("attendance.csv")

# Step 2: Data cleaning
df["Present"] = df["Present"].replace("", "Unknown")
df = df.drop_duplicates()

# Step 3: Transform categorical data
df["PresentBinary"] = df["Present"].map({"Yes": 1, "No": 0})

# Step 4: Descriptive statistics
print("Summary Statistics:")
print(df.describe())

print("\nAttendance Rate by Student:")
print(df.groupby("Student")["PresentBinary"].mean())

# Step 5: Visualization
df.groupby("Student")["PresentBinary"].mean().plot(kind="bar")
plt.ylabel("Attendance Rate")
plt.title("Attendance by Student")
plt.show()

# Step 6: Save cleaned data
df.to_csv("attendance_cleaned.csv", index=False)

Excel is suitable for quick summaries and smaller datasets. Python is better for large datasets, automation, and advanced techniques.

In Tanzania, a small shop owner in Dar es Salaam can use Excel to record daily sales of rice, beans, and cooking oil, then create a pivot table to identify which products sell best each week. By analysing this data, they can make informed decisions about which items to stock more of during peak seasons, reducing waste and increasing profits.