Converting Multiple Dummy Variables into one Column

Question

I would like to convert a table that looks like this:

            Blue    Green    Red
Thing 1     No      Yes      No
Thing 2     Yes     No       No
Thing 3     Yes     No       No

Into this style:

            Color
Thing 1     Green
Thing 2     Blue
Thing 3     Blue

Is there a nice way to do this in python or pandas? And do these table styles have names?

The names for these approaches are "dummy" or "indicator" variables in the first case, and a categorical variable in the second. See [`pandas.DataFrame.get_dummies()`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.get_dummies.html), for example, which performs the conversion in the opposite direction (categorical to dummies). — NicholasM, Dec 04 '19 at 20:43

score 6 · Accepted Answer · answered Dec 04 '19 at 20:42

6

Just use idxmax:

df.eq('Yes').idxmax(axis=1)

Output

Thing 1    Green
Thing 2     Blue
Thing 3     Blue
dtype: object

answered Dec 04 '19 at 20:42

Dani Mesejo

score 5 · Answer 2 · answered Dec 04 '19 at 20:42

5

If each row has exactly one 'yes', you can do

df.eq('Yes') @ df.columns

Output:

Thing 1    Green
Thing 2     Blue
Thing 3     Blue
dtype: object

answered Dec 04 '19 at 20:42

Quang Hoang

ansev · Answer 3 · 2019-12-04T21:02:48.467

2

Use:

df.where(df.eq('Yes')).stack().reset_index(level=1)['level_1']

Thing 1    Green
Thing 2     Blue
Thing 3     Blue
Name: level_1, dtype: object

edited Dec 04 '19 at 21:02

answered Dec 04 '19 at 20:43

ansev

3 Answers3