pythondataframeforward-fill

Python ffill with calculation for timestamp as fill value

I have a table as follows

Timestamp             |   record_id
01-04-2024 00:00      |       1
01-04-2024 00:01      |       2
01-04-2024 00:02      |       3
01-04-2024 00:03      |       4
N/A                   |       5
N/A                   |       6
01-04-2024 00:06      |       7

I know that the timestamp increments by 1 minute. Since I have missing data, I need to forward-fill it and add 1 minute to the previous values. I have consecutive N/A values. I tried a few solutions including the following but does not seem to work

missing_mask = df['Timestamp'].isna()
df.loc[missing_mask, 'Timestamp'] = df.loc[missing_mask, 'Timestamp'].fillna(method='ffill') + pd.Timedelta(minutes=1)

Is there something obvious that I'm missing here?

Solution

  • You can try pd.Series.interpolate:

    # convert to datetime if needed:
df["Timestamp"] = pd.to_datetime(df["Timestamp"])

df["Timestamp"] = df["Timestamp"].interpolate()
print(df)

    Prints:

                Timestamp  record_id
0 2024-01-04 00:00:00          1
1 2024-01-04 00:01:00          2
2 2024-01-04 00:02:00          3
3 2024-01-04 00:03:00          4
4 2024-01-04 00:04:00          5
5 2024-01-04 00:05:00          6
6 2024-01-04 00:06:00          7