Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Transposing rows to columns with multiple similar lines

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Transposing rows to columns with multiple similar lines
# 1  
Old 05-20-2015
Transposing rows to columns with multiple similar lines
Hi,

I am trying to transpose rows to columns for thousands of records. The problem is there are records that have the same lines that need to be separated. the input file as below:-

Code:
ID	1A02_HUMAN				  
AC	P01892; O19619; P06338; P10313; P30444; P30445; P30446; P30514;
AC	Q29680; Q29837; Q29899; Q95352; Q95380; Q9TPX8; Q9TPX9; Q9TPY0;
AC	Q9TQH5; Q9TQI3;
TM	1

ID	1A02_PANTR				  
AC	P16210;
TM	10

ID	1A03_GORGO				  
AC	P30377;
TM	12

ID	1A03_HUMAN				  
AC	P04439; O19546; O19756;
TM	5

ID	1A03_PANTR				  
AC	P13748; Q547D5;
TM	0

ID	1A04_GORGO				  
AC	P30378;
TM	1

and the output should be like below:-

Code:
AC	ID	TM
P01892	1A02_HUMAN	1	
O19619	1A02_HUMAN	1
P06338	1A02_HUMAN	1
P10313	1A02_HUMAN	1
P30444	1A02_HUMAN	1
P30445	1A02_HUMAN	1
P30446	1A02_HUMAN	1
P30514	1A02_HUMAN	1
Q29680	1A02_HUMAN	1
Q29837	1A02_HUMAN	1
Q29899	1A02_HUMAN	1
Q95352	1A02_HUMAN	1
Q95380	1A02_HUMAN	1
Q9TPX8	1A02_HUMAN	1
Q9TPX9	1A02_HUMAN	1
Q9TPY0	1A02_HUMAN	1
Q9TQH5	1A02_HUMAN	1
Q9TQI3	1A02_HUMAN	1
P16210	1A02_PANTR	10
P30377	1A03_GORGO	12
P04439	1A03_HUMAN	5
O19546	1A03_HUMAN	5
O19756	1A03_HUMAN	5
P13748	1A03_PANTR	0
Q547D5	1A03_PANTR	0
P30378	1A04_GORGO	1

I found a code that is very similar to my issue in this forum and i modified it a little bit as below:-

Code:
awk '/^AC/{C[i++]=$2}
     /^ID/{D[j++]=$2}
     /^TM/{M[k++]=$2} 
     END       {print "AC\tID\tTM" ;
                for (i in D) printf "%-5s\t %-10s\t %s\n",C[i],D[i],M[i]}' sample | sed 's/;//g'

However, my problem is with lines start with "AC". I did try using split function to work on it but failed. Can anyone pls help me? thanks
# 2  
Old 05-20-2015
Hi,
With (gnu) awk:
Code:
awk 'BEGIN{OFS="\t"}
/ID/,/TM/ {
  if($1 ~ "ID") ID=$2 
  if($1 ~ "TM") TM=$2
  if($1 ~ "AC") {
    gsub(/;/,"")
    for (i=2;i<=NF;i++) AC[k++]=$i
    }
}
/TM/{
  k=0
  for (j in AC) {
    print AC[j],ID,TM
  }
  delete AC
}' sample.txt

Regards.
This User Gave Thanks to disedorgue For This Post:
redse171
# 3  
Old 05-21-2015
Quote:
Originally Posted by disedorgue
Hi,
With (gnu) awk:
Code:
awk 'BEGIN{OFS="\t"}
/ID/,/TM/ {
  if($1 ~ "ID") ID=$2 
  if($1 ~ "TM") TM=$2
  if($1 ~ "AC") {
    gsub(/;/,"")
    for (i=2;i<=NF;i++) AC[k++]=$i
    }
}
/TM/{
  k=0
  for (j in AC) {
    print AC[j],ID,TM
  }
  delete AC
}' sample.txt

Regards.

Hi disedorgue,

Thank you very much. It works great on my real data. Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

To group the text (rows) by similar columns-names in a file

As part of some report generation, I've written a script to fetch the values from DB. But, unluckily, for certain Time ranges(1-9.99,10-19.99 etc), I don't have data in DB. In such cases, I would like to write zero (0) instead of empty. The desired output will be exported to csv file. ... (1 Reply)
Discussion started by: kumar_karpuram
1 Replies

2. UNIX for Dummies Questions & Answers

Awk: convert rows to columns every n lines

Hi guys! I use AWK commands under GAMS to predispose the data files to be read by GAMS. I have a file which contains groups of data I need. Unfortunately I have the data spread in 3 rows for each subject. Here's an example (the file is really long) 1 0 2.0956 100.00 250.00 100.00 2.0956... (4 Replies)
Discussion started by: Pintug
4 Replies

3. Shell Programming and Scripting

transposing lines to columns

Okay folks, here's a question. I tried searching but couldn't find exactly what I needed. I have a text file (excerpt below). This text file is an extract I did from several hundred pages of datasheets using grep so I could look only at the site history for each site. The problem is that... (2 Replies)
Discussion started by: jbrandt1979
2 Replies

4. Shell Programming and Scripting

Transposing rows and columns (pivoting) using shell scripting

Here is the contents of an input file. A,1,2,3,4 10,aaa,bbb,ccc,ddd 11,eee,fff,ggg,hhh 12,iii,jjj,lll,mmm 13,nnn,ooo,ppp I wanted the output to be A 10 1 aaa 10 2 bbb 10 3 ccc 10 4 ddd 11 1 eee 11 2 fff 11 3 ggg 11 4 hhh ..... and so on How to do it in ksh... (9 Replies)
Discussion started by: ksatish89
9 Replies

5. Shell Programming and Scripting

transposing columns into rows

Hi, I need to transpose columns of my files into rows and save it as individual files. sample contents of the file below. 0.9120 0.7782 0.6959 0.6904 0.6322 0.8068 0.9082 0.9290 0.7272 0.9870 0.7648 0.8053 0.8300 0.9520 0.8614 0.6734 0.7910 0.6413 0.7126 0.7364 0.8491 0.8868 0.7586 0.8949... (8 Replies)
Discussion started by: ida1215
8 Replies

6. Shell Programming and Scripting

Help for a Perl newcomer! Transposing data from columns to rows

I have to create a Perl script which will transpose the data output from my experiment, from columns to rows, in order for me to analyse the data. I am a complete Perl novice so any help would be greatly appreciated. The data as it stands looks like this: Subject Condition Fp1 ... (12 Replies)
Discussion started by: Sarah_W
12 Replies

7. Shell Programming and Scripting

Transposing Repeated Rows to Columns.

I have 1000s of these rows that I would like to transpose to columns. However I would like the transpose every 3 consecutive rows to columns like below, sorted by column 3 and provide a total for each occurrences. Finally I would like a grand total of column 3. 21|FE|41|0B 50\65\78 15... (2 Replies)
Discussion started by: ravzter
2 Replies

8. Shell Programming and Scripting

awk, string as record separator, transposing rows into columns

I'm working on a different stage of a project that someone helped me address elsewhere in these threads. The .docs I'm cycling through look roughly like this: 1 of 26 DOCUMENTS Copyright 2010 The Age Company Limited All Rights Reserved The Age (Melbourne, Australia) November 27, 2010... (9 Replies)
Discussion started by: spindoctor
9 Replies

9. Shell Programming and Scripting

Transposing rows into columns

I have a file like the one given below P1|V1|V2 P1|V1|V3 P1V1|V2 P2|V1|V4 P2|V2|V6 P2|V1|V4 I want it convert to P1|V1|V2|V2|V3 P2|V1|V4|V2|V6 2nd and 3rd column should be considered as together and so the tird row is duplicate Any ideas? (3 Replies)
Discussion started by: prasperl
3 Replies

10. Shell Programming and Scripting

Rows to columns transposing and reformating.

----File attached. Input file =========== COL_1 <IP Add 1> COL_2 <Service1> COL_3 <ABCDEFG> COL_4 <IP ADD:PORT> COL_4 <IP ADD:PORT> COL_1 <IP Add 2> COL_2 <Service2> COL_2 <Service3> COL_2 <Service4> COL_3 <AAAABBB> COL_4 <IP ADD:PORT> COL_4 <IP ADD:PORT> COL_4 <IP... (27 Replies)
Discussion started by: bluethunder
27 Replies
Login or Register to Ask a Question