Python Dataframe Merge Boolean Columns Data into One Column Data

Question

I have a data frame with multiple columns. I want to merge columns into one column data.

My code:

df = 
     A   foo   goo
0   10   Y     NaN
1   40   NaN   Y
2   80   Y     NaN

Expected answer:

df = 
     A   Group   
0   10   foo     
1   40   goo   
2   80   foo

My approach:

df['foo'].replace('Y','foo',inplace=True)
df['goo'].replace('Y','goo',inplace=True)
df['Group'] = df['foo']+df['goo']
df =     
     A   foo   goo   Group
0   10   foo   NaN   NaN
1   40   NaN   goo   NaN
2   80   foo   NaN   NaN

In my answer, all elements turn into NaN.

Shubham Sharma · Accepted Answer · 2021-06-03 15:03:43Z

4

We can try idxmax on axis=1

df['Group'] = df.drop('A', 1).eq('Y').idxmax(1)

    A  foo  goo Group
0  10    Y  NaN   foo
1  40  NaN    Y   goo
2  80    Y  NaN   foo

answered Jun 3, 2021 at 15:03

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BENY · Accepted Answer · 2021-06-03 15:01:59Z

3

Try with melt

out = df.melt('A',var_name = 'Group').loc[lambda x : x['value']=='Y']
Out[23]: 
    A Group value
0  10   foo     Y
2  80   foo     Y
4  40   goo     Y

answered Jun 3, 2021 at 15:01

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Amin Ba · Accepted Answer · 2021-06-03 15:20:05Z

0

dataframe:

df = pd.DataFrame({'A': [10, 40, 80],
                  'foo': ['Y', 'NaN', 'Y'],
                  'goo': ['Nan', 'Y', 'NaN']})

code:

def func(row):
  return 'foo' * (row['foo'] == 'Y') + 'goo' * (row['goo'] == 'Y')

df['Group'] = df.apply(lambda row: func(row), axis=1)

df.drop(['foo', 'goo'], axis=1)

result:

    A   Group
0   10  foo
1   40  goo
2   80  foo

The advantage of this method is that you can play with the func logic and customize it for yourself. For example, you might encounter a situation in which neither of foo and goo is Y. In my solution, it returns 0 but you can define it for yourself.

edited Jun 3, 2021 at 15:20

answered Jun 3, 2021 at 15:14

Amin Ba

2,5555 gold badges38 silver badges84 bronze badges

Comments

Nk03 · Accepted Answer · 2021-06-03 15:39:08Z

0

Adding 2 more options:

via dot:

df['Group'] = df.set_index('A').isna().dot(df.columns[1:]).values
# df['Group'] = df.drop('A' , 1).isna().dot(df.columns[1:]).values

Via stack:

df = df.set_index('A').stack().reset_index().drop(0, axis =1).rename(columns = {'level_1': 'Group'})

edited Jun 3, 2021 at 15:39

answered Jun 3, 2021 at 15:25

Nk03

15k2 gold badges11 silver badges24 bronze badges

Comments

wwnde · Accepted Answer · 2021-06-03 21:44:36Z

0

Saw this late. Another way though is;

s=df[['foo','goo']].notna()

df['group']=s.agg(lambda x: x.index[x].values,axis=1).str.join(',')

    A  foo  goo group
0  10    Y  NaN   foo
1  40  NaN    Y   goo
2  80    Y  NaN   foo

answered Jun 3, 2021 at 21:44

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

Collectives™ on Stack Overflow

Python Dataframe Merge Boolean Columns Data into One Column Data

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related