CODEWITHSUNDEEP
pythonpandasdataframe
Shawn
Shawn

Reputation: 13

Calculate mean value and do the minus

DataFrame

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?

Upvotes: 0

Views: 63

Answers (2)

jezrael
jezrael

Reputation: 863166

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')
        )

Upvotes: 1

Ali Waqas
Ali Waqas

Reputation: 335

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.

Upvotes: 0

Related Questions