我有以下内容:
df1['Combined'] = ['A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', C', 'C']
df1['Quantity'] = [0, 60, 75, 149, 205, 500, 250, 300, 500, 40, 45, 75, 80]
df2['Combined'] = ['A', 'A', 'A', 'A', 'B', 'B','B','B', 'C', 'C', 'C']
df2['Min Q'] = [0, 50, 100, 150, 100, 0, 300, 400, 5, 50, 100]
df2['Max Q'] = [49, 99, 149, 199, 199, 299, 399, 499, 60, 100, 149]
我想向df1添加一列,该列返回df2的范围。我尝试如下:
要计算df2 ['Range']:
df2['Range'] = df2['Min Q'].astype(float).astype(str) + ' - ' + df2['Max Q'].astype(float).astype(str)
查找df1 ['Range']:
def lookup_Range(Range):
match = (df2['Min Q'].astype(float) <= df1['Quantity'].astype(float)) & (df2['Max Q'].astype(float) >= df1['Quantity'].astype(float)) & (df1['Combined'] == df2['Combined'])
Range = df2['Range'][match]
return Range.values[0]
df1['Quantity'].apply(lookup_Range)
但是我收到以下错误:
Can only compare identically-labeled Series objects.
我不确定自己在做什么错。列重复一遍,但我认为我会在每个实例中获得唯一的匹配。感谢你的帮助。
使用merge
首先通过过滤boolean indexing
用between
:
df2['Range'] = df2['Min Q'].astype(str) + ' - ' + df2['Max Q'].astype(str)
df = df1.merge(df2, on='Combined')
df = df[df['Quantity'].between(df['Min Q'], df['Max Q'])]
print (df)
Combined Quantity Min Q Max Q Range
0 A 0 0 49 0 - 49
5 A 60 50 99 50 - 99
9 A 75 50 99 50 - 99
14 A 149 100 149 100 - 149
25 B 250 0 299 0 - 299
30 B 300 300 399 300 - 399
36 C 40 5 60 5 - 60
39 C 45 5 60 5 - 60
43 C 75 50 100 50 - 100
46 C 80 50 100 50 - 100
如果还希望仅过滤某些列:
df = df.loc[df['Quantity'].between(df['Min Q'], df['Max Q']), ['Combined','Quantity','Range']]
print (df)
Combined Quantity Range
0 A 0 0 - 49
5 A 60 50 - 99
9 A 75 50 - 99
14 A 149 100 - 149
25 B 250 0 - 299
30 B 300 300 - 399
36 C 40 5 - 60
39 C 45 5 - 60
43 C 75 50 - 100
46 C 80 50 - 100
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句