Hanukkah of Data 5784 - Day 2 The Contractor

:: programming, python, puzzle

The Task

Find the phone number of the customer who satisfies the following criteria:

  • Has the initials “JP” (turns out this can be ignored!)
  • Purchased both coffee and cleaning supplies on the same order

Solution

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
import pandas as pd

def solve():
    customers   = pd.read_csv('noahs-customers.csv')
    orders      = pd.read_csv('noahs-orders.csv')
    order_items = pd.read_csv('noahs-orders_items.csv')
    products    = pd.read_csv('noahs-products.csv')
    data        = customers.merge(orders).merge(order_items).merge(products)

    cleaner_order_ids = set(data[data['desc'].str.contains('cleaner', case=False)]['orderid'].drop_duplicates())
    coffee_order_ids  = set(data[data['desc'].str.contains('coffee', case=False)]['orderid'].drop_duplicates())
    common_orders     = coffee_order_ids & cleaner_order_ids

    return data[data['orderid'] == common_orders.pop()].iloc[0]['phone']

# ---------------------------------------------------------------------------------------------

assert solve() == '332-274-4185'

Commentary

First we get the order ids for cleaner and for coffee:

1
2
 
cleaner_order_ids = set(data[data['desc'].str.contains('cleaner', case=False)]['orderid'].drop_duplicates())
coffee_order_ids  = set(data[data['desc'].str.contains('coffee', case=False)]['orderid'].drop_duplicates())

Then we obtain the orders that have both via set intersection:

1
 
common_orders = coffee_order_ids & cleaner_order_ids

Finally, we return the phone number from the one order containing both coffee and cleaner:

1
 
return data[data['orderid'] == common_orders.pop()].iloc[0]['phone']