Drop spark dataframe duplicates keeping into account possible columns permutation

Viewed 69

Hello i have a dataframe which has the following structure

df = sqlContext.createDataFrame([
("x1","y1","z","w1"),
("y1","x1","w1","z"),
("y3","x1","w3","z"),
("y4","x2","w4","q"),
("x2","y5","q","p1"),
("x2","y4","q","w4"),
], ["test1_1", "test2_1","test1_2","test2_2"])

+-------+-------+-------+-------+
|test1_1|test2_1|test1_2|test2_2|
+-------+-------+-------+-------+
|x1     |y1     |z      |w1     |
|y1     |x1     |w1     |z      |
|y3     |x1     |w3     |z      |
|y4     |x2     |w4     |q      |
|x2     |y5     |q      |p1     |
|x2     |y4     |q      |w4     |
+-------+-------+-------+-------+  

Now i'd like to remove duplicates based on possible permutations of columns test1_2, test2_2 and keeping the order relationship between test1_1-test1_2 and test2_1-test2_2 and having the same value on the same column.

Target output:

+-------+-------+-------+-------+
|test1_1|test2_1|test1_2|test2_2|
+-------+-------+-------+-------+
|x1     |y1     |z      |w1     |
|x1     |y3     |z      |w3     |
|x2     |y4     |q      |w4      
|x2     |y5     |q      |p1     |
+-------+-------+-------+-------+  

so far this is what i've done:
First i've concatenated test1_1-test1_2 and test2_1-test2_2 to preserve the order:

df=df.withColumn('test1_2_1_1',F.concat_ws(';',F.col('test1_2'),F.col('test1_1')))
df=df.withColumn('test2_2_2_1',F.concat_ws(';',F.col('test2_2'),F.col('test2_1')))

i then rearranged the columns to drop duplicates:

df= df.select(F.least(df.test1_2_1_1,df.test2_2_2_1).alias('test1_2_1_1'),F.greatest(df.test1_2_1_1,df.test2_2_2_1).alias('test2_2_2_1'))
df=df.dropDuplicates(subset=['test1_2_1_1','test2_2_2_1'])

i can now split the strings again:

df=df.withColumn("test1_2", F.split(F.col("test1_2_1_1"), ";").getItem(0)).withColumn("test1_1", F.split(F.col("test1_2_1_1"), "-").getItem(1))
df=df.withColumn("test2_2", F.split(F.col("test2_2_2_1"), ";").getItem(0)).withColumn("test2_1", F.split(F.col("test2_2_2_1"), "-").getItem(1))

while this correctly removes duplicates it changes the order of the columns based on the greatest/least criteria on strings, therefore at this stage i have the following dataframe, which is not the desidered output:

+-------+-------+-------+-------+
|test1_1|test2_1|test1_2|test2_2|
+-------+-------+-------+-------+
|y1     |x1     |w1     |z      |
|y3     |x1     |w3     |z      |
|x2     |y4     |q      |w4     | 
|y5     |x2     |p1     |q      |
+-------+-------+-------+-------+  
  1. Is there a better way so far to remove those duplicates?
  2. How do i go about reordering the values in the columns test1_2 and test2_2 to have the same value in the same column (doesn't matter which column) while keeping the correct pair with test1_1 and test2_1? see output dataframe above for a practical example
0 Answers
Related