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.
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
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
- The first column defines the primary sorting order.
- The second column sorts rows that share the same primary value.
- 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
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
- How does Pandas perform arithmetic between two Series?
- How would you calculate a total amount using quantity and unit price?
- What is the difference between ascending and descending sorting?
- How can you sort a DataFrame by two columns?
- What is the purpose of
reset_index(drop=True)? - What is the difference between
sum()andmean()? - How does
describe()help with data analysis?
Hands-On Exercises
- Create a DataFrame containing product names, quantities, and prices.
- Calculate the gross amount for every product.
- Add a discount column and calculate the final amount.
- Classify products as premium or standard using a price condition.
- Sort products from highest to lowest final amount.
- Sort by category and then by price within each category.
- Calculate total sales, average sales, minimum sales, and maximum sales.
- Display a statistical summary using
describe(). - Return the three products with the highest final amounts.
- Reset the index after sorting and verify the final DataFrame.
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.