Dataframe - interpolate values based on inputs from another dataframe

Question

Here is my dataframe:

import pandas as pd
dates = ('2020-09-24','2020-10-19','2020-12-17','2021-03-17','2021-06-17','2021-09-17','2022-03-17','2022-09-20','2023-09-19','2024-09-17','2025-09-17','2026-09-17','2027-09-17','2028-09-19','2029-09-18','2030-09-17','2031-09-17','2032-09-17','2035-09-18','2040-09-18','2045-09-19')
factors = ('1','0.999994','0.999875','1.000166','1.000303','1.000438','1.00056','1.000817','1.001046','1.001412','1.001525','1.001334','1.000685','0.999376','0.997456','0.994626','0.991244','0.986754','0.982072','0.962028','0.925136')
df = pd.DataFrame()
df['dates']=dates
df['factors']=factors
df['dates'] = pd.to_datetime(df['dates'])
df.set_index(['dates'],inplace=True)
df

Here is another dataframe with a timeseries with fixed interval

interpolated = pd.DataFrame(0, index=pd.date_range('2020-09-24', '2045-09-19', freq='3M'),columns=['result'])

The goal is to populate the second dataframe with the cubic spline interpolated values from the first table. Thanks for all the ideas

Attempt

interpolated['result'] = df['factors'].interpolate(method='cubic')

However it gives only NaN values in the intepolated dataframe. Not sure how to correctly refer to the first table.

@ApplePie I did try interpolated['result'] = df['factors'].interpolate(method='cubic') — MBu
– MBu, Commented Oct 2, 2020 at 11:54
It would be helpful if you were explain whats going wrong. Is there an error message? — ItsAnApe
– ItsAnApe, Commented Oct 2, 2020 at 12:29

ItsAnApe · Accepted Answer · 2020-10-02 12:54:33Z

0

First things first, the shapes don't match. Since it seems none of the dates in the index from the df match the dates in interpolated, you just end up with NaN being filled in on the dates. I think you want something more like merge or join, as described in this post: Merging time series data by timestamp using numpy/pandas merge and join will also be helpful.

answered Oct 2, 2020 at 12:54

ItsAnApe

4221 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MBu Over a year ago

this is the challenge here. Something that can be easily done in Excel I struggle here. The thing is that the dates probably will not match. The idea is to populate the timeseries with the interpolated values for the new dates.

ItsAnApe Over a year ago

Then I think you still want a join on the time series, and after that you call interpolate on the data with lots of NaN. Right now you're interpolating on data that is already complete. I think if you look at the result of your interpolate call, it ought to be identical to your original data.

Collectives™ on Stack Overflow

Dataframe - interpolate values based on inputs from another dataframe

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related