CODEWITHSUNDEEP
pythonpandasdataframenumpy
Сергей Бекетов
Сергей Бекетов

Reputation: 23

What is the best solution to pick up value from rigth column based on range in left column?

What is the best solution to pick up value from rigth column based on range in left column? What is best not to use heavy loops: numpy array, pandas series, dict, list. Data itself in Dataframe and ranges are bigger that in the example. And not only one value it could be a row with many values.

enter image description here

There is an Excel file that shows the tariffs for transportation depending on various conditions. Tariffs change quarterly, in this file. Old data is overwritten. Therefore, the format must be left for automatic loading into Dataframe. It is necessary to select the desired line with the tariff depending on the distance. For example For 74 km I should pick up 6955,00 if condition 1.

Upvotes: 1

Views: 49

Answers (1)

Corralien
Corralien

Reputation: 120479

IIUC, split your range into two other columns (left and right) then return the right value from right column if (left <= x) & (x <= right)

df = pd.DataFrame({'range': ['51-60', '61-70'], 'value': [6505.00, 6730.00]})
df[['left', 'right']] = df['range'].str.split('-', expand=True).astype(int)

get_value = lambda x: df.loc[(df['left'] <= x) & (x <= df['right']), 'value'].squeeze()

print(df)
   range   value  left  right
0  51-60  6505.0    51     60
1  61-70  6730.0    61     70

Usage:

>>> get_value(53)
6505.0

>>> get_value(70)
6730.0

Update after your edit

import pandas as pd

# Suppose your file is organized like:
# Dist | Q1 | Q2 | Q3 | Q4
df = pd.read_excel('Tariff.xlsx')
quarter = pd.Timestamp.today().quarter  # quarter is the column number

interval = df['Distance'].str.extractall('(?P<left>\d+)[^\d]+(?P<right>\d+)') \
             .droplevel(1).astype(int)

get_value = lambda x: df.loc[(interval['left'] <= x) & (x <= interval['right'])] \
                        .iloc[:, quarter].squeeze()

Upvotes: 1

Related Questions