Find duplicates with groupby in Pandas

Question

I read a csv file using Pandas. Then, I am checking to see if there are any duplicate rows in the data using the code below:

import pandas as pd

df= pd.read_csv("data.csv", na_values=["", " ", "-"])

print df.shape
>> (71644, 15)

print df.drop_duplicates().shape
>> (31171, 15)

I find that there are some duplicate rows, so I want to see which rows appear more than once:

data_groups = df.groupby(df.columns.tolist())
size = data_groups.size()
size[size > 1]

Doing that I get Series([], dtype: int64).

Futhermore, I can find the duplicate rows doing the following:

duplicates = df[(df.duplicated() == True)]

print duplicates.shape
>> (40473, 15)

So df.drop_duplicates() and df[(df.duplicated() == True)] show that there are duplicate rows but groupby doesn't.

My data consist of strings, integers, floats and nan.

Have I misunderstood something in the functions I mention above or something else happens ?

Answer 1

Simply add the reset_index() to realign aggregates to a new dataframe.

Additionally, the size() function creates an unmarked 0 column which you can use to filter for duplicate row. Then, just find length of resultant data frame to output a count of duplicates like other functions: drop_duplicates(), duplicated()==True.

data_groups = df.groupby(df.columns.tolist())
size = data_groups.size().reset_index() 
size[size[0] > 1]        # DATAFRAME OF DUPLICATES

len(size[size[0] > 1])   # NUMBER OF DUPLICATES