how do I remove rows with duplicate values of columns in pandas data frame?

Question

I have a pandas data frame which looks like this.

  Column1  Column2 Column3
0     cat        1       C
1     dog        1       A
2     cat        1       B

I want to identify that cat and bat are same values which have been repeated and hence want to remove one record and preserve only the first record. The resulting data frame should only have.

  Column1  Column2 Column3
0     cat        1       C
1     dog        1       A

I am looking for something that will match the values in the two particular columns and then drop not for the entire data frame @piRSquared — Sayonti
– Sayonti, Commented Jun 16, 2018 at 5:04
you need keep='first' which is the default. keep=False is wrong — piRSquared
– piRSquared, Commented Jun 16, 2018 at 5:14
It returns as expected and yes it needs keep='first pandas.pydata.org/pandas-docs/stable/generated/… Also, you are using duplicated which only keeps duplcates, instead need drop_duplicates. pandas.pydata.org/pandas-docs/stable/generated/… — niraj
– niraj, Commented Jun 16, 2018 at 5:15

niraj · Accepted Answer · 2018-06-16 05:29:28Z

96

Using drop_duplicates with subset with list of columns to check for duplicates on and keep='first' to keep first of duplicates.

If dataframe is:

df = pd.DataFrame({'Column1': ["'cat'", "'toy'", "'cat'"],
                   'Column2': ["'bat'", "'flower'", "'bat'"],
                   'Column3': ["'xyz'", "'abc'", "'lmn'"]})
print(df)

Result:

  Column1   Column2 Column3
0   'cat'     'bat'   'xyz'
1   'toy'  'flower'   'abc'
2   'cat'     'bat'   'lmn'

Then:

result_df = df.drop_duplicates(subset=['Column1', 'Column2'], keep='first')
print(result_df)

Result:

  Column1   Column2 Column3
0   'cat'     'bat'   'xyz'
1   'toy'  'flower'   'abc'

answered Jun 16, 2018 at 5:29

niraj

18.2k4 gold badges36 silver badges50 bronze badges

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

Comments

zafrin · Accepted Answer · 2018-06-16 05:26:54Z

9

import pandas as pd

df = pd.DataFrame({"Column1":["cat", "dog", "cat"],
                    "Column2":[1,1,1],
                    "Column3":["C","A","B"]})

df = df.drop_duplicates(subset=['Column1'], keep='first')
print(df)

answered Jun 16, 2018 at 5:26

zafrin

4644 silver badges11 bronze badges

3 Comments

Jay Dangar Over a year ago

add 'Column2' as well inside subset parameter.

Narendra Jadhav Over a year ago

While this code snippet may be the solution, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

zafrin Over a year ago

I agree. I will try to do that. Thanks, Narendra!

Mohammed Rashad · Accepted Answer · 2022-04-08 14:52:23Z

2

Use drop_duplicates() by using column name

import pandas as pd
data = pd.read_excel('your_excel_path_goes_here.xlsx')
#print(data)
data.drop_duplicates(subset=["Column1"], keep="first")

keep=first to instruct Python to keep the first value and remove other columns duplicate values.

keep=last to instruct Python to keep the last value and remove other columns duplicate values.

Suppose we want to remove all duplicate values in the excel sheet. We can specify keep=False

answered Apr 8, 2022 at 14:52

Mohammed Rashad

2242 silver badges9 bronze badges

Comments

Mr. T · Accepted Answer · 2018-06-16 09:14:07Z

Inside the drop_duplicates() method of Dataframe you can provide a series of column names to eliminate duplicate records from your data.

The following "Tested" code does the same :

import pandas as pd

df = pd.DataFrame()
df.insert(loc=0,column='Column1',value=['cat',     'toy',    'cat'])
df.insert(loc=1,column='Column2',value=['bat',    'flower',  'bat'])
df.insert(loc=2,column='Column3',value=['xyz',     'abc',    'lmn'])

df = df.drop_duplicates(subset=['Column1','Column2'],keep='first')
print(df)

Inside of the subset parameter, you can insert other column names as well and by default it will consider all the columns of your data and you can provide keep value as :-

first : Drop duplicates except for the first occurrence.
last : Drop duplicates except for the last occurrence.
False : Drop all duplicates.

Collectives™ on Stack Overflow

how do I remove rows with duplicate values of columns in pandas data frame?

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related