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 |
+-------+-------+-------+-------+
- Is there a better way so far to remove those duplicates?
- 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