Master Pandas From Scratch

Learn Pandas fundamentals, Series, DataFrames, data selection, filtering, cleaning, sorting, grouping, merging, reshaping, string operations, time series, data analysis, performance optimization, visualization, and practical projects through structured lessons.

COURSE SYLLABUS
1. Pandas Introduction and Environment Setup
Introduction to Pandas Installation and Environment Setup
2. Pandas Core Data Structures
Data Structures Overview Pandas Series DataFrames Indexes, Rows and Columns Shape, Size and Dimensions
3. Pandas Data Input and Output
Data Input and Output Overview Reading CSV, Excel and JSON Files Writing and Exporting Files
4. Initial Data Inspection
Data Inspection Overview Head and Tail Data Info and Types Descriptive Statistics
5. Selection and Indexing
Selection and Indexing Overview Column Selection loc and iloc Index Management
6. Filtering and Conditional Selection
Filtering Overview Boolean Filtering Multiple Conditions isin, between and str.contains
7. Basic Data Cleaning
Data Cleaning Overview Missing Values Duplicate Data Rename and Replace Data Type Conversion
8. Vectorized Operations and Sorting
Operations and Sorting Overview Column Operations Sorting Values and Indexes Sum, Mean, Max and Min
9. GroupBy and Aggregation
GroupBy Overview Grouping Data Aggregation Functions Group Filtering Group Transform
10. Merging, Joining and Concatenating
Merge, Join and Concat Overview Concatenation Merging DataFrames Joining by Index
11. Reshaping and Pivoting
Reshaping Overview Pivot Tables Melting Data Stack and Unstack Cross Tabulation
12. String and Categorical Data
String and Categorical Overview String Operations Categorical Data One-Hot Encoding
13. Time Series Analysis
Time Series Overview DateTime Operations Time-Based Indexes Resampling Data Shift and Rolling Windows
14. Custom Functions and Mapping
Apply and Mapping Overview Apply and Lambda Functions Map Operations Element-Wise Operations
15. Performance and Large Datasets
Performance Overview Memory Optimization Processing Files in Chunks PyArrow, Dask and Polars
16. Pandas Data Visualization
Visualization Overview Basic Plotting Histograms, Lines, Scatter and Box Plots Matplotlib and Seaborn

Operations and Sorting in Pandas

Learn how to perform arithmetic operations on DataFrame columns, create calculated columns, sort records, calculate summary statistics, and prepare structured data for analysis.

1. Introduction to Data Operations

Pandas provides powerful tools for transforming and analyzing tabular data. You can calculate totals, apply discounts, compare values, sort records, and summarize information without writing a separate loop for every row.

Consider a sales dataset containing product names, quantities, prices, and discounts. Samadhan wants to calculate the total amount for each product, identify the most expensive products, and summarize the sales.

What You Will Learn

  • Perform arithmetic operations on Series and DataFrame columns.
  • Create new columns from existing data.
  • Apply conditional calculations.
  • Sort rows by one or multiple columns.
  • Calculate totals, averages, minimums, and maximums.
  • Validate results before using them in reports.
Source Data Product Quantity Price Operations Multiplication Discounts Totals Sorting Highest sales Lowest price Ranking Results Reports Insights Decisions A complete data-analysis workflow Transform the data, organize the results, and summarize key values.

Pandas operations and sorting help turn raw records into useful analytical results.

2. Create a Sample Sales DataFrame

We will use the following dataset throughout the lesson. Each row represents a product and its sales information.

import pandas as pd

df = pd.DataFrame({
    "Product": [
        "Laptop",
        "Mouse",
        "Keyboard",
        "Monitor",
        "Headphones"
    ],
    "Quantity": [2, 10, 5, 3, 4],
    "Price": [55000, 500, 1500, 12000, 2500],
    "Discount": [5000, 50, 100, 1000, 200]
})

print(df)

Expected Output

      Product  Quantity  Price  Discount
0      Laptop         2  55000      5000
1       Mouse        10    500        50
2    Keyboard         5   1500       100
3     Monitor         3  12000      1000
4  Headphones         4   2500       200

The DataFrame contains five products. We can use its numeric columns to calculate revenue, discounts, and final amounts.

3. Perform Arithmetic Operations

Pandas supports arithmetic operations directly on Series. When an operation uses two columns from the same DataFrame, Pandas calculates the result element by element.

Operator Operation Example
+ Addition df["Price"] + 100
- Subtraction df["Price"] - df["Discount"]
* Multiplication df["Quantity"] * df["Price"]
/ Division df["Price"] / 2
** Exponentiation df["Quantity"] ** 2
% Remainder df["Quantity"] % 2

Example: Add a Fixed Amount to Every Price

df["PriceWithFee"] = df["Price"] + 100

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

Expected Output

      Product  Price  PriceWithFee
0      Laptop  55000         55100
1       Mouse    500           600
2    Keyboard   1500          1600
3     Monitor  12000         12100
4  Headphones   2500          2600

The new column is calculated from the existing price column. The original Price values remain unchanged.

4. Create Calculated Columns

One of the most common Pandas tasks is creating a new column from existing columns. A calculated column is useful for revenue, taxes, discounts, profit, and other business metrics.

Calculate Gross Amount

The gross amount is the quantity multiplied by the unit price.

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

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

Expected Output

      Product  Quantity  Price  GrossAmount
0      Laptop         2  55000       110000
1       Mouse        10    500         5000
2    Keyboard         5   1500         7500
3     Monitor         3  12000        36000
4  Headphones         4   2500        10000

Calculate the Final Amount

In this example, the discount is a per-unit discount. Therefore, the total discount for a product is its unit discount multiplied by the quantity.

df["TotalDiscount"] = df["Quantity"] * df["Discount"]

df["FinalAmount"] = (
    df["GrossAmount"] - df["TotalDiscount"]
)

print(df[[
    "Product",
    "GrossAmount",
    "TotalDiscount",
    "FinalAmount"
]])

Expected Output

      Product  GrossAmount  TotalDiscount  FinalAmount
0      Laptop       110000          10000       100000
1       Mouse         5000            500         4500
2    Keyboard         7500            500         7000
3     Monitor        36000           3000        33000
4  Headphones        10000            800         9200
Business rule: Always confirm whether a discount is per item or per order. The correct formula depends on how the source data defines the discount.

5. Apply Conditional Operations

Pandas lets you select rows using conditions and update a column for those rows. This is useful when a calculation or classification depends on a rule.

Classify Products by Price

df["PriceCategory"] = "Standard"

df.loc[
    df["Price"] >= 10000,
    "PriceCategory"
] = "Premium"

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

Expected Output

      Product  Price PriceCategory
0      Laptop  55000       Premium
1       Mouse    500      Standard
2    Keyboard   1500      Standard
3     Monitor  12000       Premium
4  Headphones   2500      Standard

Each product starts with the category Standard. Rows whose price is at least 10,000 are then updated to Premium.

Use np.where() for Two Outcomes

import numpy as np

df["PriceCategory"] = np.where(
    df["Price"] >= 10000,
    "Premium",
    "Standard"
)

print(df[["Product", "PriceCategory"]])

np.where() selects one value when the condition is true and another when it is false. For more complex conditions, you can use multiple Boolean masks or np.select().

6. Sort Data by One Column

The sort_values() method sorts rows according to one or more column values. By default, sorting is ascending.

Sort by Price in Ascending Order

sorted_price = df.sort_values(
    by="Price"
)

print(sorted_price[["Product", "Price"]])

Expected Output

      Product  Price
1       Mouse    500
2    Keyboard   1500
4  Headphones   2500
3     Monitor  12000
0      Laptop  55000

Sort by Price in Descending Order

sorted_price_desc = df.sort_values(
    by="Price",
    ascending=False
)

print(sorted_price_desc[["Product", "Price"]])

Expected Output

      Product  Price
0      Laptop  55000
3     Monitor  12000
4  Headphones   2500
2    Keyboard   1500
1       Mouse    500

Sorting returns a DataFrame in a different row order. By default, it does not modify the original DataFrame.

7. Sort by Multiple Columns

Sometimes a dataset needs to be sorted using more than one rule. Pass a list of column names to by and a corresponding list of sort directions to ascending.

Example: Sort by Category and Price

multi_sort = df.sort_values(
    by=["PriceCategory", "Price"],
    ascending=[True, False]
)

print(multi_sort[[
    "Product",
    "Price",
    "PriceCategory"
]])

Expected Output

      Product  Price PriceCategory
0      Laptop  55000       Premium
3     Monitor  12000       Premium
4  Headphones   2500      Standard
2    Keyboard   1500      Standard
1       Mouse    500      Standard

Products are first grouped alphabetically by PriceCategory. Within each category, the price is sorted from highest to lowest.

How Multiple Sorting Works

  1. The first column defines the primary sorting order.
  2. The second column sorts rows that share the same primary value.
  3. Additional columns can be added to resolve further ties.

8. Sort by Index

The sort_index() method sorts rows by their index labels. Use it when the index order needs to be restored or arranged independently of the column values.

df_shuffled = df.sample(
    frac=1,
    random_state=42
)

sorted_index = df_shuffled.sort_index()

print(sorted_index[["Product", "Price"]])

The output returns rows to ascending index order. The random shuffle is reproducible because the example specifies a random_state.

Sort Index in Descending Order

reverse_index = df.sort_index(
    ascending=False
)

print(reverse_index.index.tolist())

Expected Output:

[4, 3, 2, 1, 0]

9. Calculate Summary Statistics

Pandas provides built-in aggregation methods that summarize numerical columns. These methods help answer questions about totals, averages, and value ranges.

Method Purpose
sum() Calculates a total.
mean() Calculates the arithmetic average.
median() Returns the middle value after ordering the data.
min() Returns the minimum value.
max() Returns the maximum value.
count() Counts non-missing values.
std() Calculates standard deviation.
describe() Produces a statistical summary of a Series or DataFrame.

Calculate Total Quantity and Average Price

print("Total quantity:", df["Quantity"].sum())
print("Average price:", df["Price"].mean())
print("Minimum price:", df["Price"].min())
print("Maximum price:", df["Price"].max())

Expected Output

Total quantity: 24
Average price: 14300.0
Minimum price: 500
Maximum price: 55000

These calculations summarize the entire column. They do not create new rows or change the original values.

10. Use describe() for Statistical Summaries

The describe() method provides a compact statistical summary for numerical columns, including count, mean, standard deviation, minimum, quartiles, and maximum.

print(df[["Quantity", "Price"]].describe())

Expected Output

        Quantity         Price
count   5.000000      5.000000
mean    4.800000  14300.000000
std     3.193744  23135.902031
min     2.000000    500.000000
25%     3.000000   1500.000000
50%     4.000000   2500.000000
75%     5.000000  12000.000000
max    10.000000  55000.000000

The quartiles help describe the distribution of the values. For example, the median price is 2,500, while the maximum price is 55,000.

11. Aggregate Multiple Columns

The agg() method lets you apply one or more aggregation functions to a Series or DataFrame. It is useful when multiple summary metrics are needed together.

Calculate Multiple Statistics for Price

price_summary = df["Price"].agg([
    "sum",
    "mean",
    "min",
    "max"
])

print(price_summary)

Expected Output

sum     71500.0
mean    14300.0
min       500.0
max     55000.0
Name: Price, dtype: float64

Use Different Aggregations for Different Columns

summary = df.agg({
    "Quantity": ["sum", "mean"],
    "Price": ["min", "max", "mean"]
})

print(summary)

The resulting DataFrame places aggregation functions in rows and source columns in columns. Some cells may contain missing values because not every function was requested for every column.

12. Find the Highest-Value Products

Sorting and calculated columns can be combined to identify the records with the highest sales values.

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

top_products = df.sort_values(
    by="GrossAmount",
    ascending=False
).head(3)

print(top_products[[
    "Product",
    "Quantity",
    "Price",
    "GrossAmount"
]])

Expected Output

      Product  Quantity  Price  GrossAmount
0      Laptop         2  55000       110000
3     Monitor         3  12000        36000
4  Headphones         4   2500        10000

The head(3) method returns the first three rows after sorting. Here, the laptop has the highest gross amount, followed by the monitor and headphones.

13. Understand Sorting and Index Behavior

Sorting changes row order, but it usually preserves the existing index labels. If a fresh sequential index is needed after sorting, call reset_index(drop=True).

sorted_df = (
    df.sort_values(
        by="Price",
        ascending=False
    )
    .reset_index(drop=True)
)

print(sorted_df[["Product", "Price"]])

Expected Output

      Product  Price
0      Laptop  55000
1     Monitor  12000
2  Headphones   2500
3    Keyboard   1500
4       Mouse    500

The sorted result now has index labels from 0 to 4. The drop=True argument prevents the old index from becoming a separate column.

14. Complete Sales Analysis Example

This final example combines calculations, sorting, and aggregation into a single workflow.

import pandas as pd

sales = pd.DataFrame({
    "Product": [
        "Laptop",
        "Mouse",
        "Keyboard",
        "Monitor",
        "Headphones"
    ],
    "Quantity": [2, 10, 5, 3, 4],
    "Price": [55000, 500, 1500, 12000, 2500],
    "Discount": [5000, 50, 100, 1000, 200]
})

# Calculate the gross amount.
sales["GrossAmount"] = (
    sales["Quantity"] * sales["Price"]
)

# Calculate the total discount.
sales["TotalDiscount"] = (
    sales["Quantity"] * sales["Discount"]
)

# Calculate the final amount.
sales["FinalAmount"] = (
    sales["GrossAmount"] - sales["TotalDiscount"]
)

# Sort products by final amount.
sales_sorted = sales.sort_values(
    by="FinalAmount",
    ascending=False
).reset_index(drop=True)

# Summarize the sales.
total_sales = sales["FinalAmount"].sum()
average_sales = sales["FinalAmount"].mean()

print(sales_sorted[[
    "Product",
    "GrossAmount",
    "TotalDiscount",
    "FinalAmount"
]])

print("Total sales:", total_sales)
print("Average sales per product:", average_sales)

Expected Output

      Product  GrossAmount  TotalDiscount  FinalAmount
0      Laptop       110000          10000       100000
1     Monitor        36000           3000        33000
2  Headphones        10000            800         9200
3    Keyboard         7500            500         7000
4       Mouse         5000            500         4500

Total sales: 153700
Average sales per product: 30740.0
Key takeaway: Calculations create useful analytical columns, sorting makes important records easier to identify, and aggregation provides a high-level view of the data.

15. Common Mistakes to Avoid

  • Performing arithmetic on text: Convert columns to numeric types before mathematical calculations.
  • Using the wrong discount formula: Confirm whether a discount applies per item or per order.
  • Forgetting ascending=False: Sorting is ascending by default.
  • Assuming sorting resets the index: Use reset_index(drop=True) if sequential labels are needed.
  • Confusing sum and mean: A total and an average answer different business questions.
  • Ignoring missing values: Understand how missing data affects the calculation and result.
  • Assuming calculated columns are automatically updated: If source columns change later, recalculate dependent columns when necessary.

16. Quick Revision

Method or Expression Purpose
df["A"] + df["B"] Add values element by element.
df["Total"] = df["Quantity"] * df["Price"] Create a calculated column.
df.loc[condition, "Column"] = value Update values for rows matching a condition.
df.sort_values("Price") Sort by a column in ascending order.
df.sort_values("Price", ascending=False) Sort by a column in descending order.
df.sort_values(["A", "B"]) Sort by multiple columns.
df.sort_index() Sort by index labels.
df.reset_index(drop=True) Create a fresh sequential index.
df["Price"].sum() Calculate the sum of a column.
df["Price"].mean() Calculate the average of a column.
df.describe() Summarize numerical columns statistically.
df.agg(["sum", "mean"]) Apply aggregation functions to the selected data.

17. Practice Questions and Exercises

Concept Questions

  1. How does Pandas perform arithmetic between two Series?
  2. How would you calculate a total amount using quantity and unit price?
  3. What is the difference between ascending and descending sorting?
  4. How can you sort a DataFrame by two columns?
  5. What is the purpose of reset_index(drop=True)?
  6. What is the difference between sum() and mean()?
  7. How does describe() help with data analysis?

Hands-On Exercises

  1. Create a DataFrame containing product names, quantities, and prices.
  2. Calculate the gross amount for every product.
  3. Add a discount column and calculate the final amount.
  4. Classify products as premium or standard using a price condition.
  5. Sort products from highest to lowest final amount.
  6. Sort by category and then by price within each category.
  7. Calculate total sales, average sales, minimum sales, and maximum sales.
  8. Display a statistical summary using describe().
  9. Return the three products with the highest final amounts.
  10. Reset the index after sorting and verify the final DataFrame.
Learning goal: Practice combining calculated columns, conditional updates, sorting, and aggregation. These operations form the foundation of many practical Pandas analysis workflows.

Summary

Pandas operations transform existing data into useful analytical values. Arithmetic expressions calculate values element by element, and calculated columns can represent totals, discounts, or other business metrics. The sort_values() and sort_index() methods organize records, while sum(), mean(), describe(), and agg() summarize the data. Always validate formulas, sorting rules, data types, and missing values before using the results in a report or business decision.