I have a pandas dataframe dd:

Experiment  Position    Lap ID     Partition    Value
Expt1       2000        104 127327203   2       52.01
Expt1       2000        105 127327203   2       51.78
Expt1       2000        106 127327203   2       51.57
Expt1       2000        107 127327203   2       51.63
Expt1       2000        108 127327203   2       51.61
Expt1       2000        109 127327203   2       51.78
Expt1       2000        110 127327203   2       51.78
Expt1       2000        111 127327203   2       51.53
Expt1       2000        112 127327203   2       51.69
Expt1       2000        113 127327203   2       51.53
Expt1       2000        114 127327203   2       51.40
Expt1       2000        115 127327203   2       51.45
Expt1       2000        116 127327203   2       51.47
Expt1       2000        117 127327203   2       51.61
Expt1       2000        118 127327203   2       50.89
Expt1       2500        104 127327203   2       52.16
Expt1       2500        105 127327203   2       53.14
Expt1       2500        106 127327203   2       52.02

My data is several thousand lines and has many experiments so the above is just a snapshot.

I want to groupby Experiment then Position, and then Lap

grouped = dd.groupby(['Experiment','Position','Lap']) 
grouped.first()

This gives me:

enter image description here

I now want to just use the 10th largest values in the 'Lap' column to give me a mean and std of the 'Value' column.

If possible, I would then like to output to a new dataframe, the experiment, position and the result of the above calculations so I can then plot.

thanks for any help

1 Answers

1
jezrael On Best Solutions

First filter by counts by GroupBy.transform and GroupBy.size with Series.ge for >=10 and boolean indexing:

df = df[df.groupby(['Experiment','Position'])['Value'].transform('size').ge(10)]

Use DataFrame.sort_values by multiple columns with GroupBy.tail:

df1 = (df.sort_values(['Experiment','Position','Lap', 'Value'])
        .groupby(['Experiment','Position'])
        .tail(10))
print (df1)
   Experiment  Position  Lap         ID  Partition  Value
5       Expt1      2000  109  127327203          2  51.78
6       Expt1      2000  110  127327203          2  51.78
7       Expt1      2000  111  127327203          2  51.53
8       Expt1      2000  112  127327203          2  51.69
9       Expt1      2000  113  127327203          2  51.53
10      Expt1      2000  114  127327203          2  51.40
11      Expt1      2000  115  127327203          2  51.45
12      Expt1      2000  116  127327203          2  51.47
13      Expt1      2000  117  127327203          2  51.61
14      Expt1      2000  118  127327203          2  50.89

df2 = df1.groupby(['Experiment','Position'])['Value'].agg([('avg','mean'),
                                                           ('q5', lambda x: x.quantile(.5))])
print (df2)
                        avg     q5
Experiment Position               
Expt1      2000      51.513  51.53