Pandas - How to perform OLS Regression of values versus time in each group of a dataframe?

Question

I have hourly readings in a dataframe of the form:

Date_Time             Temp           
2001-01-01 00:00:00  -1.3
2001-01-01 01:00:00  -2.1
2001-01-01 02:00:00  -1.9
2001-01-01 03:00:00  -2.2
2001-01-01 04:00:00  -2.8
2001-01-01 05:00:00  -2.0
2001-01-01 06:00:00  -2.2

I want to group the readings by N hours (ie. 3) and determine the OLS slope of Temp vs Time for each group.

I know how to group the dataframe:

df_g = df_g.assign(tgp = df['Temp'].groupby(pds.Grouper(freq='3h')) )

But after that I am stuck, I can not figure out where to start. Can someone help me to achieve my goal?

Answer 1

The beta of a simple (single variable) OLS regression is simply cov(x, y)/var(x)

With that in mind:

# Generate Test data
df = pd.DataFrame(np.random.rand(50), 
                  index=pd.date_range(start='2018 1 1', periods=50, freq='15T'), 
                  columns=['Temp'])
# Copy index as a part of data set
df['DateTime'] = df.index

# Choose starting point as reference date (It doesnt matter what date it is) 
# I'm just looking to convert the dates to numbers
rederence_dt = df['DateTime'].iloc[0] 
df['DateTime'] = (rederence_dt - df['DateTime']).dt.seconds

var = df.groupby(pd.Grouper(freq='3h')).var()['DateTime']
cov = df.groupby(pd.Grouper(freq='3h')).corr().loc(axis=0)[:, 'Temp']['DateTime'].reset_index(level=1, drop=True)

beta = cov/var