How can I add a new column, choose the x-lowest values from another column, and use 1 and 0 to differentiate? (Accoring to id number)
<p>I have a csv.data and want to add a new column ("new"). In this new column I like to have 1's for the two lowest values in "cycle". The rest should be 0's. This procedure should be done for each group of numbers in "id". The result should be like the following image.(This is just a example. In my case I have much more data) Hopefully, someone can help me.</p>

<p><code>
   id   cycle  new 
   1     1     1 
   1     2     1 
   1     3     0 
   2     1     1 
   2     2     1 
   2     3     0 
   3     1     1 
   3     2     1 
   3     3     0</code> </p>

<p>Here's an approach assuming <code>a</code> as the input array with those two columns -</p>

<pre><code>sorted_idx = np.lexsort(a[:,::-1].T)
idx = np.unique(a[sorted_idx,0],return_index=1)[1]
bin_arr = np.convolve(np.in1d(np.arange(a.shape[0]),idx),[1,1],'same')
out = bin_arr[sorted_idx.argsort()]
</code></pre>

<p>Few possible improvements (on performance) :</p>

<p>1) At the first step, we could alternatively have :</p>

<pre><code>sorted_idx = np.ravel_multi_index(a.T,a.max(0)+1).argsort()
</code></pre>

<p>2) Alternative way to calculate <code>idx</code> could be like so :</p>

<pre><code>a0 = a[sorted_idx,0]
idx = np.append(0,np.nonzero(a0[1:] &gt; a0[:-1])[0]+1)
</code></pre>

<p>3) Last two steps could be replaced with something like this -</p>

<pre><code>out = np.zeros(a.shape[0],dtype=int)
out[sorted_idx[(idx[:,None] + [0,1])]] = 1
</code></pre>

<p>Sample run -</p>

<pre><code>In [79]: a       # Input array
Out[79]: 
array([[ 1,  4],
       [ 1,  3],
       [ 1,  5],
       [ 2,  6],
       [ 2,  9],
       [ 2,  5],
       [ 2, 11],
       [ 3,  3],
       [ 3,  4],
       [ 3,  0]])

In [80]: np.column_stack((a,out)) # Input stacked with output
Out[80]: 
array([[ 1,  4,  1],
       [ 1,  3,  1],
       [ 1,  5,  0],
       [ 2,  6,  1],
       [ 2,  9,  0],
       [ 2,  5,  1],
       [ 2, 11,  0],
       [ 3,  3,  1],
       [ 3,  4,  0],
       [ 3,  0,  1]])
</code></pre>

python pandas numpy