Sales volume answers a practical question: how many items sold? For a category comparison, count sales records and group them by product category and customer state. Keep volume separate from revenue, which sums prices. A category can rank differently on the two measures. section 3.3.1
Ask for sales, not catalog size
A request for popular categories by sales volume and revenue first produced code that counted products in each category. That answers how many products appear in the catalog, not how many sold. A more specific prompt named both measures and asked for their variation across Brazilian regions. Check the generated calculation against the question before using its rankings. section 3.3.1
import pandas as pd
# Read DataFrames
df_products = pd.read_csv('df_products.csv')
df_product_category_translation =
pd.read_csv('df_product_category_translation.csv')
df_order_items = pd.read_csv('df_order_items.csv')
df_orders = pd.read_csv('df_orders.csv')
df_customers = pd.read_csv('df_customers.csv')
# Merge DataFrames
df_products_merged = df_products.merge(
df_product_category_translation, on='product_category_name',
how='left')
df_order_items_merged = df_order_items.merge(
df_products_merged, on='product_id', how='left')
df_orders_merged = df_orders.merge(
df_customers, on='customer_id', how='left')
df_merged = df_order_items_merged.merge(
df_orders_merged, on='order_id', how='left')
# Calculate sales volume and revenue per category
category_sales = df_merged.groupby(
['product_category_name_english', 'customer_state']).agg(
{'order_id': 'count', 'price': 'sum'}).reset_index()
category_sales.columns =
['product_category', 'customer_state', 'sales_volume', 'revenue']
# Sort by sales_volume and revenue
category_sales_sorted = category_sales.sort_values(
by=['sales_volume', 'revenue'], ascending=False)
# Display the results
print("The most popular product categories
in terms of sales volume and revenue are:")
print(category_sales_sorted.head(10))
print("\nSales performance across different regions in Brazil:")
sales_by_region =
category_sales_sorted.pivot_table(
index='product_category',
columns='customer_state',
values=['sales_volume', 'revenue'], fill_value=0)
print(sales_by_region)
The pandas workflow joins products, category translations, order items, orders, and customers, then groups by category and customer state. It counts order IDs for volume and sums prices for revenue. Inspect the joined rows before treating that count as the intended number of units. section 3.3.1
Plotting volume by category and state can make regional differences easier to inspect, but generated plotting code may fail: one attempt could not find `customer_state` in the supplied data. Verify the DataFrame and its columns before fixing the chart. If the analysis shifts to average price, freight, and payment values, treat those as different performance measures—not substitutes for volume. section 3.3.1 section 3.3.2
