pandas find median after group by

Question

df.head(10).to_clipboard(sep=';', index=True)

I have a dataframe as above and I have the following column descriptions

•   Id - the uuid of this delivery
•   PlanId - the uuid of the plan (the plan for deliveries of a given day)
 •  PlanDate - the date of delivery

•   MinTime - the minimal time (seconds from midnight) for delivering this delivery
•   MaxTime - the maximal time (seconds from midnight) for delivering this delivery
•   RouteId - the uuid of the route this delivery belongs to
•   ETA - the estimated time for arrival of this delivery on this date (from the eta you can of course order the deliveries in a route)
•   TTN - the time to next delivery in the route, i.e., at index 3 that would be the time distance between delivery index 3 and delivery index 4
•   DTN - the distance to next delivery in the route.

I need to find the the median of deliveries per route in the given plan.

the median distance travelled per route in the given plan.

the median time travelled per route in the given plan.

How may I do this?

I am wondering if this is just straightforward calculation of median where you just group by and aggregate I tried something like this to find the median distance

Tx = df.groupby(by=['plan_id','route_id'], as_index=False)['dtn'].sum()


 Tx.groupby(['plan_id','route_id'])['dtn'].median()

However I might not sure if this correct.

user_11905080 · Answer

Here is how I would do to display required figures:

# Subset dataframe to only have the desired plan_id
sub_Tx = Tx[Tx['plan_id'] == '869BB6FB-.....']

# median of deliveries per route in the given plan
sub_df = sub_Tx[['plan_id', 'route_id']]
sub_df['count_deliveries'] = 1
sub_df = sub_df.groupby(by=['plan_id', 'route_id'], axis=0, as_index=False).sum()
sub_df.groupby(by=['plan_id', 'route_id'], axis=0, as_index=False).median()

# median distance travelled per route in the given plan
sub_df = sub_Tx[['plan_id', 'route_id', 'dtn']]
sub_df = sub_df.groupby(by=['plan_id', 'route_id'], axis=0, as_index=False).sum()
sub_df.groupby(by=['plan_id', 'route_id'], axis=0, as_index=False).median()

# median time travelled per route in the given plan
sub_df = sub_Tx[['plan_id', 'route_id', 'ttn']]
sub_df = sub_df.groupby(by=['plan_id', 'route_id'], axis=0, as_index=False).sum()
sub_df.groupby(by=['plan_id', 'route_id'], axis=0, as_index=False).median()

Good luck

UPDATE :

So you can calculate median of route figures (nb deliveries, distance and time) per plan_id as follow:

# median of deliveries per route in the given plan
sub_df = sub_Tx[['plan_id', 'route_id']]
sub_df['count_deliveries'] = 1
sub_df = sub_df.groupby(by=['plan_id', 'route_id'], axis=0, as_index=False).sum()
sub_df = sub_df[['plan_id', 'count_deliveries']].rename(columns={'count_deliveries': 'median_deliveries'})
sub_df.groupby(by=['plan_id'], axis=0, as_index=False).median()

# median distance travelled per route in the given plan
sub_df = sub_Tx[['plan_id', 'route_id', 'dtn']]
sub_df = sub_df.groupby(by=['plan_id', 'route_id'], axis=0, as_index=False).sum()
sub_df = sub_df[['plan_id', 'dtn']].rename(columns={'dtn': 'median_dtn'})
sub_df.groupby(by=['plan_id'], axis=0, as_index=False).median()

# median time travelled per route in the given plan
sub_df = sub_Tx[['plan_id', 'route_id', 'ttn']]
sub_df = sub_df.groupby(by=['plan_id', 'route_id'], axis=0, as_index=False).sum()
sub_df = sub_df[['plan_id', 'ttn']].rename(columns={'ttn': 'median_ttn'})
sub_df.groupby(by=['plan_id'], axis=0, as_index=False).median()

pandas find median after group by

1 Answers