Pandas : Apply weights to another column, for certain ids only

user_12292032 0
Viewed 70

Let's take this sample dataframe and this list of ids :

df=pd.DataFrame({'Id':['A','A','A','B','C','C','D','D'], 'Weight':[50,20,30,1,2,8,3,2], 'Value':[100,100,100,10,20,20,30,30]})

  Id  Weight  Value
0  A      50    100
1  A      20    100
2  A      30    100
3  B       1     10
4  C       2     20
5  C       8     20
6  D       3     30
7  D       2     30

L = ['A','C']

Value column has same values for each id in Id column. For the specific ids of L, I would like to apply the weights of Weight column to Value column. I am currently doing the following way but it is extremely slow with my real big dataframe :

for i in L :
    df.loc[df["Id"]==i,"Value"] = (df.loc[df["Id"]==i,"Value"] * df.loc[df["Id"]==i,"Weight"] / 
    df[df["Id"]==i]["Weight"].sum())

How please could I do that efficiently ?

Expected output :

  Id  Weight  Value
0  A      50     50
1  A      20     20
2  A      30     30
3  B       1     10
4  C       2      4
5  C       8     16
6  D       3     30
7  D       2     30
pandas python performance
1 Answers

Idea is working only for filtered rows by Series.isin with GroupBy.transform and sum for sum per groups with same size like original DataFrame:

L = ['A','C']

m = df['Id'].isin(L)
df1 = df[m].copy()
s = df1.groupby('Id')['Weight'].transform('sum')
df.loc[m, 'Value'] = df1['Value'].mul(df1['Weight']).div(s)

print (df)
  Id  Weight  Value
0  A      50   50.0
1  A      20   20.0
2  A      30   30.0
3  B       1   10.0
4  C       2    4.0
5  C       8   16.0
6  D       3   30.0
7  D       2   30.0
user_29010020
Related