CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
Vanessa S.
Vanessa S.

Reputation: 133

Subtract 2 values from one another within 1 column after groupby

I am very sorry if this is a very basic question but unfortunately, I'm failing miserably at figuring out the solution.

I need to subtract the first value within a column (in this case column 8 in my df) from the last value & divide this by a number (e.g. 60) after having applied groupby to my pandas df to get one value per id. The final output would ideally look something like this:

id     
1     1523
2     1644

I have the actual equation which works on its own when applied to the entire column of the df:

(df.iloc[-1,8] - df.iloc[0,8])/60

However I fail to combine this part with the groupby function. Among others, I tried apply, which doesn't work. 

df.groupby(['id']).apply((df.iloc[-1,8] - df.iloc[0,8])/60)

I also tried creating a function with the equation part and then do apply(func)but so far none of my attempts have worked. Any help is much appreciated, thank you!

Upvotes: 3

Views: 1220

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210832

Demo:

In [204]: df
Out[204]:
   id  val
0   1   12
1   1   13
2   1   19
3   2   20
4   2   30
5   2   40

In [205]: df.groupby(['id'])['val'].agg(lambda x: (x.iloc[-1] - x.iloc[0])/60)
Out[205]:
id
1    0.116667
2    0.333333
Name: val, dtype: float64

Upvotes: 2

Related Questions