Removing values in two columns of a Pandas dataframe if row above have the same values

Question

With this sample pandas df:

ColA   ColB   ColC
Apple  Fruit  Food
Apple  Fruit  Pie
Apple  Arrow  Story

I am attempting to roll through the dataframe and if the values in ColA and ColB are the same in the current row as in the previous row, delete the current rows values for those two columns only.

The expected result would be:

ColA   ColB   ColC
Apple  Fruit  Food
              Pie
Apple  Arrow  Story

I attempted various loops with iloc grabbing the current rows values for those two columns, storing them in a variable and then checking if the subsequent row is identical. However, on my 5 row test data, kept getting error that the list index was out of range. Stumped at this point.

Is this format required by some other module? Because otherwise it seems like a less than ideal way to flag duplicates. — ALollz
– ALollz, Commented Sep 9, 2021 at 17:46
@ALollz The actual data I have is the result of some pivoting, with total and sub total rows, but to convert the multi index those operations produced, it returns a dataframe with all these rows, in the first two columns, duplicated with eventually a row that is different whose contents I want to retain, etc. — John Taylor
– John Taylor, Commented Sep 9, 2021 at 17:48

not_speshal · Accepted Answer · 2021-09-09 20:31:22Z

1

Try:

df[["ColA", "ColB"]] = df[["ColA", "ColB"]].where(~df.duplicated(["ColA", "ColB"]), "")

>>> df
    ColA   ColB   ColC
0  Apple  Fruit   Food
1                  Pie
2  Apple  Arrow  Story

If your data is not sorted by ColA and ColB and you want to only delete duplicates in consecutive rows, use:

df[["ColA", "ColB"]] = df[["ColA", "ColB"]].where(df[["ColA", "ColB"]].ne(df[["ColA", "ColB"]].shift()).any(1), "")

edited Sep 9, 2021 at 20:31

answered Sep 9, 2021 at 17:45

not_speshal

23.2k2 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Removing values in two columns of a Pandas dataframe if row above have the same values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related