Creating a function which creates a new column based on values in two columns?-CodePudding

I have data frame like -

ID     Min_Value    Max_Value
1       0           0.10562
2    0.10563        0.50641
3      0.50642      1.0

I have another data frame that contains Value as a column. I want to create a new column in second data frame which returns ID when Value is between Min_Value and Max_Value for a given ID as above data frame. I can use if-else conditions but number of ID's are large and code becomes too bulky. Is there a efficient way to do this?

CodePudding user response：

If I understand correctly, just join/merge it into one DataFrame, using "between" function you can choose right indexes which will be located in the second DataFrame.

import pandas as pd

data = {"Min_Value": [0, 0.10563, 0.50642], 
        "Max_Value": [0.10562, 0.50641, 1.0]}

df = pd.DataFrame(data, 
                  index=[1, 2, 3])

df2 = pd.DataFrame({"Value": [0, 0.1, 0.58]}, index=[1,2,3])

df = df.join(df2)

mask_between_values = df['Value'].between(df['Min_Value'], df['Max_Value'], inclusive="neither")

# This is the result
df2[mask_between_values]

1   0.00
3   0.58

CodePudding user response：

You can create new dataframe by checking the value in between them and you can get the ID and value for the new dataframe using df.loc as shown below.

new_df = df.loc(df['Max_Value'] > df['Value'] & 
                         df['Min_Value'] < df['Value'],['ID', 'Value'])

CodePudding user response：

Suppose you have two dataframes df and new_df. You want to assign a new column as 'new_column' into new_df dataframe. The value of 'Value' column must be between 'Min_Value' and 'Max_Value' from df dataframe. Then this code may help you.

for i in range(0,len(df)):
    if df.loc[i,'Max_Value'] > new_df.loc[i,'Value'] and df.loc[i,'Min_value'] < new_df.loc[i,'Value']:
        new_df.loc[i,'new_column'] = df.loc[i, 'ID']