topic Dashboard use case - order of bars in Data Engineering

Dashboard use case - order of bars

Henrik_ — Mon, 02 Sep 2024 11:56:27 GMT

On a spark dataframe, is there any smart way to set the order of a categorical feature explicitly, equivalent to Categorical(ordered=list) in Pandas? The use case here is a dashboard in Databricks, and I want the bars to be arranged in certain order.

Re: Dashboard use case - order of bars

holly — Wed, 04 Sep 2024 14:15:34 GMT

Hi there, you can use a map function. Create a map with the creatively named create_map, and then sort by the values in the map.

The code will look sooooomething like this (although not tested this to take it as pseudo code)

from pyspark.sql.functions import create_map, lit, col

categories=['small', 'medium', 'large', 'xlarge']

map = create_map([val for (i, category_col) in enumerate(categories) for val in (category_col, lit(i))])

#gives <'map(small, 0, medium, 1, large, 2, xlarge, 3)'> 

display(df.orderBy(map[col('category_col')]))

Re: Dashboard use case - order of bars

Henrik_ — Tue, 03 Sep 2024 13:32:28 GMT

Thanks! One question, this code will order the whole dataframe based on the logic from create_map. However, I want to put on several figures, all with their own sorting logic, on display in a dashboard. I don' think this method will work for that use-case?

Re: Dashboard use case - order of bars

holly — Wed, 04 Sep 2024 14:25:35 GMT

Ah, I think I see. Let's say your dataset has category_col1 with {S, M, L, XL} values, then category_col2 with {XS, S M} and you want to sort the data by category_col1 and category_col2.

If you want to specify the order for the user, you can duplicate the create_map step with and make map_1 and map_2 and then order by two columns. You can do this as part of your pipeline and save the results to your table so it's not only available as part of the dataframe.

BUT

If you want the end user to be able to sort the end Databricks visualisation / table by clicking values that's something we don't have at the moment. I think it's a sensible ask so I'll raise this with our BI team.

Re: Dashboard use case - order of bars

Henrik_ — Wed, 04 Sep 2024 14:27:38 GMT

Thanks for your effort!