creating a binary vector from the dataframe column values

Question

I have a dataframe df

    ID   KD     DT   
0    1    2     5.6  
1    1    5     8.7  
4    4    9     1.9  
5    4    2     1.7  
6    4    7     8.8  
2    6    9     8.3  
3    6    7     7.2  
9    7   36     3.1  
10   7    2     2.2  
12   7    7     5.6

I want to create a dataframe such that for each unique KD value, new columns of {-1,0,1} are added depending on ID (from a list of ID values) and DT. ID = [1,2,4,6,7,8]. New dataframe should have len(ID)+1 columns with first column the unique KD value and len(D) columns such that column ID = 1 if df.loc[(df.ID==id) & (df.KD==kd),'DT'] >= 5, column ID = 0 if (kd,id) pair is not in df and column ID = -1 if df.loc[(df.ID==id) & (df.KD==kd),'DT'] < 5

For the dataframe given above new dataframe should be

df2

    KD     1     2    4     6     7     8    

0    2     1     0    -1    0     -1    0 
1    5     1     0     0    0      0    0  
2    7     0     0     1    1      1    0
3    9     0     0    -1    1      0    0
4   36     0     0     0    0     -1    0

In fact, number of unique KD and ID are very large (in the range of 10K). Any help in finding a very efficient way to do this. please ?

Is your row 3 column 4 correct? KD=9 and ID=4 with DT value of 1.9 which is less than 5, shouldn't be -1? — Scott Boston
– Scott Boston, Commented Apr 28, 2020 at 19:29

Scott Boston · Accepted Answer · 2020-04-28 19:26:46Z

1

Let's try this using pivot and mask:

ID = [1,2,4,6,7,8]
df_p = df.pivot('KD', 'ID', 'DT')
df_p.mask((df_p >= 5), 1).mask(df_p < 5, -1).reindex(ID, axis=1)\
    .fillna(0).reset_index()

Output:

ID  KD    1    2    4    6    7    8
0    2  1.0  0.0 -1.0  0.0 -1.0  0.0
1    5  1.0  0.0  0.0  0.0  0.0  0.0
2    7  0.0  0.0  1.0  1.0  1.0  0.0
3    9  0.0  0.0 -1.0  1.0  0.0  0.0
4   36  0.0  0.0  0.0  0.0 -1.0  0.0

answered Apr 28, 2020 at 19:26

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

creating a binary vector from the dataframe column values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related