How to Reverse arrangement DataFrame in Apache Spark

Question

How can I reverse this DataFrame using Scala. I saw sort functions but must specific column, I only want to reverse them

+---+--------+-----+
|id |    name|note |
+---+--------+-----+
|1  | james  |any  |
|3  | marry  |some |
|2  | john   |some |
|5  | tom    |any  |
+---+--------+-----+

to:

+---+--------+-----+
|id |    name|note |
+---+--------+-----+
|5  | tom    |any  |
|2  | john   |some |
|3  | marry  |some |
|1  | james  |any  |
+---+--------+-----+

Do note that in most cases there is no guranteed order of the dataframe which is why you won't find any operation such as reverse (if you sort by a column it's no problem however). See e.g.: https://issues.apache.org/jira/browse/SPARK-16207 — Shaido, Jul 30 '19 at 09:04

score 6 · Accepted Answer · answered Jul 30 '19 at 06:39

You can add a column with increasing id with use of monotonically_increasing_id() and sort in descending order

val dff = Seq(
  (1, "james", "any"),
  (3, "marry", "some"),
  (2, "john", "some"),
  (5, "tom", "any")
).toDF("id", "name", "note")

dff.withColumn("index", monotonically_increasing_id())
  .sort($"index".desc)
  .drop($"index")
  .show(false)

Output:

+---+-----+----+
|id |name |note|
+---+-----+----+
|5  |tom  |any |
|2  |john |some|
|3  |marry|some|
|1  |james|any |
+---+-----+----+

score -1 · Answer 2 · answered Jul 30 '19 at 06:28

-1

You could do something like this:

val reverseDf = df.withColumn("row_num", row_number.over(Window.partitionBy(lit(1)).orderBy(lit(1))))
                .orderBy($"row_num".desc)
                .drop("row_num")

Or refer this instead of row number.

answered Jul 30 '19 at 06:28

wypul

807
6
9

1

It won't work for a large dataset (that's larger than the RAM of the most powerful executor) since it leads to OOME. – Jacek Laskowski Jul 30 '19 at 19:11

How to Reverse arrangement DataFrame in Apache Spark

2 Answers2