How to use Range.RemoveDuplicates in the xlwings API way

The RemoveDuplicates method in the Range object is a powerful feature for data cleaning in Excel, allowing users to eliminate duplicate rows from a specified range based on selected columns. In xlwings, this functionality is accessible through the API, enabling automation of duplicate removal in workbooks directly from Python scripts. This method is particularly useful for preprocessing datasets before analysis, ensuring data integrity by retaining only unique entries.

Functionality
RemoveDuplicates identifies and removes duplicate rows within a range, keeping the first occurrence of each unique row. It operates on one or more columns as criteria for determining duplicates. For instance, if you specify a single column, rows with identical values in that column are considered duplicates; if multiple columns are selected, rows must match in all specified columns to be flagged as duplicates. The method modifies the range in place, deleting duplicate rows and shifting remaining cells up, similar to Excel’s built-in “Remove Duplicates” feature.

Syntax
In xlwings, the RemoveDuplicates method is called on a Range object. The syntax is as follows:

range_object.remove_duplicates(columns, header)

• columns: A list of integers representing the column indices within the range to check for duplicates. The indices are 1-based relative to the range, not the entire worksheet. For example, if the range starts at column A, [1] refers to column A within that range. Multiple columns can be specified, such as [1, 3] for the first and third columns of the range.
• header: An optional parameter that indicates whether the range has a header row. It accepts the xlYes, xlNo, or xlGuess constants from the xlwings.constants module. xlYes (or 1) treats the first row as a header and excludes it from duplicate checking. xlNo (or 2) includes all rows, including the first, in duplicate removal. xlGuess (or 0) lets Excel determine if a header exists based on the data. The default is xlNo.

Example Usage
Consider an Excel worksheet with data in columns A to C, where column A contains IDs, column B names, and column C values. To remove duplicate rows based on the ID column (column A), assuming the data starts at cell A1 and includes a header row, you can use the following xlwings code:

import xlwings as xw
from xlwings.constants import DeleteShiftDirection

# Connect to the active workbook
wb = xw.books.active
ws = wb.sheets['Sheet1']

# Define the range, e.g., A1:C10
data_range = ws.range('A1:C10')

# Remove duplicates based on the first column (ID) with a header
data_range.remove_duplicates(columns=[1], header=1) # header=1 corresponds to xlYes

In this example, columns=[1] specifies that duplicates should be identified using the first column of the range (column A). The header=1 parameter ensures the header row is not considered for duplicates. After execution, only rows with unique IDs remain in the range A1:C10, with other rows removed and cells shifted up.

For a more complex scenario, if you want to remove duplicates based on both ID and name (columns A and B), you can adjust the columns parameter:

# Remove duplicates based on columns A and B
data_range.remove_duplicates(columns=[1, 2], header=1)