1
$\begingroup$

I'm trying to find a way in Python in which to drop rows where duplicates occur within specific columns, but only to drop those duplicates where they are not attributed to the latest date.

In the example below I want to drop rows where 'CODE' and 'BC' match, but only when they are not the most recent date. If 'CODE' and 'BC' match and both have the same date, then rows with the lowest 'ID' number would be dropped instead.

CODE BC DATE ID
12345 567 01/01/2017 111
12345 567 01/01/2017 111
12345 567 10/01/2018 555
12345 567 10/01/2018 555
12345 789 16/03/2017 777
12345 789 17/09/2021 888
23456 354 21/10/2020 333
23456 354 21/10/2020 444

Ideal Outcome:

CODE BC DATE ID
12345 567 10/01/2018 555
12345 567 10/01/2018 555
12345 789 17/09/2021 888
23456 354 21/10/2020 444

Thanks

$\endgroup$
2
  • $\begingroup$ In the Ideal Outcome, why do you have two rows with 555?? $\endgroup$ Dec 10, 2021 at 12:43
  • $\begingroup$ The ID can be assigned to multiple rows in the data base. Aslong as it's the latest date I want ALL the rows which have the latest date to feature. The Date should be used as the main condition over ID. As rows 555 have both have the most recent date for that BC and have the same ID they both remain $\endgroup$
    – Oli Dewes
    Dec 10, 2021 at 13:04

1 Answer 1

1
$\begingroup$

I think the following should do what you are looking for. It first counts the number of rows based on the CODE and BC columns to check if it is a duplicate. In addition, it checks if the ID is equal to the highest ID within the group (instead of looking at the latest date, as this would give an extra row for BC 354). The dataframe can then be filter down to only select the rows (and columns) you are looking for.

(
    df
    .assign(
        count = lambda x: x.groupby(["CODE", "BC"])["ID"].transform("count"),
        id_max = lambda x: x["ID"] == x.groupby(["CODE", "BC"])["ID"].transform("max")
    )
    .loc[lambda x: (x["count"] == 1) | (x["id_max"]), ["CODE", "BC", "DATE", "ID"]]
)
CODE BC DATE ID
12345 567 10/01/2018 555
12345 567 10/01/2018 555
12345 789 17/09/2021 888
23456 354 21/10/2020 444
$\endgroup$
5
  • $\begingroup$ Thank you, This looks to have worked! I'm new to python so forgive me but how do you think would be best to validate this if the data set totals over 3m rows? $\endgroup$
    – Oli Dewes
    Dec 10, 2021 at 12:02
  • $\begingroup$ Just looked at some specific rows - it doesn't look as if it has fully done what is needed. I spotted at one point that the CODE and BC was the same but there were still two different dates attributed $\endgroup$
    – Oli Dewes
    Dec 10, 2021 at 12:11
  • $\begingroup$ @OliDewes In the case where there are two different date attributes, what IDs did those rows have? Based on the logic I would expect the IDs to be same, since the current logic selects the rows with the highest ID if it is considered a duplicate. Regarding validating it, first step would be to check if the code aligns with the logic you want. In addition, it's probably good to look at a few different scenarios you see in your data (e.g. no duplicate, duplicate with multiple IDs etc.), and see if the code gives the expected result for all scenarios. $\endgroup$
    – Oxbowerce
    Dec 10, 2021 at 13:08
  • $\begingroup$ So ID changes when Date changes too, but ID can also change when dates are the same if the change to a form was made twice on the same date. I would at that point want the largest ID to only include the most recent form change. $\endgroup$
    – Oli Dewes
    Dec 10, 2021 at 13:15
  • $\begingroup$ Indeed, that is also how I currently implemented the logic. See for example the last two rows of your input, both have the same CODE, BC, and DATE but a different ID, and the code only select the last row which has the highest ID. $\endgroup$
    – Oxbowerce
    Dec 10, 2021 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.