Impute value based on other columns value

Question

There is a dataframe (df) in below format:

Name,   Col-1,  Col-2, Col-3, Col-4
abc,    0,      1,      0,      0
cba,    1,      0,      0,      0
bns     1,      0,      0,      0
abd     0       0,      0,      1

Now i am trying to add new column to this dataframe like below:

Name,   Col-1,  Col-2, Col-3, Col-4,    Type
abc,    0,      1,      0,      0,      Col-2
cba,    1,      0,      0,      0,      Col-1
bns     1,      0,      0,      0,      Col-1
abd     0       0,      0,      1,      Col-4

Please suggest how to get it done, i tried below but throws error.

df['Type'] = [lambda x: x if x == 1 for x in df.columns]

Are these dummies mutually exclusive, so that only ever 1 column is a 1 for each row? — ALollz
– ALollz, Commented Apr 4, 2019 at 19:02

Andy Hayden · Accepted Answer · 2019-04-04 18:57:53Z

You can use idxmax:

In [11]: df
Out[11]:
  Name  Col-1  Col-2  Col-3  Col-4
0  abc      0      1      0      0
1  cba      1      0      0      0
2  bns      1      0      0      0
3  abd      0      0      0      1

In [12]: df.iloc[:, 1:]
Out[12]:
   Col-1  Col-2  Col-3  Col-4
0      0      1      0      0
1      1      0      0      0
2      1      0      0      0
3      0      0      0      1

In [13]: df.iloc[:, 1:].idxmax(axis=1)
Out[13]:
0    Col-2
1    Col-1
2    Col-1
3    Col-4
dtype: object

In [14]: df["Type"] = df.iloc[:, 1:].idxmax(axis=1)

In [15]: df
Out[15]:
  Name  Col-1  Col-2  Col-3  Col-4   Type
0  abc      0      1      0      0  Col-2
1  cba      1      0      0      0  Col-1
2  bns      1      0      0      0  Col-1
3  abd      0      0      0      1  Col-4

ALollz · Accepted Answer · 2019-04-04 19:03:14Z

1

Since mutually exclusive can use .dot

df['Type'] = df.iloc[:, 1:].dot(df.columns[1:])

  Name  Col-1  Col-2  Col-3  Col-4   Type
0  abc      0      1      0      0  Col-2
1  cba      1      0      0      0  Col-1
2  bns      1      0      0      0  Col-1
3  abd      0      0      0      1  Col-4

answered Apr 4, 2019 at 19:03

ALollz

59.7k7 gold badges73 silver badges97 bronze badges

Collectives™ on Stack Overflow

Impute value based on other columns value

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related