Python Integration to SAS® Viya® - Part 20 - Remove Duplicate Rows

Welcome to the continuation of my series Getting Started with Python Integration to SAS Viya. In this post I'll discuss how to remove duplicate rows from a distributed CAS table using the both the Pandas API in the SWAT package and the native CAS action. The Pandas API drop_duplicates method was introduced to the SWAT package in version 1.13.1.

Load and prepare data in the CAS server

For this example, I'll use the same data from the pandas drop_duplicate method documentation. I'll use the small table for training purposes. Remember, the distributed CAS server is typically reserved for larger data.

To create the CAS table for this post, run the code found in the block below. It creates a client-side DataFrame and uses the upload_frame SWAT method to load the DataFrame to the CAS server as a distributed table named DUP_TBL and places it in the Casuser caslib. The upload_frame method returns a client-side reference to the CAS table and stores it in the variable castbl. Make sure to add your CAS connection information in the CAS method.

## Packages
import swat
import pandas as pd

## Connect to the CAS server
conn = swat.CAS(/*Enter your connection information */)

## Create the local DataFrame
df = pd.DataFrame({
'brand': ['Yum Yum', 'Yum Yum', 'Indomie', 'Indomie', 'Indomie'],
'style': ['cup', 'cup', 'cup', 'pack', 'pack'],
'rating': [4, 4, 3.5, 15, 5]
})

# Load the DataFrame to CAS as a distributed CASTable
castbl = conn.upload_frame(df, casout = {'name':'dup_tbl',
'caslib':'casuser',
'replace':True})

## View the value of castbl and view rows from the CAS table
display(type(castbl), castbl.head())

## and the results
NOTE: Cloud Analytic Services made the uploaded file available as table DUP_TBL in caslib CASUSER(Peter).
NOTE: The table DUP_TBL has been created in caslib CASUSER(Peter) from binary data uploaded to Cloud Analytic Services.
swat.cas.table.CASTable

The results show the DataFrame was uploaded to the distributed CAS server successfully, and a client-side reference has been made to the CAS table in the variable castbl.

Using the drop_duplicates method in the SWAT package

Starting in SWAT 1.13.1 you can use the drop_duplicates method on a CAS table. The method works similarly to the pandas drop_duplicates method. However, there is a new parameter when using the SWAT method.

Remove duplicate rows based on all columns

For example, what if you want to remove all duplicate rows based on all columns and create a new CAS table with the results? All you need to do is specify the CAS table, then the drop_duplicates method. The biggest change in the SWAT drop_duplicates method is that you need to add the casout parameter to specify output CAS table information. Lastly, I'll add the tableInfo action to view available in-memory tables in the Casuser caslib.

no_dup_castbl = castbl.drop_duplicates(casout = {'name':'drop_dups', 'caslib':'casuser'})

conn.tableInfo(caslib = 'casuser')

The results show that the drop_duplicates method created a new CAS table named DROP_DUPS in the Casuser caslib. The new table has four rows, removing the one entirely duplicated row from the original table.

The drop_duplicates method returns a client-side reference to the new CAS table. To work with the new CAS table, execute methods on the no_dup_castbl CASTable object. Here I'll execute the SWAT head method to preview the CAS table.

no_dup_castbl.head()

The results show that the duplicate row was removed.

Remove duplicate rows based on specific columns

The SWAT drop_duplicates method also enables you to drop duplicate rows based on specific columns like its pandas counterpart. Simply add a column (or list of columns) to the subset parameter. Then use the casout parameter to create a new CAS table named DROP_DUPS_SUBSET in the Casuser caslib. Finally, run the head method to preview the new CAS table.

no_dup_subset = castbl.drop_duplicates(subset=['brand','style'],
casout={'name':'drop_dups_subset', 'caslib':'casuser'})

no_dup_subset.head()

The results show that duplicate rows based on the columns brand and subset were removed. As of SWAT version 1.13.1 you cannot specify which rows to output using the keep parameter.

Using the native CAS action to remove duplicate rows

You can also use the native deduplication.deduplicate CAS action to remove duplicate rows. To use the action you first have to load the deduplication action set.

conn.loadActionSet('deduplication')

Next, use the deduplicate action within the action set to remove duplicate rows. In the deduplicate action you must specify which columns to use to determine duplicate rows. This is done by adding the columns in the groupby parameter of the CASTable object. In this example, I'll create a list of all the columns in the CAS table and add that to the groupby parameter. Then, in the action you specify the noDupKeys parameter to eliminate rows that have duplicate group-by variable values. Only one row per group is retained. Further, add the casout parameter to specify the name and location of the new distributed CAS table.

colNames = castbl.columns.to_list()
castbl.groupby = colNames
castbl.deduplicate(noDupkeys = True,
casout = {'name':'no_dup_rows_action',
'caslib':'casuser'})

## and the results
NOTE: There were 5 rows read from the table DUP_TBL.
NOTE: The table no_dup_rows_action has 4 rows and 3 columns.

Lastly, I'll run the tableInfo action to view available CAS tables.

conn.tableInfo(caslib = 'casuser')

The results show that the new CAS table NO_DUP_ROWS_ACTION was created and removed the single duplicate row. To use the new table simply create a client-side reference to it using the swat.CASTable method.

Summary

The SWAT package blends the world of pandas and CAS to process your distributed data. In this example, I focused on showing both the Pandas API drop_duplicate method and the deduplicate CAS action from the SWAT package to remove duplicate rows.

Additional and related resources

Getting Started with Python Integration to SAS® Viya® - Index

SWAT API Reference

deduplication.deduplicate CAS action

SAS® Cloud Analytic Services: Fundamentals

CAS Action Documentation

CAS Action! - a series on fundamentals

SAS Course - SAS® Viya® and Python Integration Fundamentals

Python Integration to SAS® Viya® - Part 20 - Remove Duplicate Rows was published on SAS Users.