2

I have a data frame with many columns with binaries representing the presence of a category in the observation. Each observation has exactly 3 categories with a value of 1, the rest 0. I want to create 3 new columns, 1 for each category, where the value is instead the name of the category (so the name of the binary column) if it's equal to one.To make it clearer:

I have:

x|y|z|k|w
---------
0|1|1|0|1

To be:

cat1|cat2|cat3
--------------
y   |z   |w

Can I do this ?

jpp
  • 159,742
  • 34
  • 281
  • 339
NTiberio
  • 95
  • 7
  • Possible duplicate : https://stackoverflow.com/questions/26762100/reconstruct-a-categorical-variable-from-dummies-in-pandas . – arnaud Feb 27 '18 at 13:44
  • Very similar, but this only seems to allow to work when there is only one category. At least, it is the case in that example. – NTiberio Feb 27 '18 at 14:08
  • @NTiberio - Is important performance? Please check timings in my answer. – jezrael Feb 28 '18 at 08:49
  • @NTiberio, if one of the below solutions helped feel free to accept (tick on left) so that others users know. – jpp Feb 28 '18 at 11:33

3 Answers3

3

For better performance use numpy solution:

print (df)
   x  y  z  k  w
0  0  1  1  0  1
1  1  1  0  0  1

c = df.columns.values
df = pd.DataFrame(c[np.where(df)[1].reshape(-1, 3)]).add_prefix('cat')
print (df)
  cat0 cat1 cat2
0    y    z    w
1    x    y    w

Details:

#get indices of 1s
print (np.where(df))
(array([0, 0, 0, 1, 1, 1], dtype=int64), array([1, 2, 4, 0, 1, 4], dtype=int64))

#seelct second array
print (np.where(df)[1])
[1 2 4 0 1 4]

#reshape to 3 columns
print (np.where(df)[1].reshape(-1, 3))
[[1 2 4]
 [0 1 4]]

#indexing
print (c[np.where(df)[1].reshape(-1, 3)])
[['y' 'z' 'w']
 ['x' 'y' 'w']]

Timings:

df = pd.concat([df] * 1000, ignore_index=True)

#jezrael solution
In [390]: %timeit (pd.DataFrame(df.columns.values[np.where(df)[1].reshape(-1, 3)]).add_prefix('cat'))
The slowest run took 4.62 times longer than the fastest. This could mean that an intermediate result is being cached.
1000 loops, best of 3: 503 µs per loop

#jpp solution
In [391]: %timeit (pd.DataFrame(df.apply(lambda row: [x for x in df.columns if row[x]], axis=1).values.tolist()))
10 loops, best of 3: 111 ms per loop

#Zero solution working only with one row DataFrame, so not included
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
2

Here is one way:

import pandas as pd

df = pd.DataFrame({'x': [0, 1], 'y': [1, 1], 'z': [1, 0], 'k': [0, 1], 'w': [1, 1]})

split = df.apply(lambda row: [x for x in df.columns if row[x]], axis=1).values.tolist()
df2 = pd.DataFrame(split)

#    0  1  2     3
# 0  w  y  z  None
# 1  k  w  x     y
jpp
  • 159,742
  • 34
  • 281
  • 339
2

You could

In [13]: pd.DataFrame([df.columns[df.astype(bool).values[0]]]).add_prefix('cat')
Out[13]:
  cat0 cat1 cat2
0    y    z    w
Zero
  • 74,117
  • 18
  • 147
  • 154