Slice DataFrame based on Multiple conditions

user_14535260 0
Viewed 233

I hope you are doing well. I have a question regarding a DataFrame in Python.

Company Job Title Person Name
Apple Founder Steve Jobs
Apple Co-Founder Steve Wozniak
Microsoft Founder Bill Gates
Microsoft Co-Founder Paul Allen
Paypal Co-Founder Peter Thiel
Tesla Board Member Larry Ellison
Tesla CFO Zach Kirkhorn
Snapchat Devan Fearman Devan Fearman

I would like to filter and create a new Dataframe which keeps only the rows based on the following conditions:

  1. if Founder then keep row
  2. if can't find Founder then Co-Founder
  3. if can't find Co-Founder then Board Member
  4. if can't find Board Member then Board Member CFO

Drop all other rows from the Dataframe. Note that I am allowed to have only 1 row per company.

Therefore I would like to have the following output below:

Company Job Title Person Name
Apple Founder Steve Jobs
Microsoft Founder Bill Gates
Paypal Co-Founder Peter Thiel
Tesla Board Member Larry Ellison

I am sure there is a way to do this using a loop function, however since I am very new to Python this is still very hard for me. I am open to using all libraries like pandas and numpy. Please explain your code as clearly as possible.

Hope to hear from you soon. Thank you and please let me know if I was not clear with my question.

pandas dataframe filter for-loop python
3 Answers

You can use pd.Category here:

df['Job Title'] = pd.Categorical(df['Job Title'].str.strip(),
                                 categories=['Founder', 'Co-Founder', 'Board Member', 'CFO'],
                                 ordered=True
                                )

df.sort_values(['Job Title']).drop_duplicates('Company').dropna()

Output:

      Company     Job Title    Person Name
0      Apple        Founder     Steve Jobs
2  Microsoft        Founder     Bill Gates
4     Paypal     Co-Founder    Peter Thiel
5      Tesla   Board Member  Larry Ellison
user_42384080

Create a dictionary of Job Title ranking. Map to the Job Title. Drop any Job Title not in the rank. Sort values and drop duplicated companies.

 d={'Founder':1,'Co-Founder':2,'Board Member':3,'Board Member CFO':4}
 df=df.assign(rank=df['JOB TITLE'].map(d)).sort_values(by='rank').dropna().drop_duplicates(subset=['COMPANY']).drop('rank', 1)

     COMPANY     JOB TITLE    PERSON NAME  rank
0      Apple       Founder     Steve Jobs     1
2  Microsoft       Founder     Bill Gates     1
4     Paypal    Co-Founder    Peter Thiel     2
5      Tesla  Board Member  Larry Ellison     3
user_89869750

I would start by creating an extra column Aux which helps with the Job Title priority. Let's suppose your sample dataframe is defined as df:

aux = {'Job Title':['Founder','Co-Founder','Board Member','CFO'],'Rank':[1,2,3,4]}
df['Rank'] = df['Job Title'].merge(aux,on='Job Title')
df_filtered = df.groupby('COMPANY')['Rank'].min().reset_index().merge(aux,on='Rank').drop(columns='Rank')
user_118970070
Related