Finding the middle row of different sized groupby within a data frame

Admin 1
Viewed 444

I have several daily yields and I have grouped them into months using the following code:

df2['trd_exctn_dt'] = pd.to_datetime(df2['trd_exctn_dt'])

group_ym = df2.groupby(df2['trd_exctn_dt'].dt.strftime('%Y-%m'))

I now want to find the middle row within each of these groups, however how would I do this as each group has a different number of rows in, therefore I cannot use a specific number to find the middle row

For example:

cusip_id yld_pt trd_exctn_dt trd_exctn_tm
00077TB0 6.58902 2015-01-05 578906.09
00077TB0 6.43672 2015-01-06 523452.12
00077TB0 6.45628 2015-01-07 555532.10
00077TB0 6.23452 2015-02-10 567392.02
00077TB0 6.34552 2015-03-12 545930.98

My desired answer would be:

cusip_id yld_pt trd_exctn_dt trd_exctn_tm
00077TB0 6.43672 2015-01-06 523452.12
00077TB0 6.23452 2015-02-10 567392.02
00077TB0 6.34552 2015-03-12 545930.98
pandas median finance group-by python
3 Answers

You can try groupby and a lambda function:

(df2.groupby(df2['trd_exctn_dt'].dt.strftime('%Y-%m'))
    .apply(lambda x: x.iloc[(len(x)+1)//2])
)
user_42384080
  1. You can use nth() function of group by for this. First get the order for the middle elements of each group.
  2. Then, you can put your mid_id_list for each group into nth method as a parameter.

Sample Code:

mid_id_list = data.groupby(by=['Age'])['id'].count().apply(lambda x: x//2).tolist() 
data.groupby(by=['Age'])['id'].nth(mid_id_list)
user_137999300

Previous replies didn't solve my issues:

  • Apply is extremely slow for big dataframes
  • nth() cannot be applied vectorized

It is possible to apply a vectorized method by:

  • Create a sequential index (in my case the original index is not sequential)
  • Groupby indexing columns and extract first position and #rows in block
  • Calculate middle points with start & #rows
  • Use to index to index

For my application this is 100x faster than the apply method:

df['index'] = pd.RangeIndex(stop=df.shape[0])
# Extract group positions and number of rows
idxpos = df.groupby(idx_columns, sort=False, as_index=False, group_keys=False, dropna=False)['index'].agg(['first','count']).values
# Create middle rows position vector
idxmid = idxpos[:,0] + idxpos[:,1]//2
# Extract them and drop the index that we created
df_mids = df.iloc[idxmid].drop('index',axis=1)

Note that in case of an even number of rows it will return the upper one

If you already have a rangeindex as index, you can use df.reset_index().groupby... instead of declaring a new column. And of course, don't do the .drop at the end

user_184806350
Related