Folding multiple columns by MULTIPLE patterns

Luciana
Luciana Registered Posts: 1

Hello, I have a large number of columns that all follow the same pattern, with 3 parts that can change: Path#_Field_Year

So either the Path #, Field, or Year could vary. For example, I could have:

1_Car_2030	1_Car_2031	1_Car_2032	2_Car_2030	2_Car_2031	2_Car_2032

Or I could have:

2_Bus_2040      2_Bus_2041      2_Bus_2042      5_Bus_2030      5_Bus_2031      5_Bus_2032

I want to transpose or "fold" the columns into rows, but I still want to have columns for the Path #, Field, and Year so I can identify the different combinations. The VALUE column would just equal the original number associated with the Path#_Field_Year combination as below:

Path #

Field

Year

VALUE

1

Car

2030

435

1

Car

2031

44

1

Car

2032

53

2

Car

2030

742

2

Car

2031

7

2

Bus

2032

32

etc

etc

etc

etc

I found the article below that would help me if I only had ONE pattern, but I have multiple patterns to deal with.

Thank you in advance - I'm a relatively new user and very much appreciate the power of Dataiku when it comes to wrangling data.

https://doc.dataiku.com/dss/latest/preparation/processors/fold-columns-by-pattern.html

Operating system used: Windows

Operating system used: Windows

Answers

Setup Info
    Tags
      Help me…