Which kinds of products bring in the most revenue, and does that change across Brazilian states? A product category gives each product a grouping that can be used to compare sales, revenue, and customer reviews. The grouping is useful only when the question and the count match: the most common category in a product catalog is not necessarily the one with the most sales. section 3.3.1
To answer a sales question, connect products to their category translations, then to order items, orders, and customers. Group the resulting records by English category name and customer state. Count order-item records for sales volume and sum prices for revenue. A category count made directly from the products table answers a different question: how many catalog entries carry each label. 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 the tables and calculates category-by-state counts and revenue. Inspect the join keys and aggregation columns before using its results. section 3.3.1
A chart can make regional differences easier to inspect: grouped bars compare categories within states, while heatmaps place categories and states on opposing axes. Check that the plotting DataFrame actually contains the named columns; one attempted bar plot failed because it could not find customer_state. Crowded category labels can also obscure the comparison. section 3.3.1
Categories also let you ask a different business question: how do review scores vary by product type and seller? Join orders, order items, products, reviews, and category translations; then count records by category, seller, and review score. A grouped bar chart can show those counts, though many categories and score levels make it busy. section 3.3.3
def categorize_product_category(category):
category_mapping = {
'Home & Furniture': ['bed_bath_table', 'furniture_bedroom',
'furniture_decor', 'furniture_living_room',
'furniture_mattress_and_upholstery',
'kitchen_dining_laundry_garden_furniture', 'home_confort',
'home_comfort_2', 'home_construction', 'housewares'],
# ... Include other mappings here ...
}
for key, values in category_mapping.items():
if category in values:
return key
return 'Unknown'
review_score_distribution['product_category_group'] =
review_score_distribution['product_category_name_english'].apply(
categorize_product_category)
A mapping combines individual category names into broader product groups and returns Unknown when no name matches. Check those unmatched names before interpreting a grouped chart. section 3.3.3
