pandas df - interpolate value in column A for an interpolated value in column B

10mjg 0
Viewed 40

given this dataframe

import pandas as pd

df = pd.DataFrame({
    'RATIO': [1, .8, .6, .3, .2, 0],
    'VALUE': [100, 75, 55, 45, 35, 25]
})
print (df)

    RATIO  VALUE
0    1.0    100
1    0.8     75
2    0.6     55
3    0.3     45
4    0.2     35
5    0.0     25

what is the most efficient way to interpolate a VALUE column value for an interpolated RATIO column value of .75?

must I set the index to RATIO, then reindex, adding .75 to the index, then sort, then interpolate (as shown below?) or is there a more convenient approach?

thank you.

df = df.set_index('RATIO')
df = df.reindex(df.index.values.tolist() + [.75])
df = df.sort_index(ascending=False)
df['VALUE'] = df['VALUE'].interpolate(method='linear')
print (df)

       VALUE
RATIO       
1.00   100.0
0.80    75.0
0.75    65.0
0.60    55.0
0.30    45.0
0.20    35.0
0.00    25.0

h/t to @dermen, worth noting that depending on desired output value, might pass interpolate method='index' as opposed to method='linear' as i did above

df['VALUE'] = df['VALUE'].interpolate(method='index')
interpolation pandas python
1 Answers

Note, your proposed method is actually incorrect, you need to set method='index', otherwise the values are treated as evenly spaced (as per the df.interpolate documentation)

In any case, I would simple use scipy directly as its compatible with Series:

import pandas
from scipy.interpolate import interp1d

df = pandas.DataFrame({
    'RATIO': [1, .8, .6, .3, .2, 0],
    'VALUE': [100, 75, 55, 45, 35, 25]
})

val = interp1d(df.RATIO, df.VALUE, bounds_error=False, fill_value=np.nan, kind='linear')(0.75)
print(val)
# 70

Note, there's no sorting required for this approach...

dermen0
Related