How do I sort hierarchical data in Palantir?

user_5804800 0
Viewed 67

Lets say I have flight data (from Foundry Academy).

Starting dataset:

Date flight_id origin_state carrier_name
jan 000000001 California delta air
jan 000000002 Alabama delta air
jan 000000003 California southwest
feb 000000004 California southwest
... ... ... ...

I'm doing monthly data aggregation by state and by carrier. Header of my aggregated data looks like this:

origin state carrier name jan feb ...
Alabama delta air 1 0 ...
California delta air 1 0 ...
California southwest 1 1 ...

I need to get subtotals for each state; I need to sort by most flights; and I want it to be sorted by states, then by carrier.

desired output

origin state carrier name jan feb ...
California null 2 1 ...
California delta air 1 0 ...
California southwest 1 1 ...
Alabama null 1 0 ...
Alabama delta air 1 0 ...

PIVOT - doesn't provide subtotals for categories;

EXPRESSION - doesn't offer possibility to split date column into columns.

palantir-foundry hierarchical-data
1 Answers

I solved it on Contour. not the prettiest solution, but it works.

I've created two paths to the same dataset:

| Date | flight_id | origin_state | carrier_name |
| ---- | --------- | ------------ | ------------ |
| ...  | ...       | ...          | ...          |

1st path was used to calculate full aggregation. pivot table and switch to pivoted data:

Switch to pivoted data: using column "date", 
grouped by "origin_state" and "carrier_name",
aggregated by Count

2nd path was used to get subtotals:

Switch to pivoted data: using column "date", 
grouped by "origin_state",
aggregated by Count

Afterwards I've added empty column "carrier_name" to second dataset. And made union of both datasets

Add rows that appear in "second_path" by column name

After that I've added additional column with expression

Add new column "order" from max("Jan") OVER (
PARTITION BY "origin_state" )

After that I sorted resulting dataset.

Sort dataset by "order" descending, then by "Jan" descending

I received result. but it has additional column, and now I wish to change row formatting of subtotals.

Other approaches are welcome. as my real data has more hierarchical levels.

user_58048000
Related