Making a composite file of transposed columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Making a composite file of transposed columns
# 1  
Old 04-22-2014
Making a composite file of transposed columns

Hello,

I have a directory with allot of tab delimited text files that have data that look like,
Code:
filePath	distance
(1,4-dioxan-2-ylmethyl)methylamine	0.0
4-methylmorpholine	0.0755473632594
1-propyl-4-piperidone	0.157792911954
heptaminol	0.158142893249
N-acetylputrescine	0.158689628956
spermidine	0.170417125303

for simplicity, I have include the first 7 rows of the file, but there are 25.

I need to sort through these and extract the string at column 1 row 2 and column 2 rows 3 through n. I then need to transpose this data so that there is one row for each input file.

For the data above, the row would look like,
Code:
(1,4-dioxan-2-ylmethyl)methylamine	0.075547363	0.157792912	0.158142893	0.158689629	0.170417125

There are many files, so if the second input file was,
Code:
filePath	distance
(1-methyl(4-piperidyl))(3-pyridylmethyl)amine	0.0
lidocaine	0.0971033747257
methoxyphenamine	0.106031307815
meperidine	0.107826404718
fenspiride	0.118603492524
tetracaine	0.122268535847

The output for both files would look like,
Code:
(1,4-dioxan-2-ylmethyl)methylamine	0.075547363	0.157792912	0.158142893	0.158689629	0.170417125
(1-methyl(4-piperidyl))(3-pyridylmethyl)amine	0.097103375	0.106031308	0.107826405	0.118603493	0.122268536

I'm guessing that it could be done with awk, but I have never transposed columns before. My alternative is excel, so suggestions would be appreciated. I have about 1500 files to sort through, so this will take a while if I can't get something automated.

thanks,

LMHmedchem
# 2  
Old 04-22-2014
something along these lines as a starter:
Code:
awk 'FNR==1 && FNR!=NR{print x;next} FNR==2{printf $1;next} FNR>2{printf("%s%s", OFS, $2)}END {print}' file1 file2 fileN

This User Gave Thanks to vgersh99 For This Post:
# 3  
Old 04-22-2014
This seems to work, but I still need to do some checking,
Code:
#!/bin/bash

FILELIST=$(ls './'*'.out.txt')

awk 'FNR==1 && FNR!=NR{print x;next} FNR==2{printf $1;next} FNR>2{printf("%s%s", OFS, $2)}END {print}' $FILELIST > _out.txt

The last row seems to have a problem. This is the input file for that row,
Code:
filePath	distance
{[3-(dimethylamino)propyl]amino}-N-naphthylcarboxamide	0.0
cyheptamide	0.0653914864686
oxyphenbutazone	0.0757147160492
melatonin	0.0870885855315
doxylamine	0.0925023853048
pyrilamine	0.0959876565376
tripelennamine	0.0978033137399
fentanyl	0.102771505703
R-apocodeine	0.103639227882
deacetyldiltiazem	0.104552836744
acepromazine	0.107673347298
diphenhydramine	0.110669230413
nefopam	0.110982685756
propiomazine	0.120416954487
desipramine	0.122117385721
benzydamine	0.124971244941
isoxsuprine	0.127177834512
propranolol	0.131049138037
promazine	0.132307894169
cinchonine	0.133675134701
dimefline	0.134699085898
nortriptyline	0.136270819322
benzphetamine	0.137019529216
imipramine	0.140966900695
ractopamine	0.14360602345
apomorphine	0.146103096076

and this is the output row,
{[3-(dimethylamino)propyl]amino}-N-naphthylcarboxamide 0.065391486 0.075714716 0.087088586 0.092502385 0.095987657 0.097803314 0.102771506 0.103639228 0.104552837 0.107673347 0.11066923 0.110982686 0.120416954 0.122117386 0.124971245 0.127177835 0.131049138 0.132307894 0.133675135 0.134699086 0.136270819 0.137019529 0.140966901 0.143606023 0.146103096076apomorphine

You can see that the text from column 1 has gotten into the value somehow.

LMHmedchem
# 4  
Old 04-22-2014
sorry about. Change END {print} to END {print x}
Login or Register to Ask a Question

Previous Thread | Next Thread

7 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to concatenate 2-columns by 2 -columns for a text file?

Hello, I want to concatenate 2-columns by 2-columns separated by colon. How can I do so? For example, I have a text file containing 6 columns separated by tab. I want to concatenate column 1 and 2; column 3 and 4; column 5 and 6, respectively, and put a colon in between. input file: 1 0 0 1... (10 Replies)
Discussion started by: huiyee1
10 Replies

2. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

3. UNIX for Advanced & Expert Users

Help in Deleting columns and Renaming Mutliple columns in a .Csv File

Hi All, i have a .Csv file in the below format startTime, endTime, delta, gName, rName, rNumber, m2239max, m2239min, m2239avg, m100016509avg, m100019240max, metric3min, m100019240avg, propValues 11-Mar-2012 00:00:00, 11-Mar-2012 00:05:00, 300.0, vma3550a, a-1_CPU Index<1>, 200237463, 0.0,... (9 Replies)
Discussion started by: mahi_mayu069
9 Replies

4. Shell Programming and Scripting

Reading columns, making a new file using another as template

Hi fellas, I have two files such as: File 1 interacao,AspAsp,AspCys,CysAsp,CysCys,classe File 2 interacao,AspAsp,CysAsp,AspCys,CysCys,classe beta_alfa, DA, CA, DD, CD,ppi Thus, I want to make a File 3 using the File 1 as model: e.g. File 3... (2 Replies)
Discussion started by: valente
2 Replies

5. UNIX for Dummies Questions & Answers

Removing columns from a text file that do not have any values in second and third columns

I have a text file that has three columns. But at the end of the text file, there are trailing lines that have missing second and third columns: 4 0.04972604 KLHL28 4 0.0497332 CSTB 4 0.04979822 AIF1 4 0.04983331 DECR2 4 0.04990344 KATNB1 4 4 4 4 How can I remove the trailing... (3 Replies)
Discussion started by: evelibertine
3 Replies

6. UNIX for Advanced & Expert Users

Relational Join (Composite Key)

hi, i have file 1: ====== 0501000|X1 0502000|X2 0501231|X3 0981222|X4 0502000|X6 0503000|X7 0932322|X8 file 2: ======= 050 0501 0502 09 098 (1 Reply)
Discussion started by: magedfawzy
1 Replies

7. Shell Programming and Scripting

CSV Table Filtered/transposed/matched using CSH

Hello Everybody: I have a csv file that i would want to be converted to a table (csv also) filtered, transposed and matched with the header (quite confusing, sorry). So the output can used on a spreadsheet and plot on a grap. I'm using CSH on unix. To further explain, here is an example input... (2 Replies)
Discussion started by: elmer1503
2 Replies
Login or Register to Ask a Question