Pandas : Getting unique rows for a given column but conditional on some criteria of other columns
<p>I'm using python 2.7.  From a given data as follows:</p>

<pre><code>data = pd.DataFrame({'id':['001','001','001','002','002','003','003','003','004','005'],
                   'status':['ground','unknown','air','ground','unknown','ground','unknown','unknown','unknown','ground'],
                   'value':[10,-5,12,20,-12,2,-4,-1,0,6]})
</code></pre>

<p>The data looks like this:</p>

<pre><code>id     status     value
001    ground       10
001    unknown      -5
001    air          12
002    ground       20
002    unknown     -12
003    ground        2
003    unknown      -4
003    unknown      -1
004    unknown       0
005    ground        6
</code></pre>

<p>I would like to get the output in dataframe that has unique id conditional to the following criteria: For a given id</p>

<pre><code> 'status': If 'air' does exist, pick 'air'.
           If 'air' does not exist, pick 'ground'.  
           If both 'air' and 'ground' do not exist, pick 'unknown'.

 'value': Sum of values for each id
 'count': Count the number of rows for each id
</code></pre>

<p>Therefore, the expected output is the following.</p>

<pre><code>id     status     value     count
001     air        17        3
002    ground       8        2
003    ground      -3        3
004    unknown      0        1
005    ground       6        1
</code></pre>

<p>I can do looping for each unique id but it is not elegant enough and computation is also expensive, especially when data becomes large.  May i know the better pythonic style and more efficient way to come up with this output?  Thank you in advance.</p>

<p>One option would be changing the type of status column to category and sorting based on that in groupby.agg:</p>

<pre><code>df['status'] = df['status'].astype('category', categories=['air', 'ground', 'unknown'], ordered=True)

df.sort_values('status').groupby('id').agg({'status': 'first', 'value': ['sum', 'count']})
Out: 
      status value      
       first   sum count
id                      
001      air    17     3
002   ground     8     2
003   ground    -3     3
004  unknown     0     1
005   ground     6     1
</code></pre>

<p>Here, since the values are sorted in <code>'air'</code>, <code>'ground'</code> and <code>'unknown'</code> order, <code>'first'</code> returns the correct value. If you don't want to change the type, you can define your own function that returns <code>air</code>/<code>ground</code>/<code>unknown</code> and instead of <code>'first'</code> you can pass that function.</p>

python pandas