Get all the rows which have mismatch between values in columns in pyspark dataframe

user_14353779 0
Viewed 96

I have the following pyspark dataframe df1 :-

SL No category 1 category 2
1 Apples Oranges
2 Apples APPLE FRUIT
3 Grapes Grape
4 Bananas Oranges
5 Orange Grape

I want to get the rows of the pyspark dataframe where the column values are not matching b/w columns category 1 and category 2 handling for case partial string match(category 1 contains only Apples/Bananas/Orange/Grapes strings and likewise Category 2 only contains only those distinct strings under Category 2) :-

SL No category 1 category 2
1 Apples Oranges
4 Bananas Oranges
5 Orange Grape
pyspark
1 Answers

First, please avoid column names with spaces.

My df

df=spark.createDataFrame([(1,   'Apples',   'Oranges'),
(2, 'Apples',   'APPLE FRUIT'),
(3, 'Grapes',   'Grape'),
(4, 'Bananas',  'Oranges'),
(5, 'Orange',   'Grape')],
('SL No',   'category1 ',   'category 2'))

df.show()



   new =(
    #Make string columns have a common case and put them into a comma separated array
     df.select('*',*[split(initcap(F.col(c)),'\s').alias(c+f'{"_1"}') for c in df.drop('SL No').columns])
    #Filter the non wanted using a higher order functio
     .filter(expr("filter(category1_1, (x,i)->rlike(x,category2_1[i]))")[0].isNull())
    #Drop columns not wanted
     .drop('category1_1','category2_1')
     ).show()

+-----+---------+---------+
|SL No|category1|category2|
+-----+---------+---------+
|    1|   Apples|  Oranges|
|    4|  Bananas|  Oranges|
|    5|   Orange|    Grape|
+-----+---------+---------+
user_89869750
Related