Skip to main content
Solved

Transposing Data - Making Rows into Columns and Naming Columns Based on Attributes

  • June 9, 2022
  • 6 replies
  • 185 views
R
Forum|alt.badge.img

Process_OverviewI'm attempting to transform the input Rain Gauge data set to the desired output as shown in the above inserted image but I'm at a loss on how to proceed?

 

The important factors are:

  • The time period is defined by the user, in this example 01/01/2022 - 10/01/2022 and the output table needs to include all days in that range.
  • The input Rain Gauge data only contains data for when it rained so not all dates are included.
  • I need to some how produce a column per Rain Gauge reference, then look up the associated rain fall value for each of the days and populate the new table structure.
  • The number of rain gauges can vary.

 

I have completed the process of creating a list of all dates for the requested time period.

 

I can't begin to fathom how to manipulate the data as shown and explained above.

If completing this in Excel I would have created a pivot to produce the table below:

 Example_Pivot 

Any help will be gratefully received!

 

Thank you.

Best answer by geomancer

Something like this?

RainGauges

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

6 replies

geomancer
Evangelist
Forum|alt.badge.img+58
  • geomancerEvangelist
  • Evangelist
  • 932 replies
  • Best Answer
  • June 9, 2022

Something like this?

RainGauges

R
Forum|alt.badge.img
  • rob_nason
  • Author
  • 14 replies
  • June 9, 2022

Something like this?

RainGauges

Thanks for the amazing reply.

 

I can see in the first AttributeManager you've set up the Output Attributes as RG01 and RG02. If I had further RG's e.g. RG03, RG04 etc I have to manually add them to this transformer? Could that instead be automated by generating a unique list of RG references from the Input Data?

geomancer
Evangelist
Forum|alt.badge.img+58
  • geomancerEvangelist
  • Evangelist
  • 932 replies
  • June 9, 2022

The alternative (lower) route uses @Value(RG_Ref) to define attribute names. That creates unexposed attributes RG03, RG04 etc.

Afterwards you will have to explicitly expose those attributes. For this you can create a text document with all unique RG-values from the input file (take a look at the DuplicateFilter), and manually import this document in the AttributeExposer to expose all these attributes. Next you can process the whole input file.

R
Forum|alt.badge.img
  • rob_nason
  • Author
  • 14 replies
  • June 9, 2022

The alternative (lower) route uses @Value(RG_Ref) to define attribute names. That creates unexposed attributes RG03, RG04 etc.

Afterwards you will have to explicitly expose those attributes. For this you can create a text document with all unique RG-values from the input file (take a look at the DuplicateFilter), and manually import this document in the AttributeExposer to expose all these attributes. Next you can process the whole input file.

Great idea, I used a DuplicateFilter to create a unique list of the RG_Refs in the CSV Reader. Then imported that into the AttributeExposer which works.

 

What about the AttributeManager on the upper route in the bookmark named - Define Attributes? Into this attributemanager I may need to add 50+ rain gauge attributes and set up the conditional formatting. Is there away of bulk completing that?

geomancer
Evangelist
Forum|alt.badge.img+58
  • geomancerEvangelist
  • Evangelist
  • 932 replies
  • June 9, 2022

Hi @rob_nason​ , the AttributeManager on the upper route in the bookmark named Define Attributes you can also perform an Import, but I suspect this will only import the attribute names, not the definitions of the conditional values.

If somebody has other ideas, please feel free to participate!

R
Forum|alt.badge.img
  • rob_nason
  • Author
  • 14 replies
  • June 9, 2022

Hi @rob_nason​ , the AttributeManager on the upper route in the bookmark named Define Attributes you can also perform an Import, but I suspect this will only import the attribute names, not the definitions of the conditional values.

If somebody has other ideas, please feel free to participate!

The import function does indeed bring in the attribute names but I can't see a way of populating the conditional values other than by hand.

 

Perhaps a price I have to pay, your workflow is still a big improvement over the current Excel process!

Community Stats

32,065
Posts
121,730
Replies
39,691
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.