Given data in the following format:
Time Id Status In SId Value
0 2018-02-10 23:09:06.843000 1.139982 OPEN False 17144126 8.80
1 2018-02-10 23:11:09.954000 1.139982 OPEN False 17144126 8.80
2 2018-02-10 23:11:09.954000 1.139982 OPEN False 16848301 2.88
3 2018-02-10 23:13:09.943000 1.139982 OPEN False 16848301 2.90
I want to transform the data to following format:
Time SId_17144126 SId_16848301
0 2018-02-10 23:09:06.843000 8.80 Na
1 2018-02-10 23:11:09.954000 8.80 2.88
2 2018-02-10 23:13:09.943000 Na 2.90
Im trying with the following code but it wont work since merge is not available for a grouped object:
groups = df.groupby(['Id','SId'])
groups.merge(how='outer', left_on='Time', right_on='Time')
