Pandas groupby and append the original values. Count the mean of per row

Ahsan Published at Dev

Ahsan

I have a dataframe of IDs and Values. Where IDs are kind of repetition of trial and Values are the results. I want to do groupby by ID and for same IDs the Values will be added to adjacent columns. Finally I want to calculate the mean of each of the rows.

>>>df
   ID  Value
0   1    1.1
1   2    1.2
2   3    2.4
3   1    1.7
4   2    4.3
5   3    2.2
>>>groups = df.groupby(by='ID')

#Now I cannot figure it what to do for my desired output.

I want the output like

   ID  Value_1  Value_2  Mean
0   1    1.1    1.7    1.9
1   2    1.2    4.3    2.75
2   3    2.4    2.2    2.3

jezrael

Use DataFrame.assign for new column created by counter per groups by GroupBy.cumcount, reshape by DataFrame.pivot, change columns names by DataFrame.add_prefix, add new column filled by means and last data cleaning - DataFrame.reset_index with DataFrame.rename_axis:

df = (df.assign(g = df.groupby('ID').cumcount().add(1))
        .pivot('ID','g','Value')
        .add_prefix('Value_')
        .assign(Mean = lambda x: x.mean(axis=1))
        .reset_index()
        .rename_axis(None, axis=1))
print (df)
   ID  Value_1  Value_2  Mean
0   1      1.1      1.7  1.40
1   2      1.2      4.3  2.75
2   3      2.4      2.2  2.30

Collected from the Internet

Please contact [email protected] to delete if infringement.