Condensing Multiple DataFrame Columns into a Single Indicator Column in Pandas

user_9571463 0
Viewed 175

Let's say I have a DataFrame like the following:

import pandas as pd
import numpy as np

d = {'ID': [1,2,3,4], 
     'name': ['bob','shelby','jordan','jeff'],
     'type1': [1,1,0,0],
     'type2':[1,0,1,0],
     'type4':[1,0,0,0],
     'type5':[0,0,1,0],
     'type6':[0,1,0,0],
     'type8':[0,0,1,0]}
df: pd.DataFrame = pd.DataFrame(data=d)
print(df.head(9))

   ID    name  type1  type2  type4  type5  type6  type8
0   1     bob      1      1      1      0      0      0
1   2  shelby      1      0      0      0      1      0
2   3  jordan      0      1      0      1      0      1
3   4    jeff      0      0      0      0      0      0

I would like to condense columns 'type5', 'type6' and 'type8' into a single column called 'other' and also aggregate any '1' indicators as a '1' in the new 'other' column. So if 'jordan' had an 'other' column entry he should have a '1' indicator since he had a '1' in type6 and type8(so using a max() operation across the row for each selected column (type5, type6, type8)

Desired frame should be something like:

   ID    name  type1  type2  type4  other 
0   1     bob      1      1      1      0     
1   2  shelby      1      0      0      1      
2   3  jordan      0      1      0      1      
3   4    jeff      0      0      0      0

I would need to make a boolean mask for the columns I want to condense and then aggregate across them to create the new column while dropping the old ones. How could I do this?

pandas dataframe group-by python
2 Answers

Try your logic here

drop_cols = ['type5','type6','type8']

df = (df.assign(other=df[drop_cols].max(1))  # new column with max value
        .drop(columns=drop_cols)             # drop the old columns
     )

Output:

   ID    name  type1  type2  type4  other
0   1     bob      1      1      1      0
1   2  shelby      1      0      0      1
2   3  jordan      0      1      0      1
3   4    jeff      0      0      0      0
user_42384080

Using any:

df['other'] = df.loc[:, ['type5','type6','type8']].any(axis=1).astype(int)
df = df.drop(['type5','type6','type8'], axis=1)

result:

   ID    name  type1  type2  type4  other
0   1     bob      1      1      1      0
1   2  shelby      1      0      0      1
2   3  jordan      0      1      0      1
3   4    jeff      0      0      0      0
user_133869790
Related