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.
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.