How to match rows based on certain columns in pandas?
<p>I have a dataframe like this:</p>

<pre><code>id     date          event    name     time
1      2016-10-01    A        leader   12:45
2      2016-10-01    A        AA       12:87
3      2016-10-01    A        BB       12:45
</code></pre>

<p>There are rows for each member in the event, but one row has the leader data as well. I want to exclude the rows with the data about the leader and add a column <code>is_leader</code> to indicate whether a member is the leader or not. Something like this:</p>

<pre><code>id     date          event    name     time    is_leader
2      2016-10-01    A        AA       12:87   0
3      2016-10-01    A        BB       12:45   1
</code></pre>

<p>So, I know at <code>id=3</code> is the leader based on the time, which is 12:45 for both here. We can assume that this time won't be the same for any other members. </p>

<p>What is an efficient way to accomplish this in pandas. Here I have just one event as an example, but I'll have several of these and I need to do this for each event.</p>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> with custom function <code>f</code> which return new column <code>is_leader</code> with <code>True</code> for all rows where is same <code>time</code> as <code>time</code> of row with text <code>leader</code> in column <code>name</code>:</p>

<pre><code>print (df)
   id       date event    name   time
0   1 2016-10-01     A  leader  12:45
1   2 2016-10-01     A      AA  12:87
2   3 2016-10-01     A      BB  12:45
3   1 2016-10-01     B  leader  12:15
4   2 2016-10-01     B      AA  12:15
5   3 2016-10-01     B      BB  12:45

def f(x):
    x['is_leader'] = x.time == x.ix[x['name'] == 'leader', 'time'].iloc[0]
    return x

df= df.groupby('event').apply(f)
print (df)
   id       date event    name   time is_leader
0   1 2016-10-01     A  leader  12:45      True
1   2 2016-10-01     A      AA  12:87     False
2   3 2016-10-01     A      BB  12:45      True
3   1 2016-10-01     B  leader  12:15      True
4   2 2016-10-01     B      AA  12:15      True
5   3 2016-10-01     B      BB  12:45     False
</code></pre>

<hr>

<p>One row solution with lambda function:</p>

<pre><code>df['is_leader'] = df.groupby('event')
                    .apply(lambda x: x.time == x.ix[x['name'] == 'leader', 'time'].iloc[0])
                    .reset_index(drop=True, level=0)
print (df)
   id       date event    name   time is_leader
0   1 2016-10-01     A  leader  12:45      True
1   2 2016-10-01     A      AA  12:87     False
2   3 2016-10-01     A      BB  12:45      True
3   1 2016-10-01     B  leader  12:15      True
4   2 2016-10-01     B      AA  12:15      True
5   3 2016-10-01     B      BB  12:45     False
</code></pre>

<p>Then remove rows with <code>leader</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a> and cast <code>boolean</code> column to <code>int</code>:</p>

<pre><code>df = df[df.name != 'leader']
df.is_leader = df.is_leader.astype(int)
print (df)
   id       date event name   time  is_leader
1   2 2016-10-01     A   AA  12:87          0
2   3 2016-10-01     A   BB  12:45          1
4   2 2016-10-01     B   AA  12:15          1
5   3 2016-10-01     B   BB  12:45          0
</code></pre>

python pandas feature-extraction