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>Here's a NumPy based approach -</p>

<pre><code># Extract the relevant cond column as a 1D NumPy array and pad with False at
# either ends, as later on we would try to find the start (rising edge) 
# and stop (falling edge) for each interval of True values
arr = np.concatenate(([False],df.cond.values,[False]))

# Determine the rising and falling edges as start and stop 
start = np.nonzero(arr[1:] &gt; arr[:-1])[0]
stop = np.nonzero(arr[1:] &lt; arr[:-1])[0]

# Get the interval lengths and determine the largest interval ID
maxID = (stop - start).argmax()

# With maxID get max interval range and thus get mean on the second col
out = df.data.iloc[start[maxID]:stop[maxID]].mean()
</code></pre>

<p><strong>Runtime test</strong></p>

<p>Approaches as functions -</p>

<pre><code>def pandas_based(df): # @ayhan's soln
    res = df['data'].groupby((df['cond'] != df['cond'].shift()).\
                                cumsum()).agg(['count', 'mean'])
    return res[res['count'] == res['count'].max()]

def numpy_based(df):
    arr = np.concatenate(([False],df.cond.values,[False]))
    start = np.nonzero(arr[1:] &gt; arr[:-1])[0]
    stop = np.nonzero(arr[1:] &lt; arr[:-1])[0]
    maxID = (stop - start).argmax()
    return df.data.iloc[start[maxID]:stop[maxID]].mean()
</code></pre>

<p>Timings -</p>

<pre><code>In [208]: # Setup dataframe
     ...: N = 1000  # Datasize
     ...: df = pd.DataFrame(np.random.rand(N),columns=['data'])
     ...: df['cond'] = np.random.rand(N)&gt;0.3 # To have 70% True values
     ...: 

In [209]: %timeit pandas_based(df)
100 loops, best of 3: 2.61 ms per loop

In [210]: %timeit numpy_based(df)
1000 loops, best of 3: 215 Âµs per loop

In [211]: # Setup dataframe
     ...: N = 10000  # Datasize
     ...: df = pd.DataFrame(np.random.rand(N),columns=['data'])
     ...: df['cond'] = np.random.rand(N)&gt;0.3 # To have 70% True values
     ...: 

In [212]: %timeit pandas_based(df)
100 loops, best of 3: 4.12 ms per loop

In [213]: %timeit numpy_based(df)
1000 loops, best of 3: 331 Âµs per loop
</code></pre>

python performance pandas numpy