I have a Pandas dataframe like this {each row in B is a string with values joined with | symbol}:
A B
a 1|2|3
b 2|4|5
c 3|2|5
I want to create columns which say that the value is present in that row(of 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 have tried this by looping the columns. But, can it be done using lambda or comprehensions?
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 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
pandas.concat([df,df.B.str.get_dummies(sep='|')],axis=1)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
