blocks|key|4202323|text|试试下面的代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4202324|df+=+pd.DataFrame()
df['Key']+=+[(row['A'],row['B'])+for+idx,row+in+df1.iterrows()]
values_col_test+=+[(c1,c2)+for+c1,c2+in+df2.itertuples(index=False)]
values_col+=+[]

for+i+in+range(0,len(values_col_test),2):
+++++try:
++++++++++values_col.append(list(values_col_test[i:i%2B2]))
+++++except:
++++++++++values_col.append(list(values_col_test[i]))

df['Value']+=+values_col
print(df)|code-block|syntax|javascript|4202325|输出：|4202326|++++++++++++Key+++++++++++++++++++++++++++Value
0++(10.5,+20.5)+++[(2.34,+11.9),+(3.68,+19.13)]
1++(30.5,+40.5)++[(33.5,+35.6),+(35.12,+38.76)]
2++(50.5,+60.5)++++++++++++++++++[(50.6,+59.1)]|4202327|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Try the below code:

<pre><code>df = pd.DataFrame()
df['Key'] = [(row['A'],row['B']) for idx,row in df1.iterrows()]
values_col_test = [(c1,c2) for c1,c2 in df2.itertuples(index=False)]
values_col = []

for i in range(0,len(values_col_test),2):
 try:
 values_col.append(list(values_col_test[i:i+2]))
 except:
 values_col.append(list(values_col_test[i]))

df['Value'] = values_col
print(df)
</code></pre>

Output:

<pre><code> Key Value
0 (10.5, 20.5) [(2.34, 11.9), (3.68, 19.13)]
1 (30.5, 40.5) [(33.5, 35.6), (35.12, 38.76)]
2 (50.5, 60.5) [(50.6, 59.1)]
</code></pre>

blocks|key|4202352|text|它的速度不是很快(在我的电脑上大约30秒)，但如果你有多个内核，使用multiprocessing包可以很容易地加速。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4202353|生成数据：|4202354|def+get_fake(n):
++++df+=+pd.DataFrame(np.random.rand(n+*+2).reshape(-1,+2))
++++df.loc[:,+1]+%2B=+1
++++return+df

df1+=+get_fake(200)
df2+=+get_fake(90000)|code-block|syntax|javascript|4202355|然后对于处理部分：|4202356|from+collections+import+defaultdict
result+=+defaultdict(list)
for+index,+start,+stop+in+df1.itertuples():
++++subdf+=+df2[(start+<+df2.iloc[:,+0])+&+(df2.iloc[:,+1]+<+stop)]
++++result[(start,+stop)]+%2B=+subdf.values.tolist()|4202357|Result是一个字典，但如果需要，可以很容易地将其转换为Series。|4202358|entityMap^0|Y|F|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|10|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|11|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|T|$]]

It is not blazing fast (~ 30 secs) on my computer) but could easily be accelerated with the <code>multiprocessing</code> package if you have multiple cores.

Generating data : 

<pre><code>def get_fake(n):
 df = pd.DataFrame(np.random.rand(n * 2).reshape(-1, 2))
 df.loc[:, 1] += 1
 return df

df1 = get_fake(200)
df2 = get_fake(90000)
</code></pre>

Then for the processing part :

<pre><code>from collections import defaultdict
result = defaultdict(list)
for index, start, stop in df1.itertuples():
 subdf = df2[(start &lt; df2.iloc[:, 0]) &amp; (df2.iloc[:, 1] &lt; stop)]
 result[(start, stop)] += subdf.values.tolist()
</code></pre>

Result is a dict but could easily be converted to a Series if necessary.

blocks|key|4202466|text|如果你使用interval+index也就很容易了|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4202467|idx+=+pd.IntervalIndex.from_arrays(df['A'],df['B'])
keys+=+df.values.tolist()
values+=+df2.groupby(df.loc[idx.get_indexer(df2['C'])].index).apply(lambda+x+:+x.values)

new_df+=+pd.DataFrame({'key':+keys+,+'value':+values})

++++++++++key++++++++++++++++++++++++++++value
0++[10.5,+20.5]++[[12.34,+15.9],+[13.68,+19.13]]
1++[30.5,+40.5]+++[[33.5,+35.6],+[35.12,+38.76]]
2++[50.5,+60.5]+++++++++++++++++++[[50.6,+59.1]]|code-block|syntax|javascript|4202468|基于区间索引访问数据将为您提供关键字，以便您可以分组和聚合，即|4202469|df.loc[idx.get_indexer(df2['C'])]
+++++A+++++B
0++10.5++20.5
0++10.5++20.5
1++30.5++40.5
1++30.5++40.5
2++50.5++60.5|4202470|entityMap^0|5|E|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

It will be easy if you use <code>interval index</code> i.e 

<pre><code>idx = pd.IntervalIndex.from_arrays(df['A'],df['B'])
keys = df.values.tolist()
values = df2.groupby(df.loc[idx.get_indexer(df2['C'])].index).apply(lambda x : x.values)

new_df = pd.DataFrame({'key': keys , 'value': values})

 key value
0 [10.5, 20.5] [[12.34, 15.9], [13.68, 19.13]]
1 [30.5, 40.5] [[33.5, 35.6], [35.12, 38.76]]
2 [50.5, 60.5] [[50.6, 59.1]]
</code></pre>

Accessing data based on interval index will give you the keys so you can groupby and aggregate i.e 

<pre><code>df.loc[idx.get_indexer(df2['C'])]
 A B
0 10.5 20.5
0 10.5 20.5
1 30.5 40.5
1 30.5 40.5
2 50.5 60.5
</code></pre>

blocks|key|4202515|text|一种解决方案是使用apply，例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4202516|#+first+create+your+output+DF+with+the+keys+from+your+df+with+A+and+B
df+=+pd.DataFrame({'Key':[(a,b)+for+a,b+in+df1.itertuples(index=False)]})
#+define+a+function+to+find+the+range+in+df2+within+the+range+from+the+Keys+column
def+find_range(+key,+df_2):
++++mask+=+(key[0]+<=+df_2['C'])+&+(key[1]+>=+df_2['D'])
++++return+[(c,d)+for+c,d+in+df_2[mask].itertuples(index=False)]
#now+create+the+column+Values+with+apply
df['Values']+=+df['Key'].apply(find_range,+args=(df2,))
#+output
++++++++++++Key+++++++++++++++++++++++++++Values
0++(10.5,+20.5)++[(12.34,+15.9),+(13.68,+19.13)]
1++(30.5,+40.5)+++++++++++++++++++[(33.5,+35.6)]|code-block|syntax|javascript|4202517|注意:我假设在您的数据中，列C始终低于D，如果不是，您必须更改函数中的掩码，以检查C和D是否都在key和key1中。另外，我没有得到您所有的输入，所以第1行的值与您显示的值不同，但只是一个输入差异。|4202518|entityMap^0|9|5|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

one solution is to use <code>apply</code> such as:

<pre><code># first create your output DF with the keys from your df with A and B
df = pd.DataFrame({'Key':[(a,b) for a,b in df1.itertuples(index=False)]})
# define a function to find the range in df2 within the range from the Keys column
def find_range( key, df_2):
 mask = (key[0] &lt;= df_2['C']) &amp; (key[1] &gt;= df_2['D'])
 return [(c,d) for c,d in df_2[mask].itertuples(index=False)]
#now create the column Values with apply
df['Values'] = df['Key'].apply(find_range, args=(df2,))
# output
 Key Values
0 (10.5, 20.5) [(12.34, 15.9), (13.68, 19.13)]
1 (30.5, 40.5) [(33.5, 35.6)]
</code></pre>

Note: I assume in your data the column C is always lower than D, if not, you have to change the mask in the function to check if both C and D are within key[0] and key[1]. Also, I did not have all your input, so Values for the row number 1 is different that what you show, but just an input difference.

Suppose I have dataframe df1 which includes two columns - A &amp; B. Value of A represents the lower range and value of B represents the upper range.

<pre><code> A B
10.5 20.5
30.5 40.5
50.5 60.5
</code></pre>

I've another dataframe which includes two columns - C &amp; D containing a different range of numbers.

<pre><code> C D
12.34 15.90
13.68 19.13
33.5 35.60
35.12 38.76
50.6 59.1
</code></pre>

Now I want to list all the pairs from df2 that fall under the groups (between the lower and upper range) in the df1. 

Final output should be like this - 

<pre><code> Key Values
(10.5, 20.5) [(12.34, 15.90), (13.68, 19.13)]
(30.5, 40.5) [(33.5, 35.60), (35.12, 38.76)]
(50.5, 60.5) [(50.6, 59.1)]
</code></pre>

The solution should be efficient as I have 5000 groups of range and 85000 numbers from different range.

How to list all the pairs of numbers which fall under a group of range?

假设我有dataframe df1，它包括两列-A和B。A的值代表下限范围，B的值代表上限范围。  A     B10.5  20.530.5  40.550.5  60.5我有另一个数据帧，它包括两列-C&D，其中包含不同的数字范围。  C     D12.34  15.9013.68  19.1333.5   35...

问如何列出属于一组范围的所有数字对？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何列出属于一组范围的所有数字对？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何列出属于一组范围的所有数字对？
EN