{‘ID’: {0: 1001,
1: 1001,
2: 1001,
3: 1001,
4: 1001,
5: 1005,
6: 1005,
7: 1005,
8: 1005,
9: 1005,
10: 1005,
11: 1005},
‘Date’: {0: np.datetime64(‘2016-06-30’),
1: np.datetime64(‘2017-07-31’),
2: np.datetime64(‘2018-06-30’),
3: np.datetime64(‘2019-04-30’),
4: np.datetime64(‘2019-05-31’),
5: np.datetime64(‘2016-06-30’),
6: np.datetime64(‘2016-10-31’),
7: np.datetime64(‘2016-12-31’),
8: np.datetime64(‘2018-12-31’),
9: np.datetime64(‘2019-01-29’),
10: np.datetime64(‘2019-02-28’),
11: np.datetime64(‘2019-03-31’)},
‘Classification’: {0: ‘Better’,
1: ‘Good’,
2: ‘Risky’,
3: ‘Risky’,
4: ‘Risky’,
5: ‘Better’,
6: ‘Good’,
7: ‘Good’,
8: ‘Risky’,
9: ‘Risky’,
10: ‘Risky’,
11: ‘Risky’}}
Method used by me are:
Import pandas as pd
pivotTable = pd.pivot_table(FileRead,values=‘Date’,index=[‘ID’],columns=[‘Classification’],aggfunc=np.max)
Classification Better Good Risky
ID
1001 2016-06-30 2017-07-31 2019-05-31
1005 2016-06-30 2016-12-31 2019-03-31
above method gives the latest date value for each classification type, but I also need find latest date if same classification repeated continuously then I need when it first classified. E.g. for customer ID 1005 the Risky classification was started to repeat continuously from 31st December 2018 to 31st March 2019, in this case the date I need is 31st December 2018 not the 31st March 2019. Please help how to do this?