1

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

Mi.
  • 510
  • 1
  • 4
  • 20

1 Answers1

2

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
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • What should I change when I have a row like this "1: Li_In-AR-B, Or-Ba-AR-B"? I need both of those in a separate column. – Mi. Aug 18 '18 at 20:42
  • @ThelMi Do you think `1: Li_In-AR-B, Or_Ba-AR-B`? `_` after `Or` ? – jezrael Aug 19 '18 at 05:33
  • Also there is space after `,` ? – jezrael Aug 19 '18 at 10:05
  • @ThelMi - Hmmm, I create solution and unfortunately cannot be used old one. What do you think, is possible create new question with change `1 Li_In-AR-B` to `1 Li_In-AR-B, Or_Ba-AR-B` with changing expected output? In my opinion itis better like editing this question. Thanks. – jezrael Aug 19 '18 at 11:57
  • Then I can add answer to new question. – jezrael Aug 19 '18 at 11:57
  • Yeah I tried using your old solution to solve this new case but I couldn't change it so that I would actually help me. I'm going to create a new question right now. – Mi. Aug 19 '18 at 12:58
  • @ThelMi sorry, I am offline, on phone only, so untested solution. – jezrael Aug 19 '18 at 13:41