Turning multiple binary columns into categorical (with less columns) with Python Pandas

Question

I want to turn these categories into values of categorical columns. The values in each category are the current binary columns present in the data frame. We have : A11, A12.. is a detail of A1 so if the value in A11 ==1 it will necessarily imply having A1==1 but the inverse is not valid. Respecting the following conditions :

1. maximaum of existing types is 4

2. if A11==1 value of type1 should be equal to 'A11' and we ignore 'A1'

3. if A11==1 and A12==1 we keep both, each one in a different column and ignore 'A1'

4. if A1==1 & A11==0 & A12==0 then type1 should be equal to 'A1' for not having a detailed info A1X

5. if none is equal to 1 then NaN

What I have :

df_test=pd.DataFrame({'A1':[1,0,1,1],'A11':[1,0,1,0],'A12':[1,0,1,0],
                      'B1':[0,1,0,0],'B11':[0,1,0,0],
                      'C1':[1,1,0,0],
                      'D1':[0,1,0,1],'D11':[0,1,0,1],'D12':[0,0,0,1],
                      'E1':[0,1,0,1],'E11':[0,0,0,0],'E12':[0,1,0,0],'E13':[0,0,0,0]})
df_test

    A1 A11 A12 B1  B11  C1  D1 D11 D12  E1 E11 E12 E13
0   1   1   1   0   0   1   0   0   0   0   0   0   0
1   0   0   0   1   1   1   1   1   0   1   0   1   0
2   1   1   1   0   0   0   0   0   0   0   0   0   0
3   1   0   0   0   0   0   1   1   1   1   0   0   0

Desired result I want :

   type1    type2   type3   type4
0   A11     A12      C1      NaN
1   B11     C1      D11      E12
2   A11     A12     NaN      NaN
3   A1      D11     D12      E1
 

Answer 1

You could do it with pandas.DataFrame.apply and a custom function:

Example:

import pandas as pd
import numpy as np

df = pd.DataFrame({'A1':[1,0,1,1],'A11':[1,0,1,0],'A12':[1,0,1,0],
                      'B1':[0,1,0,0],'B11':[0,1,0,0],
                      'C1':[1,1,0,0],
                      'D1':[0,1,0,1],'D11':[0,1,0,1],'D12':[0,0,0,1],
                      'E1':[0,1,0,1],'E11':[0,0,0,0],'E12':[0,1,0,0],'E13':[0,0,0,0]})

def get_types(row):
    # Get the column names where the value is 1
    cols = row.eq(1).dot(df.columns[:]+',').rstrip(',').split(',')
    # Get the unique types depending on the first character of the columns
    unique = np.unique([c[0] for c in cols])
    # Separate in sub lists depending on the type
    x = [[e for e in cols if e[0] == letter] for letter in unique]
    # Remove the first element of the sub lists if it contains more than 1 element
    # For example, if A1, A11 and A12 exist then it will drop A1 and keep A11 and A12
    x = [e[1:] if len(e) > 1 else [e[0]] for e in x]
    # Flatten the array
    x = [s for e in x for s in e]
    # Convert the rows to series and fill with nan if the length is not 4
    # Note: the length is now a constant so you have to consider using a variable
    #      if you need to work with more types in the future
    x = pd.Series(np.append(np.array(x), np.repeat(np.nan, 4 - len(x))))

    return x

df1 = pd.DataFrame()
df1 = df.apply(get_types, axis=1)
df1.rename(columns={0: 'type1', 1: 'type2', 2: 'type3', 3: 'type4'}, inplace=True)

Printing df1 returns the following dataframe:

    type1   type2   type3   type4
0   A11     A12     C1      nan
1   B11     C1      D11     E12
2   A11     A12     nan     nan
3   A1      D11     D12     E1