Pyspark drop_duplicates(keep=False)

Question

i need a Pyspark solution for Pandas drop_duplicates(keep=False). Unfortunately, the keep=False option is not available in pyspark...

Pandas Example:

import pandas as pd

df_data = {'A': ['foo', 'foo', 'bar'], 
         'B': [3, 3, 5],
         'C': ['one', 'two', 'three']}
df = pd.DataFrame(data=df_data)
df = df.drop_duplicates(subset=['A', 'B'], keep=False)
print(df)

Expected output:

     A  B       C
2  bar  5  three

A conversion .to_pandas() and back to pyspark is not an option.

Thanks!

Groupby ['A', 'B'], get the count of groups, and remove all groups with size > 1. Not sure how to do this with pyspark but that's how I'd do it in the absence of keep=False. — cs95
– cs95, Commented Jan 9, 2019 at 18:49
Possible duplicate of Remove all rows that are duplicates with respect to some rows — pault
– pault, Commented Jan 9, 2019 at 23:02

akuiper · Accepted Answer · 2019-01-09 19:06:19Z

3

Use window function to count the number of rows for each A / B combination, and then filter the result to keep only rows that are unique:

import pyspark.sql.functions as f

df.selectExpr(
  '*', 
  'count(*) over (partition by A, B) as cnt'
).filter(f.col('cnt') == 1).drop('cnt').show()

+---+---+-----+
|  A|  B|    C|
+---+---+-----+
|bar|  5|three|
+---+---+-----+

Or another option using pandas_udf:

from pyspark.sql.functions import pandas_udf, PandasUDFType

# keep_unique returns the data frame if it has only one row, otherwise 
# drop the group
@pandas_udf(df.schema, PandasUDFType.GROUPED_MAP)
def keep_unique(df):
    return df.iloc[:0] if len(df) > 1 else df

df.groupBy('A', 'B').apply(keep_unique).show()
+---+---+-----+
|  A|  B|    C|
+---+---+-----+
|bar|  5|three|
+---+---+-----+

edited Jan 9, 2019 at 19:06

answered Jan 9, 2019 at 19:00

akuiper

216k33 gold badges362 silver badges379 bronze badges

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

Comments

Amit Joshi · Accepted Answer · 2020-10-06 11:34:52Z

0

Simple way is to count such rows and then select only those with have one occurrence to avoid any row from duplicates and then drop the extra column.

df= df.groupBy('A', 'B').agg(f.expr('count(*)').alias('Frequency'))
df=df.select('*').where(df.Frequency==1)
df=df.drop('Frequency')

edited Oct 6, 2020 at 11:34

Amit Joshi

16.6k23 gold badges88 silver badges154 bronze badges

answered Oct 6, 2020 at 11:32

Surbhi Jain

11 bronze badge

Collectives™ on Stack Overflow

Pyspark drop_duplicates(keep=False)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related