Check, if items of one dataframe are inside a range, defined in another dataframe Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Check, if items of one dataframe are inside a range, defined in another dataframe without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I have a defined range:

df = pd.DataFrame([["1", "10"], ["11", "67"], ["90", "115"]], columns=['start', 'end'])

And a list of strings:

df2 = pd.DataFrame([["1"], ["3"], ["31"], ["70"], ["71"], ["90"], ["99"], ["100"], ["200"]], columns=['reference'])

And I try to get a result that looks as follows:

df3 = pd.DataFrame([["1", "1", "10"], ["3", "1", "10"], ["31", "11", "67"], ["70", "no range", "no range"], 
                ["71", "no range", "no range"], ["90", "90", "115"], ["99", "90", "115"], 
                ["100", "90", "115"], ["200", "no range", "no range"]], columns=['reference', "start", "end"])

I tried to do something similar earlier on, but with using numpy only. The solution then looked like this:

result_good=[]
result_bad=[]
for d in extension:
    categories = np.logical_and(d >= ranges[:,1], d <= ranges[:,2])
    if (ranges[:,0][categories]):
        result_good.append(ranges)
    else:
        result_bad.append(d)

This basically worked. I want to get this to work with Pandas though. But all I get to work is to compare two dataframes of the same length or to do it “brute force” with a loop. There must be a more elegant way to do that. Thank you for your help.

Answer

#create a dict to store reference number and its start and end range.
d = {'default':{'start':'no range','end':'no range'}}
#populate the range dict
df.apply(lambda x: [d.update({str(k):{'start':x.start, 'end':x.end} for k in range(int(x.start),int(x.end)+1)})],axis=1)
#get the range start and end for each reference point and merge with df2.
df2.apply(lambda x: pd.Series(d.get(x.reference,d.get('default'))),axis=1).join(df2)[['reference','start','end']]
Out[240]: 
  reference     start       end
0         1         1        10
1         3         1        10
2        31        11        67
3        70  no range  no range
4        71  no range  no range
5        90        90       115
6        99        90       115
7       100        90       115
8       200  no range  no range
We are here to answer your question about Check, if items of one dataframe are inside a range, defined in another dataframe - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji