Analyzing Store Sales Data with Pandas: Precision Control, Percent Formatting, and Labeling

Photo by Christopher Gower on Unsplash

In this post, we’ll explore how to use Pandas to analyze store sales data in a practical and efficient way. We’ll start by preparing the dataset, including controlling numerical precision and converting decimal values into percentage formats for clearer reporting. Then, we’ll apply conditional logic to evaluate sales performance and automatically assign labels to sales records based on predefined rules. This step-by-step approach helps transform raw sales data into structured, interpretable insights that can support better business decisions.

1. Constructing the dataset

# Construct the dataset
import pandas as pd
import numpy as np

# Optional: fix the random seed for reproducible results
np.random.seed(42)

df = pd.DataFrame({
    "store_id": ["Store_000001", "Store_000002", "Store_000003", "Store_000004", "Store_000005"],
    "valid_order_revenue": np.random.randint(1000, 4679, size=5),
    "valid_order_count": np.random.randint(250, 460, size=5),
    "product_cost": np.random.randint(890, 3600, size=5)
})

print(df)

Code output is:

       store_id  valid_order_revenue  valid_order_count  product_cost
0  Store_000001                  4174                352          2104
1  Store_000002                  4507                372          3068
2  Store_000003                  1860                371          1466
3  Store_000004                  2294                444          2347
4  Store_000005                  2130                299          1805

2. Setting numerical precision for average order cost

# Calculate average order value (AOV) and set numerical precision to 2 decimal places
df["average_order_value"] = (df["valid_order_revenue"] / df["valid_order_count"]).round(2)

print(df)

Code output is:

       store_id  valid_order_revenue  valid_order_count  product_cost  average_order_value
0  Store_000001                  4174                352          2104                11.86
1  Store_000002                  4507                372          3068                12.12
2  Store_000003                  1860                371          1466                 5.01
3  Store_000004                  2294                444          2347                 5.17
4  Store_000005                  2130                299          1805                 7.12

3. Converting decimal values to percentage format for store gross margin

# Calculate gross margin, express it as a percentage, and set numerical precision to 2 decimal places
df["gross_margin"] = (
    (df["valid_order_revenue"] - df["product_cost"]) / df["valid_order_revenue"]
).apply(lambda x: format(x, ".2%"))

print(df)

Code output is:

       store_id  valid_order_revenue  valid_order_count  product_cost  average_order_value gross_margin
0  Store_000001                  4174                352          2104                11.86       49.61%
1  Store_000002                  4507                372          3068                12.12       31.88%
2  Store_000003                  1860                371          1466                 5.01       21.18%
3  Store_000004                  2294                444          2347                 5.17       -2.30%
4  Store_000005                  2130                299          1805                 7.12       15.25%

4. Applying conditional logic to assign labels for each store

# Add columns to determine profit and loss status
df["gross_profit_amount"] = df["valid_order_revenue"] - df["product_cost"]
df["profit_loss_status"] = np.where(
    df["gross_profit_amount"] > 0, "Profit",
    np.where(df["gross_profit_amount"] == 0, "Break-even", "Loss")
)

print(df)

Code output is:

       store_id  valid_order_revenue  valid_order_count  product_cost  average_order_value gross_margin  gross_profit_amount profit_loss_status
0  Store_000001                  4174                352          2104                11.86       49.61%                 2070             Profit
1  Store_000002                  4507                372          3068                12.12       31.88%                 1439             Profit
2  Store_000003                  1860                371          1466                 5.01       21.18%                  394             Profit
3  Store_000004                  2294                444          2347                 5.17       -2.30%                 -53              Loss
4  Store_000005                  2130                299          1805                 7.12       15.25%                  325             Profit

In the above example, we demonstrated a step-by-step approach to analyze store sales data using Pandas. We started by constructing a sample dataset and calculating key metrics such as average order value (AOV) and gross margin. By converting values into percentages and rounding numerical precision, we ensured clear and readable results.

Finally, we introduced conditional logic to classify each store’s profit and loss status as Profit, Break-even, or Loss. This workflow provides a solid foundation for transforming raw sales data into actionable insights for better business decisions.Hope you can apply this to more complex projects.