Pandas Analysis

What is Pandas Analysis?

Pandas analysis means using the Pandas library to inspect, transform, summarize, compare, and understand structured data. It is commonly used for sales analysis, customer analysis, employee reports, financial analysis, inventory management, and many other business tasks.

Raw Data

→

Clean Data

→

Analyze

→

Insights

→

Business Decision

DataFrame

A two-dimensional table containing rows and columns.

Filtering

Select only the records that satisfy specific conditions.

Grouping

Divide data into groups and calculate summaries for each group.

Aggregation

Calculate totals, averages, counts, minimums, maximums, and more.

1. Installing and Importing Pandas

Pandas is usually installed using pip.

Install Pandas

pip install pandas

Import Pandas using the common alias pd.

Python

import pandas as pd

2. Creating a Real-Time Sales Dataset

Suppose an online store wants to analyze its monthly sales. We can create a DataFrame representing order records.

Python

import pandas as pd

data = {
    "OrderID": [1001, 1002, 1003, 1004, 1005, 1006],
    "Product": ["Laptop", "Phone", "Laptop", "Headphones", "Phone", "Tablet"],
    "Category": ["Electronics", "Electronics", "Electronics",
                 "Accessories", "Electronics", "Electronics"],
    "City": ["Pune", "Mumbai", "Pune", "Delhi", "Mumbai", "Pune"],
    "Quantity": [2, 3, 1, 5, 2, 4],
    "Price": [60000, 30000, 60000, 3000, 30000, 25000]
}

df = pd.DataFrame(data)

print(df)

Example: The DataFrame now represents customer orders that can be analyzed using Pandas.

3. Understanding the Dataset

View the First Rows

head()

print(df.head())

View the Last Rows

tail()

print(df.tail())

Check Number of Rows and Columns

shape

print(df.shape)

Example Output:
(6, 6)

4. Inspecting Data Types and Information

Use info() to understand column names, data types, and missing values.

Python

df.info()

You can also inspect individual data types.

Python

print(df.dtypes)

5. Selecting Columns

Selecting columns allows you to focus on the information needed for a particular analysis.

Single Column

print(df["Product"])

Multiple Columns

print(df[["Product", "Quantity", "Price"]])

6. Calculating Sales Amount

A common analysis task is calculating the total value of every order.

Python

df["Sales"] = df["Quantity"] * df["Price"]

print(df[["OrderID", "Product", "Quantity", "Price", "Sales"]])

Business Meaning: The new Sales column represents the total order value.

7. Basic Statistical Analysis

Total Sales

sum()

total_sales = df["Sales"].sum()

print("Total Sales:", total_sales)

Average Sales

mean()

average_sales = df["Sales"].mean()

print("Average Sales:", average_sales)

Maximum and Minimum Sales

max() and min()

print("Maximum:", df["Sales"].max())
print("Minimum:", df["Sales"].min())

8. Using describe()

The describe() function provides statistical information such as count, mean, standard deviation, minimum, maximum, and quartiles.

Python

print(df.describe())

Useful for: quickly understanding the distribution of numerical columns.

9. Filtering Data

Filtering helps answer questions such as: "Which orders generated more than ₹50,000?"

Python

high_value_orders = df[df["Sales"] > 50000]

print(high_value_orders)

Another example is finding all orders from Pune.

Python

pune_orders = df[df["City"] == "Pune"]

print(pune_orders)

10. Filtering with Multiple Conditions

Use & for AND conditions and | for OR conditions.

AND Condition

result = df[
    (df["City"] == "Pune") &
    (df["Sales"] > 50000)
]

print(result)

OR Condition

result = df[
    (df["City"] == "Pune") |
    (df["City"] == "Mumbai")
]

print(result)

11. Sorting Data

Sorting is useful when identifying the highest-selling products or customers.

Descending Sales

sorted_df = df.sort_values(
    by="Sales",
    ascending=False
)

print(sorted_df)

Real-Time Use: A sales manager can immediately see the largest orders first.

12. Counting Categories

value_counts() helps understand how frequently each category appears.

Product Frequency

print(df["Product"].value_counts())

Orders by City

print(df["City"].value_counts())

13. GroupBy Analysis

groupby() is one of the most important Pandas tools for business analysis.

Total Sales by City

Python

city_sales = df.groupby("City")["Sales"].sum()

print(city_sales)

Total Sales by Product

Python

product_sales = df.groupby("Product")["Sales"].sum()

print(product_sales)

14. Multiple Aggregations

You can calculate multiple statistics for each group.

Python

summary = df.groupby("Product").agg(
    Total_Sales=("Sales", "sum"),
    Average_Sales=("Sales", "mean"),
    Total_Quantity=("Quantity", "sum"),
    Number_of_Orders=("OrderID", "count")
)

print(summary)

Business Insight: This single analysis can show how much each product sold, its average order value, total units, and order count.

15. Finding the Best-Selling Product

Python

product_sales = df.groupby("Product")["Sales"].sum()

best_product = product_sales.idxmax()
best_sales = product_sales.max()

print("Best Product:", best_product)
print("Sales:", best_sales)

Important: idxmax() returns the label associated with the largest value.

16. Finding the Lowest-Selling Product

Python

product_sales = df.groupby("Product")["Sales"].sum()

lowest_product = product_sales.idxmin()
lowest_sales = product_sales.min()

print("Lowest Product:", lowest_product)
print("Sales:", lowest_sales)

17. Analyzing Quantity Sold

Revenue alone does not tell the complete story. A product may have high revenue because of a high price, while another product may sell many more units.

Python

quantity_by_product = df.groupby("Product")["Quantity"].sum()

print(quantity_by_product)

18. Analyzing Sales by City

Python

city_analysis = df.groupby("City").agg(
    Total_Sales=("Sales", "sum"),
    Total_Quantity=("Quantity", "sum"),
    Orders=("OrderID", "count")
)

print(city_analysis)

Real-Time Use: Regional managers can compare revenue, units sold, and order volume for different cities.

19. Calculating Sales Percentage

Businesses often want to know what percentage of total revenue comes from each product.

Python

product_sales = df.groupby("Product")["Sales"].sum()

sales_percentage = (
    product_sales / product_sales.sum()
) * 100

print(sales_percentage)

20. Finding the Average Order Value

Average Order Value, or AOV, is a common e-commerce metric.

Python

aov = df["Sales"].mean()

print("Average Order Value:", aov)

21. Adding Business Metrics

You can create additional columns to support analysis.

Profit Calculation

df["Cost"] = df["Sales"] * 0.70
df["Profit"] = df["Sales"] - df["Cost"]

print(df[["Product", "Sales", "Cost", "Profit"]])

Business Meaning: Here, the example assumes product cost is 70% of sales. In a real application, actual cost data should be used.

22. Profit Analysis by Product

Python

profit_by_product = df.groupby("Product")["Profit"].sum()

print(profit_by_product)

23. Detecting Missing Values

Missing values can affect analysis results, so they should be checked before calculating important metrics.

Python

print(df.isnull().sum())

A result of zero means that column currently contains no missing values.

24. Handling Missing Values

Fill Numeric Missing Values

Python

df["Price"] = df["Price"].fillna(
    df["Price"].mean()
)

Remove Rows with Missing Values

Python

df = df.dropna()

25. Removing Duplicate Records

Python

df = df.drop_duplicates()

print(df)

This is useful when the same transaction has accidentally been loaded more than once.

26. Reading Sales Data from CSV

Real business analysis usually starts with files rather than manually created DataFrames.

Python

df = pd.read_csv("sales.csv")

print(df.head())

CSV files are commonly used to exchange structured data between business applications.

27. Reading Excel Data

Python

df = pd.read_excel("sales.xlsx")

print(df.head())

28. Real-Time Example: Customer Analysis

An e-commerce company wants to identify customers who have spent more than ₹100,000.

Python

customer_data = {
    "Customer": ["Amit", "Priya", "Rahul", "Sneha", "Vikas"],
    "Orders": [8, 12, 5, 15, 10],
    "TotalSpent": [75000, 145000, 45000, 180000, 120000]
}

customers = pd.DataFrame(customer_data)

high_value_customers = customers[
    customers["TotalSpent"] > 100000
]

print(high_value_customers)

Business Insight: The company can use this analysis to identify customers for loyalty programs or personalized offers.

29. Real-Time Example: Employee Salary Analysis

Python

employees = pd.DataFrame({
    "Name": ["Amit", "Priya", "Rahul", "Sneha", "Vikas"],
    "Department": [
        "IT", "HR", "IT", "Finance", "IT"
    ],
    "Salary": [
        65000, 55000, 80000, 72000, 90000
    ]
})

department_salary = employees.groupby(
    "Department"
)["Salary"].agg(["mean", "min", "max"])

print(department_salary)

Real-Time Use: HR teams can compare salary ranges across departments.

30. Real-Time Example: Inventory Analysis

A warehouse needs to identify products that are running low.

Python

inventory = pd.DataFrame({
    "Product": ["Laptop", "Phone", "Tablet", "Monitor", "Mouse"],
    "Stock": [25, 8, 40, 6, 75],
    "ReorderLevel": [10, 15, 15, 10, 20]
})

low_stock = inventory[
    inventory["Stock"] < inventory["ReorderLevel"]
]

print(low_stock)

Business Insight: The result identifies products that may need replenishment.

31. Real-Time Example: Student Performance Analysis

Python

students = pd.DataFrame({
    "Student": ["A", "B", "C", "D", "E"],
    "Math": [85, 72, 91, 65, 78],
    "Science": [88, 75, 89, 70, 82],
    "English": [80, 78, 92, 68, 85]
})

students["Average"] = students[
    ["Math", "Science", "English"]
].mean(axis=1)

print(students)

Find students with an average score above 80.

Python

top_students = students[
    students["Average"] > 80
]

print(top_students)

32. Real-Time Example: Monthly Sales Analysis

Python

monthly_sales = pd.DataFrame({
    "Month": [
        "January", "February", "March",
        "April", "May", "June"
    ],
    "Sales": [
        120000, 145000, 132000,
        168000, 175000, 190000
    ]
})

monthly_sales["Growth"] = (
    monthly_sales["Sales"].pct_change() * 100
)

print(monthly_sales)

Business Insight: pct_change() helps measure percentage change between consecutive periods.

33. Finding Sales Growth

Growth analysis helps determine whether business performance is increasing or decreasing over time.

Python

first_month = monthly_sales["Sales"].iloc[0]
last_month = monthly_sales["Sales"].iloc[-1]

growth = (
    (last_month - first_month) / first_month
) * 100

print("Overall Growth:", growth, "%")

34. Finding Top N Records

nlargest() can quickly identify the highest values.

Top 3 Orders

top_orders = df.nlargest(
    3,
    "Sales"
)

print(top_orders)

Similarly, nsmallest() can find the lowest values.

Lowest 3 Orders

lowest_orders = df.nsmallest(
    3,
    "Sales"
)

print(lowest_orders)

35. Comparing Categories

You can compare categories using grouped statistics.

Category Analysis

category_analysis = df.groupby("Category").agg(
    Revenue=("Sales", "sum"),
    Units=("Quantity", "sum"),
    Orders=("OrderID", "count")
)

print(category_analysis)

36. Using Pivot Tables

Pivot tables summarize data across multiple dimensions.

Sales by City and Product

pivot = pd.pivot_table(
    df,
    values="Sales",
    index="City",
    columns="Product",
    aggfunc="sum",
    fill_value=0
)

print(pivot)

Real-Time Use: Management can compare product revenue across cities in a single table.

37. Combining DataFrames

Concatenating Data

Python

january = pd.DataFrame({
    "Product": ["Laptop", "Phone"],
    "Sales": [120000, 90000]
})

february = pd.DataFrame({
    "Product": ["Laptop", "Phone"],
    "Sales": [140000, 110000]
})

combined = pd.concat(
    [january, february],
    ignore_index=True
)

print(combined)

38. Merging Customer and Order Data

In real applications, customer information and order information may be stored in separate tables.

Python

customers = pd.DataFrame({
    "CustomerID": [1, 2, 3],
    "CustomerName": ["Amit", "Priya", "Rahul"]
})

orders = pd.DataFrame({
    "CustomerID": [1, 2, 1, 3],
    "Sales": [50000, 75000, 30000, 90000]
})

merged = pd.merge(
    customers,
    orders,
    on="CustomerID",
    how="inner"
)

print(merged)

39. Real-Time Example: Customer Revenue Analysis

Python

customer_revenue = merged.groupby(
    "CustomerName"
)["Sales"].sum()

print(customer_revenue)

Business Insight: This analysis identifies how much revenue each customer generated.

40. Real-Time Example: E-Commerce Sales Analysis

The following example combines several Pandas analysis techniques into one workflow.

Complete Example

import pandas as pd

sales = pd.DataFrame({
    "OrderID": [1, 2, 3, 4, 5, 6, 7, 8],
    "Product": [
        "Laptop", "Phone", "Laptop", "Tablet",
        "Phone", "Laptop", "Tablet", "Phone"
    ],
    "City": [
        "Pune", "Mumbai", "Delhi", "Pune",
        "Mumbai", "Delhi", "Pune", "Delhi"
    ],
    "Quantity": [2, 3, 1, 4, 2, 1, 5, 3],
    "Price": [
        60000, 30000, 60000, 25000,
        30000, 60000, 25000, 30000
    ]
})

# Calculate revenue
sales["Revenue"] = (
    sales["Quantity"] * sales["Price"]
)

# Total revenue
total_revenue = sales["Revenue"].sum()

# Revenue by product
product_revenue = sales.groupby(
    "Product"
)["Revenue"].sum()

# Revenue by city
city_revenue = sales.groupby(
    "City"
)["Revenue"].sum()

# Top orders
top_orders = sales.nlargest(
    3,
    "Revenue"
)

print("Total Revenue:")
print(total_revenue)

print("\nRevenue by Product:")
print(product_revenue)

print("\nRevenue by City:")
print(city_revenue)

print("\nTop Orders:")
print(top_orders)

Analysis Workflow: Load data → calculate revenue → summarize products → summarize cities → identify high-value orders.

41. Creating an Analysis Summary

A summary DataFrame can combine important business metrics.

Python

summary = pd.DataFrame({
    "Metric": [
        "Total Orders",
        "Total Revenue",
        "Average Order Value",
        "Maximum Order"
    ],
    "Value": [
        len(df),
        df["Sales"].sum(),
        df["Sales"].mean(),
        df["Sales"].max()
    ]
})

print(summary)

42. Exporting Analysis Results

Save to CSV

Python

summary.to_csv(
    "sales_analysis.csv",
    index=False
)

Save to Excel

Python

summary.to_excel(
    "sales_analysis.xlsx",
    index=False
)

43. Useful Pandas Analysis Functions

Function	Purpose	Example
`head()`	View first rows	`df.head()`
`info()`	Inspect dataset	`df.info()`
`describe()`	Statistical summary	`df.describe()`
`sum()`	Calculate total	`df["Sales"].sum()`
`mean()`	Calculate average	`df["Sales"].mean()`
`count()`	Count records	`df["OrderID"].count()`
`groupby()`	Group records	`df.groupby("City")`
`sort_values()`	Sort records	`df.sort_values("Sales")`
`value_counts()`	Count categories	`df["City"].value_counts()`
`nlargest()`	Find highest values	`df.nlargest(5, "Sales")`
`nsmallest()`	Find lowest values	`df.nsmallest(5, "Sales")`
`pivot_table()`	Create summaries	`pd.pivot_table(...)`

44. Common Pandas Analysis Mistakes

1. Analyzing Dirty Data: Missing values, duplicates, and invalid values can produce misleading results.

2. Ignoring Data Types: Numbers stored as text cannot always be analyzed correctly.

3. Forgetting Filters: Always verify that the records being analyzed represent the intended population.

4. Misinterpreting Averages: An average can hide large differences between individual records.

45. Pandas Analysis Best Practices

Inspect the dataset before performing calculations.
Check for missing and duplicate values.
Verify column data types.
Use meaningful column names.
Create calculated columns for important business metrics.
Use groupby() for category-level analysis.
Use filtering to answer specific business questions.
Validate totals before publishing reports.
Keep raw data separate from transformed data.
Export important analysis results for reporting.

46. Complete Real-Time Business Analysis

Data

Collect and Load

Clean

Prepare Records

Analyze

Find Patterns

Act

Use Insights

Imagine an e-commerce company receives thousands of orders every day. A Pandas analysis workflow could look like this:

orders.csv

→

Clean Data

→

Group & Calculate

→

Find Trends

→

Business Report

Complete Workflow

import pandas as pd

# 1. Load data
df = pd.read_csv("orders.csv")

# 2. Inspect data
print(df.head())
print(df.info())

# 3. Remove duplicate records
df = df.drop_duplicates()

# 4. Handle missing values
df["Quantity"] = df["Quantity"].fillna(0)
df["Price"] = df["Price"].fillna(df["Price"].mean())

# 5. Calculate revenue
df["Revenue"] = df["Quantity"] * df["Price"]

# 6. Total revenue
total_revenue = df["Revenue"].sum()

# 7. Product analysis
product_analysis = df.groupby("Product").agg(
    Revenue=("Revenue", "sum"),
    Units=("Quantity", "sum"),
    Orders=("OrderID", "count")
)

# 8. City analysis
city_analysis = df.groupby("City")["Revenue"].sum()

# 9. Top orders
top_orders = df.nlargest(10, "Revenue")

# 10. Save results
product_analysis.to_csv(
    "product_analysis.csv"
)

city_analysis.to_csv(
    "city_analysis.csv"
)

print("Total Revenue:", total_revenue)
print(product_analysis)
print(city_analysis)
print(top_orders)

Final Result: The company can use the generated analysis to understand total revenue, product performance, regional performance, and high-value orders.

47. Important Pandas Analysis Rules

Pandas DataFrames are commonly used for structured data analysis.
Always inspect the data before analyzing it.
Use describe() for a quick statistical overview.
Use Boolean filtering to select relevant records.
Use groupby() to summarize categories.
Use aggregation functions such as sum(), mean(), count(), min(), and max().
Use sort_values() to identify high and low values.
Use pivot_table() for multi-dimensional summaries.
Check for missing values and duplicates before important calculations.
Validate analysis results against the original data.
Keep raw data separate from cleaned and analyzed data.
Document important assumptions used in calculations.
Export final results when they need to be shared or reported.

Python Pandas Analysis • Real-Time Examples • Data Analysis Learning