Tip: Using Regex to restore Column headers after GroupBy/Ungroup

Maybe this is known to most, but for me it was a struggle, so I thought I'd share this little tip:

When using the GroupBy node, the Header will contain things such as

  • List(....)
  • List*(...)
  • First(....)
  • etc.

Not sure if of interest to change the headers back to the original already after this step (though possible as well), but certainly after using Ungroup node one might want to restore original headers. There is a solution out with 2 or three nodes, saving the header names, but that is a bit unflexible imho, esp if one has added columns after GroupBy. Alt. one can of course just rename every column one by one (certainly unflexible).

With the node Column Rename (Regex), it can be done in one step. In Main Configuration tab (See also attached picture) write:

  • Search String (regexp): ^[^()]+\((.*)\)$
  • Replacement: $1

This should cover all of above, even if there are () inside the bracket, i.e.

List*(Myheader(mytext)) will give Myheader(mytext).

Of course I haven't covered all scenarious, but I think this solution covers a lot.  Hope this helps others. And please do leave comments/suggestions.

PS: no, I am not a regex expert, I had help from a colleague at work.

1 Like

Hi,

thanks for the useful tip. However the Column naming option of the GroupBy node lets you specify the pattern for the group column. It provides the following options

  • Aggregation method (column name) this is the default and results in names such as List(...)
  • Column name(aggregation method) results in names such as ...(List)
  • Keep original name(s) does not alter the column name at all but allows you to use each column only once

So if the node should retain the original column names simply select the Keep original name(s) option and you are done.

Bye,

Tobias

2 Likes

Seriously? Doooh, I missed that.

Much simpler. Of course....

 

1 Like

I was stuck on this exact issue for months!

@Docminus Thanks for taking the initiative to work out a regex solution and sharing it with the community (even if it wasn’t used in this case)!