Calculate mean value and do the minus-CodePudding

I got a data frame as the image,

I am wondering how to get the mean of each day Numb from only Pos1,3,5,7 and then minus ctrl.

Take 2022/03/03 for instance: (92 17 51 79)/4 - 38.

My thought is to make two dataframe one including pos1~7 and get the average of it then concave it with ctrl.

Any suggestion?

CodePudding user response：

First replace Numb to missing value in Series.mask if position is Ctrl, so possible aggregate by mean and last, then subtract ctrl values:

df = (df.assign(Numb = df['Numb'].mask(df['Pos'].eq('Ctrl')))
        .groupby('Date').agg(ctrl = ('Numb','last'),
                             avg = ('Numb','mean'))
        .eval("avg-ctrl")
        .reset_index(name='ctrl'))

If possible ctrl value is not last, solution is:

df = (df.assign(Numb = df['Numb'].mask(df['Pos'].eq('Ctrl')),
                ctrl = df['Numb'].where(df['Pos'].eq('Ctrl')))
        .groupby('Date').agg(ctrl = ('Numb','last'),
                              avg = ('Numb','mean'))
        .eval("avg-ctrl")
        .reset_index(name='ctrl')
        )

CodePudding user response：

you can use array functions, for example,

dayMeans=[]
for i in range(0, numOfDays, 4): # i increments by 4
     dayMean = np.mean(Numb[i:i 4]) - Num[i 4]
     dayMeans.append(dayMean)
print(dayMeans)

we start at 0 index, and increment by 4 steps which are entries for each day. Appending results to the required array.