Determine mean value of âdataâ where the highest number of CONTINUOUS cond=True
<p>I have a pandas Dataframe with a 'data' and 'cond'(-ition) column. I need the mean value (of the data column) of the rows with the highest number of CONTINUOUS True objects in 'cond'.  </p>

<pre><code>    Example DataFrame:

        cond  data
    0   True  0.20
    1  False  0.30
    2   True  0.90
    3   True  1.20
    4   True  2.30
    5  False  0.75
    6   True  0.80

    Result = 1.466, which is the mean value of row-indexes 2:4 with 3 True
</code></pre>

<p>I was not able to find a âvectorizedâ solution with a groupby or pivot method. So I wrote a func that loops the rows. Unfortunately this takes about an hour for 1 Million lines, which is way to long. Unfortunately, the @jit decoration does not reduce the duration measurably. </p>

<p>The data I want to analyze is from a monitoring project over one year and I have every 3 hours a DataFrame with one Million rows. Thus, about 3000 such files. </p>

<p>An efficient solution would be very important. I am also very grateful for a solution in numpy.  </p>

<p>Using the approach from <a href="http://stackoverflow.com/questions/29142487/calculating-the-number-of-specific-consecutive-equal-values-in-a-vectorized-way">Calculating the number of specific consecutive equal values in a vectorized way in pandas</a>:</p>

<pre><code>df['data'].groupby((df['cond'] != df['cond'].shift()).cumsum()).agg(['count', 'mean'])[lambda x: x['count']==x['count'].max()]
Out: 
      count      mean
cond                 
3         3  1.466667
</code></pre>

<p>Indexing by a callable requires 0.18.0, for earlier versions, you can do:</p>

<pre><code>res = df['data'].groupby((df['cond'] != df['cond'].shift()).cumsum()).agg(['count', 'mean'])

res[res['count'] == res['count'].max()]
Out: 
      count      mean
cond                 
3         3  1.466667
</code></pre>

<p>How it works:</p>

<p>The first part, <code>df['cond'] != df['cond'].shift()</code> returns a boolean array:</p>

<pre><code>df['cond'] != df['cond'].shift()
Out: 
0     True
1     True
2     True
3    False
4    False
5     True
6     True
Name: cond, dtype: bool
</code></pre>

<p>So the value is False whenever the row is the same as the above. That means that if you take the cumulative sum, these rows (consecutive ones) will have the same number:</p>

<pre><code>(df['cond'] != df['cond'].shift()).cumsum()
Out: 
0    1
1    2
2    3
3    3
4    3
5    4
6    5
Name: cond, dtype: int32
</code></pre>

<p>Since groupby accepts any Series to group on (it is not necessary to pass a column, you can pass an arbitrary list), this can be used to group the results. <code>.agg(['count', 'mean']</code> part just gives the respective counts and means for each group and at the end it selects the one with the highest count.</p>

<p>Note that this would group consecutive False's together, too. If you want to only consider consecutive True's, you can change the grouping Series to:</p>

<pre><code>((df['cond'] != df['cond'].shift()) | (df['cond'] != True)).cumsum()
</code></pre>

<p>Since we want False's when the condition is True, the condition became 'not equal to the row below <em>OR</em> not True'. So the original line would change to:</p>

<pre><code>df['data'].groupby(((df['cond'] != df['cond'].shift()) | (df['cond'] != True)).cumsum()).agg(['count', 'mean'])[lambda x: x['count']==x['count'].max()]
</code></pre>

python performance pandas numpy