<p>使用<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.map.html" rel="nofollow noreferrer">^{<cd1>}</a>将词典映射到每个<code>VARIABLE</code>。然后我们使用<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.between.html" rel="nofollow noreferrer">^{<cd3>}</a>检查每个<code>VALUE</code>是否在范围内</p>
<p>最后,我们使用<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.where.html" rel="nofollow noreferrer">^{<cd5>}</a>将<code>False</code>值转换为<code>NaN</code></p>
<pre><code>ranges = df['VARIABLE'].map(accepted_ranges)
df['VALUE'] = df['VALUE'].where(df['VALUE'].between(ranges.str[0], ranges.str[1]))
VARIABLE VALUE
0 A 3.0
1 A 4.0
2 A NaN
3 A 5.0
4 B 1.0
5 B 2.0
6 B 3.0
7 B NaN
8 C 0.0
9 C 1.0
</code></pre>
<hr/>
<h3>快一点</h3>
<p><code>.str</code>访问器的速度可能相当慢,并且大多是后台的“循环”实现。尤其是由于数据中有大约10万行,这可能会导致代码效率降低。我们可以通过在两个字典中拆分<code>accepted_ranges</code>来解决这个问题,从而使用<code>Series.map</code>创建两个向量:</p>
<pre><code>accepted_ranges1 = {k: v[0] for k, v in accepted_ranges.items()}
accepted_ranges2 = {k: v[1] for k, v in accepted_ranges.items()}
ranges1 = df['VARIABLE'].map(accepted_ranges1)
ranges2 = df['VARIABLE'].map(accepted_ranges2)
m1 = df['VALUE'].between(ranges1, ranges2)
m2 = ~df['VARIABLE'].isin(list(accepted_ranges.keys()))
df['VALUE'] = df['VALUE'].where(m1|m2)
</code></pre>
<hr/>
<h3>速度比较</h3>
<pre><code># create example dataframe of 10m rows
dfbig = pd.concat([df]*1000000, ignore_index=True)
dfbig.shape
# (10000000, 2)
</code></pre>
<pre><code># Erfan 1
%%timeit
ranges = dfbig['VARIABLE'].map(accepted_ranges)
dfbig['VALUE'].where(dfbig['VALUE'].between(ranges.str[0], ranges.str[1]))
10 s ± 466 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>
<pre><code># Erfan 2
%%timeit
accepted_ranges1 = {k: v[0] for k, v in accepted_ranges.items()}
accepted_ranges2 = {k: v[1] for k, v in accepted_ranges.items()}
ranges1 = dfbig['VARIABLE'].map(accepted_ranges1)
ranges2 = dfbig['VARIABLE'].map(accepted_ranges2)
dfbig['VALUE'].where(dfbig['VALUE'].between(ranges1, ranges2))
1.03 s ± 22.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>
<pre><code># piRSquared
%%timeit
mask = [
accepted_ranges[k][0] <= v <= accepted_ranges[k][1]
for k, v in zip(dfbig.VARIABLE, dfbig.VALUE)
]
dfbig.VALUE.where(mask)
3.11 s ± 106 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
</code></pre>