alocate column value to two different columns in a df python

Sara Silva 0
Viewed 29

I have a df with 4 columns: ID, Value, UpperBound and LowerBound, like this:

ID | Value | UpperBound | LowerBound

1     23        NaN         NaN
2     55        NaN         NaN
3     87        NaN         NaN
4     99        NaN         NaN
5     NaN       50          5
6     NaN       5           1
7     NaN       95          50
8     NaN       99          95

And I want to convert the values in Value column to UpperBound and LowerBound according to the respective bounds (23 for example fits between 50-5). So that the output looks something like this:

ID | Value | UpperBound | LowerBound

1     NaN        50         5
2     NaN        95         50
3     NaN        95         50
4     NaN        99         95
5     NaN       50          5
6     NaN       5           1
7     NaN       95          50
8     NaN       99          95

The Value column will end up with only NaN values, so I can eliminate it later. The UpperBound and LowerBound are the following:

1-5
5-50
50-95
95-99

Can someone help me create the necessary code to do this?

Thank you so much in advance!!

dataframe pandas python
1 Answers

Assume the original dataframe looks like this:

import pandas as pd
import numpy as np

df = pd.DataFrame({'Value': [23,55,87,99,999],
                   'UpperBound': [np.nan, np.nan, np.nan, np.nan, np.nan],
                   'LowerBound': [np.nan, np.nan, np.nan, np.nan, np.nan] })
print(df)

   Value  UpperBound  LowerBound
0     23         NaN         NaN
1     55         NaN         NaN
2     87         NaN         NaN
3     99         NaN         NaN
4    999         NaN         NaN

Here's one way to do it, assuming lower bound is included and upper bound is excluded:

def check_bound(x):
    for (a,b) in [(1,5), (5,50), (50,95), (95,100)]:
        if x in np.arange(a,b):
            return [a,b]      #this will exit the function
    return [np.nan, np.nan]   #iteration ended means bound is not found

df[['LowerBound', 'UpperBound']] = df['Value'].apply(lambda x: check_bound(x)).tolist()
print(df)

   Value  UpperBound  LowerBound
0     23        50.0         5.0
1     55        95.0        50.0
2     87        95.0        50.0
3     99       100.0        95.0
4    999         NaN         NaN
perpetualstudent0
Related