Convert dataframe string into multiple dummy variables in Python

Question

I have a dataframe with several columns. One column is "category", which is a space separated string. A sample of the df's category is:

             3 36 211 433 474 533 690 980
                                 3 36 211
                  3 16 36 211 396 398 409
                        3 35 184 590 1038
                67 179 208 1008 5000 5237

I have another list of categories dict = [3,5,7,8,16,5000]. What I would like to see is a new data frame with dict as columns, and 0/1 as entries. If a row in df contains the dict entry, it's 1, else it's 0. So the output is:

3  5  7  8  16  36 5000
1  0  0  0  0   1   0
1  0  0  0  0   1   0
1  0  0  0  1   1   0 
1  0  0  0  0   0   0 
0  0  0  0  0   0   1

Have tried something like:

for cat in level_0_cat:
    df[cat] = df.apply(lambda x: int(cat in map(int, x.category)), axis = 1)

But it does not work for large dataset (10 million rows). Have also tried isin, but have not figured out. Any idea is appreciated.

Answer 1

This ought to do it.

# Read your data
>>> s = pd.read_clipboard(sep='|', header=None)

# Convert `cats` to string to make `to_string` approach work below
>>> cats = list(map(str, [3,4,7,8,16,36,5000]))
>>> cats
['3', '4', '7', '8', '16', '36', '5000']

# Nested list comprehension... Checks whether each `c` in `cats` exists in each row
>>> encoded = [[1 if v in set(s.ix[idx].to_string().split()) else 0 for idx in s.index] for v in cats]
>>> encoded
[[1, 1, 1, 1, 0], [0, 0, 0, 0, 0], [0, 0, 0, 0, 0], [0, 0, 0, 0, 0], [0, 0, 1, 0, 0], [0, 0, 0, 0, 1]]


>>> import numpy as np

# Convert the whole thing to a dataframe to add columns
>>> encoded = pd.DataFrame(data=np.matrix(encoded).T, columns=cats)
>>> encoded
   3  4  7  8  16  36  5000
0  1  0  0  0   0   1     0
1  1  0  0  0   0   1     0
2  1  0  0  0   1   1     0
3  1  0  0  0   0   0     0
4  0  0  0  0   0   0     1

Edit: answer way to do this without directly calling any pandas indexing methods like ix or loc.

encoded = [[1 if v in row else 0 for row in s[0].str.split().map(set)] for v in cats]

encoded
Out[18]: 
[[1, 1, 1, 1, 0],
 [0, 0, 0, 0, 0],
 [0, 0, 0, 0, 0],
 [0, 0, 0, 0, 0],
 [0, 0, 1, 0, 0],
 [1, 1, 1, 0, 0],
 [0, 0, 0, 0, 1]]

encoded = pd.DataFrame(data=np.matrix(encoded).T, columns=cats)

encoded
Out[20]: 
   3  4  7  8  16  36  5000
0  1  0  0  0   0   1     0
1  1  0  0  0   0   1     0
2  1  0  0  0   1   1     0
3  1  0  0  0   0   0     0
4  0  0  0  0   0   0     1

Answer 2

You don't need to convert every line to integers, it's simpler to convert to strings the elements of the list of categories...

categories = [l.strip() for l in '''\
         3 36 211 433 474 533 690 980
                             3 36 211
              3 16 36 211 396 398 409
                    3 35 184 590 1038
            67 179 208 1008 5000 5237'''.split('\n')]

result = [3,5,7,8,16,5000]
d = [str(n) for n in result]
for category in categories:
    result.append([1 if s in category else 0 for s in d])

Please don't use dict (that is a builtin function) to name one of your objects.