My python pandas dataframe looks like this:
df=pd.DataFrame({'ID1': {0: 1,
1: 1,
2: 1,
3: 1,
4: 1,
5: 2,
6: 2,
7: 2,
8: 2,
9: 2},
'Date': {0: '2019-01-01',
1: '2019-01-01',
2: '2019-01-02',
3: '2019-01-02',
4: '2019-01-02',
5: '2019-01-01',
6: '2019-01-01',
7: '2019-01-01',
8: '2019-01-02',
9: '2019-01-02'},
'LoadTime': {0: '1:57:00.0000000',
1: '3:26:00.0000000',
2: '01:26:00.0000000',
3: '04:53:00.0000000',
4: '07:04:00.0000000',
5: '02:33:00.0000000',
6: '04:15:00.0000000',
7: '06:37:00.0000000',
8: '16:26:00.0000000',
9: '18:49:00.0000000'},
'Quantity': {0: 8844.0,
1: 8969.0,
2: 9017.0,
3: 9058.0,
4: 8999.0,
5: 9004.0,
6: 9003.0,
7: 9012.0,
8: 8973.0,
9: 8725.0}})
ID1 Date LoadTime Quantity
0 1 2019-01-01 1:57:00.0000000 8844.0
1 1 2019-01-01 3:26:00.0000000 8969.0
2 1 2019-01-02 01:26:00.0000000 9017.0
3 1 2019-01-02 04:53:00.0000000 9058.0
4 1 2019-01-02 07:04:00.0000000 8999.0
5 2 2019-01-01 02:33:00.0000000 9004.0
6 2 2019-01-01 04:15:00.0000000 9003.0
7 2 2019-01-01 06:37:00.0000000 9012.0
8 2 2019-01-02 16:26:00.0000000 8973.0
9 2 2019-01-02 18:49:00.0000000 8725.0
I want to convert the shape of to something like this:
ID1 Date LoadTime1 LoadTime2 LoadTime3 \
0 1 2019-01-01 1:57:00.0000000 3:26:00.0000000 NaT
1 1 2019-01-02 01:26:00.0000000 04:53:00.0000000 07:04:00.0000000
2 2 2019-01-01 02:33:00.0000000 04:15:00.0000000 06:37:00.0000000
3 2 2019-01-02 16:26:00.0000000 18:49:00.0000000 NaT
Quantity1 Quantity2 Quantity3
0 8844.0 8969.0 NaN
1 9017.0 9058.0 8999.0
2 9004.0 9003.0 9012.0
3 8973.0 8725.0 NaN
I know that I have maximum 3 values per day but it would be nice if the code could figure out the maximum needed number of columns on its own. what is the best way to do it?
