how to check if list of values are in range of values present in two columns?-CodePudding

I am new to python and still learning. I have some values which I want to check if any of them are within certain ranges. ranges are placed in two columns in a df table, so basically I want to go over the table row by row and check if any of the values are in range and count them

columnA  columnB   Counts
1           
6           10
11          15
16          20

and the numbers are in a list

list = [1,2,3,6,7,12,19]

Final output:

columnA  columnB   Counts
1            5      3
6           10      1
11          15      1
16          20      1

what I thought about is

counts=[]
for i in list:
    if i >= df['columnA'] & <= df['columnB']:
        counts.append(i)

CodePudding user response：

try this :

import pandas as pd
df = pd.DataFrame({'columnA': [1,6,11,16], 'columnB': [5,10,15,20]})
lst = [1,2,3,6,7,12,19]
df['Counts']=0
for index,row in df.iterrows():
    column_a=df.loc[index,'columnA']
    column_b=df.loc[index,'columnB']
    counts=[]
    for value in lst:
        if (value >= column_a) & (value  <= column_b):
            df.loc[index,'Counts'] =1

Output :

CodePudding user response：

You can construct a Series from original list and use Series.between to check the value that in between range.

lst = [1,2,3,6,7,12,19]
s = pd.Series(lst)
df['Count'] = df.apply(lambda row: s.between(row['columnA'], row['columnB']).sum(), axis=1)

print(df)

   columnA  columnB  Counts  Count
0        1        5       3      3
1        6       10       1      2
2       11       15       1      1
3       16       20       1      1

Series.between has an argument inclusive to determine whether to set each bound as closed or open. Optional values are “both”, “neither”, “left”, “right”.