Before judging order satisfaction, determine whether multiple reviews belong to the same product, order, or customer. Data harmonization connects separate datasets into one coherent representation so those relationships can be checked rather than assumed. section 2.6.4
Treat the task like assembling a meal from ingredients with different labels: confirm that units, column names, and meanings agree before interpreting the result. Exploratory data analysis places mapping and harmonization alongside distribution checks, visualization, and data-meaning clarification. section 2.4.2
Link reviews to customers
Suppose 547 orders have multiple reviews. A tempting prompt-generated solution merges df_order_reviews directly with df_customers on customer_id, but that relationship is not available directly in the reviews data. First join orders to customers, keep order_id and customer_unique_id, then join that result to reviews by order_id. Finally, count distinct customers per order and inspect any order associated with more than one customer. section 2.6.4
merged_df = df_order_reviews.merge(orders_customers[['order_id', 'customer_unique_id']], on='order_id', how='left')
Use the indirect pandas merge to carry customer_unique_id from the orders-customer relationship into the review records. section 2.6.4
unique_customers_per_order =
df_order_reviews.groupby('order_id')['customer_unique_id'].nunique()
multiple_customers_orders =
unique_customers_per_order[unique_customers_per_order > 1]
if len(multiple_customers_orders) > 0:
print(f"There are {len(multiple_customers_orders)} orders with reviews
from multiple customers.")
print("\nOrder IDs and the number of unique customers:")
print(multiple_customers_orders)
else:
print("There are no orders with reviews from multiple customers.")
Validate the harmonized records by grouping reviews by order_id and checking whether any order has reviews from multiple unique customers. section 2.6.4
Inspect generated code and outputs instead of trusting a plausible join. Check column availability, join paths, matched values, and flagged records against the raw data; no single preparation protocol fits every dataset. section 2.6.4 section 2.4.2
