0

The following list here is just an example i created. I have a much longer list with a car serial number in the first column and in the second the date. In the columns behind there is a lot of data. One car (serial number) has always three reports and they should stay together. But the dates always change. So one day "car_01" has the newest entry and the other day "car_24" has the newest entry. The newest entry with the car serial number should be on top. Then following the two other reports from the serial number. The fourth should be the second newest report with the both reports following from the second serial number. 

    Cars    date
0   Car_01  2019-01-22
1   Car_01  2019-05-23
2   Car_01  2019-06-17
3   Car_02  2019-02-15
4   Car_02  2019-06-17
5   Car_02  2019-09-18
6   Car_03  2019-02-27
7   Car_03  2019-10-19
8   Car_03  2019-11-22
9   Car_04  2019-04-12
10  Car_04  2019-11-17
11  Car_04  2019-01-16

Is there a way to create groups and then sort for the date?

vs = vs.sort_values(by=['date'], ascending=False)

If i sort it like this the cars are getting mixed up.

    Cars    date
8   Car_03  2019-11-22
10  Car_04  2019-11-17
7   Car_03  2019-10-19
5   Car_02  2019-09-18
2   Car_01  2019-06-17
4   Car_02  2019-06-17
1   Car_01  2019-05-23
9   Car_04  2019-04-12
6   Car_03  2019-02-27
3   Car_02  2019-02-15
0   Car_01  2019-01-22
11  Car_04  2019-01-16

The Output should look like that if sorted correctly.

    Cars    date
0   Car_03  2019-11-22
1   Car_03  2019-10-19
2   Car_03  2019-02-27
3   Car_04  2019-11-17
4   Car_04  2019-04-12
5   Car_04  2019-01-16
6   Car_02  2019-09-18
7   Car_02  2019-06-17
8   Car_02  2019-02-15
9   Car_01  2019-06-17
10  Car_01  2019-05-23
11  Car_01  2019-01-22
Kuempsmichi
  • 69
  • 2
  • 10
  • You should add raw data not image or screenshot – bharatk Jul 08 '19 at 06:57
  • vs.sort_values(by=['Cars', 'date'], ascending=False) – Florian H Jul 08 '19 at 07:00
  • @FlorianH In that case the newest date doesnt stay on top. – Kuempsmichi Jul 08 '19 at 07:09
  • @jezrael This also doesn't solve my problem. It doesn't matter which car is on top they should just stay together in groups. The important part is the date. – Kuempsmichi Jul 08 '19 at 07:28
  • Can you add expected output to question? – jezrael Jul 08 '19 at 07:29
  • @jezrael so added the expected output. Hope it helps explaining my question. – Kuempsmichi Jul 08 '19 at 07:33
  • @jezrael Could you pls remove the duplicate mark? It soesnt answer my question. – Kuempsmichi Jul 08 '19 at 07:45
  • @jezrael Sorry also that doesnt fix the problem. The list here is just an example i created. I have a much longer list with a serial number for cars in one column and in another the date and in columns behind that a lot of data. On vehicle is always three times in the list and they have to stay together. but the dates always change. So one day "car_01" has the newest entry and the other day "car_24" has the newest entry. The newest entry with the car serial number should be on top. Then following the two other reports from the serial number. the fourth should be the second newest report. – Kuempsmichi Jul 08 '19 at 08:05
  • `On vehicle is always three times in the list` - not sure if understand, is possible extract this value and pass to solution above? Maybe here should be best change question with [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve). – jezrael Jul 08 '19 at 08:09

1 Answers1

0

You can create helper column with GroupBy.transform and max, sorting by this column and last remove it:

vs['new'] = vs.groupby('Cars')['date'].transform('max')
print (vs)
      Cars        date         new
0   Car_01  2019-05-22  2019-07-23
1   Car_01  2019-07-23  2019-07-23
2   Car_01  2019-01-25  2019-07-23
3   Car_02  2019-08-24  2019-08-24
4   Car_02  2019-04-14  2019-08-24
5   Car_02  2019-03-27  2019-08-24
6   Car_03  2019-02-25  2019-12-16
7   Car_03  2019-05-17  2019-12-16
8   Car_03  2019-12-16  2019-12-16
9   Car_04  2019-09-28  2019-11-19
10  Car_04  2019-03-17  2019-11-19
11  Car_04  2019-11-19  2019-11-19

vs = vs.sort_values(by=['new', 'Cars','date'], ascending=False).drop('new', axis=1)
print (vs)
      Cars        date
8   Car_03  2019-12-16
7   Car_03  2019-05-17
6   Car_03  2019-02-25
11  Car_04  2019-11-19
9   Car_04  2019-09-28
10  Car_04  2019-03-17
3   Car_02  2019-08-24
4   Car_02  2019-04-14
5   Car_02  2019-03-27
1   Car_01  2019-07-23
0   Car_01  2019-05-22
2   Car_01  2019-01-25
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252