Split only some columns?

[woodsy-sounding-wilful-sapwood]

Say I have a data source like this:

Ticker,Name,Quarter,Result
A,A Inc,Q1,100
A,A Inc,Q2,200
A,A Inc,Q3,300
B,B Corp,Q1,500
B,B Corp,Q2,400

I would like to pivot this so the index/left-most columns are the ticker and name and the columns are Q1, Q2, Q3 etc

(Essentially df.pivot_table(index=['Ticker', 'Name'], columns='Quarter', values='Result'))

If I do group_by(['Ticker', 'Name']) and split_by(['Quarter']), perspective puts the name on a separate line which is a) annoying if there are multiple columns in the index and b) confusing because you lose the column name

If I do group_by(['Ticker']) and split_by(['Quarter']), perspective repeats the name for every quarter which is redundant.

Is there a way to say "group by ticker and split result by quarter but do not split name"?

[texodus]

How about using an expression column for your group_by:

{
    "group_by": ["concat(\"Ticker\",', ',\"Name\")"],
    "split_by": ["Quarter"],
    "columns": ["Result"],
    "expressions":["concat(\"Ticker\",', ',\"Name\")"]
}

[woodsy-sounding-wilful-sapwood]

This works in a pinch the problem is anyone who downloads the CSV has to then re-split ROOT_PATH and also the column names are lost. (So say I have a date_from and date_to column, I have to remember the order or put a label somewhere else on the page the table itself does not have the info)

Where in the source tree should I start if I want to add a custom field to sort_by? I am guessing this is handled in cpp?

[texodus]

The column names are "lost" but the expression column you create is named whatever you define it to be named, e.g.:

{
    "group_by": ["Ticker and Name"],
    "split_by": ["Quarter"],
    "columns": ["Result"],
    "expressions":["//Ticker and Name\nconcat(\"Ticker\",', ',\"Name\")"]
}

These should be preserved in the CSV output, in this case the column title is "Ticker and Name (group by 1)". __ROW_PATH__ (I assume what you mean by ROOT_PATH) has been deprecated in this format for some versions. This is the only option currently for this in-engine with the data configuration provided.

I don't understand your second point re: ordering or sorting custom fields, can you be more specific? Perspective does support sorting by hidden and/or expression columns.

[woodsy-sounding-wilful-sapwood]

So for example this table, the grouper is Ticker,Name,ShareCount,MarketValue

Because there are no column headers you can't tell at a glance which number is supposed to be ShareCount and which number is supposed to be MarketValue. I could add a label outside the table somewhere ("in the following table the first column shows x y and z in that order") but that's not ideal