Modify column B values based on the range of column A-CodePudding

Following is my test dataframe:

df.head(25)

    freq        pow         group   avg
0   0.000000    10.716615   0       NaN
1   0.022888    -9.757687   0       NaN
2   0.045776    -9.203844   0       NaN
3   0.068665    -8.746512   0       NaN
4   0.091553    -8.725540   0       NaN
...
95  2.174377    -12.697743  0       NaN
96  2.197266    -7.398328   0       NaN
97  2.220154    -23.002036  0       NaN
98  2.243042    -22.591483  0       NaN
99  2.265930    -13.686127  0       NaN

I am trying to assign values from 1-24 in group column based on range of values in freq column. For example, using df.loc[(df['freq'] >= 0.1) & (df['freq'] <= 0.2)] yields the following:

    freq        pow         group avg
5   0.114441    -8.620905   0   NaN
6   0.137329    -10.633629  0   NaN
7   0.160217    -9.098974   0   NaN
8   0.183105    -9.381907   0   NaN

So, if I select any particular range as shown above, I would want to change the values in group column from 0 to 1 as shown below.

    freq        pow         group avg
5   0.114441    -8.620905   1   NaN
6   0.137329    -10.633629  1   NaN
7   0.160217    -9.098974   1   NaN
8   0.183105    -9.381907   1   NaN

Similarly, I want to change more values in group column from 0 to anything from 1-24 depending on the range I provide. For example, df.loc[(df['freq'] >= 1) & (df['freq'] <= 1.2)] results in

    freq        pow         group avg
44  1.007080    -13.826930  0   NaN
45  1.029968    -12.892703  0   NaN
46  1.052856    -14.353349  0   NaN
47  1.075745    -15.389498  0   NaN
48  1.098633    -12.519916  0   NaN
49  1.121521    -15.118952  0   NaN
50  1.144409    -15.986558  0   NaN
51  1.167297    -13.262798  0   NaN
52  1.190186    -12.629713  0   NaN

But I want to change the value of group column for this range from 0 to 2

    freq        pow         group avg
44  1.007080    -13.826930  2   NaN
45  1.029968    -12.892703  2   NaN
46  1.052856    -14.353349  2   NaN
47  1.075745    -15.389498  2   NaN
48  1.098633    -12.519916  2   NaN
49  1.121521    -15.118952  2   NaN
50  1.144409    -15.986558  2   NaN
51  1.167297    -13.262798  2   NaN
52  1.190186    -12.629713  2   NaN

and once I have allocated the entire table with custom group values, I would need to calculate mean from values in pow column for the respective ranges and edit the avg column accordingly.

I cannot seem to figure out on how modify the multiple values in group column to a single value for a particular range of freq column. Any help would be greatly appreciated for this problem. Thank you in advance.

CodePudding user response：

You can use -

df.loc[(df['freq'] >= 1) & (df['freq'] <= 1.2), 'group'] = 2

in the same way for mean-

df.loc[(df['freq'] >= 1) & (df['freq'] <= 1.2), 'avg'] = df[(df['freq'] >= 1) & (df['freq'] <= 1.2)]['pow'].mean

You can loop these commands changing the ranges of df['freq'] & the update integer (1-24 as mentioned in the question)