Spline interpolation on dataframes by row

Question

I have the following data frame:

OBJECTID    2017    2018    2019            2020            2021
    1.0     NaN     NaN     7569.183179     7738.162829     7907.142480
    2.0     NaN     NaN     766.591146      783.861122      801.131099
    3.0     NaN     NaN     8492.215747     8686.747704     8881.279662
    4.0     NaN     NaN     40760.327825    41196.877473    41633.427120
    5.0     NaN     NaN     6741.819674     6788.981231     6836.142788

I am trying to apply a spline interpolation on each row to get the values for 2017 and 2018 using the following code:

years = list(range(2017,2022))
df[years] = df[years].interpolate(method="spline", order =1, limit_direction="both", axis=1)

However, I get the following error: ValueError: Index column must be numeric or datetime type when using spline method other than linear. Try setting a numeric or datetime index column before interpolating.

The dataframe in this question is just a subset of a much larger dataset I am using. All of the examples I have seen do the spline interpolation down each column, but I can't seem to get it work across each row. I feel like it's a simple solution and I'm just missing it. Could someone please help?

Could you update with an expected output please? I never used this parameter (interpolate) before but I like playing with dataframes. — Drakax, Jun 02 '22 at 17:31
The expected outputs could vary depending on which interpolation method you use. In general the 2017 and 2018 rows should follow the the general trend of you are seeing in the rows with values. So I would expect the 2018 values to be a little less than the 2019 values and the 2017 values to be a little less than the 2018 values. — ACAYA, Jun 02 '22 at 17:42

jch · Accepted Answer · 2022-06-02T18:39:09.657

It appears to be because the dtype of the index (really columns for axis=1) is probably object in your case since the index contains a string column name also. Even though you are grabbing a slice of the columns that contains only integer years the overall index dtype remains the same - object. Then it looks like interpolate looks at the dtype and punts when it sees a dtype of object.

Example - even though the years are stored as integers the overall dtype is object:

df.columns

Index(['OBJECTID', 2017, 2018, 2019, 2020, 2021], dtype='object')

If we did this:

df.drop(columns=['OBJECTID'], inplace=True)
df.columns = df.columns.astype('uint64')
df.columns

UInt64Index([2017, 2018, 2019, 2020, 2021], dtype='uint64')

Then the axis=1 interpolation works:

years = list(range(2017,2022))
df[years] = df[years].interpolate(method="spline", order =1, limit_direction="both", axis=1)

           2017          2018          2019          2020          2021
0   7231.223878   7400.203528   7569.183179   7738.162829   7907.142480
1    732.051193    749.321169    766.591146    783.861122    801.131099
2   8103.151832   8297.683789   8492.215747   8686.747704   8881.279662
3  39887.228530  40323.778178  40760.327825  41196.877473  41633.427120
4   6647.496560   6694.658117   6741.819674   6788.981231   6836.142788

Dropping the OBJECTID was done to illustrate what is going on.

Spline interpolation on dataframes by row

1 Answers1