Pandas groupby countif with dynamic columns
<p>I have a dataframe with this structure:</p>

<pre><code>time,10.0.0.103,10.0.0.24
2016-10-12 13:40:00,157,172
2016-10-12 14:00:00,0,203
2016-10-12 14:20:00,0,0
2016-10-12 14:40:00,0,200
2016-10-12 15:00:00,185,208
</code></pre>

<p>It details the number of events per IP address for a given 20 minute period. I need a dataframe of how many 20 minute periods per miner had 0 events, from which I need to derive IP 'uptime' as a percent. The number of IP addresses is dynamic. Desired output:</p>

<pre><code>IP,noEvents,uptime
10.0.0.103,3,40
10.0.0.24,1,80
</code></pre>

<p>I have tried with groupby, agg and lambda to no avail. What is the best way of doing a 'countif' by dynamic columns?</p>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sum.html" rel="nofollow"><code>sum</code></a> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.mean.html" rel="nofollow"><code>mean</code></a> of boolean mask by condition <code>df == 0</code>. Last <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow"><code>concat</code></a> both <code>Series</code>:</p>

<pre><code>df.set_index('time', inplace=True)
mask = (df == 0)
print (mask)
                    10.0.0.103 10.0.0.24
time                                    
2016-10-12 13:40:00      False     False
2016-10-12 14:00:00       True     False
2016-10-12 14:20:00       True      True
2016-10-12 14:40:00       True     False
2016-10-12 15:00:00      False     False

noEvents = mask.sum()
print (noEvents)
10.0.0.103    3
10.0.0.24     1
dtype: int64

uptime = 100 * mask.mean()
print (uptime)
10.0.0.103    60.0
10.0.0.24     20.0
dtype: float64

print (pd.concat([noEvents, uptime], axis=1, keys=('noEvents','uptime'))
         .reset_index()
         .rename(columns={'index':'IP'}))

           IP  noEvents  uptime
0  10.0.0.103         3    60.0
1   10.0.0.24         1    20.0
</code></pre>

python pandas sum multiple-columns mean