An order status is a categorical label recording an order’s current or observed state, such as approved, shipped, or delivered. Comparing statuses across regions, product categories, and sellers can reveal logging problems, fulfillment issues, and user-experience opportunities. section 3.3.4
Analyze order statuses by:
- Merging order, item, product, and category-translation data into one DataFrame. section 3.3.4
- Grouping records by status, product category, and seller, then counting occurrences. section 3.3.4
- Using a stacked bar chart to compare status distributions and total order counts across categories. section 3.3.4
- Inspecting surprising results: an approved order that never appears to have shipped may indicate a data or fulfillment problem. section 3.3.4
A first status check
A grouped result can show how approved, shipped, and delivered orders vary by category and seller. A stacked chart makes broad differences visible, but unusual records still require checking against the underlying data before drawing conclusions. section 3.3.4
Listing 3.2 Preparing DataFrame with order statuses
import pandas as pd orders_items = pd.merge(df_orders, df_order_items, on='order_id') # Merge orders_items with df_products on 'product_id' orders_items_products = pd.merge( orders_items, df_products, on='product_id') # Merge orders_items_products with df_product_category_translation on 'product_category_name' merged_data = pd.merge(orders_items_products, df_product_category_translation, on='product_category_name') # Group by 'order_status', 'product_category_name_english', and 'seller_id', then count the occurrences status_distribution = merged_data.groupby(['order_status', 'product_category_name_english', 'seller_id']).size().reset_index( name='count') # Sort the result by 'order_status', 'product_category_name_english', and 'count' in descending order status_distribution_sorted = status_distribution.sort_values(['order_status', 'product_category_name_english', 'count'], ascending=[True, True, False]) print(status_distribution_sorted)
Use the prepared DataFrame workflow to merge related order tables and calculate status counts by category and seller. section 3.3.4
