Skip to main content
Question

Transpose multi-indexed data


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
Evangelist
  • June 2, 2018

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.

  • Author
  • June 2, 2018

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
Evangelist
  • June 2, 2018
samhayashi wrote:

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
Safer
Forum|alt.badge.img+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


  • Author
  • June 3, 2018

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings