Aggregating hub flows from trip data

user_14419192 0
Viewed 30

I'm working on a problem where I need to aggregate trip data to summarize activity at hubs. More specifically I'd like to know for a given hour how many trips went in or out of a station.

This is what my data looks like to start:

    trip_id     start_time             end_time               start_station  end_station
0   326702093   2019-10-01 00:07:03    2019-10-01 00:12:09    3020           3163
1   326702092   2019-10-01 00:11:31    2019-10-01 00:40:00    3057           3037
2   326702091   2019-10-01 00:13:07    2019-10-01 00:40:28    3057           3037
3   326702090   2019-10-01 00:13:43    2019-10-01 00:25:44    3026           3164
4   326702089   2019-10-01 00:13:59    2019-10-01 00:40:33    3057           3026

This is where I would like to end up:

hour                  station    in    out
2019-10-01 00:00:00   3020       0     1
2019-10-01 00:00:00   3057       0     3
2019-10-01 00:00:00   3026       1     1
2019-10-01 00:00:00   3163       1     0
2019-10-01 00:00:00   3037       2     0
2019-10-01 00:00:00   3164       1     0

So far my approach has been to split the observations into departures and arrivals and stack them so that station is unified, then move forward with aggregation

    trip_id     time                      station    in     out
0   326702093   2019-10-01 00:07:03       3020       0      1
1   326702092   2019-10-01 00:11:31       3057       0      1
2   326702091   2019-10-01 00:13:07       3057       0      1
3   326702090   2019-10-01 00:13:43       3026       0      1
4   326702089   2019-10-01 00:13:59       3057       0      1
0   326702093   2019-10-01 00:12:09       3163       1      0
1   326702092   2019-10-01 00:40:00       3037       1      0
2   326702091   2019-10-01 00:40:28       3037       1      0
3   326702090   2019-10-01 00:25:44       3164       1      0
4   326702089   2019-10-01 00:40:33       3026       1      0

When I try to resample it I get something like this:

stacked.groupby(pd.Grouper(freq='H')).agg('sum)


    hour                 in out
    2019-10-01 00:00:00  5  5
pandas python
2 Answers

Continuing your thought process, maybe add 'station' to the groupby:

df.groupby([pd.Grouper(key='time', freq='H'), 'station']).sum()
user_130448780

I found a workaround that's not very elegant but achieves exactly the result I was looking for.

I split the data by station then aggregated observations for each station by hour and merged them back together.

There must be a better way but this is what I ended up doing:

    # create a list of the stations
stations = stacked['station'].unique()

# create a dict to create and hold the dataframes
station_frames = {}

# loop through the data for each station
for station in stations:
    
    #select the data for that station
    mask = stacked['station'] == station
    small = stacked.loc[mask, :]
    
    # aggregate it
    small.set_index('datetime', inplace=True)
    grouped = small.resample('H').agg({'flow' : 'sum'
                         , 'incoming_duration' : 'mean'
                        , 'outgoing_duration' : 'mean'
                        , 'trip_route_category' : 'mean'})
    
    # preserves the int type better than .agg('mean')
    grouped['station'] = station
    
    station_frames[station] = grouped

unified = pd.DataFrame()
for station in stations:
    unified = pd.concat([unified, station_frames[station]])
user_144191920
Related