Dynamically select from a dataframe using the values of another
<p>Using Pandas.py, I have csv of this format, which is dynamically created:</p>

<pre><code>time,name,status
2016-10-09 00:15:50,10.0.0.24,REJECTED
2016-10-09 00:15:50,10.0.0.24,REJECTED
2016-10-09 00:15:54,10.0.0.24,accepted
2016-10-09 00:15:57,10.0.0.24,accepted
2016-10-09 00:15:58,10.0.0.103,accepted
2016-10-09 00:15:59,10.0.0.24,accepted
2016-10-09 00:16:08,10.0.0.24,accepted
</code></pre>

<p>This is a raw log file of events from numerous IP addresses.</p>

<p>I then group the data with groupby and make the following dataframes (here in csv format). </p>

<p>An event count grouped by time (here 20 mins):</p>

<pre><code>time,10.0.0.103,10.0.0.24
2016-10-09 00:00:00,36,44
2016-10-09 00:20:00,143,199
2016-10-09 00:40:00,195,182
2016-10-09 01:00:00,174,200
2016-10-09 01:20:00,176,212
2016-10-09 01:40:00,165,186
2016-10-09 02:00:00,167,218
2016-10-09 02:20:00,171,222
2016-10-09 02:40:00,189,221
</code></pre>

<p>And a summary table:</p>

<pre><code>name,count,numReject,numAccept,percentAccept
10.0.0.103,9476,1,9475,99
10.0.0.24,12030,0,12030,100
</code></pre>

<p>I need a column in the summary table which defines when a given IP last had an event. A 'last seen' column. Desired output:</p>

<pre><code>name,count,numReject,numAccept,percentAccept, lastSeen
10.0.0.103,9476,1,9475,99,2016-10-09 00:15:58
10.0.0.24,12030,0,12030,100,2016-10-09 00:16:08
</code></pre>

<p>I have tried loops, but I feel like that is entirely the wrong way to go about things here. I can do it statically with a boolean iloc mask given a known IP value, but with an unknown number of IP addresses I cannot figure out where to begin!</p>

<p><strong><em>assumed imports</em></strong></p>

<pre><code>import pandas as pd
import numpy as np
</code></pre>

<p><strong><em>general solution</em></strong><br>
for more than 2 types of status</p>

<pre><code>g = df.groupby('name')
pd.concat([
        g.status.size().rename(''),
        g.time.last().rename(''),
        g.status.value_counts().unstack(fill_value=0),
        g.status.value_counts(normalize=True).unstack(fill_value=0)
    ], axis=1, keys=['Count', 'Last', 'status_counts', 'status_frequency']) \
    .rename_axis([None, None], axis=1) \
    .rename_axis([None])
</code></pre>

<p><a href="https://i.stack.imgur.com/b6vjA.png" rel="nofollow"><img src="https://i.stack.imgur.com/b6vjA.png" alt="enter image description here"></a></p>

<p><strong><em>tailored solution</em></strong><br>
assuming just <code>accepted</code> and <code>REJECTED</code></p>

<pre><code>df['status_bool'] = df.status.eq('accepted') * 1
summary = df.groupby('name').agg(
    dict(status=dict(Count='count'),
         status_bool=dict(Accepted='sum',
                          Freq=lambda x: np.sum(x) * 1. / np.size(x)),
         time=dict(Last='last')
        ))
summary.columns = summary.columns.droplevel(0)
summary
</code></pre>

<p><a href="https://i.stack.imgur.com/YCwGi.png" rel="nofollow"><img src="https://i.stack.imgur.com/YCwGi.png" alt="enter image description here"></a></p>

python csv pandas