根据在另一个数据框中找到的范围在数据框中填充列 [英] Populate column in data frame based on a range found in another dataframe

问题描述

我正在尝试根据该记录的索引值是否落在另一个数据帧中的两列所定义的范围内来填充数据帧中的一列.

I'm attempting to populate a column in a data frame based on whether the index value of that record falls within a range defined by two columns in another data frame.

df1看起来像:

    a
0   4
1   45
2   7
3   5
4   48
5   44
6   22
7   89
8   45
9   44
10  23

和df2是:

  START STOP CLASS
0   2   3   1
1   5   7   2
2   8   8   3

我想要的样子:

    a   CLASS
0   4   nan
1   45  nan
2   7   1
3   5   1
4   48  nan
5   44  2
6   22  2
7   89  2
8   45  3
9   44  nan
10  23  nan

df2中的START列是该范围的最小值，而STOP列是最大值.

The START column in df2 is the minimum value of the range and the STOP column is the max.

推荐答案

您可以使用IntervalIndex(需要v0.20.0).

You can use IntervalIndex (requires v0.20.0).

首先构造索引:

df2.index = pd.IntervalIndex.from_arrays(df2['START'], df2['STOP'], closed='both')

df2
Out: 
        START  STOP  CLASS
[2, 3]      2     3      1
[5, 7]      5     7      2
[8, 8]      8     8      3

现在，如果您索引到第二个DataFrame，它将在间隔中查找值.例如

Now if you index into the second DataFrame it will lookup the value in the intervals. For example,

df2.loc[6]
Out: 
START    5
STOP     7
CLASS    2
Name: [5, 7], dtype: int64

返回第二类.我不知道它是否可以与merge或merge_asof一起使用，但可以选择使用map:

returns the second class. I don't know if it can be used with merge or with merge_asof but as an alternative you can use map:

df1['CLASS'] = df1.index.to_series().map(df2['CLASS'])

请注意，我首先将索引转换为Series，以便能够使用Series.map方法.结果是

Note that I first converted the index to a Series to be able to use the Series.map method. This results in

df1
Out: 
     a  CLASS
0    4    NaN
1   45    NaN
2    7    1.0
3    5    1.0
4   48    NaN
5   44    2.0
6   22    2.0
7   89    2.0
8   45    3.0
9   44    NaN
10  23    NaN

这篇关于根据在另一个数据框中找到的范围在数据框中填充列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！