topic Re: Columns to lines in Talend Studio

Columns to lines

carosim — Sat, 16 Nov 2024 02:17:39 GMT

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

Re: Columns to lines

manodwhb — Fri, 29 May 2020 10:10:15 GMT

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

Re: Columns to lines

carosim — Fri, 29 May 2020 10:42:13 GMT

@manodwhb I have all my data in one column

Test

col1

2.2

7.1

col2

1.4

8.2

col3

5.7

1.0

Re: Columns to lines

manodwhb — Fri, 29 May 2020 11:02:45 GMT

@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?

Re: Columns to lines

carosim — Fri, 29 May 2020 12:06:42 GMT

@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.

Re: Columns to lines

manodwhb — Fri, 29 May 2020 12:12:05 GMT

@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.

Re: Columns to lines

carosim — Fri, 29 May 2020 13:00:18 GMT

@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.

Re: Columns to lines

manodwhb — Fri, 29 May 2020 13:07:23 GMT

@carosim , yes maybe because of that

Re: Columns to lines

carosim — Fri, 29 May 2020 16:01:02 GMT

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=;
         }
      }

Re: Columns to lines

tornilleiro — Sun, 31 May 2020 00:37:56 GMT

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.