Modify Value Of Pandas Dataframe Groups

We have the following dataframe (df) that has 3 columns. The goal is to make sure that the summation of 'Load' for each group based on IDs is equal to 1. pd.DataFrame({'ID':['AEC',

Solution 1:

You can use drop_duplicates to keep the first record in each group and then change the Load value so that its group Load sum is 1.

df.loc[df.ID.drop_duplicates().index, 'Load'] -= df.groupby('ID').Load.sum().subtract(1).values

df
Out[92]: 
   Num   ID      Load
0    1  AEC  0.461591
1    2  AEC  0.538409
2    3  CIZ  0.106869
3    4  CIZ  0.746566
4    5  CIZ  0.146566

df.groupby('ID').Load.sum()
Out[93]: 
ID
AEC    1.0
CIZ    1.0
Name: Load, dtype: float64

Solution 2:

I am using resample random pick one value from each group to make the change

df['New']=(1-df.groupby('ID').Load.transform('sum'))

df['Load']=df.Load.add(df.groupby('ID').New.apply(lambda x : x.sample(1)).reset_index('ID',drop=True)).fillna(df.Load)

df.drop('New',1)
Out[163]: 
   Num   ID      Load
0    1  AEC  0.209327
1    2  AEC  0.790673
2    3  CIZ  0.146566
3    4  CIZ  0.746566
4    5  CIZ  0.106869

Check

df.drop('New',1).groupby('ID').Load.sum()
Out[164]: 
ID
AEC    1.0
CIZ    1.0
Name: Load, dtype: float64