Columns to lines

[carosim]

Hello,

I have a file with an array whose number of rows and columns is changing.

I would just transpose the columns into rows but I can't. I tried several components: tnormalize, tunpivot, etc ... but I can't get the desired result.

Initial table :

Test col1 col2 col3 etc...

1  2.2 1.4 5.7 etc...

2 7.1 8.2 1.0 etc...

Desired result

Test 1 2

col1 2.2 7.1

col2 1.4 8.2

col3 5.7 1.0

The final objective is to make aggragations like average and others for each column.

Maybe I don't think right if you have a solution I'm interested.

Thank you

[manodwhb]

@carosim m when you use tunpivot what is the output you got?

[carosim]

@manodwhb I have all my data in one column

Test

1

2

col1

2.2

7.1

col2

1.4

8.2

col3

5.7

1.0

[manodwhb]

@carosim , before tunpivot generate seqid and choose that sequence id in row section of unpivot. unpivot schema should be seqid,key,value. can you try are you abe to get it?

[carosim]

@manodwhb 

Thank you, I think it's the right way, but I created a sequence with tmap

My schema before tunpivotrow is :

seq

line

My data is :

1|Test 1 2

2|col1 2.2 7.1

3|col2 1.4 8.2

4|col3 5.7 1.0

but the result after tunpivotrow is wrong :

1|line|Test 1 2

2|line|col1 2.2 7.1

3|line|col2 1.4 8.2

4|line|col3 5.7 1.0

There is no pivot.

[manodwhb]

@carosim , you need to have data seperatly columns likeTest in col1 and 1 in  col2 and 2 in col3 then you can do with tunpivot.

[carosim]

@manodwhb  ok thank you I understand but when I use a simple tunpivot, I don't have the number of column in advance. It is for this reason that my input schema  is very simple.

[manodwhb]

@carosim , yes maybe because of that

[carosim]

I'm still looking for the right solution because the treatment is long but in the meantime I used a tjava.

String content = (String) input_row.content;
String[] lines =  content.split("\n");

String [] lineTab = lines[5].split("\t");
int nbline_tabpivot = lineTab.length;
int nbcol_tabpivot = lines.length-4;
int itabinit ;

String tabinit[][] = new String [nbcol_tabpivot][nbline_tabpivot];
String tabpivot[][] = new String [nbline_tabpivot][nbcol_tabpivot];


	for(int i = 4 ; i < lines.length ; i++){
		lineTab = lines[i].split("\t");
		for(int j = 0; j<lineTab.length ;j++) {
			itabinit = i-4;
			tabinit[itabinit][j] = lineTab[j]; 
			//System.out.print(tabinit[itabinit][j] + " ; ");
			}
		//System.out.print("\n");
		}



for(int i = 0; i<nbline_tabpivot ;i++){
         for(int j = 0; j<nbcol_tabpivot ;j++){
            tabpivot[i][j] = tabinit[j][i];
            System.out.print(tabpivot[i][j]);
         }
      }

for(int i = 0; i<nbline_tabpivot ;i++){
         for(int j = 0; j<nbcol_tabpivot ;j++){
            tabpivot[i][j] = tabinit[j][i];
            output_row.line=;
         }
      }
      

[tornilleiro]

Dear @carosim,

If you could guess the maximum number of columns i.e. How many \t you have by line.  You could create an schema with enough number columns, and use tExtractDelimitedFields before pass the flow to tunpivot component.