Finding efficiently pandas (part of) rows with unique values
<p>Given a pandas dataframe with a row per individual/record. A row includes a property value and its evolution across time (0 to N). </p>

<p>A schedule includes the estimated values of a variable 'property' for a number of entities from day 1 to day 10 in the following example. </p>

<p>I want to filter entities with unique values for a given period and get those values</p>

<pre><code>csv=',property,1,2,3,4,5,6,7,8,9,10\n0,100011,0,0,0,0,3,3,3,3,3,0\n1,100012,0,0,0,0,2,2,2,8,8,0\n2, \
100012,0,0,0,0,2,2,2,2,2,0\n3,100012,0,0,0,0,0,0,0,0,0,0\n4,100011,0,0,0,0,2,2,2,2,2,0\n5, \
180011,0,0,0,0,2,2,2,2,2,0\n6,110012,0,0,0,0,0,0,0,0,0,0\n7,110011,0,0,0,0,3,3,3,3,3,0\n8, \
110012,0,0,0,0,3,3,3,3,3,0\n9,110013,0,0,0,0,0,0,0,0,0,0\n10,100011,0,0,0,0,3,3,3,3,4,0'

from StringIO import StringIO
import numpy as np

schedule = pd.read_csv(StringIO(csv), index_col=0)
print schedule

    property  1  2  3  4  5  6  7  8  9  10
0     100011  0  0  0  0  3  3  3  3  3   0
1     100012  0  0  0  0  2  2  2  8  8   0
2     100012  0  0  0  0  2  2  2  2  2   0
3     100012  0  0  0  0  0  0  0  0  0   0
4     100011  0  0  0  0  2  2  2  2  2   0
5     180011  0  0  0  0  2  2  2  2  2   0
6     110012  0  0  0  0  0  0  0  0  0   0
7     110011  0  0  0  0  3  3  3  3  3   0
8     110012  0  0  0  0  3  3  3  3  3   0
9     110013  0  0  0  0  0  0  0  0  0   0
10    100011  0  0  0  0  3  3  3  3  4   0
</code></pre>

<p>I want to find records/individuals for who property has not changed during  a given period and the corresponding unique values</p>

<p>Here is what i came with : I want to locate individuals with property in  [100011, 100012, 1100012] between days 7 and 10</p>

<pre><code>props = [100011, 100012, 1100012]
begin = 7
end = 10

res = schedule['property'].isin(props)
df = schedule.ix[res, begin:end]
print "df \n%s " %df 
</code></pre>

<p>We have :</p>

<pre><code>df 
    7  8  9
0   3  3  3
1   2  8  8
2   2  2  2
3   0  0  0
4   2  2  2
10  3  3  4 

res = df.apply(lambda x: np.unique(x).size == 1, axis=1)
print  "res : %s\n" %res
df_f = df.ix[res,]
print "df filtered  %s \n" % df_f

res = pd.Series(df_f.values.ravel()).unique().tolist()
print "unique values : %s " %res
</code></pre>

<p>Giving : </p>

<pre><code>res :
 0      True
1     False
2      True
3      True
4      True
10    False
dtype: bool

df filtered 
    7  8  9
0  3  3  3
2  2  2  2
3  0  0  0
4  2  2  2 

unique values : [3, 2, 0] 
</code></pre>

<p>As those operations need to be run many times (in millions) on a million rows dataframe,  i need to be able to run it as quickly as possible.</p>

<p>(@MaxU) : schedule can be seen as a database/repository updated many times.  The repository is then requested as well many times for unique values </p>

<p>Would you have some ideas for improvements/ alternate ways ?</p>

<p>Given your df</p>

<pre><code>    7  8  9
0   3  3  3
1   2  8  8
2   2  2  2
3   0  0  0
4   2  2  2
10  3  3  4 
</code></pre>

<p>You can simplify your code to:</p>

<pre><code>df_f = df[df.apply(pd.Series.nunique, axis=1) == 1]
print(df_f)
   7  8  9
0  3  3  3
2  2  2  2
3  0  0  0
4  2  2  2
</code></pre>

<p>And the final step to:</p>

<pre><code>res = df_f.iloc[:,0].unique().tolist()

print(res)
[3, 2, 0]
</code></pre>

<p>It's not fully vectorised, but maybe this clarifies things a bit towards that?</p>

python performance pandas