Pandas dataframe problem. Create column where a row cell gets the value of another row cell

Question

I have this pandas dataframe. It is sorted by the "h" column. What I want is to add two new columns where: The items of each zone, will have a max boundary and a min boundary. (They will be the same for every item in the zone). The max boundary will be the minimum "h" value of the previous zone, and the min boundary will be the maximum "h" value of the next zone

name    h   w   set row zone
ZZON5   40  36  A   0   0
DWOPN   38  44  A   1   0
5SWYZ   37  22  B   2   0
TFQEP   32  55  B   3   0
OQ33H   26  41  A   4   1
FTJVQ   24  25  B   5   1
F1RK2   20  15  B   6   1
266LT   18  19  A   7   1
HSJ3X   16  24  A   8   2
L754O   12  86  B   9   2
LWHDX   11  68  A   10  2
ZKB2F   9   47  A   11  2
5KJ5L   7   72  B   12  3
CZ7ET   6   23  B   13  3
SDZ1B   2   10  A   14  3
5KWRU   1   59  B   15  3

what i hope for:

name    h   w   set row zone maxB minB
ZZON5   40  36  A   0   0         26
DWOPN   38  44  A   1   0         26
5SWYZ   37  22  B   2   0         26
TFQEP   32  55  B   3   0         26
OQ33H   26  41  A   4   1    32   16
FTJVQ   24  25  B   5   1    32   16
F1RK2   20  15  B   6   1    32   16
266LT   18  19  A   7   1    32   16
HSJ3X   16  24  A   8   2    18   7
L754O   12  86  B   9   2    18   7
LWHDX   11  68  A   10  2    18   7
ZKB2F   9   47  A   11  2    18   7
5KJ5L   7   72  B   12  3    9  
CZ7ET   6   23  B   13  3    9
SDZ1B   2   10  A   14  3    9
5KWRU   1   59  B   15  3    9  

Any ideas?

Answer 1

First group-by zone and find the minimum and maximum of them

min_max_zone = df.groupby('zone').agg(min=('h', 'min'), max=('h', 'max'))

Now you can use apply:

df['maxB'] = df['zone'].apply(lambda x: min_max_zone.loc[x-1, 'min'] 
                               if x-1 in min_max_zone.index else np.nan)

df['minB'] = df['zone'].apply(lambda x: min_max_zone.loc[x+1, 'max']             
                               if x+1 in min_max_zone.index else np.nan)