Python Split a column into multiple columns using Split

Question

I have a Pandas dataframe like this {each row in B is a row with values ​​associated with | symbol}:

A B
a 1|2|3
b 2|4|5
c 3|2|5

I want to create columns that say the value is present on this row (column B) or not:

A B     1 2 3 4 5
a 1|2|3 1 1 1 0 0
b 2|4|5 0 1 0 1 1
c 3|5   0 0 1 0 1

I tried this, sorting through the columns. But can this be done with lambda or understanding?

Answer 1

You can try get_dummies:

print df
   A      B
0  a  1|2|3
1  b  2|4|5
2  c  3|2|5

print df.B.str.get_dummies(sep='|')
   1  2  3  4  5
0  1  1  1  0  0
1  0  1  0  1  1
2  0  1  1  0  1

And if you need an old column B, use join:

print df.join(df.B.str.get_dummies(sep='|'))
   A      B  1  2  3  4  5
0  a  1|2|3  1  1  1  0  0
1  b  2|4|5  0  1  0  1  1
2  c  3|2|5  0  1  1  0  1

Answer 2

Hope this helps.

In [19]: df
Out[19]: 
   A      B
0  a  1|2|3
1  b  2|4|5
2  c  3|2|5

In [20]: op = df.merge(df.B.apply(lambda s: pd.Series(dict((col, 1)  for col in s.split('|')))), 
left_index=True, right_index=True).fillna(0)

In [21]: op
Out[21]: 
   A      B  1  2  3  4  5
0  a  1|2|3  1  1  1  0  0
1  b  2|4|5  0  1  0  1  1
2  c  3|2|5  0  1  1  0  1