Merge timestamp column in pandas

Question

I have a log file that has large number of columns and I would like to merge timestamp columns into one as shown in the example below

start time  End time process ID. . . 
10:02       11:10    A       1
10:32       11:23    B       1
...         ....     A       2
...         ....     B       2
...         ....     C       2

Note: Single ID can have multiple similar processes e.g. multiple A in an ID

time     process   ID . . .
10:02    A_start   1  
11:10    A_end     1
10:32    B_start   1
11:23    B_end     1
....     A_start   2
....     A_end     2
....     B_start   2
....       ...    ..

Trying with melt on pandas but it seem I am missing something here

df.melt(id_vars=["ID", "process"], 
        var_name="End time", 
        )

Do you always have a single given process per ID? or can you have multiple A/1? — mozway, Jun 28 '22 at 09:22
No I mean multiple **same** process per ID (if yes, please update the example) — mozway, Jun 28 '22 at 09:25
yes as you can see in the example ID 1 has A , B processes and ID 2 also has A, B processes — alex3465, Jun 28 '22 at 09:35
still not my question, but doesn't matter, let me know if [my answer](https://stackoverflow.com/a/72783921/16343464) works for your, if not we'll work from there ;) — mozway, Jun 28 '22 at 09:36
yes this could be possible when ID can have multiple similar processes — alex3465, Jun 28 '22 at 09:41

score 1 · Accepted Answer · answered Jun 28 '22 at 09:26

1

IIUC, you can use melt with a bit of reshaping:

out = (df
 .rename(columns={'start time': 'start', 'End time': 'end'})
 .melt(['process', 'ID'], value_name='time')
 .assign(process=lambda d: d['process']+'_'+d.pop('variable'))
)

output:

   process  ID   time
0  A_start   1  10:02
1  B_start   1  10:32
2    A_end   1  11:10
3    B_end   1  11:23

used input:

  start time End time process  ID
0      10:02    11:10       A   1
1      10:32    11:23       B   1

answered Jun 28 '22 at 09:26

mozway

194,879
13
39
75

I have to add all the column names except time column in `.melt()`, rest it works fine. – alex3465 Jun 28 '22 at 09:44
Yes, if you have many, you can use `df.columns.difference` to help you ;) – mozway Jun 28 '22 at 09:46

Merge timestamp column in pandas

1 Answers1

Linked