Python Dataframe Merge Boolean Columns Data into One Column Data

user_11922765 0
Viewed 296

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.

pandas dataframe numpy python
5 Answers

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
user_128331660

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
user_79645270

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.

user_110658740

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'})
user_154380330

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
user_89869750
Related