I have a DF as shown below:
DF =
id Result
1 Li_In-AR-B
1 Li_In-AR-L
3 N
4 Lo_In-AR-U
5 Li_In-AR-U
6 Or_Ba-AR-B
6 Or_Ba-AR-L
7 N
Now I want to create new columns for every unique value in Result before the first "-". Every other value in the new column should be set to N.
DF =
id Result Li_In Lo_In Or_Ba
1 Li_In-AR-B Li_In-AR-B N N
1 Li_In-AR-L Li_In-AR-L N N
3 N N N N
4 Lo_In-AR-U N Lo_In-AR-U N
5 Li_In-AR-U Li_In-AR-U N N
6 Or_Ba-AR-B N N Or_Ba-AR-B
6 Or_Ba-AR-L N N Or_Ba-AR-L
7 N N N N
I thought I could do this easily using .get_dummies but this only returns a binary value in each cell.
DF_dummy = DF.Result.str.get_dummies(sep='-')
DF = pd.concat([DF,DF_dummy ],axis=1)
Any ideas
Create boolean DataFrame by split, remove column N and compare by 1. Then create DataFrame with same columns like mask and repalce values by DataFrame.where:
m = DF['Result'].str.split('-', n=1).str[0].str.get_dummies().drop('N', axis=1) == 1
df1 = pd.concat([DF['Result']] * len(m.columns), axis=1, keys=m.columns)
df = DF.join(df1.where(m.values, 'N'))
print (df)
id Result Li_In Lo_In Or_Ba
0 1 Li_In-AR-B Li_In-AR-B N N
1 1 Li_In-AR-L Li_In-AR-L N N
2 3 N N N N
3 4 Lo_In-AR-U N Lo_In-AR-U N
4 5 Li_In-AR-U Li_In-AR-U N N
5 6 Or_Ba-AR-B N N Or_Ba-AR-B
6 6 Or_Ba-AR-L N N Or_Ba-AR-L
7 7 N N N N