0

I have below DF:

DF is sorted by VEHICLE_ID_FW and secondly by TRANSACTION_DATE_FW (ascending=False).

Task:

I need correct values in ODOMETER_FW column. (There are mileages of vehicles). For single VEHICLE_ID_FW I need check:

  1. If some values in ODOMETER_FW is greater than last mileage in the month. –> if True –> convert this value to 0

  2. If ODOMETER_FW for single VEHICLE_ID_FW < last mileage from month (always top record from ODOMETER_FW for single VEHICLE_ID_FW) – 10000 –> if True –> convert this value to 0.

Current DF (errors on red): enter image description here

Example Output: enter image description here

I really tried to do this myself and I have written a code in python and converted everything to multiple list / matrix. Next I have written many difrent functions to correct it. It works but it is a tones of code lines and I think it is spaghetti code. I really doubt that what I have done is best way to solve it, especially that it is a possibility to make it in PANDAS ?

Ventre90
  • 57
  • 6

1 Answers1

0

Data: Current DF:

VEHICLE_ID_FW   ODOMETER_FW TRANSACTION_DATE_FW
DC19YTZ         11833       2020-02-08
DC19YTZ         0           2020-02-05
DC19YUA         14878       2020-02-06
DC19YUA         144754      2020-02-04
DC19YUB         10952       2020-02-07
DC19YUB         1007        2020-02-05
DC19YUB         10502       2020-02-03
DC19YUE         11792       2020-02-08
DC19YUE         11697       2020-02-05
DC19YUF         9080        2020-02-08
DC19YUF         8878        2020-02-06
DC19YUF         0           2020-02-03
DC19YUG         14578       2020-02-07
DC19YUG         14351       2020-02-05

Output:

VEHICLE_ID_FW   ODOMETER_FW TRANSACTION_DATE_FW
DC19YTZ         11833       2020-02-08
DC19YTZ         0           2020-02-05
DC19YUA         14878       2020-02-06
DC19YUA         0           2020-02-04
DC19YUB         10952       2020-02-07
DC19YUB         0           2020-02-05
DC19YUB         10502       2020-02-03
DC19YUE         11792       2020-02-08
DC19YUE         11697       2020-02-05
DC19YUF         9080        2020-02-08
DC19YUF         8878        2020-02-06
DC19YUF         0           2020-02-03
DC19YUG         14578       2020-02-07
DC19YUG         14351       2020-02-05
Ventre90
  • 57
  • 6