Pandas: Remove rows and columns with a certain string based on a complex logic

Question

Below is my Dataframe:

df = pd.DataFrame({'A': ['a1', 'a1', 'a1', 'a1', 'a1', 'a1', 'a1', 'a1', 'a1', 'all', 'all', 'all', 'all', 'all', 'all', 'all', 'all', 'all'],
              'B': ['b1', 'b1', 'b1', 'b2', 'b2', 'b2', 'all', 'all', 'all', 'b1', 'b1', 'b1', 'b2', 'b2', 'b2', 'all', 'all', 'all'],
              'C': ['c1', 'c2', 'all', 'c1', 'c2', 'all', 'c1', 'c2', 'all', 'c1', 'c2', 'all', 'c1', 'c2', 'all', 'c1', 'c2', 'all'],
              'D': ['D1', 'D2', 'all', 'D1', 'D2', 'all', 'D1', 'D2', 'all', 'D1', 'D2', 'all', 'D1', 'D2', 'all', 'D1', 'D2', 'all'],
              'E': ['E1', 'E1', 'E1', 'E2', 'E2', 'E2', 'all', 'all', 'all', 'E1', 'E1', 'E1', 'E2', 'E2', 'E2', 'all', 'all', 'all'],
              'F': [1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9]})

After pivot, below is the output:

In [226]: df.pivot(index=['A', 'B', 'C'], columns=['E', 'D'])
Out[226]: 
               F                          (NO)  (NO)           
E             E1   E1  E1    E2   E2  E2   all  all  all      
D             D1   D2  all   D1   D2  all   D1   D2  all
A   B   C                                               
a1  all all  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  9.0 
a1  all c1   NaN  NaN  NaN  NaN  NaN  NaN  7.0  NaN  NaN  -> (NO)
a1  all c2   NaN  NaN  NaN  NaN  NaN  NaN  NaN  8.0  NaN  -> (NO)
a1  b1  all  NaN  NaN  3.0  NaN  NaN  NaN  NaN  NaN  NaN
a1  b1  c1   1.0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
a1  b1  c2   NaN  2.0  NaN  NaN  NaN  NaN  NaN  NaN  NaN
a1  b2  all  NaN  NaN  NaN  NaN  NaN  6.0  NaN  NaN  NaN
a1  b2  c1   NaN  NaN  NaN  4.0  NaN  NaN  NaN  NaN  NaN
a1  b2  c2   NaN  NaN  NaN  NaN  5.0  NaN  NaN  NaN  NaN
all all all  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  9.0
all all c1   NaN  NaN  NaN  NaN  NaN  NaN  7.0  NaN  NaN  -> (NO)
all all c2   NaN  NaN  NaN  NaN  NaN  NaN  NaN  8.0  NaN  -> (NO)
all b1  all  NaN  NaN  3.0  NaN  NaN  NaN  NaN  NaN  NaN  -> (NO)
all b1  c1   1.0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  -> (NO)
all b1  c2   NaN  2.0  NaN  NaN  NaN  NaN  NaN  NaN  NaN  -> (NO)
all b2  all  NaN  NaN  NaN  NaN  NaN  6.0  NaN  NaN  NaN  -> (NO)
all b2  c1   NaN  NaN  NaN  4.0  NaN  NaN  NaN  NaN  NaN  -> (NO)
all b2  c2   NaN  NaN  NaN  NaN  5.0  NaN  NaN  NaN  NaN  -> (NO)

I need to remove rows and columns which I've pointed out by (NO).

Logic is: Starting from the most inside index, i.e., C, I need to keep rows and columns where all occurs in a backward manner. So, rows with index a1 all all , a1 b1 all , a1 b2 all and all all all are kept because all is occurring in a backward manner.

Rows with index a1 all c1 , a1 all c2, all all c1, all b1 all, etc. need to be removed because there's no continuity in all starting backwards from C.

Same logic goes for columns as well. Starting from D, need to remove columns with index D1 all and D2 all, rest are fine.

Expected Output:

In [227]: result_df
Out[227]: 
               F                          
E             E1   E1  E1    E2   E2  E2   all      
D             D1   D2  all   D1   D2  all  all
A   B   C                                     
a1  all all  NaN  NaN  NaN  NaN  NaN  NaN  9.0 
a1  b1  all  NaN  NaN  3.0  NaN  NaN  NaN  NaN
a1  b1  c1   1.0  NaN  NaN  NaN  NaN  NaN  NaN
a1  b1  c2   NaN  2.0  NaN  NaN  NaN  NaN  NaN
a1  b2  all  NaN  NaN  NaN  NaN  NaN  6.0  NaN
a1  b2  c1   NaN  NaN  NaN  4.0  NaN  NaN  NaN
a1  b2  c2   NaN  NaN  NaN  NaN  5.0  NaN  NaN
all all all  NaN  NaN  NaN  NaN  NaN  NaN  9.0

Update with new data:

df = pd.DataFrame({'A': ['a1', 'a1', 'a1', 'a1', 'a1', 'a1', 'a1', 'a1', 'a1', 'all', 'all', 'all', 'all', 'all',
                                 'all::1::2', 'all::2', 'all', 'all'],
                           'B': ['b1', 'b1', 'b1', 'b2', 'b2', 'b2', 'all', 'all::3::4', 'all', 'b1', 'b1', 'b1', 'b2', 'b2',
                                 'b2', 'all', 'all', 'all'],
                           'C': ['c1', 'c2', 'all', 'c1', 'c2', 'all', 'c1', 'c2', 'all', 'c1', 'c2', 'all', 'c1', 'c2',
                                 'all', 'c1', 'c2', 'all::5::all'],
                           'D': ['D1', 'D2', 'all', 'D1', 'D2', 'all::3::2', 'D1', 'D2', 'all', 'D1', 'D2', 'all', 'D1', 'D2',
                                 'all', 'D1', 'D2', 'all'],
                           'E': ['E1', 'E1', 'E1', 'E2', 'E2', 'E2', 'all', 'all', 'all', 'E1', 'E1', 'E1', 'E2', 'E2',
                                 'E2', 'all::1::2', 'all', 'all'],
                           'measure_F': [1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9]})

After pivot:

In [334]: df
Out[334]: 
                                measure_F                                (NO) (NO)                (NO)         
E                                      E1             E2                 all             E2 all::1::2
D                                      D1   D2  all   D1   D2 all::3::2   D1   D2  all  all        D1
A         B         C                                                                                
a1        all       all               NaN  NaN  NaN  NaN  NaN       NaN  NaN  NaN  9.0  NaN       NaN
                    c1                NaN  NaN  NaN  NaN  NaN       NaN  7.0  NaN  NaN  NaN       NaN  -> (NO)
          all::3::4 c2                NaN  NaN  NaN  NaN  NaN       NaN  NaN  8.0  NaN  NaN       NaN  -> (NO)
          b1        all               NaN  NaN  3.0  NaN  NaN       NaN  NaN  NaN  NaN  NaN       NaN
                    c1                1.0  NaN  NaN  NaN  NaN       NaN  NaN  NaN  NaN  NaN       NaN
                    c2                NaN  2.0  NaN  NaN  NaN       NaN  NaN  NaN  NaN  NaN       NaN
          b2        all               NaN  NaN  NaN  NaN  NaN       6.0  NaN  NaN  NaN  NaN       NaN
                    c1                NaN  NaN  NaN  4.0  NaN       NaN  NaN  NaN  NaN  NaN       NaN
                    c2                NaN  NaN  NaN  NaN  5.0       NaN  NaN  NaN  NaN  NaN       NaN
all       all       all::5::all       NaN  NaN  NaN  NaN  NaN       NaN  NaN  NaN  9.0  NaN       NaN
                    c2                NaN  NaN  NaN  NaN  NaN       NaN  NaN  8.0  NaN  NaN       NaN  -> (NO)
          b1        all               NaN  NaN  3.0  NaN  NaN       NaN  NaN  NaN  NaN  NaN       NaN  -> (NO)
                    c1                1.0  NaN  NaN  NaN  NaN       NaN  NaN  NaN  NaN  NaN       NaN  -> (NO)
                    c2                NaN  2.0  NaN  NaN  NaN       NaN  NaN  NaN  NaN  NaN       NaN  -> (NO)
          b2        c1                NaN  NaN  NaN  4.0  NaN       NaN  NaN  NaN  NaN  NaN       NaN  -> (NO)
                    c2                NaN  NaN  NaN  NaN  5.0       NaN  NaN  NaN  NaN  NaN       NaN  -> (NO)
all::1::2 b2        all               NaN  NaN  NaN  NaN  NaN       NaN  NaN  NaN  NaN  6.0       NaN  -> (NO)
all::2    all       c1                NaN  NaN  NaN  NaN  NaN       NaN  NaN  NaN  NaN  NaN       7.0  -> (NO)

Update-2:

df = pd.DataFrame({'A':['test', 'test', 'test', 'test', 'test'], 'B':['a', 'b', 'c', 'd', 'e'], 'C':['x', 'x', 'all_group', 'y', 'y'], 'D':['abc', 'all_group::1::all_group', 'all_group::1::all_group', 'abc', 'abc'], 'E':[13, 36, 26, 39,40]})

In [599]: df.pivot(index=['A', 'B'], columns=['C', 'D'])
Out[599]: 
           E                                                      
C          x                                       all_group     y
D        abc all_group::1::all_group all_group::1::all_group   abc
A    B                                                            
test a  13.0                     NaN                     NaN   NaN
     b   NaN                    36.0                     NaN   NaN
     c   NaN                     NaN                    26.0   NaN
     d   NaN                     NaN                     NaN  39.0
     e   NaN                     NaN                     NaN  40.0

jezrael · Accepted Answer · 2020-10-15T07:04:05.760

Create mask for index and columns with helper DataFrames last pass to DataFrame.loc:

df1 = df.index.to_frame()
m1 = df1.where(df1=='all').bfill(axis=1).count(axis=1).isin([0, df.index.nlevels])
df2 = df.columns.to_frame()
m2 = df2.where(df2=='all').bfill(axis=1).count(axis=1).isin([0, df.columns.nlevels])

df = df.loc[m1, m2]
print (df)    
                F                              
E             E1             E2            all
D             D1   D2  all   D1   D2  all  all
A   B   C                                     
a1  all all  NaN  NaN  NaN  NaN  NaN  NaN  9.0
    b1  all  NaN  NaN  3.0  NaN  NaN  NaN  NaN
        c1   1.0  NaN  NaN  NaN  NaN  NaN  NaN
        c2   NaN  2.0  NaN  NaN  NaN  NaN  NaN
    b2  all  NaN  NaN  NaN  NaN  NaN  6.0  NaN
        c1   NaN  NaN  NaN  4.0  NaN  NaN  NaN
        c2   NaN  NaN  NaN  NaN  5.0  NaN  NaN
all all all  NaN  NaN  NaN  NaN  NaN  NaN  9.0
    b1  all  NaN  NaN  3.0  NaN  NaN  NaN  NaN
    b2  all  NaN  NaN  NaN  NaN  NaN  6.0  NaN

Details:

Use MultiIndex.to_frame for DataFrame:

print (df.index.to_frame())    

               A    B    C
A   B   C                 
a1  all all   a1  all  all
        c1    a1  all   c1
        c2    a1  all   c2
    b1  all   a1   b1  all
        c1    a1   b1   c1
        c2    a1   b1   c2
    b2  all   a1   b2  all
        c1    a1   b2   c1
        c2    a1   b2   c2
all all all  all  all  all
        c1   all  all   c1
        c2   all  all   c2
    b1  all  all   b1  all
        c1   all   b1   c1
        c2   all   b1   c2
    b2  all  all   b2  all
        c1   all   b2   c1
        c2   all   b2   c2

Then replace non all to missing values by DataFrame.where:

print (df1.where(df1=='all'))
               A    B    C
A   B   C                 
a1  all all  NaN  all  all
        c1   NaN  all  NaN
        c2   NaN  all  NaN
    b1  all  NaN  NaN  all
        c1   NaN  NaN  NaN
        c2   NaN  NaN  NaN
    b2  all  NaN  NaN  all
        c1   NaN  NaN  NaN
        c2   NaN  NaN  NaN
all all all  all  all  all
        c1   all  all  NaN
        c2   all  all  NaN
    b1  all  all  NaN  all
        c1   all  NaN  NaN
        c2   all  NaN  NaN
    b2  all  all  NaN  all
        c1   all  NaN  NaN
        c2   all  NaN  NaN

Back filling non misisng, here all, by bfill:

print (df1.where(df1=='all').bfill(axis=1))
               A    B    C
A   B   C                 
a1  all all  all  all  all
        c1   all  all  NaN
        c2   all  all  NaN
    b1  all  all  all  all
        c1   NaN  NaN  NaN
        c2   NaN  NaN  NaN
    b2  all  all  all  all
        c1   NaN  NaN  NaN
        c2   NaN  NaN  NaN
all all all  all  all  all
        c1   all  all  NaN
        c2   all  all  NaN
    b1  all  all  all  all
        c1   all  NaN  NaN
        c2   all  NaN  NaN
    b2  all  all  all  all
        c1   all  NaN  NaN
        c2   all  NaN  NaN

Count non missing values by DataFrame.count:

print (df1.where(df1=='all').bfill(axis=1).count(axis=1))
A    B    C  
a1   all  all    3
          c1     2
          c2     2
     b1   all    3
          c1     0
          c2     0
     b2   all    3
          c1     0
          c2     0
all  all  all    3
          c1     2
          c2     2
     b1   all    3
          c1     1
          c2     1
     b2   all    3
          c1     1
          c2     1

Test if 0 - it means back filled rows/columns and if all NaN it matched number of levels by MultiIndex.nlevels:

print (df1.where(df1=='all').bfill(axis=1).count(axis=1).isin([0, df.index.nlevels]))
 A    B    C  
a1   all  all     True
          c1     False
          c2     False
     b1   all     True
          c1      True
          c2      True
     b2   all     True
          c1      True
          c2      True
all  all  all     True
          c1     False
          c2     False
     b1   all     True
          c1     False
          c2     False
     b2   all     True
          c1     False
          c2     False
dtype: bool

EDIT: Here are compared subtrings and then all values of mask by helper arrays created by np.triu chained with only Falses rows:

f = lambda x: x.str.contains('all')
arr1 = np.triu(np.ones((df.index.nlevels,df.index.nlevels), dtype=bool), False)
arr2 = np.triu(np.ones((df.columns.nlevels,df.columns.nlevels), dtype=bool), False)
print (arr1)
[[ True  True  True]
 [False  True  True]
 [False False  True]]

arr11 = df.index.to_frame().astype(str).apply(f).to_numpy()
arr22 = df.columns.to_frame().astype(str).apply(f).to_numpy()

#https://stackoverflow.com/a/51352806/2901002
m1 = (arr11[:, None] == arr1).all(-1).any(axis=1) | ~arr11.any(axis=1)
m2 = (arr22[:, None] == arr2).all(-1).any(axis=1) | ~arr22.any(axis=1)

df = df.loc[m1, m2]
print (df) 
                    measure_F                                       
E                          E1             E2                 all  E2
D                          D1   D2  all   D1   D2 all::3::2  all all
A   B   C                                                           
a1  all all               NaN  NaN  NaN  NaN  NaN       NaN  9.0 NaN
    b1  all               NaN  NaN  3.0  NaN  NaN       NaN  NaN NaN
        c1                1.0  NaN  NaN  NaN  NaN       NaN  NaN NaN
        c2                NaN  2.0  NaN  NaN  NaN       NaN  NaN NaN
    b2  all               NaN  NaN  NaN  NaN  NaN       6.0  NaN NaN
        c1                NaN  NaN  NaN  4.0  NaN       NaN  NaN NaN
        c2                NaN  NaN  NaN  NaN  5.0       NaN  NaN NaN
all all all::5::all       NaN  NaN  NaN  NaN  NaN       NaN  9.0 NaN

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/223074/discussion-on-answer-by-jezrael-pandas-remove-rows-and-columns-with-a-certain-s). — Samuel Liew, Oct 15 '20 at 07:17

Pandas: Remove rows and columns with a certain string based on a complex logic

Update with new data:

Update-2:

1 Answers1