Pandas Resample with Linear Interpolation

Question

I have some hourly data, such as below, with odd sample times.

#	Date Time, GMT-08:00	Temp, °C
1	10/31/23 15:51	13.41
2	10/31/23 16:51	7.49
3	10/31/23 17:51	7.61
4	10/31/23 18:51	7.39
5	10/31/23 19:51	7.34
6	10/31/23 20:51	7.33
7	10/31/23 21:51	7.38

I would like to resample with interpolation so the data points occur on the hour. I.e. 1500, 1600, 1700...

I assumed the following would work, but I've been unable to make this do what I expected.

df.resample('60min').first().interpolate('linear')

Answer 1

IIUC:

• First use df.resample to resample your series into 1 minute bins ('T'), get .first, and apply linear interpolation (.interpolate), method='linear' being the default.
• Next, downsample into hourly bins ('H'), and apply .asfreq.

import pandas as pd

# df
data = {'Date Time, GMT-08:00': {1: '10/31/23 15:51', 2: '10/31/23 16:51', 
                                 3: '10/31/23 17:51', 4: '10/31/23 18:51', 
                                 5: '10/31/23 19:51', 6: '10/31/23 20:51', 
                                 7: '10/31/23 21:51'}, 
        'Temp, °C': {1: 13.41, 2: 7.49, 3: 7.61, 4: 7.39, 5: 7.34, 6: 7.33, 7: 7.38}
        }
df = pd.DataFrame(data)

# preliminary steps: convert to timestamps & set as index
df['Date Time, GMT-08:00'] = pd.to_datetime(df['Date Time, GMT-08:00'], 
                                            format='%m/%d/%y %H:%M')

df = df.set_index('Date Time, GMT-08:00')

# resample to 1min, get first, interpolate, resample to 1h, and get `asfreq`
df = df.resample('T').first().interpolate().resample('H').asfreq()
df

                      Temp, °C
Date Time, GMT-08:00          
2023-10-31 15:00:00        NaN
2023-10-31 16:00:00    12.5220
2023-10-31 17:00:00     7.5080
2023-10-31 18:00:00     7.5770
2023-10-31 19:00:00     7.3825
2023-10-31 20:00:00     7.3385
2023-10-31 21:00:00     7.3375

Answer 2

import pandas as pd

# Example data
data = {
    'Date Time, GMT-08:00': [
        '10/31/23 15:51', '10/31/23 16:51', '10/31/23 17:51',
        '10/31/23 18:51', '10/31/23 19:51', '10/31/23 20:51',
        '10/31/23 21:51'
    ],
    'Temp, °C': [13.41, 7.49, 7.61, 7.39, 7.34, 7.33, 7.38]
}

# Convert to DataFrame
df = pd.DataFrame(data)

# Convert the date-time column to datetime objects and set as index
df['Date Time, GMT-08:00'] = pd.to_datetime(df['Date Time, GMT-08:00'], format='%m/%d/%y %H:%M')
df = df.set_index('Date Time, GMT-08:00')

# Resample to hourly intervals, using the mean to aggregate and interpolate missing values
resampled_df = df.resample('H').mean().interpolate('linear')

print(resampled_df)