pandas round to neareast custom list-CodePudding

My question is very similar to here, except I would like to round to closest, instead of always round up, so cut() doesn't seem to work.

import pandas as pd
import numpy as np

df = pd.Series([11,16,21, 125])
rounding_logic = pd.Series([15, 20, 100])
labels = rounding_logic.tolist()
rounding_logic = pd.Series([-np.inf]).append(rounding_logic) # add infinity as leftmost edge

pd.cut(df, rounding_logic, labels=labels).fillna(rounding_logic.iloc[-1])

The result is [15,20,100,100], but I'd like [15,15,20,100], since 16 is closest to 15 and 21 closest to 20.

CodePudding user response：

You can try pandas.merge_asof with direction=nearest

out = pd.merge_asof(df.rename('1'), rounding_logic.rename('2'),
                    left_on='1',
                    right_on='2',
                    direction='nearest')

print(out)

     1    2
0   11   15
1   16   15
2   21   20
3  125  100

CodePudding user response：

Get the absolute difference for the values, and take minimum value from rounding_logic:

>>> rounding_logic.reset_index(drop=True, inplace=True)
>>> df.apply(lambda x: rounding_logic[rounding_logic.sub(x).abs().idxmin()])

0     15.0
1     15.0
2     20.0
3    100.0
dtype: float64

PS: You need to reset the index in rounding_logic cause you have duplicate index after adding -inf to the start of the series.