607. Sales Person.md

607. Sales Person

Question Link

Intuition

Didn't use any complex ways or algorithm ;-)

Approach

1. merge the sales_person and orders, using how='outer'to match the salesman and the order they made. The result table is a.
2. merge the a and company, using how='left'to match the salesman, order and company. The result table is b.
3. We only need salesman's name(which is b's name_x) and company's name(which is b's name_y). Filter these two and the result is c.
4. name_red is salemans who negotiated any order with RED. Filter them.
5. merge c and name_red and get d. Now, in name_y_y column of d, every salesman who have ever made a order with RED, has a RED in his/her row.
6. Filter those who doesn't have RED and create a new DataFrame. Don't forget drop_duplicates() when outputing.

Code

import pandas as pd

def sales_person(sales_person: pd.DataFrame, company: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    a=pd.merge(
        sales_person,orders,how='outer',on='sales_id'
    )
    b=pd.merge(
        a,company,how='left',on='com_id'
    )
    c=b.loc[:,['name_x','name_y']]
    name_red=c[c['name_y'] == 'RED']
    d=pd.merge(
        c,name_red,how='outer',on='name_x'
    )
    result=pd.DataFrame({'name':d[d['name_y_y'] != 'RED']['name_x']})
    return result.drop_duplicates()