1

I am new to python. I want to calculate the row-wise average based on unique IDs.

My DataFrame is:

       ID            Time[h]  concentration[g/L]  
15127  V527          23.425      59.9  
20361  V527          27.570      73.4  
21880  V527          29.281      75.4
33133  V560          27.677      75.9  
35077  V560          30.183      75.7  
37117  V560          31.847      74.6

I want to calculate the row wise average based on each ID. so that my output looks something like this

       ID            Time[h]  concentration[g/L]  avg [g/L]
15127  V527          23.425      59.9               NaN
20361  V527          27.570      73.4               66.5
21880  V527          29.281      75.4               74.4
33133  V560          27.677      75.9               NaN 
35077  V560          30.183      75.7               66.5 
37117  V560          31.847      74.6               75.8

I tried:

df.groupby(['ID'])['concentration[g/L]'].mean()

But this returned mean for each ID, as a whole.

So I tried this:

df.groupby(['ID'])['concentration[g/L]'].transform('mean')

This returns again the mean of each group, but fills to the same length of my df.

Can you please help me, if something is not clear I can rephrase my question.

Thanks in Advance!

s4pi3n
  • 13
  • 2
  • I tried looking up more suggestions, but it was too advanced for me to understand. If there is a similar question and a solution (possibly with explanation), please share the link. Thanks. – s4pi3n Sep 28 '20 at 15:07
  • BTW, what is the full formula or mathematical algorithm to compute `avg` out of `ID`, `time`, and `concentration`? – Arty Sep 28 '20 at 15:17
  • This link might help you. https://stackoverflow.com/questions/13996302/python-rolling-functions-for-groupby-object – jalazbe Sep 28 '20 at 15:53

2 Answers2

1

Try using pd.rolling.mean with a window of 2:

>>> df['avg [g/L]'] = df.groupby('ID')['concentration[g/L]'].rolling(2).mean().values
>>> df
         ID  Time[h]  concentration[g/L]  avg [g/L]
15127  V527   23.425                59.9        NaN
20361  V527   27.570                73.4      66.65
21880  V527   29.281                75.4      74.40
33133  V560   27.677                75.9        NaN
35077  V560   30.183                75.7      75.80
37117  V560   31.847                74.6      75.15
Sayandip Dutta
  • 15,602
  • 4
  • 23
  • 52
0

You can use shift:

df['avg'] = df.groupby('ID')['concentration[g/L]'].apply(lambda x: (x + x.shift())/2)

print(df)

         ID  Time[h]  concentration[g/L]    avg
15127  V527   23.425                59.9    NaN
20361  V527   27.570                73.4  66.65
21880  V527   29.281                75.4  74.40
33133  V560   27.677                75.9    NaN
35077  V560   30.183                75.7  75.80
37117  V560   31.847                74.6  75.15
YOLO
  • 20,181
  • 5
  • 20
  • 40
  • worked as well! I can only add one accepted answer, so I chose the one above. Thanks for the answer. – s4pi3n Sep 28 '20 at 15:28