CODEWITHSUNDEEP
pythonpython-3.xpandas
emehex
emehex

Reputation: 10548

Pandas between range lookup filtering

My data looks like this:

import pandas as pd

pd.DataFrame({
    'x_range':['101-200','101-200','201-300','201-300'], 
    'y':[5,6,5,6], 
    'z': ['Cat', 'Dog', 'Fish', 'Snake']
})

How might I filter on an x value (that fit's inside x_range) and a y value to return an appropriate z value? For instance, if x = 248 and y= 5, I'd like to return Fish...

Upvotes: 0

Views: 871

Answers (2)

emehex
emehex

Reputation: 10548

I don't love this, but I hacked together a solution:

Split: 

df['low'], df['high'] = df['x_range'].str.split('-', 1).str
df['low'] = pd.to_numeric(df['low'])
df['high'] = pd.to_numeric(df['high'])

Filter:

x = 248
y = 6
row = df[(pd.Series.between(x, left=df.low, right=df.high, inclusive=True)) & 
(df.y == y)]
row['z']

Upvotes: 0

A.Kot
A.Kot

Reputation: 7913

Simple filtering exercise:

Save your dataframe:

df['x_range_start'] = [int(i.split('-')[0]) for i in df.x_range]

Add two columns for range start and end:

df['x_range_start'] = [int(i.split('-')[0]) for i in df.x_range]
df['x_range_end'] = [int(i.split('-')[1]) for i in df.x_range]

Filter to find values:

x_value = 113
y_value = 5

df[(df.x_range_start <= x_value) &(x_value <= df.x_range_end)][df.y == y_value]['z']

Upvotes: 1

Related Questions