I can't interpolate data properly with pandas time series.
I build a small dataframe to interpolate:
import pandas as pd
# Build a dataframe with 3 distances
df = pd.DataFrame([0., 5., 10.], columns=['distance'])
# Add a column representing the time to reach these distances
# at a constant speed of 2.572 meters per second
speed_mps = 2.572
df['time'] = df.apply(lambda r: r.distance/speed_mps, axis=1)
df
distance time
0 0.0 0.000000
1 5.0 1.944012
2 10.0 3.888025
I can interpolate the way I want with scipy.interpolate:
# Interpolate distance at every second with scipy
from scipy.interpolate import interp1d
# Get the interpolation function
f = interp1d(df.time, df.distance)
seconds = [0., 1., 2., 3.]
# Interpolate distances and build the expected dataframe
expected_df = pd.DataFrame(list(zip(f(seconds),seconds)),
columns=['distance', 'time'])
expected_df
distance time
0 0.000 0.0
1 2.572 1.0
2 5.144 2.0
3 7.716 3.0
It would be acceptable if the resulting dataframe contains both the original and the interpolated data:
acceptable_df = pd.concat([expected_df, df], axis=0, sort=True).
sort_values(by='time').drop_duplicates().reset_index(drop=True)
acceptable_df
distance time
0 0.000 0.000000
1 2.572 1.000000
2 5.000 1.944012
3 5.144 2.000000
4 7.716 3.000000
5 10.000 3.888025
But I didn't succeed to interpolate properly using pandas only:
# Try to interpolate distance (and a bunch of other columns ignored here)
# at every second using pandas time series features
pd_interp_df = df.copy(deep=True)
pd_interp_df['timedelta'] = pd.to_timedelta(pd_interp_df.time, unit='s')
pd_interp_df.resample(rule='s', on='timedelta').mean().interpolate()
distance time
timedelta
00:00:00 0.0 0.000000
00:00:01 5.0 1.944012
00:00:02 7.5 2.916019
00:00:03 10.0 3.888025
The timedelta index and the time column values are not consistent. How can I build the expected or the acceptable dataframe above using pandas only ?