Pandas - how to make further sub-groups after groupby( ) and get_group( )

Question

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?

Answer 1

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'])