Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Transpose from row to column using timestamp in first column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Transpose from row to column using timestamp in first column
# 1  
Old 08-07-2018
Transpose from row to column using timestamp in first column
Gents,

Transpose from row to column, taking in consideration the first column, which contends the date.

Input file

Code:
72918,111000009,111000009,111000009,111000009,111000009,111000009,111000009,111000009,111000009
72918,2356,2357,2358,2359,2360,2361,2362,2363,2364
72918,0,0,0,0,0,0,0,0,0
72918,0,0,0,0,0,0,1,0,0
72918,1496,1502,1752,1752,1752,1752,1751,974,972
73018,111000004,111000005,111000003,111000002,111000009,111000009,111000009,111000009,111000009
73018,2349,2350,2351,2352,2353,2354,2355,2356,2357
73018,0,0,0,0,0,0,0,0,0
73018,0,0,0,0,0,0,0,0,0
73018,1524,1526,1752,1752,1752,1752,1752,256,250


Output desired

Code:
72918,111000009,2356,0,0,1496
72918,111000009,2357,0,0,1502
72918,111000009,2358,0,0,1752
72918,111000009,2359,0,0,1752
72918,111000009,2360,0,0,1752
72918,111000009,2361,0,0,1752
72918,111000009,2362,0,1,1751
72918,111000009,2363,0,0,974
72918,111000009,2364,0,0,972
73018,111000004,2349,0,0,1524
73018,111000005,2350,0,0,1526
73018,111000003,2351,0,0,1752
73018,111000002,2352,0,0,1752
73018,111000009,2353,0,0,1752
73018,111000009,2354,0,0,1752
73018,111000009,2355,0,0,1752
73018,111000009,2356,0,0,256
73018,111000009,2357,0,0,250

With this code i get the output desired, but the number of fields in the input need to the same within each block

Code:
awk -F, '
 func init_block() {ts=$1;delete a;cnt=0;nf0=NF}
 func dump_block() {for(f=2;f<=nf0;f+=1){printf("%s",ts);for(r=1;r<=cnt;r+=1){printf(",%s",a[r,f])};print ""}}
 BEGIN{ts=-1}
 ts<0{init_block()}
 ts!=$1{dump_block();init_block()}
 {cnt+=1;for(f=1; f<=NF; f++) a[cnt,f]=$f}
 END{dump_block()}' <input.txt >output.txt

Is there other option code to get the results even if the fields are not the same?

Thanks in advance
Last edited by jiam912; 08-08-2018 at 06:14 PM..
# 2  
Old 08-07-2018
Hello jiam912,

Could you please try following and let me know if this helps.

Code:
awk -F, '
$0~/^[0-9]+,111000009,111000009,111000009,111000009,111000009,111000009,111000009,111000009,111000009/ && array[1]{
   for(i=2;i<=NF;i++){
     print array[1],array[i]
     delete array[i]
   }
   delete array[1]
}
{
   if($0 ~ /^[0-9]+,111000009,111000009,111000009,111000009,111000009,111000009,111000009,111000009,111000009/){
     array[1]=$1
   }
   for(i=2;i<=NF;i++){
     array[i]=array[i]?array[i] OFS $i:$i
   }
}
END{
   if(array[1]){
     for(i=2;i<=NF;i++){
        print array[1],array[i]
        delete array[i]
     }
   }
}' OFS=,   Input_file

Thanks,
R. Singh
Last edited by RavinderSingh13; 08-08-2018 at 03:18 AM..
This User Gave Thanks to RavinderSingh13 For This Post:
jiam912
# 3  
Old 08-08-2018
Hi jiam912,
The problem with giving us sample input with no specification of the format of that input is that we have to make assumptions about what the input format is. Ravinder's code assumes that the start of a block of input is delimited by a line with a field that "contends" a date followed by nine fields that contain the string 111000009. And, from your sample data, that seems to fit your requirements perfectly.

If I was looking at your data, I might make the assumption that your input format contains groups of five lines. That also seems to fit your requirements perfectly given your sample data.

You say that you want code that works if the number of input fields is not a constant. But you don't specify what that means! Does the number of input fields vary in different sets of five lines while being constant within a set of five lines? Does the number of fields vary on each input line? (And, if so, what values are to be printed for fields that are not specified in the input?)

Did Ravinder guess correctly at how the first line of a group is identified? Or, are there a fixed number of lines in a group? If Ravinder didn't guess correctly but there are varying numbers of lines in a group, how are we supposed to determine which line starts a group?
This User Gave Thanks to Don Cragun For This Post:
jiam912
# 4  
Old 08-08-2018
Hi Don.
Sorry for the confusion, I have changed a little the examples.
The fields after the date will be not always 9, in the example are 9 fields but can be more.
The code of Ravinders works perfectly with the example before, but not if the values change in the first line when the date changes. As i explain the value 111000009 is not constant.
Appreciate your help.
Please check the new input file
Many thanks.
# 5  
Old 08-09-2018
In post #3, I asked you six questions. You have now answered one of them:
Quote:
Did Ravinder guess correctly at how the first line of a group is identified?
you answered "No" to that question.

Please give us answers to the other questions so we have a chance of helping you figure out what needs to be done to reach your goal.
This User Gave Thanks to Don Cragun For This Post:
jiam912
# 6  
Old 08-09-2018
added some data
Hi Don

Appreciate your answer.

I have attached 2 files with more data to be more clear.
File tmp1.txt ( input file )
File tmp2.txt ( desired output) - 7 fields separated by ,

As you can see in the input file the date block change every each 6 lines (fixed number of lines in a group) and the files after column1 (date) are not the same allways. So the pursose is to traspose the colums after date example in date 061218 after date, are 14 fields, then should have 14 lines like in the otput file attached.

But can be cases where the date does not change after 6 lines, can be each 12 like the example on date 061318 in that case the values of the second part lines 7-12 need to be added to the END ond the lines 1-6. To get correctly in the output file.

My script works fine if the input has only fixed number of lines ( 6 for each block ), but if there is case as the date 061318 it does not work.

Hope you can help me to figer out this case.

Appreciate your help.
tmp1.txt (2.1 KB) 
tmp2.txt (2.3 KB) 
# 7  
Old 08-11-2018
Hi jiam912,
In your original sample input, there were groups of 5 lines (10 lines total in the sample). In your latest sample (attached to post #6), you say that there are groups of 6 lines (30 lines total in the sample).

I asked if there are always a constant number of fields in a group of lines. You have not answered that question.

I asked how we are supposed to determine which line is a first line of a group if there can be a different number of lines in a group. You have not answered that question. And, as pointed out in the first paragraph above, the number of lines in a group in your samples is not a constant. Without a clear answer to how you determine that a line is the first line of a group, I have no idea how we can help you solve your problem!

I asked whether or not the number of fields within a group of lines is a constant. You have not answered that question. The number is a constant in your samples, but without a clear specification of your input file format, I don't want to make any wild assumptions that will invalidate any suggestions we might provide. If the number of fields within a group is lines is not a constant, I asked what default values should be provided for fields that are not supplied in a group. You have not answered that question.

If you won't give us a clear specification of your input file format, don't expect us to waste any more time guessing at code that will likely be invalidated by your next post with a new sample input file format.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Transpose the Row and column

Hi, I have data in form of A ram B shyam C seeta D geeta A bob B methew C Richad D Mike and i want it in this form. A B C D ram shyam seeta geeta bob methew Richard Mike. please help by providing the scripting for this. (3 Replies)
Discussion started by: ricbha
3 Replies

2. Shell Programming and Scripting

How transpose column in a row?

Hello guys, First of all happy holidays and happy new year. I'm new in bioinformatic and also it is my first time that I write in this forum. Therefore, sorry if I make some mistakes. I'm writing to ask your help to fix a problem: I have a file like this: gene1 GO:0016491|GO:0055114... (8 Replies)
Discussion started by: Salvatore_espos
8 Replies

3. Shell Programming and Scripting

Transpose row to column

I'm using the testawk.awk from the following thread https://www.unix.com/shell-programming-and-scripting/18897-row-column-transpose.htmlI'm getting the following output fieldname1 data1 fieldname2 data2 fieldname3 data3 How can I get like this instead 1 fieldname1 data1 2 fieldname2 data2... (1 Reply)
Discussion started by: makkan
1 Replies

4. Shell Programming and Scripting

To transpose row into column

Hi All, In shell, I have below data coming from some some text file as below: . 351706 5861.8 0.026 0.012 12.584 0.026 0.012 12.582 0.000 0.000 0.000 Now i need the above data to be transposed as below 351706... (16 Replies)
Discussion started by: Anamica
16 Replies

5. Shell Programming and Scripting

Column to row Transpose

Hi Folks, Iam a kinda newbie to unix shell scripting, the scenario is i have a text file containing the following info Charlie chicago 15 Charlie newyork 26 jonny chicago 14 jonny newyork 15 joe chicago 15 joe newyork 18output should be Name chicago ... (3 Replies)
Discussion started by: tech_frk
3 Replies

6. UNIX for Dummies Questions & Answers

Row to column transpose

Hi there, Below is sample three rows which i need transpose into multiple rows. By keeping first 2 fields static and split them into multiple rows depend following date field. Each into seperate rows. Sample code: ... (6 Replies)
Discussion started by: ganeshd
6 Replies

7. Shell Programming and Scripting

Row to column transpose between same pattern.

Hi All, I have been trying to transpose rows to column in an large file (about 15000 lines) between matching pattern. Searched all posts in this forum, but not able find the solution to my problem. Any help appreciated.!! Input /*------XXXXXX-------*/ owner: XXXX location: XXXX... (3 Replies)
Discussion started by: RobP
3 Replies

8. Shell Programming and Scripting

Transpose column to row

Hi i have a file which has values seperated by "," as shown below and I want to transpose for every doc_id in one row. Input: DOC_ID,KEYWORD 105,REGISTROS 105,GEOLOGIA 105,NUCLEOS 105,EXPEDIENTE 105,PROGRAMAS 10025,EXPEDIENTE 10025,LOCALIZACIONES 10025,OFICIOS 10025,PROGRAMAS... (4 Replies)
Discussion started by: juelillo
4 Replies

9. Shell Programming and Scripting

How do I transpose a column of results to a row

Hi, Can anyone advise me what command I could use to display the results of the following command as a row. Thanks Gareth (6 Replies)
Discussion started by: m223464
6 Replies

10. Shell Programming and Scripting

Row to column transpose

Can we transpose rows to columns? Fields within row are separated by a comma. (10 Replies)
Discussion started by: videsh77
10 Replies
Login or Register to Ask a Question