3

I want to one-hot encode pandas dataframe for whole columns, not for each column.

If there is a dataframe like below:

df = pd.DataFrame({'A': ['A1', 'A1', 'A1', 'A1', 'A4', 'A5'], 'B': ['A2', 'A2', 'A2', 'A3', np.nan, 'A6], 'C': ['A4', 'A3', 'A3', 'A5', np.nan, np.nan]})
df =
        A    B   C
    0  A1   A2  A4
    1  A1   A2  A3
    2  A1   A2  A3
    3  A1   A3  A5
    4  A4   NaN NaN
    5  A5   A6  NaN

I want to encode it like below:

    df =
            A1    A2   A3   A4   A5   A6
        0    1     1    0    1    0    0
        1    1     1    1    0    0    0
        2    1     1    1    0    0    0
        3    1     0    1    0    1    0
        4    0     0    0    1    0    0
        5    0     0    0    0    1    1

However, if I write a code like belows, the results are like belows:

df = pd.get_dummies(df, sparse=True)
df = 
   A_A1  A_A4  A_A5  B_A2  B_A3  B_A6  C_A3  C_A4  C_A5
0     1     0     0     1     0     0     0     1     0
1     1     0     0     1     0     0     1     0     0
2     1     0     0     1     0     0     1     0     0
3     1     0     0     0     1     0     0     0     1
4     0     1     0     0     0     0     0     0     0
5     0     0     1     0     0     1     0     0     0

How do I one-hot encode for whole columns? If I use prefix = '', it also makes columns such as _A1 _A4 _A5 _A2 _A3 _A6 _A3 _A4 _A5. (I hope to make code using pandas or numpy library, not for-loop naive code because my data are so huge; 16000000 rows, so iterative for-loop naive code will require long calculation time).

Hyunseung Kim
  • 493
  • 1
  • 6
  • 17

2 Answers2

4

In your case 

df.stack().str.get_dummies().sum(level=0)
Out[116]: 
   A1  A2  A3  A4  A5  A6
0   1   1   0   1   0   0
1   1   1   1   0   0   0
2   1   1   1   0   0   0
3   1   0   1   0   1   0
4   0   0   0   1   0   0
5   0   0   0   0   1   1

Or fix your pd.get_dummies with prefix

pd.get_dummies(df, prefix='',prefix_sep='').sum(level=0,axis=1)
Out[118]: 
   A1  A4  A5  A2  A3  A6
0   1   1   0   1   0   0
1   1   0   0   1   1   0
2   1   0   0   1   1   0
3   1   0   1   0   1   0
4   0   1   0   0   0   0
5   0   0   1   0   0   1
BENY
  • 317,841
  • 20
  • 164
  • 234
3

Quicker

# Pandas 0.24 or greater use `.to_numpy()` instead of `.values`
v = df.values
n, m = v.shape
j, cols = pd.factorize(v.ravel())  # -1 when `np.nan`

# Used to grab only non-null values
mask = j >= 0
i = np.arange(n).repeat(m)[mask]
j = j[mask]

out = np.zeros((n, len(cols)), dtype=int)
# Useful when not one-hot.  Otherwise use `out[i, j] = 1`
np.add.at(out, (i, j), 1)

pd.DataFrame(out, df.index, cols)

   A1  A2  A4  A3  A5  A6
0   1   1   1   0   0   0
1   1   1   0   1   0   0
2   1   1   0   1   0   0
3   1   0   0   1   1   0
4   0   0   1   0   0   0
5   0   0   0   0   1   1

Not quicker

This is intended to show that you can join the row values then use str.get_dummies

df.stack().groupby(level=0).apply('|'.join).str.get_dummies()

   A1  A2  A3  A4  A5  A6
0   1   1   0   1   0   0
1   1   1   1   0   0   0
2   1   1   1   0   0   0
3   1   0   1   0   1   0
4   0   0   0   1   0   0
5   0   0   0   0   1   1

sklearn

from sklearn.preprocessing import MultiLabelBinarizer as MLB

mlb = MLB()
out = mlb.fit_transform([[*filter(pd.notna, x)] for x in zip(*map(df.get, df))])

pd.DataFrame(out, df.index, mlb.classes_)

   A1  A2  A3  A4  A5  A6
0   1   1   0   1   0   0
1   1   1   1   0   0   0
2   1   1   1   0   0   0
3   1   0   1   0   1   0
4   0   0   0   1   0   0
5   0   0   0   0   1   1
piRSquared
  • 285,575
  • 57
  • 475
  • 624