Skip to main content
Solved

how to Transpose CSV Data and Add Rows For New Data Pairs

  • May 31, 2023
  • 4 replies
  • 102 views
ecx
Supporter
Forum|alt.badge.img+5
  • ecxSupporter
  • Supporter
  • 31 replies

I currently have a dataset, see top half of picture below, that I wish to transform/transpose into the format that can be seen in the bottom half of the image. The blue/green/peach coloured fields are date/measurement.

 

Data example, top half is current data, bottom half is desired output 

I am totally unsure of where to begin with this, the current transposer tools seem to only be able to do simple transposing without adding the additional rows of location/sourcetype/date/measurement I need. As can be seen I want to make a individual row for each date/measurement combination.

 

I have also attached sample data in xlsx and csv.

 

Any help would be appreciated!

Best answer by nielsgerrits

One way to do this is to explode the attributes into features and create Date and FlowTotal from _attr_name and _attr_value. Added sample workspace demonstrating this.

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.

4 replies

nielsgerrits
VIP
Forum|alt.badge.img+60
  • nielsgerritsVIP
  • 2937 replies
  • Best Answer
  • May 31, 2023

One way to do this is to explode the attributes into features and create Date and FlowTotal from _attr_name and _attr_value. Added sample workspace demonstrating this.

Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...
ebygomm
Influencer
Forum|alt.badge.img+44
  • ebygommInfluencer
  • Influencer
  • 3422 replies
  • May 31, 2023

This sort of workflow should work here

AttributeExploder keeping the Attributes, followed by keeping only the relevant attributes, followed by keeping only those features where the Date field contain something in the format Apr-19

imageYou then probably need to do some date formatting to get the date in your preferred format

image

ecx
Supporter
Forum|alt.badge.img+5
  • ecxSupporter
  • Author
  • Supporter
  • 31 replies
  • June 2, 2023

One way to do this is to explode the attributes into features and create Date and FlowTotal from _attr_name and _attr_value. Added sample workspace demonstrating this.

This worked great! The attribute exploders did exactly what was needed in this situation. Thank you!

ecx
Supporter
Forum|alt.badge.img+5
  • ecxSupporter
  • Author
  • Supporter
  • 31 replies
  • June 2, 2023

This sort of workflow should work here

AttributeExploder keeping the Attributes, followed by keeping only the relevant attributes, followed by keeping only those features where the Date field contain something in the format Apr-19

imageYou then probably need to do some date formatting to get the date in your preferred format

image

Great! Thank you for your answer, the attribute exploder was the key to getting this done!

Community Stats

32,061
Posts
121,725
Replies
39,688
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.