[Python] Transpose several columns into one and groupby several columns

I have a dataset which contains two time stamps and several data columns. My aim is to put anything in three columns: two time columns and one data column which results having several rows of identical time stamps but with different data.

The data set is a simple dataframe:

df = pd.DataFrame([['2024-12-07 13:43:09.598022400', 49389, 13.043, 2.174, 0.000, 0.000, 0.000],
['2024-12-07 13:43:10.601990400', 49390, 16.667, 0.000, 0.000, 0.000, 0.000],
['2024-12-07 13:43:11.601984000', 49391, 14.894, 0.000, 3.021, 0.000, 1.428],
['2024-12-07 13:43:12.591004800', 49392, 12.766, 2.128, 0.000, 2.128, 0.000],
['2024-12-07 13:43:13.592035200', 49393, 14.894, 2.128, 0.000, 0.000, 0.000],
], columns=['DateTime', 'UTCs', 'Data1', 'Data2', 'Data3', 'Data4', 'Data5'])
df


The result should look like

DateTime UTCs Data
0 2024-12-07 13:43:09.598022400 49389 13.043
1 2024-12-07 13:43:09.598022400 49389 2.174
2 2024-12-07 13:43:09.598022400 49389 0.000
3 2024-12-07 13:43:09.598022400 49389 0.000
4 2024-12-07 13:43:09.598022400 49389 0.000
5 2024-12-07 13:43:10.601990400 49390 16.667
6 2024-12-07 13:43:10.601990400 49390 0.000
7 2024-12-07 13:43:10.601990400 49390 0.000
8 2024-12-07 13:43:10.601990400 49390 0.000
9 2024-12-07 13:43:10.601990400 49390 0.000
10 2024-12-07 13:43:11.601984000 49391 14.894
11 2024-12-07 13:43:11.601984000 49391 0.000
12 2024-12-07 13:43:11.601984000 49391 3.021
13 2024-12-07 13:43:11.601984000 49391 0.000
14 2024-12-07 13:43:11.601984000 49391 1.428
:
:


...and so on..

I tried to modify this post: [1]: Transpose multiple columns into one column using Python which uses melt and works well in principle but the columns appear not sorted by the 'DateTime' and the 'UTCs' column. Following I make a 'groupby' to gain the result I want to have but it seems not to be the solution. Any hint for me what I oversee?

My code so far:

new = df.melt(id_vars = ['DateTime','UTCs'],
value_vars = df.columns.drop('DateTime').tolist())
new.groupby(['DateTime', 'UTCs']).size()


First line results in:

DateTime UTCs variable value
0 2024-12-07 13:43:09.598022400 49389 Data1 13.043
1 2024-12-07 13:43:10.601990400 49390 Data1 16.667
2 2024-12-07 13:43:11.601984000 49391 Data1 14.894
:
5 2024-12-07 13:43:09.598022400 49389 Data2 2.174
6 2024-12-07 13:43:10.601990400 49390 Data2 0.000
7 2024-12-07 13:43:11.601984000 49391 Data2 0.000
:
12 2024-12-07 13:43:11.601984000 49391 Data3 3.021
13 2024-12-07 13:43:12.591004800 49392 Data3 0.000
14 2024-12-07 13:43:13.592035200 49393 Data3 0.000
15 2024-12-07 13:43:09.598022400 49389 Data4 0.000
16 2024-12-07 13:43:10.601990400 49390 Data4 0.000
:
22 2024-12-07 13:43:11.601984000 49391 Data5 1.428
23 2024-12-07 13:43:12.591004800 49392 Data5 0.000
24 2024-12-07 13:43:13.592035200 49393 Data5 0.000



And the groupby-function only results in:

2024-12-07 13:43:09.598022400 49389 5
2024-12-07 13:43:10.601990400 49390 5
2024-12-07 13:43:11.601984000 49391 5
2024-12-07 13:43:12.591004800 49392 5
2024-12-07 13:43:13.592035200 49393 5
dtype: int64


what am I missing?

Continue reading...