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

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


• Welcome to DataScienceSE. This looks like a very specific problem which doesn't have much to do with binary/categorical representation, there's no correspondence between the input groups of variables and the 'types'. There a few things unclear: what happens if the constraints lead to more than 4 types? What the letters A..E represent, are they independent? The rules don't seem sufficient to handle all the cases. It's hard for me to see a 'data science' part in this, I'm afraid. Jun 4 at 10:02

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