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>Transpose the <code>DF</code>:</p>

<pre><code>df = df.T
</code></pre>

<p>Since you tried along the lines of using <code>groupby</code>, you could further proceed using <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.value_counts.html" rel="nofollow"><code>value_counts</code></a> to get the count of zeros in every group after stacking it to produce a <code>series</code> object and later unstack it back to the <code>DF</code> as shown:</p>

<pre><code>grp = df.stack().to_frame('val').groupby(level=0)['val']
df['noEvents'] = grp.value_counts().unstack()[0]
</code></pre>

<p>Later, divide the values with the size of that group to get it's percentage distribution:</p>

<pre><code>df['upTime'] = (100*df['noEvents']/grp.size())
</code></pre>

<p>For Aesthetic purpose:</p>

<pre><code>df = df[['noEvents', 'upTime']].astype(int)
df.index.name = 'IP'
df.columns.name = None
df
</code></pre>

<p><a href="https://i.stack.imgur.com/zf06U.png" rel="nofollow"><img src="https://i.stack.imgur.com/zf06U.png" alt="Image"></a></p>

python pandas sum multiple-columns mean