0
$\begingroup$

Sample data in .csv format

| No.|   IP     |      Unix_time     |    # integer unix time
| 1  | 1.1.1.1  |     1563552000     |    # equivalent to 12:00:00 AM
| 2  | 1.1.1.1  |     1563552030     |    # equivalent to 12:00:30 AM
| 3  | 1.1.1.1  |     1563552100     |    # equivalent to 12:01:40 AM
| 4  | 1.1.1.1  |     1563552110     |    # equivalent to 12:01:50 AM
| 5  | 1.1.1.1  |     1563552150     |    # equivalent to 12:02:30 AM
| 6  | 1.2.3.10 |     1563552120     |    

Here's the current working code using pandas groupby( ) and get_group( ) functions:

data = pd.read_csv(some_path, header=0)
root = data.groupby('IP')

for a in root.groups.keys():
    t = root.get_group(a)['Unix_time']
    print(a + 'has' + t.count() + 'record')

You will see the results below:

1.1.1.1 has 5 record
1.2.3.10 has 1 record

Now, I want some changes.

For the same IP value (e.g., 1.1.1.1), I want to make further sub-groups based on a maximum time interval (e.g., 60 seconds), and count the number of elements in each sub-group. For example, in above sample data:

Start from row 1: row 2 Unix_time value is within 60 seconds, but row 3 is beyond 60 seconds.

Thus, row 1-2 is a group, row 3-4 is a separate group, row 5 is a separate group. In other words, group '1.1.1.1' has 3 sub-groups now.

How to make it?

$\endgroup$
1
  • $\begingroup$ Is that question still relevant? if so, do you want that kind of grouping for all IPs or really only for some of them? You want to consider all events in the same group for which the previous event was not more than 60 seconds earlier, right? $\endgroup$
    – jottbe
    Sep 28, 2019 at 9:48

1 Answer 1

1
$\begingroup$

You can do that by using a combination of shift to compare the values of two consecutive rows and cumsum to produce subgroup-ids.

So the code looks like this:

# define a function that assigns subgroups
def get_time_group(ser):
    # calculate the time difference between
    # each time and the time of the previous
    # time
    # the backfill has the effect, that the first
    # row gets diff=0
    time_diff= ser - ser.shift(1).fillna(method='backfill')
    # now assign the id which is increased any time the
    # time difference between two records is greather than 60
    return (time_diff > 60).cumsum()

# group by the IP to compare the times only for the same IP
# and call the get_time_group from transform to assign the
# new group to each row (the row will be unique in combination
# with the IP
grouped= df.groupby('IP')
df['sub_group']= grouped['Unix_time'].transform(get_time_group)

After execting this code the df now looks like this:

           IP   Unix_time  sub_group
No.                                 
1     1.1.1.1  1563552000          0
2     1.1.1.1  1563552030          0
3     1.1.1.1  1563552100          1
4     1.1.1.1  1563552110          1
5     1.1.1.1  1563552150          1
6    1.2.3.10  1563552120          0

Now you can use the sub_group as required. E.g. if you want to have the start-end of each subgroup and the number of records per subgroup, you can do:

result= df.groupby(['IP', 'sub_group']).agg({'Unix_time': ['first', 'last', 'count']})
result.columns= ['Group_start', 'Group_end', 'Num_entries']

# the result looks like this:
                    Group_start   Group_end  Num_entries
IP       sub_group                                      
1.1.1.1  0           1563552000  1563552030            2
         1           1563552100  1563552150            3
1.2.3.10 0           1563552120  1563552120            1

The test dataframe for the output above is from your sample data, you get it by executing:

import io
import pandas as pd

raw=\
"""| No.|   IP     |      Unix_time     |    Comment
| 1  | 1.1.1.1  |     1563552000     |    # equivalent to 12:00:00 AM
| 2  | 1.1.1.1  |     1563552030     |    # equivalent to 12:00:30 AM
| 3  | 1.1.1.1  |     1563552100     |    # equivalent to 12:01:40 AM
| 4  | 1.1.1.1  |     1563552110     |    # equivalent to 12:01:50 AM
| 5  | 1.1.1.1  |     1563552150     |    # equivalent to 12:02:30 AM
| 6  | 1.2.3.10 |     1563552120     |    #"""

df= pd.read_csv(io.StringIO(raw), sep='\s*\|\s*', dtype={'Unix_time': 'Int64'}, engine='python', index_col=0, usecols=['No.', 'IP', 'Unix_time'])
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.