Rearranging Columns in Descending Order using Pyspark

user_17682024 0
Viewed 102

Hi I have an issue automatically rearranging columns in a spark dataframe using Pyspark.

I'm currently summarizing the dataframe according to the aggregation below:

df_agg = df.agg(*[sum(col(c)).alias(c) for c in df.columns])

This results in a summarized table looking something like this (but with hundreds of columns):

col_1 col_2 col_3 ... col_N
73 55 983 ... integer

I would like to automatically rearrange my columns so they are in descending order resulting in a data frame (using the example table above):

col_3 col_1 col_2 ... col_N
983 73 55 ... integer (<55)

Very thankful for all your support!

Best regards

pyspark apache-spark-sql apache-spark python
3 Answers

If you can afford converting to pandas, you can do this:

cols = df_agg.toPandas().T.sort_values(by=0, ascending=False).index.tolist()
df_agg.select(cols).show()
user_46489690

dict to the rescue.

df = spark.createDataFrame(
    [
       (1, 72, 5, -5)
    ],
    ["c_1", "c_2", "c_3", "c_4"]  
)
dict = list(map(lambda row: row.asDict(), df.collect()))[0]
sorted_dict={k: v for k, v in sorted(dict.items(), key=lambda item: item[1], reverse=True)}
sorted_cols = [c for c in sorted_dict.keys()]
df.select(sorted_cols).show()

returns:

+---+---+---+---+
|c_4|c_1|c_3|c_2|
+---+---+---+---+
| 72|  5|  1| -5|
+---+---+---+---+

Could do via to rdd conversion, sort and back to dataframe.

user_69339930

A slight variation to thebluephantom's solution. You can use python's built-in sorted() within the RDD map.

Given a dataframe (say, data_sdf) like following.

+----+----+----+----+----+----+----+----+----+----+
|col0|col1|col2|col3|col4|col5|col6|col7|col8|col9|
+----+----+----+----+----+----+----+----+----+----+
|  64|  63|  44|  71|  97|  99|  41|  40|  30|  42|
+----+----+----+----+----+----+----+----+----+----+

Use the RDD Row.asDict() and sorted within that to sort the columns based on values. It's not straightforward as the row-dict is first converted to list of tuples. The list of tuples is sorted and converted back to a dict. Lastly retain the dict-keys as a list.

sorted_cols = data_sdf.rdd. \
    map(lambda r: list(dict(sorted(r.asDict().items(), key=lambda t: t[1], reverse=True)).keys())). \
    collect()[0]

data_sdf.select(*sorted_cols).show()

# +----+----+----+----+----+----+----+----+----+----+
# |col5|col4|col3|col0|col1|col2|col9|col6|col7|col8|
# +----+----+----+----+----+----+----+----+----+----+
# |  99|  97|  71|  64|  63|  44|  42|  41|  40|  30|
# +----+----+----+----+----+----+----+----+----+----+

print(sorted_cols)
# ['col5',
#  'col4',
#  'col3',
#  'col0',
#  'col1',
#  'col2',
#  'col9',
#  'col6',
#  'col7',
#  'col8']
user_82795850
Related