New interpolated column from existing column and lookup table

Question

I am trying to transform a column in a dataframe to get a new column, with each row in the new column interpolated from the old column and a lookup table. Example:

Original dataframe:

Date       StartingValue
2010-01-01            10
2010-01-02            25
2010-01-03            15
2010-01-04            20

Lookup Table dataframe:

StartingValue  NewValue
           10       500
           20      1200
           30      2750

Desired result:

Date       StartingValue  NewValue
2010-01-01            10       500
2010-01-02            25      1975
2010-01-03            15       850
2010-01-04            20      1200

The index will stay the same, and the interpolation should be linear between the closest rows in the lookup table.

I have looked at maybe map() or apply() but can't get a sense of how to use them here, especially with the interpolation. All help is appreciated.

Answer 1

numpy.interp has this feature:

import numpy as np

df['NewValue'] = np.interp(df['StartingValue'].values,
                           lookup_table['StartingValue'].values,
                           lookup_table['NewValue'].values)

Answer 2

The numpy option is more succinct, but here is how you can perform this task just with pandas:

vals = lookup['StartingValue']
df.merge(lookup.set_index('StartingValue').reindex( \
  range(vals.min(), vals.max()+1, 5)).interpolate().reset_index(), \
  on='StartingValue')
#          Date  StartingValue  NewValue
# 0  2010-01-01             10     500.0
# 1  2010-01-02             25    1975.0
# 2  2010-01-03             15     850.0
# 3  2010-01-04             20    1200.0