R: find first non-NA observation in data.table column by group
<p>I have a <code>data.table</code> with many missing values and I want a variable which gives me a 1 for the first non-missin value in each group.</p>

<p>Say I have such a data.table:</p>

<pre><code>library(data.table)
DT &lt;- data.table(iris)[,.(Petal.Width,Species)]
DT[c(1:10,15,45:50,51:70,101:134),Petal.Width:=NA]
</code></pre>

<p>which now has missings in the beginning, at the end and in between. I have tried two versions, one is:</p>

<pre><code>DT[min(which(!is.na(Petal.Width))),first_available:=1,by=Species]
</code></pre>

<p>but it only finds the global minimum (in this case, setosa gets the correct 1), not the minimum by group. I think this is the case because <code>data.table</code> first subsets by i, then sorts by group, correct? So it will only work with the row that is the global minimum of <code>which(!is.na(Petal.Width))</code> which is the first non-NA value. </p>

<p>A second attempt with the test in j:</p>

<pre><code>DT[,first_available:= ifelse(min(which(!is.na(Petal.Width))),1,0),by=Species]
</code></pre>

<p>which just returns a column of 1s. Here, I don't have a good explanation as to why it doesn't work.</p>

<p>my goal is this:</p>

<pre><code>DT[,first_available:=0]
DT[c(11,71,135),first_available:=1]
</code></pre>

<p>but in reality I have hundreds of groups. Any help would be appreciated!</p>

<p>Edit: <a href="https://stackoverflow.com/questions/15776064/r-first-observation-by-group-using-data-table-self-join">this</a> question does come close but is not targeted at NA's and does not solve the issue here if I understand it correctly. I tried:</p>

<pre><code>DT &lt;- data.table(DT, key = c('Species'))
DT[unique(DT[,key(DT), with = FALSE]), mult = 'first']
</code></pre>