Interpolate values based in date in pandas

user_5404647 0
Viewed 130

I have the following datasets

import pandas as pd
import numpy as np
df = pd.read_excel("https://github.com/norhther/datasets/raw/main/ncp1b.xlsx", 
                   sheet_name="Sheet1")
df2 = pd.read_excel("https://github.com/norhther/datasets/raw/main/ncp1b.xlsx", 
                    sheet_name="Sheet2")
df2.dropna(inplace = True)

For each group of values on the first df X-Axis Value, Y-Axis Value, where the first one is the date and the second one is a value, I would like to create rows with the same date. For instance, df.iloc[0,0] the timestamp is Timestamp('2020-08-25 23:14:12'). However, in the following columns of the same row maybe there is other dates with different Y-Axis Value associated. The first one in that specific row being X-Axis Value NCVE-064 HPNDE with a timestap 2020-08-25 23:04:12 and a Y-Axis Value associated of value 0.952.

What I want to accomplish is to interpolate those values for a time interval, maybe 10 minutes, and then merge those results to have the same date for each row.

For the df2 is moreless the same, interpolate the values in a time interval and add them to the original dataframe. Is there any way to do this?

pandas scipy numpy
1 Answers

The trick is to realize that datetimes can be represented as seconds elapsed with respect to some time.

Without further context part the hardest things is to decide at what times you wants to have the interpolated values.

import pandas as pd
import numpy as np
from scipy.interpolate import interp1d

df = pd.read_excel(
    "https://github.com/norhther/datasets/raw/main/ncp1b.xlsx", 
    sheet_name="Sheet1",
)

x_columns = [col for col in df.columns if col.startswith("X-Axis")]

# What time do we want to align the columsn to?
# You can use anything else here or define equally spaced time points
# or something else.
target_times = df[x_columns].min(axis=1)



def interpolate_column(target_times, x_times, y_values):
    ref_time = x_times.min()
    
    # For interpolation we need to represent the values as floats. One options is to
    # compute the delta in seconds between a reference time and the "current" time.
    deltas = (x_times - ref_time).dt.total_seconds()
    
    # repeat for our target times
    target_times_seconds = (target_times - ref_time).dt.total_seconds()
    
    return interp1d(deltas, y_values, bounds_error=False,fill_value="extrapolate" )(target_times_seconds)



output_df = pd.DataFrame()
output_df["Times"] = target_times

output_df["Y-Axis Value NCVE-063 VPNDE"] = interpolate_column(
    target_times,
    df["X-Axis Value NCVE-063 VPNDE"],
    df["Y-Axis Value NCVE-063 VPNDE"],
)
# repeat for the other columns, better in a loop
user_9385300
Related