Pyspark: Add new Column contain a value in a column counterpart another value in another column that meets a specified condition

Question

Add new Column contain a value in a column counterpart another value in another column that meets a specified condition For instance, original DF as follows:

    +-----+-----+-----+
    |col1 |col2 |col3 |
    +-----+-----+-----+
    |    A|   17|    1|
    |    A|   16|    2|
    |    A|   18|    2|
    |    A|   30|    3|
    |    B|   35|    1|
    |    B|   34|    2|
    |    B|   36|    2|
    |    C|   20|    1|
    |    C|   30|    1|
    |    C|   43|    1|
    +-----+-----+-----+

I need to repeat the value in col2 that counterpart to 1 in col3 for each col1's groups. and if there are more value =1 in col3 for any group from col1 repeat the minimum value the desired Df as follows:

    +----+----+----+----------+
    |col1|col2|col3|new_column|
    +----+----+----+----------+
    |   A|  17|   1|        17|
    |   A|  16|   2|        17|
    |   A|  18|   2|        17|
    |   A|  30|   3|        17|
    |   B|  35|   1|        35|
    |   B|  34|   2|        35|
    |   B|  36|   2|        35|
    |   C|  20|   1|        20|
    |   C|  30|   1|        20|
    |   C|  43|   1|        20|
    +----+----+----+----------+

Your question assumes that the rows are in order. SQL tables represent *unordered* sets. You need a column that specifies the ordering. If not, then SQL is not the appropriate tool for this operation. — Gordon Linoff, Apr 04 '18 at 11:42

score 1 · Accepted Answer · answered Apr 05 '18 at 02:00

df3=df.filter(df.col3==1)

+----+----+----+
|col1|col2|col3|
+----+----+----+
|   B|  35|   1|
|   C|  20|   1|
|   C|  30|   1|
|   C|  43|   1|
|   A|  17|   1|
+----+----+----+


df3.createOrReplaceTempView("mytable")

To obtain minimum value of col2 I followed the accepted answer in this link How to find exact median for grouped data in Spark

df6=spark.sql("select col1, min(col2) as minimum from mytable group by col1 order by col1")

df6.show()
+----+-------+
|col1|minimum|
+----+-------+
|   A|     17|
|   B|     35|
|   C|     20|
+----+-------+

df_a=df.join(df6,['col1'],'leftouter')

+----+----+----+-------+
|col1|col2|col3|minimum|
+----+----+----+-------+
|   B|  35|   1|     35|
|   B|  34|   2|     35|
|   B|  36|   2|     35|
|   C|  20|   1|     20|
|   C|  30|   1|     20|
|   C|  43|   1|     20|
|   A|  17|   1|     17|
|   A|  16|   2|     17|
|   A|  18|   2|     17|
|   A|  30|   3|     17|
+----+----+----+-------+

Is there way better than this solution?

Pyspark: Add new Column contain a value in a column counterpart another value in another column that meets a specified condition

1 Answers1