Python: Change value in dataframe column w.r.t variable value and other column value as well

Question

have got input dataframe like below and a variable 'extra'

input_df

Item    Space   refill      Min     Max
Apple   0.25    12.53       0.125   0.25
Lemon   0.25    11.2        0.25    0.375       
Potato  0.375   10.3        0.375   0.75
Melon   0.375   9.2         0.25    0.75

'extra' variable can be negative or positive.

Expected output_df if extra = 0.25 (this extra 0.25 space need to be splitted in the multiples of 0.125 and added to items with higher refill order. In this case, 0.125 + 0.125 = 0.25 so two items space will be updated. Also should consider Max value here, on adding space it should not exceed Max value, if happens skip that item and move to next.)

Item    Space   refill      Min     Max
Apple   0.25    12.53       0.125   0.25        #item skipped since it exceeds Max limit if extra space added
Lemon   0.375   11.2        0.25    0.375       #0.125 from extra space added here
Potato  0.5     10.3        0.375   0.75        #remaining 0.125 from extra space added here
Melon   0.375   9.2         0.25    0.75        #no more space to be added

Expected output_df if extra = -0.25 (this extra -0.25 space[which is two times of -0.125] need to be reduced from items with higher refill. Also should consider Min Value here, on reducing space it should not go below Min value, if happens skip that item and move to next.)

Item    Space   refill      Min     Max
Apple   0.125   12.53       0.125   0.25        #-0.125 from extra space reduced here
Lemon   0.25    11.2        0.25    0.375       #item skipped since it doesn't satisfy Min limit if reduced
Potato  0.375   10.3        0.375   0.75        #item skipped since it doesn't satisfy Min limit if reduced
Melon   0.25    9.2         0.25    0.75        #rem -0.125 from extra space reduced here

Any help will be appreciated. Thank You!

ouroboros1 · Accepted Answer · 2022-10-22 15:52:14Z

1

Assumptions:

Both extra and the values in columns Min and Max should always contain multiples of 0.125. E.g. df.Min % 0.125 should always lead to a series of zeros.
The df index contains unique values only. If this is not yet the case, use df.reset_index(drop=True, inplace=True) to reset it. Or use drop=False, if you want to keep the original index values. You can then reset them afterwards, using df.set_index('index', drop=True, inplace=True).

Given the assumptions, the following should achieve this:

import pandas as pd
import numpy as np

data = {'Item': {0: 'Apple', 1: 'Lemon', 2: 'Potato', 3: 'Melon'}, 
        'Space': {0: 0.25, 1: 0.25, 2: 0.375, 3: 0.375}, 
        'refill': {0: 12.53, 1: 11.2, 2: 10.3, 3: 9.2}, 
        'Min': {0: 0.125, 1: 0.25, 2: 0.375, 3: 0.25}, 
        'Max': {0: 0.25, 1: 0.375, 2: 0.75, 3: 0.75}}

def f(df, extra):
    length = int(abs(extra/0.125))
    repeats = np.repeat([0.125 * -1 if np.signbit(extra) else 0.125], len(df))

    tmp = np.where((pd.Series(repeats).lt(0) & df.Space.gt(df.Min)) | 
             (pd.Series(repeats).gt(0) & df.Space.lt(df.Max)), 
             df.Space.add(repeats), df.Space)
    
    new_series = np.where(df.Space.ne(tmp).cumsum().gt(length), df.Space, tmp)
    return new_series

Function applied:

df = pd.DataFrame(data)
df.Space = f(df, 0.25)

print(df)

     Item  Space  refill    Min    Max
0   Apple  0.250   12.53  0.125  0.250
1   Lemon  0.375   11.20  0.250  0.375
2  Potato  0.500   10.30  0.375  0.750
3   Melon  0.375    9.20  0.250  0.750

df = pd.DataFrame(data)
df.Space = f(df, -0.25)

print(df)

     Item  Space  refill    Min    Max
0   Apple  0.125   12.53  0.125  0.250
1   Lemon  0.250   11.20  0.250  0.375
2  Potato  0.375   10.30  0.375  0.750
3   Melon  0.250    9.20  0.250  0.750

Suppose we use extra= 0.375, then the last item will also change:

df = pd.DataFrame(data)
df.Space = f(df, 0.375)

print(df)

     Item  Space  refill    Min    Max
0   Apple  0.250   12.53  0.125  0.250
1   Lemon  0.375   11.20  0.250  0.375
2  Potato  0.500   10.30  0.375  0.750
3   Melon  0.500    9.20  0.250  0.750 # also changed

Explanation:

Inside function f, we first create an np.array named repeats with a length equal to your df, filled either with -0.125 or 0.125, depending on the sign of extra (using np.signbit).
Next, we use np.where and check either (values in column Space < 0 and > values in column Min) or (values in column Space > 0 and < values in column Max). For each True we want df.Space.add(repeats), for each False we keep the values from df.Space. We store the result in a variable tmp. See Series.lt and Series.gt.
Finally, we apply np.where again to limit the amount of changes to be made. In order to figure this out, we first apply Series.ne to tmp and get True for each mismatch (i.e. a change). Applying Series.cumsum will get us a cumulative sum of all the True values. Once, a value inside this series becomes greater than int(abs(extra/0.125)) we need to stop making changes. So, from that point onwards, we fall back on the original values in column Space.

edited Oct 22, 2022 at 15:52

answered Oct 22, 2022 at 10:54

ouroboros1

15.2k7 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user12345 Over a year ago

Your assumption is right! Will work it out and let you know shortly. Thanks for the help.

user12345 Over a year ago

I'm getting 'ValueError: operands could not be broadcast together with shapes (105,) (99,) (99,)' on calculating 'tmp'. The original dataframe shape is 99 but shape of result on calculating 'tmp' using np.where comes 105. Kindly guide me through this.

ouroboros1 Over a year ago

I think I know why this is happening: your actual df will probably have duplicate index values. Before using df.Space = f(df, -0.125), you want to reset the index: df.reset_index(drop=True, inplace=True). Or use drop=False if you want to keep the original index values. You can then use df.set_index('index', drop=True, inplace=True) after the function call to get it back as the index.

user12345 Over a year ago

Perfect! You saved my day. Thank You! That's the issue, after resetting index it worked fine.

ouroboros1 Over a year ago

Excellent! You're welcome, and good luck with your project! I'll update the answer with the comment that the index needs to contain unique values.

|

Collectives™ on Stack Overflow

Python: Change value in dataframe column w.r.t variable value and other column value as well

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related