Pandas lookup value in a range in another table


Bath

In the example below, I am trying to check if the "value" in table 1 is within the range of values ​​for the "start" and "stop" columns of a row in table 2 . I want to return the type of "fruit" if the value is in that range. The method in between seems to be able to accomplish this, but not sure how to apply it to a row of another table. Note that I need to use a large dataset to perform this task, and methods outside the pandas library can be used.

enter image description here

sample code

df1 = pd.DataFrame({'Date': {0: '06-01', 1: '06-02', 2: '06-03', 3: '06-04'},
                   'Value': {0: 3, 1: 7, 2: 9, 3: 16}, })

df2 = pd.DataFrame({'Start': {0: 1, 1: 6, 2: 11, 3: 16},
                    'Stop': {0: 5, 1: 10, 2: 15, 3: 20},
                    'Fruit': {0: 'Apple', 1: 'Orange', 2: 'Pear', 3: 'Mango'},})

Table 1

    Date  Value
0  06-01      3
1  06-02      7
2  06-03      9
3  06-04     16

Table 2

    Fruit  Start  Stop
0   Apple      1     5
1  Orange      6    10
2    Pear     11    15
3   Mango     16    20

Table 1 Desired output

    Date  Value  Fruit
0  06-01      3   Apple
1  06-02      7  Orange
2  06-03      9  Orange
3  06-04     16   Mango
BEN_YO

This is how to use, instead of using IntervalIndex, we check with numpyboard-cast

s1=df2.Start.values
s2=df2.Stop.values
s=df1.Value.values[:,None]
np.dot((s>=s1)&(s<=s2),df2.Fruit)
array(['Apple', 'Orange', 'Orange', 'Mango'], dtype=object)

Related


Pandas lookup value in a range in another table

Bath In the example below, I am trying to check if the "value" in table 1 is within the range of values for the "start" and "stop" columns of a row in table 2 . I want to return the type of "fruit" if the value is in that range. The method in between seems to

Pandas lookup value in a range in another table

Bath In the example below, I am trying to check if the "value" in table 1 is within the range of values for the "start" and "stop" columns of a row in table 2 . I want to return the type of "fruit" if the value is in that range. The method in between seems to

Pandas lookup value in a range in another table

Bath In the example below, I am trying to check if the "value" in table 1 is within the range of values for the "start" and "stop" columns of a row in table 2 . I want to return the type of "fruit" if the value is in that range. The method in between seems to

Efficient lookup in range table

gitb I have a table of 1.6M IP ranges with organization names. IP addresses will be converted to integers. The form of the table is: I have a list of 2000 unique IP addresses (eg 321223, 531223, etc.) that need to be converted to organization names. I loaded t

Delete a row in a table based on a lookup value in another table

Grid I have two (simplified) tables in a database. Table: queuelist 'songID', 'lastplayed' '7376', '12/01/2013' '9322', '16/08/2012' Table: songlist 'ID', 'artist' '7376', 'Michael Jackson' '2345', 'Nirvana' 'songID'and 'ID'are the same fields. I am provided

Delete a row in a table based on a lookup value in another table

Grid I have two (simplified) tables in a database. Table: queuelist 'songID', 'lastplayed' '7376', '12/01/2013' '9322', '16/08/2012' Table: songlist 'ID', 'artist' '7376', 'Michael Jackson' '2345', 'Nirvana' 'songID'and 'ID'are the same fields. I am provided

Get value from another dataframe based on lookup value in Pandas

Panda I have two pandas dataframes: df1 = pd.DataFrame({'Type':list('ABCD'), 'Set':list('ZZXY')}) df2 = pd.DataFrame({'Type':list('ABCDEF'), 'Test':list('PQRSTM')}) I want to check if the value of df2['Type'] exists in df1['Type'] and if so, replace the corre

Set new column value based on primary key lookup in another table

Heather Stark I have a table where I want to add an extra column whose values are defined by a primary key lookup on another table. I can do this easily by creating a new table with the original table and the required extra columns : CREATE TABLE FG_LABELLED S