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>

<pre><code>gb = df.groupby(['var1', 'var2', 'var3', 'label'])
size = gb.size()
size

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

<hr>

<pre><code>lvls = list(range(size.index.nlevels))
size2 = size.groupby(level=lvls[:-1]).size()
size2

var1  var2  var3
1     2     3       1
4     5     6       2
7     8     9       1
8     8     8       1
dtype: int64
</code></pre>

<hr>

<pre><code>(size2 == 1).sum() / float(size2.shape[0])

0.75
</code></pre>

python pandas