Filter dataframe columns using multiple dictionaries

Question

I have a dataframe that has a department, its function, its subfunction and its sub-subfunction. A example of the dataframe would be this:

d = [['Dept1'  ,               'HR'  ,        'Talent'     ,     'Good Employee 3'],
    ['Dept2'  ,               'IT'   ,      'Garbage'  ,  'Analysis HR 2'],
       ['Dept3'   ,              'IT'  ,        'Tech Sup' ,           'IT Tech 2'],
      [ 'Dept4'   ,              'HR'  ,        'Hardware' ,        'Trash Data' ] ,             
      [ 'Dept5'   ,              'MKT'  ,         'Sales'  ,         'Facebook Promo 1'],
       ['Dept6'   ,              'MKT'  ,       NaN,     'Car profit ']]
df = pd.DataFrame(d, columns=['Department', 'Function' ,     'Subfunction' ,  'Sub-subfunction'])

       Department           Function      Subfunction   Sub-subfunction
0        Dept1                 HR          Talent          Good Employee 3
1        Dept2                 IT         Garbage     Analysis HR 2
2        Dept3                 IT          Tech Sup            IT Tech 2
3        Dept4                 HR          Hardware         Trash Data              
4        Dept5                 MKT           Sales           Facebook Promo 1
5        Dept6                 MKT        NaN               Car profit

I need to create a rule that would check if a department has a certain value in function, it only allow a possible list of values in subfunction. Then, in subfunction the same, each unique value would allow only a possible list of values in sub-subfunction.

The mapping would be the following:

subfunction = {'HR': ['Talent', 'Analysis Human'],
              'IT': ['Tech Sup', 'Hardware'],
              'MKT': ['Sales', 'Communication']}

sub_subfunction = {'Talent': ['Good Employee 1', 'Good Employee 2', 'Good Employee 3'],
                   'Analysis Human': [ 'Analysis HR 1', 'Analysis HR 2', 'Analysis HR 3'],
                   'Tech Sup': ['IT Tech 1', 'IT Tech 2', 'IT Tech 3', 'Tech Master'],
                   'Hardware': ['PC pieces', 'Phone pieces'],
                   'Sales': ['Car profit', 'Bolt profit'],
                   'Communication': ['Facebook Promo 1', 'Instagram Promo 1']}

In this dataframe, this function would return the ones that do not obey this rule, in this example it would return:

       Department           Function      Subfunction   Sub-subfunction
1        Dept2                 IT         Garbage    Analysis HR 2
3        Dept4                 HR          Hardware         Trash Data               
4        Dept5                 MKT           Sales           Facebook Promo 1
5        Dept6                 MKT           NaN             Car profit

What would be the best way to apply those rules? As you can see, the values of the dataframe can be outside the values of the mapped dictionary (although I can have the "Function" values all mapped in the subfunction dictionary if that helps too much).

Thank you for the support!

user_4333359 · Answer

Read your dicts into DataFrames, reshape, and merge them together so you form the DataFrame of all allowable combinations of ['Function', 'Subfunction', 'Sub-Subfunction']. Then using a 'left' merge with an indicator we can query the ones that do not match and return only that subset.

df1 = (pd.DataFrame.from_dict(subfunction, orient='index')
         .stack()
         .reset_index()
         .drop(columns='level_1')
         .rename(columns={'level_0': 'Function', 0: 'Subfunction'}))

df2 = (pd.DataFrame.from_dict(sub_subfunction, orient='index')
         .stack()
         .reset_index()
         .drop(columns='level_1')
         .rename(columns={'level_0': 'Subfunction', 0: 'Sub-subfunction'}))

# All allowable combinations 
key = df1.merge(df2)

res = (df.merge(key, indicator=True, how='left')
         .query('_merge == "left_only"')
         .drop(columns='_merge'))

print(res)

  Department Function    Subfunction   Sub-subfunction
1      Dept2       IT        Garbage     Analysis HR 2
3      Dept4       HR       Hardware        Trash Data
4      Dept5      MKT          Sales  Facebook Promo 1
5      Dept6      MKT  Communication        Car profit

Filter dataframe columns using multiple dictionaries

1 Answers