How to pivot N observations of a time series column at a time

There is a way of achieving your desired output using a Hankel matrix and some array manipulation. You can construct a Hankel matrix with the scipy.linalg.hankel function.

In the following I define a custom function time_series_to_hankel() that takes as input your pandas DataFrame, the time-series variable you want to stack in one row, and the number of time steps.

import numpy as np
import pandas as pd
from scipy.linalg import hankel

def time_series_to_hankel(data, ts_col, n_steps):
    
    # generate hankel dataframe for the time series column
    h = hankel(data[ts_col])[:(data.shape[0] - n_steps + 1), :n_steps]
    h_df = pd.DataFrame(h, columns=['t_' + str(i) for i in range(h.shape[1])])
    
    # manipulate the original df
    temp_df = data.drop(columns=['value']).loc[:(h.shape[0] - 1)]
    
    # concat the two dataframes
    return pd.concat([temp_df, h_df], axis=1)

I suggest you to go run it step by step if you want to understand the rationale in all the passages.

Example

import numpy as np
import pandas as pd
from scipy.linalg import hankel

# similar to your sample dataset
df = pd.DataFrame({
    'date': pd.date_range('2018-02-28 09:00:00', '2018-11-30 15:00:00', freq='H'),
    'test_var': np.random.randint(1, 10, size=6607),
    'value': np.linspace(78700, 69400, num=6607).astype(int)
})

time_series_to_hankel(df, 'value', n_steps=6)
                    date  test_var    t_0    t_1    t_2    t_3    t_4    t_5
0    2018-02-28 09:00:00         7  78700  78698  78697  78695  78694  78692
1    2018-02-28 10:00:00         9  78698  78697  78695  78694  78692  78691
2    2018-02-28 11:00:00         2  78697  78695  78694  78692  78691  78690
3    2018-02-28 12:00:00         8  78695  78694  78692  78691  78690  78688
4    2018-02-28 13:00:00         1  78694  78692  78691  78690  78688  78687
...                  ...       ...    ...    ...    ...    ...    ...    ...
6597 2018-11-30 06:00:00         8  69412  69411  69409  69408  69407  69405
6598 2018-11-30 07:00:00         4  69411  69409  69408  69407  69405  69404
6599 2018-11-30 08:00:00         3  69409  69408  69407  69405  69404  69402
6600 2018-11-30 09:00:00         6  69408  69407  69405  69404  69402  69401
6601 2018-11-30 10:00:00         4  69407  69405  69404  69402  69401  69400