Pandas - stack time columns with time and date

Question

I have date and time data now I want to reduce this dataframe to two columns with Timestamp (date+time) in a column and value in another column

current df -

Date                    8 am   10 am   1 pm
-----------------------------------------------
23/02/2022              5        10    11
24/02/2022              2        17    15         
25/02/2022              7        90    175

desired df -

Timestamp                       value
----------------------------------------------
2022-02-23 00:08:00               5
2022-02-23 00:10:00               10
2022-02-23 00:13:00               11
2022-02-24 00:08:00               2
2022-02-24 00:10:00               17
2022-02-24 00:13:00               15
2022-02-25 00:08:00               7
2022-02-25 00:10:00               90
2022-02-25 00:13:00               175

Here is original list from which I'm creating my dataframe -

[['Date', '08:00', '10:00', '12:00', '14:00', '19:00', '22:00', '03:00'],
 ['23/02/2022', '140', '244', '191', '88', '263', '252', '159'],
 ['24/02/2022', '184', '235', '189', '108', '283', '300', '202'],
 ['25/02/2022', '131', '217', '135', '179', '207', '284', '177'],
 ['26/02/2022', '112', '188', '96', '139', '148', '188', '125'],
 ['27/02/2022', '130', '189', '104', '163', '210', '221', '139'],
 ['28/02/2022', '118', '89', '84', '113', '259', '234', '105'],
 ['01/03/2022', '98', '89', '77', '82', '138', '174', '71'],
 ['02/03/2022', '87', '187', '69', '118', '199', '178', '59'],
 ['03/03/2022', '90', '200', '110', '102', '180', '216', '72']]

Answer 1

IIUC, use melt and to_datetime:

(df
   .melt(id_vars='Date', var_name='time')
   .assign(Timestamp=lambda d: pd.to_datetime(d['Date']+' '+d['time']))
   [['Timestamp', 'value']]
   # below optional
   .sort_values(by='Timestamp').reset_index(drop=True)
 )

Output:

            Timestamp  value
0 2022-02-23 08:00:00      5
1 2022-02-23 10:00:00     10
2 2022-02-23 13:00:00     11
3 2022-02-24 08:00:00      2
4 2022-02-24 10:00:00     17
5 2022-02-24 13:00:00     15
6 2022-02-25 08:00:00      7
7 2022-02-25 10:00:00     90
8 2022-02-25 13:00:00    175

Answer 2

Set date as index, stack and coerce concatenated time component into datetime;

s =df.set_index('Date').stack().to_frame('value').reset_index()
s=s.assign(Timestamp=pd.to_datetime(s['Date'].str.cat(s['level_1'], sep =' ')))[['Timestamp', 'value']]


 

             Timestamp   value
0 2022-02-23 08:00:00      5
1 2022-02-23 10:00:00     10
2 2022-02-23 13:00:00     11
3 2022-02-24 08:00:00      2
4 2022-02-24 10:00:00     17
5 2022-02-24 13:00:00     15
6 2022-02-25 08:00:00      7
7 2022-02-25 10:00:00     90
8 2022-02-25 13:00:00    175