Skip to main content
Question

Cross tabulating columns


Forum|alt.badge.img
Dear all,

 

I have some data that looks like the first table. I usually use Microsoft Access to create a cross-tabulation as in the second table. Does anyone have an idea of how I could accomplish that in FME?

 

 

Thanks!

15 replies

david_r
Celebrity
  • July 7, 2015

Forum|alt.badge.img
  • Author
  • July 7, 2015
Dear David,

 

 

I have tried the AttributePivoter and it doesn't work. The reason is because the parameter "Attribute To Analyze" has to be numeric and, in my case it is the Designation thing.

 

Not really sure what I am trying to do is feasible with FME to be honest. If you have any ideas of how it could achieved, they would be much appreciated.

 

 

Darius

takashi
Influencer
  • July 7, 2015
Hi Darius,

 

 

The AttributePivoter can also be used to pivot a table by a string column in some cases like yours. Try setting Max (or Min) to the "Pivot Summary Statistic Types" parameter.

 

 

 

In addition, you will have to expose the newly created attribute names (Blue, Green, Purple, Red) with the AttributeExposer, if you need to manipulate them with other transformers.

 

 

However, as you say, since the transformer seems to be designed for calculating statistics for a numerical column, the usage mentioned above might be non-recommended. If you feel uneasiness, you can use a combination of AttributeCreator (or BulkAttributeRenamer) and Aggregator in this case. Regarding the way, see the link that David pasted.

 

 

Takashi

Forum|alt.badge.img
  • Author
  • July 8, 2015
Hi Takashi,

 

 

The AttributePivoter fails regardless what Pivot Summary Statistic Type you choose (Min, Max or any other). The errors seem to be same. As indicated above, it has to see with the fact that the attributtes are string and no calculations are possible. Not sure this transformer can give what I need :(

 

 

The five attributes to group by are the species related information (in the first example I only showed Species & Group).

 

 

 

 

 

 

Darius

takashi
Influencer
  • July 8, 2015
I cannot specify the reason for the error because your first table is not the same as the actual data.

 

Try using a combination of the AttributeCreator (or BulkAttributeRenamer) and the Aggregator, instead of the AttributePivoter.

 


pratap
Contributor
Forum|alt.badge.img+11
  • Contributor
  • July 8, 2015
Hi,This is an alternative way, if your unique category values are less orelse we have to create numerous attributes based on column

gio
Contributor
Forum|alt.badge.img+15
  • Contributor
  • July 8, 2015
You can solve this by using aggregator and/or listbuilder &co in conjunciotn with attributeexploder.

 

Then create a txtfile with a header line (at texlinenumer=1 and subsequent datalines (nrs >=2 ). Write to textfile.

 

Read textfile and write to excell (this automatically exposes the created attributes). (this latter can be automated using a workspacecaller)

 

 

A couple of these type workspaces in this forum doing these kinds of table-transforms.

 

 

 

Forum|alt.badge.img
  • Author
  • July 10, 2015
Thanks to everybody who replied to this but I can't get my head around your suggestions. I have tried most of them I don't get any closer to what I need as an ouptut, so I give up.

 

Regards,

 

Dario

pratap
Contributor
Forum|alt.badge.img+11
  • Contributor
  • July 10, 2015
Can you provide your email ID?

 

I will send the workbench which I have prepared based on spreadsheet you have provided.

davidrasner5
Contributor
Forum|alt.badge.img+4

Sorry for digging up an old thread, but I'm stuck on the same problem and can't find a good solution for this.

I'm trying to convert an IFC file to an Excel.

Something like this:

  1. Import IFC
  2. GeometryPropertyExtractor for Pset.Attribute
  3. AttributeExploder for "_attr_name" and "_attr_value"
  4. So far no issues:

The result:

  • Neat, but how do I pivot (crosstab, group by "GlobalID") this?

I can filter unique Attribute Names (_attr_name), export it as a csv and import for the AttributeExposer and that works:

But I really need to do this more dynamically.

The question is, how?

  • The AttributePivoter doesn't work with strings (for some crazy reason).
  • I was suggested a SQL querry using InlineQuerier? Unfortunately I'm hopeless with SQL
  • I'm okay with R, but the RCaller is kind of ugh, need to research this more.

What else is there?


davidrasner5
Contributor
Forum|alt.badge.img+4
davidrasner5 wrote:

Sorry for digging up an old thread, but I'm stuck on the same problem and can't find a good solution for this.

I'm trying to convert an IFC file to an Excel.

Something like this:

  1. Import IFC
  2. GeometryPropertyExtractor for Pset.Attribute
  3. AttributeExploder for "_attr_name" and "_attr_value"
  4. So far no issues:

The result:

  • Neat, but how do I pivot (crosstab, group by "GlobalID") this?

I can filter unique Attribute Names (_attr_name), export it as a csv and import for the AttributeExposer and that works:

But I really need to do this more dynamically.

The question is, how?

  • The AttributePivoter doesn't work with strings (for some crazy reason).
  • I was suggested a SQL querry using InlineQuerier? Unfortunately I'm hopeless with SQL
  • I'm okay with R, but the RCaller is kind of ugh, need to research this more.

What else is there?

ifc2csv2.fmwTest IFC.zip

Test files, to speed things up.

Also, the TLDR version:

How to?

Every basic ETL tool does this out of the box. :(


ebygomm
Influencer
Forum|alt.badge.img+32
  • Influencer
  • May 7, 2019
davidrasner5 wrote:

Sorry for digging up an old thread, but I'm stuck on the same problem and can't find a good solution for this.

I'm trying to convert an IFC file to an Excel.

Something like this:

  1. Import IFC
  2. GeometryPropertyExtractor for Pset.Attribute
  3. AttributeExploder for "_attr_name" and "_attr_value"
  4. So far no issues:

The result:

  • Neat, but how do I pivot (crosstab, group by "GlobalID") this?

I can filter unique Attribute Names (_attr_name), export it as a csv and import for the AttributeExposer and that works:

But I really need to do this more dynamically.

The question is, how?

  • The AttributePivoter doesn't work with strings (for some crazy reason).
  • I was suggested a SQL querry using InlineQuerier? Unfortunately I'm hopeless with SQL
  • I'm okay with R, but the RCaller is kind of ugh, need to research this more.

What else is there?

If the end goal is excel then you don't necessarily need to expose the attributes on the canvas, because a dynamic schema can take care of that part. Do you have an example of the initial input that you can share?

I usually pivot data like this by using attribute values to create the attributes then aggregating and altering the schema

e.g.

pivot_to_excel.fmwt

 


ebygomm
Influencer
Forum|alt.badge.img+32
  • Influencer
  • May 7, 2019
davidrasner5 wrote:

ifc2csv2.fmwTest IFC.zip

Test files, to speed things up.

Also, the TLDR version:

How to?

Every basic ETL tool does this out of the box. :(

Looking at this data, there is no need to use an attribute exploder and then try and pivot the data, you just need to dynamically write to excel. I'd suggest posting as a new question.

 

 


davidrasner5
Contributor
Forum|alt.badge.img+4
ebygomm wrote:

Looking at this data, there is no need to use an attribute exploder and then try and pivot the data, you just need to dynamically write to excel. I'd suggest posting as a new question.

 

 

@egomm Hello, thanks for the reply.

Yeah, I'm kind of stuck in this ETL way of thinking. I have to do some research on writing attributes dynamically.

But your pivot_to_excel.fmwt looks interesting.

Could I bother you to tweak the workspace to make it work with this file? IFC Example.csv

As a learning exercise. :)

My secondary goal here is to push the IFC data to Azure for the ML Studio, that's why the pivot part is so important.


ebygomm
Influencer
Forum|alt.badge.img+32
  • Influencer
  • May 8, 2019
davidrasner5 wrote:

@egomm Hello, thanks for the reply.

Yeah, I'm kind of stuck in this ETL way of thinking. I have to do some research on writing attributes dynamically.

But your pivot_to_excel.fmwt looks interesting.

Could I bother you to tweak the workspace to make it work with this file? IFC Example.csv

As a learning exercise. :)

My secondary goal here is to push the IFC data to Azure for the ML Studio, that's why the pivot part is so important.

You're only needing to 'pivot' the data because of the attribute exploder. Your start point is one feature per row and you want to end up with one feature per row.

I think you just need to handle dynamically writing the attributes, example using your sample file

ifc_to_excel.fmwt


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