Data grouping answers practical questions such as which product categories sell most and how performance differs across Brazilian regions. A groupby operation partitions rows by one or more key columns, creating a separate subset for each category–region or category–review combination. Aggregations such as averages and sums then turn those subsets into a comparison table. section 3.3.2 section 4.4
A practical grouping workflow combines calculation with inspection:
- Choose keys that match the business question, such as product category plus customer state or category plus review type. section 3.3.2 section 4.4
- Select meaningful measures and aggregations, such as mean product price, freight value, payment value, or summed review counts. section 3.3.2 section 4.4
- Flatten the grouped result with reset_index() so the keys become ordinary columns for tables and charts. section 3.3.2
- Inspect column names, row meanings, and scale before interpreting patterns; generated code may refer to an undefined DataFrame or produce misleading visual emphasis. section 3.3.2 section 4.4
From grouped counts to review ratios
For review analysis, group a review-score distribution by product category and review category, sum count, and reset the index. Then group the result by product group and use transform() to express each category’s count as a share of its group total. The resulting table supports both count comparisons and positive-to-negative review ratios. section 4.4
import pandas as pd # Assuming you have already merged the DataFrames and named it 'merged_data' # Group the data by product category and region (state) grouped_data = merged_data.groupby( ['product_category_name_english', 'customer_state']) # Calculate the average product price, freight value, and payment value for each group average_values = grouped_data[ ['price', 'freight_value', 'payment_value']].mean() # Reset the index to have a flat DataFrame average_values = average_values.reset_index() # Rename the columns for clarity average_values.columns = ['product_category', 'state', 'avg_product_price', 'avg_freight_value', 'avg_payment_value'] # You can now explore the 'average_values' DataFrame to analyze the differences across product categories and regions
Browse a complete pandas workflow that groups product categories and customer states, averages selected numeric columns, flattens the result, and renames columns for analysis. section 3.3.2
