In the ascending order, data is sorted from minimum to maximum age so we can keep the last value and remove other data rows. In the following code, we sort the age values in ascending order using data.sort_values() function. In this case, the row with age 22 for Rohan should be removed.įor this requirement, we use an additional Python script function sort_values(). We want to remove the row with a minimum age. Both rows look similar however, one row shows age 22 while another row has age value 23. For example, Rohan has two entries in this sheet. We want to remove the duplicate values but keep the row that has maximum age value for an employee. Someone has entered the wrong age for the employees. Now, suppose we have a new column Age in the excel sheet. The script works, and we get unique rows in the output.Įxample 3: Remove duplicates by keeping the maximum and minimum value To test the above code, let’s add a new row in the excel and execute the above code. In the output, we do not get any row because we do not have any unique rows in the excel sheet. drop_duplicates ( subset =, keep = False ) It is the default behaviors so that we can exclude this parameter here as well.ĭata. We also specify another argument keep=first to instruct Python to keep the first value and remove further duplicates. It excludes the remarks column in this case. Pandas check for these columns and remove the duplicate values. In the following Python scripts, we specify column names in the subset argument. We have few records in our excel sheet that contains more duplicate values if we do not consider the remarks column. It means if the row contains similar values in the rest of the columns, it should be a duplicate row. Suppose, we want to exclude Remarks columns for checking duplicates.

It retains the first value and removes further duplicates.Įxample 2: Use drop_duplicates() along with column namesīy default, Pandas creates a data frame for all available columns and checks for duplicate data. It is the default behavior of the drop_duplicate() function. In the output, we can see that it removes rows with index id 1,5 and 7. Python display NaN for the cells that do not have any value/text. In this data, few columns contain NaN in the remarks column. Here, the print statement prints the data frame that consists of excel sheet data.įirst, we import the pandas library to read and write the excel sheets. Create a new code block in SQL Notebook and execute the code. We use the pandas read_excel() function to import an excel file. Here, we can see both pandas and NumPy package along with pip utility. Once you click on Manage Packages, it gives you a list of installed packages. You can click on Manage Extensions in Azure Data Studio for it. Launch SQL Notebook in Azure Data Studio and verify pandas, NumPy packages existence. Python scripts for removing duplicates in an excelīefore we start with Python, make sure you run through the pre-requisites specified in the article, Python scripts to format data in Microsoft Excel. Let’s look at the Python way of handling duplicate data in excel. Our script should be capable of handling such duplicate data and remove per our requirements such as remove all duplicates, remove all but the last duplicate, remove all but first duplicate. If that excel contains duplicate values, we might not want to use Excel functionalities for it. Suppose you are working in excel using Python language. We have the following data after removing duplicates from this. Let’s click on Remove Duplicates and select all columns.Ĭlick ok, and it removes the duplicate values 3 duplicate values and retains 5 unique values. This option checks duplicate values and retains the FIRST unique value and removes other values. In Microsoft Excel, we use the Remove Duplicates button from the Data menu. We want to get rid of duplicate values in this sheet. Suppose we have the following data in an excel sheet. In this article, we will look at removing duplicate data from excel using the Python.Ī quick recap of removing duplicate rows in Microsoft Excel

You can go through various use cases of Python on SQLShack. Python is an interesting high-level programming language. In the article, Python scripts to format data in Microsoft Excel, we used Python scripts for creating an excel and do various data formatting.

Dropping duplicate rows in pandas