How to calculate the number of matched groups of rows in pandas dataframe?
<p>Having been banging my head against the wall for 2 days now for this one. Got some ideas, tried to implement, but the speed was terribly slow, so wonder whether someone can point out a better way of doing this. Here's what I want:</p>

<p>I have a dataframe like this:</p>

<p><code>pd.DataFrame({'var1':[1, 1, 4, 4, 4, 7, 8], 'var2': [2, 2, 5, 5, 5, 8, 8], 'var3':[3, 3, 6, 6, 6, 9, 8], 'label':['a', 'a', 'b', 'b', 'c', 'd', 'd']})</code></p>

<pre><code>    label     var1    var2    var3
 0    a         1       2       3
 1    a         1       2       3
 2    b         4       5       6
 3    b         4       5       6
 4    c         4       5       6
 5    d         7       8       9
 6    d         8       8       8
</code></pre>

<p>So, what I want is to calculate the percentage of matched labels given duplicated vars. For example, row 0, row 1 are duplicates based on var1, var2, and var3, the same happens with row 2, row 3 and row 4, while row 5 and 6 are not duplicates due to the difference in values of var3. Out of the two duplicate groups, if we check the labels, we see that group 1 (rows 0, 1) labels also match ('a', 'a'), while group 2 (rows 2, 3, 4) are not matched ('b', 'b', 'c'). Therefore, the percentage is 1/2 = 50%.</p>

<p>Currently, this is what I am doing:</p>

<ol>
<li>Sort the data frame using var1, var2, var3</li>
<li>Loop thru the rows and record index of rows that are not duplicate of the next row</li>
<li>Use previous index to slice dataframe to keep only rows that have duplicate(s). Each set of duplicated rows is a group. Count the total number of groups.</li>
<li>In the new dataframe, check each group's label column, if all labels match in a group, count it as a matched group.</li>
<li>Use number of matched groups to divide total number of groups.  </li>
</ol>

<p>Any help is appreciated!</p>

<p>Using a <code>groupby</code> approach:</p>

<pre><code>def matched_group(grp):
    if len(grp) == 1:
        return np.nan    
    return grp.nunique() == 1

is_matched = df.groupby(['var1', 'var2', 'var3'])['label'].apply(matched_group).dropna()
match_pcnt = is_matched.sum()/len(is_matched)
</code></pre>

<p>The <code>matched_group</code> function returns a Boolean indicating if all of the labels within a group of variables are unique, or <code>np.nan</code> if the group of variables has only one element, meaning the group is not duplicated.  Then, after dropping null values, just count the matches and divide by the total number of duplicate groups.</p>

<p>The code above gives a value of <code>0.5</code> for <code>match_pcnt</code>.</p>

python pandas