Question

Transpose multi-indexed data

S

I have a table that I'm downloading from an FTP site as an attribute (all data is in a single cell) that I need to inject into another part of my workflow as if it were coming from a csv reader table (data is broken out into their respective cells). I've been able to get the data broken down into it's indexed components but can't figure out how to reassemble them back into a table. I've looked at the transpose demo video but I'm not sure how to handle

I've attached a workspace that shows where I'm at as well as an image of what I'm trying to replicate. Any help would be very appreciated! :)

5 replies

takashi
Rank
Userlevel 2
Badge +17

Hi @samhayashi, since the CSV reader supports to read a pipe-separated value table, you can read it directly with a FeatureReader (Format: CSV) if you save the downloaded table into a file.

  • Delimiter Character: | (pipe)
  • Field Names Line: None (or blank in older versions)
  • Data Start Line: 1

With the parameters setting above, the CSV reader generates default attribute names (col0, col1, col2, ...) for each column.Just be aware that the FeatureReader won't expose the attribute names automatically. If necessary, you will have to expose them manually via the Attributes to Expose parameter in the FeatureReader, or an AttributeExposer transformer.
S

Hi @takashi! Thank you for the quick response. Unfortunately I am unable to save the file locally, the processing must happen in memory. I had already built what you are describing when I found out about the restriction. Any ideas on how to reconstruct the CSV using the index values? Appreciate your help! :)

takashi
Rank
Userlevel 2
Badge +17

Hi @takashi! Thank you for the quick response. Unfortunately I am unable to save the file locally, the processing must happen in memory. I had already built what you are describing when I found out about the restriction. Any ideas on how to reconstruct the CSV using the index values? Appreciate your help! :)

An easy way is to expose list elements on the AttributeSplitter_3 and then rename them appropriately.

 

By the way, why aren't you able to save the data into a local file? Since you have stored the data in a feature attribute already, I think you can use the AttributeFileWriter to save it into a file, even if you were not able to download it into a file directly for some reason..

 

nathanatsafe
Rank
Badge +7

Hi @samhayashi,

 

Thanks for the question. This is a category of problem I came across when working with the US Census API (read more here). I've added a few transformers to your workspace and think I've achieved your results. Take a look at the attached workspace to see the process.

 

Some key transformers to use in this kind of problem are the AttributeCreator and Aggregator, which allow you to transpose data and create columns based on existing column values.

 

I think @takashi is onto another possible solution. Hopefully this one provides you with an option to stay completely in memory.

 

Best,

 

Nathan

S

Hi @NathanAtSafe - that was exactly what I was looking for!! Thank you so much, you're a life saver! :)

Reply


Community Stats

28,664
Posts
109,338
Replies
36,533
Members
Terms & ConditionsCookie settings
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.