CODEWITHSUNDEEP
pythonpandas
vnguyen56
vnguyen56

Reputation: 55

Easier way to combine all these binary columns into categorical columns?

These are the categories that I want to change to one columns. The values in each list are the current binary columns present in the dataframe. 

housesitu = ['tipovivi1', 'tipovivi2', 'tipovivi3', 'tipovivi4', 'tipovivi5']
educlevels = ['instlevel1', 'instlevel2', 'instlevel3', 'instlevel4', 'instlevel5', 'instlevel6', 'instlevel7',
             'instlevel8', 'instlevel9']
regions = ['lugar1', 'lugar2', 'lugar3', 'lugar4', 'lugar5', 'lugar6']
relations = ['parentesco1', 'parentesco2', 'parentesco3', 'parentesco4', 'parentesco5', 'parentesco6',
            'parentesco7', 'parentesco8', 'parentesco9', 'parentesco10', 'parentesco11', 'parentesco12']

I currently have this code to combine binary columns into categorical columns:

    train['housesitu'] = train[housesitu].idxmax(axis=1)
    train.drop(train[housesitu], axis=1, inplace=True)
    train['educlevels'] = train[educlevels].idxmax(axis=1)
    train.drop(train[educlevels], axis=1, inplace=True)
    train['regions'] = train[regions].idxmax(axis=1)
    train.drop(train[regions], axis=1, inplace=True)
    train['relations'] = train[relations].idxmax(axis=1)
    train.drop(train[relations], axis=1, inplace=True)
    train['marital'] = train[marital].idxmax(axis=1)
    train.drop(train[marital], axis=1, inplace=True)
    train['rubbish'] = train[rubbish].idxmax(axis=1)
    train.drop(train[rubbish], axis=1, inplace=True)
    train['energy'] = train[energy].idxmax(axis=1)
    train.drop(train[energy], axis=1, inplace=True)
    train['toilets'] = train[toilets].idxmax(axis=1)
    train.drop(train[toilets], axis=1, inplace=True)
    train['floormat'] = train[floormat].idxmax(axis=1)
    train.drop(train[floormat], axis=1, inplace=True)
    train['roofmat'] = train[roofmat].idxmax(axis=1)
    train.drop(train[roofmat], axis=1, inplace=True)
    train['wallmat'] = train[wallmat].idxmax(axis=1)
    train.drop(train[wallmat], axis=1, inplace=True)
    train['floorqual'] = train[floorqual].idxmax(axis=1)
    train.drop(train[floorqual], axis=1, inplace=True)
    train['wallqual'] = train[wallqual].idxmax(axis=1)
    train.drop(train[wallqual], axis=1, inplace=True)
    train['roofqual'] = train[roofqual].idxmax(axis=1)
    train.drop(train[roofqual], axis=1, inplace=True)
    train['waterprov'] = train[waterprov].idxmax(axis=1)
    train.drop(train[waterprov], axis=1, inplace=True)
    train['electric'] = train[electric].idxmax(axis=1)
    train.drop(train[electric], axis=1, inplace=True)

I would like to know if there is a shorter way to do this.

Upvotes: 1

Views: 364

Answers (1)

BENY
BENY

Reputation: 323346

I can only think about a groupby with idxmax, since you column named as XXXddd 

df.groupby(df.columns.to_series().str.replace('\d+',''),axis=1).idxmax(1)
Out[1100]: 
    A   B
0  A2  B2
1  A1  B1
2  A1  B1

Data Input 

df=pd.DataFrame({'A1':[1,2,3],'A2':[2,1,3],'B1':[1,2,3],'B2':[2,1,3]})

Upvotes: 1

Related Questions