Resample into equidistant time based on multiple indices

user_335427 0
Viewed 47

I have a dataframe with some network flows similar to this

flow = {'date': ['2020-11-13 13:57:51','2020-11-13 13:57:51','2020-11-13 13:57:52','2020-11-13 13:59:53','2020-11-13 13:59:54'],
        'source_ip': ['192.168.1.1','192.168.1.2','10.0.0.1','192.168.1.1','192.168.1.1'],
        'destination_ip': ['10.0.0.1', '10.0.0.1', '192.168.1.1', '192.168.1.2', '192.168.1.2'],
        'source_bytes':[5,1,2,3,3]
        }

df = pd.DataFrame(flow, columns = ['date', 'source_ip', 'destination_ip', 'source_bytes']).set_index('date')

Looks like this

date                | source_ip     | destination_ip| source_bytes
2020-11-13 13:57:51 | 192.168.1.1   | 10.0.0.1      | 5
2020-11-13 13:57:51 | 192.168.1.2   | 10.0.0.1      | 1 
2020-11-13 13:57:52 | 10.0.0.1      | 192.168.1.1   | 2
2020-11-13 13:59:53 | 192.168.1.1   | 192.168.1.2   | 3
2020-11-13 13:59:54 | 192.168.1.2   | 192.168.1.1   | 3

I would like to resample them into 1min ticks but also grouped by ip. Then source_bytes needs to be aggregated regardless if the ip is in either source_ip or destination_ip

Should become something like that. (Calculated manually. Hopefully did not do any mistakes here). For every minute all ip's should be represented but filled with zeros if there is no value.

ip          | date                  | source_bytes_sum
192.168.1.1 | 2020-11-13 13:57:00   | 7
192.168.1.2 | 2020-11-13 13:57:00   | 1
10.0.0.1    | 2020-11-13 13:57:00   | 8
192.168.1.1 | 2020-11-13 13:59:00   | 6
192.168.1.2 | 2020-11-13 13:59:00   | 6
10.0.0.1    | 2020-11-13 13:59:00   | 0

Here the same representation just 'grouped' by the ip

ip          | date                  | source_bytes_sum
192.168.1.1 | 2020-11-13 13:57:00   | 7
            | 2020-11-13 13:59:00   | 6
192.168.1.2 | 2020-11-13 13:57:00   | 1
            | 2020-11-13 13:59:00   | 6
10.0.0.1    | 2020-11-13 13:57:00   | 8
            | 2020-11-13 13:59:00   | 0

I started experimenting with the following but that only groups by source_ip and ignores destination_ip. Also it does not add zero values

grouped = df.groupby(['source_ip', pd.Grouper(key='date', freq='1min')])[['source_bytes']].agg(['sum'])
grouped

                                source_bytes
                                sum
source_ip   date    
10.0.0.1    2020-11-13 13:57:00 2
192.168.1.1 2020-11-13 13:57:00 5
            2020-11-13 13:59:00 6
192.168.1.2 2020-11-13 13:57:00 1
pandas time-series resampling
1 Answers

Use unpivot first by DataFrame.melt, then use your solution with Grouper and for 0 values add Series.unstack with DataFrame.stack:

df = (df.melt(['date', 'source_bytes'], value_name='ip')
        .groupby(['ip', pd.Grouper(key='date', freq='1min')])['source_bytes']
        .sum()
        .unstack(fill_value=0)
        .stack()
        .reset_index(name='sum'))
print (df)
            ip                date  sum
0     10.0.0.1 2020-11-13 13:57:00    8
1     10.0.0.1 2020-11-13 13:59:00    0
2  192.168.1.1 2020-11-13 13:57:00    7
3  192.168.1.1 2020-11-13 13:59:00    6
4  192.168.1.2 2020-11-13 13:57:00    1
5  192.168.1.2 2020-11-13 13:59:00    6

Or use DataFrame.stack with appended source_bytes to MultiIndex with DataFrame.set_index:

df = (df.set_index(['date','source_bytes'])
        .stack()
        .reset_index(name='ip')
        .groupby(['ip', pd.Grouper(key='date', freq='1min')])['source_bytes']
        .sum()
        .unstack(fill_value=0)
        .stack()
        .reset_index(name='sum')
       )
print (df)
            ip                date  sum
0     10.0.0.1 2020-11-13 13:57:00    8
1     10.0.0.1 2020-11-13 13:59:00    0
2  192.168.1.1 2020-11-13 13:57:00    7
3  192.168.1.1 2020-11-13 13:59:00    6
4  192.168.1.2 2020-11-13 13:57:00    1
5  192.168.1.2 2020-11-13 13:59:00    6

EDIT: For use more aggregate function use:

df = pd.DataFrame(flow, columns = ['date', 'source_ip', 'destination_ip', 'source_bytes'])
df['date'] = pd.to_datetime(df['date'])


df2 = (df.melt(['date', 'source_bytes'], value_name='ip')
        .groupby(['ip', pd.Grouper(key='date', freq='1min')])['source_bytes']
        .agg(['sum','min','mean'])
        .unstack(fill_value=0)
        .stack()
        .reset_index()
        )
print (df2)
            ip                date  sum  min      mean
0     10.0.0.1 2020-11-13 13:57:00    8    1  2.666667
1     10.0.0.1 2020-11-13 13:59:00    0    0  0.000000
2  192.168.1.1 2020-11-13 13:57:00    7    2  3.500000
3  192.168.1.1 2020-11-13 13:59:00    6    3  3.000000
4  192.168.1.2 2020-11-13 13:57:00    1    1  1.000000
5  192.168.1.2 2020-11-13 13:59:00    6    3  3.000000
user_29010020
Related