0
$\begingroup$

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
 
$\endgroup$
1
  • $\begingroup$ 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. $\endgroup$
    – Erwan
    Jun 4 at 10:02

1 Answer 1

0
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.