Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

To transpose columns + edit in a txt file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting To transpose columns + edit in a txt file
# 1  
Old 05-22-2014
To transpose columns + edit in a txt file
Hi,

I have a txt file that looks like

Code:
log2FoldChange	Ontology_term 
8.50624450251828	GO:0003700,GO:0003707,GO:0005634,GO:0006355,GO:0043401,GO:0003700,GO:0005634,GO:0006355,GO:0008270,GO:0043565
7.03936870356684	GO:0005515,GO:0008080
6.49606183738682	
6.49525073909629	GO:0005515
6.46698208267016	GO:0005524,GO:0005674,GO:0006367
6.40042264926934	GO:0004057,GO:0016598,GO:0005509
6.23313175594231	GO:0005975,GO:0008184
6.04689195740275	GO:0008152,GO:0016491,GO:0055114
6.03709051857717

And I need to:

1. Delete the rows that don't have an Ontology_term associated
2. A new txt file with just one Ontology_term by row with the associated log2FoldChange

Example:

Code:
log2FoldChange	Ontology_term 
8.506244503	GO:0003700
8.506244503	GO:0003707
8.506244503	GO:0005634
8.506244503	GO:0006355
8.506244503	GO:0043401
8.506244503	GO:0003700
8.506244503	GO:0005634
8.506244503	GO:0006355
8.506244503	GO:0008270
8.506244503	GO:0043565
7.039368704	GO:0005515
7.039368704	GO:0008080
6.495250739	GO:0005515
6.466982083	GO:0005524
6.466982083	GO:0005674
6.466982083	GO:0006367
6.400422649	GO:0004057
6.400422649	GO:0016598
6.400422649	GO:0005509
6.233131756	GO:0005975
6.233131756	GO:0008184
6.046891957	GO:0008152
6.046891957	GO:0016491
6.046891957	GO:0055114

To simplify, my initial file its like:

Code:
1 a,b,c
2
3 d,e
4 f
5 g,h,i,j,k
6
7
8 l,m

And I need:

Code:
1 a
1 b
1 c
3 d
3 e
4 f
5 g
5 h
5 i
5 j
5 k
8 l
8 m

Any idea about how can I do that?
Thank you for your time.
# 2  
Old 05-23-2014
Code:
awk 'NF>1 {n=split($2, a, ","); for (i=1; i<=n; i++) {print $1, a[i]}}' file

# 3  
Old 05-23-2014
You don't need the NF>1, because if $2 is empty, n will = 0 and nothing will be printed.
# 4  
Old 05-23-2014
Also try:

Code:
$ awk '{$1=$1} NR==1 || $2 && gsub(/,/,RS $1 OFS)' OFS='\t' file 

log2FoldChange	        Ontology_term
8.50624450251828	GO:0003700
8.50624450251828	GO:0003707
8.50624450251828	GO:0005634
8.50624450251828	GO:0006355
....
....

---------- Post updated at 08:34 PM ---------- Previous update was at 08:29 PM ----------

remove NR==1 ||, if your file does not contain header..
# 5  
Old 05-23-2014
Quote:
Originally Posted by RudiC
You don't need the NF>1, because if $2 is empty, n will = 0 and nothing will be printed.
Perhaps it appears to not need it. But what would you say it requires more computation:
A simple logical operation or a call to split and then loop on it {n=split($2, a, ","); for (i=1; i<=n; i++)

It is a choice to skip the second more expensive computation for lines that do not need it.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Solaris

I can't edit a txt file shared from Window server to Solaris

Hello, I'm working with Solaris and I need to share files between Windows Server 2012 and Solaris through an authentication with an Active Directory user, where Windows Server is the "main server". All was going good, until after see the WS files and I tried to open one and modify it, but I cannot... (1 Reply)
Discussion started by: QeratD0
1 Replies

2. Shell Programming and Scripting

Transpose a txt file

Hello, I have a text file with 148 rows and 2532691 columns. I need to transpose the data. The command that I am using is awk ' { for (i=1; i<=NF; i++) { a = $i } } NF>p { p = NF } END { for(j=1; j<=p; j++) { str=a for(i=2; i<=NR; i++){ ... (6 Replies)
Discussion started by: nans
6 Replies

3. UNIX for Dummies Questions & Answers

Transpose matrix, and rearrange columns common with another file

This is my first post, I apologize if I have broken rules. Some assistance with the following will be very helpful. I have a couple of files, both should ultimately have common columns only, arranged in the same order. This file needs to be transposed, to bring the rows to columns ... (2 Replies)
Discussion started by: abh.kumar
2 Replies

4. Shell Programming and Scripting

Command to extract all columns except the last few from a txt file

hello, i have publicly available txt file with little less than 300000 rows. i want to extract from column 1 to column 218 and save it in another text file. i use the cut command but the file is saved with multiple rows from the source file onto a single row in the destination. basically it is... (6 Replies)
Discussion started by: madrazzii
6 Replies

5. Shell Programming and Scripting

Transpose whole file and specific columns

Hi, I have a file like this a b c d e f g h i j k l Case1: I want to transpose the whole file Output1 a d g j b e h k c f i l Case2 Transpose a specific column - Say 3rd (6 Replies)
Discussion started by: jacobs.smith
6 Replies

6. Shell Programming and Scripting

Transpose few columns alone in a Text file

Hi Friends, I am Stuck up with a problem on transposing Rows to Coloumns.. Though there are many threads on this my problem is little difficult.. I have a tab separated file like Below, computer selling_loc currency_type manufacturer_name salesweek-wk1 sales-wk2 ...wk-3 ..wk4 till... (7 Replies)
Discussion started by: heinz_holan
7 Replies

7. Shell Programming and Scripting

Rows to Columns - File Transpose

Hi I have an input file and I want to transpose it but I need to take care that if any field is missing for a record it should be popoulated with space for that field - using a shell script INFILE ---------- emp=1 sal=2 loc=abc emp=2 sal=21 sal=22 loc=xyz emp=5 loc=abc OUTFILE... (10 Replies)
Discussion started by: 46019
10 Replies

8. Shell Programming and Scripting

How to edit a txt file ?

Hi, I need to edit a text file which is like this.. -- D3341000600 AGEC901164 XYZ SE0109 1RNVX AH 2009-01-19 2009-01-11 2009-01-21 -- D3341000600 AGEC901164 XYZ SE0109 1RNVX AH 2009-01-19 2009-01-11 2009-01-21 -- D3341006000 AGEC921472 ... (4 Replies)
Discussion started by: RRVARMA
4 Replies

9. Shell Programming and Scripting

Edit txt file using vi editor

Dear All I have a file called sample.txt which contains as follows HR Files records Loaded RecordDate Unloaded -- ---- -------- --------- ------------ ------ 00 567 77777 67896 0 0 01 345 345567 45678 0 ... (1 Reply)
Discussion started by: tkbharani
1 Replies

10. AIX

How to edit txt file by shell script?

What I want to do is just delete some lines from a text file, I know it's easy using copy and redirect function, but what I have to do is edit this file (delete the lines) directly, as new lines may be added to the text file during this period. Can AIX do this ? # cat text 1:line1 2:line2... (3 Replies)
Discussion started by: dupeng
3 Replies
Login or Register to Ask a Question