merge combines tabular data by matching rows on shared key columns. A practical question might be whether each order review belongs to the expected customer. The tempting solution is to merge df_order_reviews with df_customers on customer_id using a left join, preserving every review row. section 2.6.4
Choose the key before merging
That first merge is not automatically valid: customer_id does not identify customers reliably in this data. Since order_id is the actual identifier, first merge orders with customers to obtain customer_unique_id, then merge that result into reviews on order_id. section 2.6.4
merged_df = df_order_reviews.merge(df_customers, on='customer_id', how='left')
Inspect the straightforward left join that adds customer_unique_id to review rows through customer_id. section 2.6.4
merged_df = df_order_reviews.merge(orders_customers[['order_id', 'customer_unique_id']], on='order_id', how='left')
Inspect the corrected two-stage merge that carries customer_unique_id through order_id. 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 result by counting distinct customers per order and identifying orders associated with multiple customers. section 2.6.4
