Column manipulation


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Column manipulation
# 1  
Old 02-18-2015
Column manipulation

Hi,

I's like to ask for help with the following question:

this is an example file:

HTML Code:
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10
I need to have it re-arranged in the following manner: first 2 columns, zero, zero, last two columns, columns 3 to 8

the result should look like this:

HTML Code:
1 2 0 0 9 10 3 4 5 6 7 8
1 2 0 0 9 10 3 4 5 6 7 8
1 2 0 0 9 10 3 4 5 6 7 8
1 2 0 0 9 10 3 4 5 6 7 8
I've tried to do it with awk:

HTML Code:
awk '{print $1, $2, "0", "0", $9, $10; for(i=3;i<=8;i++) printf "%s ",$i ; print ""}' input-file > output-file
but the result looks like this:

Code:
1 2 0 0 9 10
3 4 5 6 7 8
1 2 0 0 9 10
3 4 5 6 7 8
etc.

Any help to achieve the file I need would be greatly appreciated!

Many thanks in advance!

Last edited by Scrutinizer; 02-18-2015 at 11:29 PM.. Reason: code tags
# 2  
Old 02-18-2015
Why you dont pass $3, $4, $5, $6, $7, $8 but rather try to use an incomplete for loop?
# 3  
Old 02-18-2015
I'm not sure what you mean. Do you suggest to write out $3 to $8 instead of the loop?
The thing is that this is an example. In reality, the file had over 200K columns and I need the loop (or something) to get all the columns.
Thanks!
# 4  
Old 02-18-2015
Yes i did thought to write it out.
So, this would mean, the changes are only to 'insert' two empty (as in contains '0') colums replacing colum 3 to (max colums - 2), and append those columns (3 to 2 less than maxium) at the end?

But with 200k columns... sorry.

---------- Post updated at 03:37 ---------- Previous update was at 03:33 ----------

Nevermind, untested, but it might work:
Code:
TMP=$$.tmp
printf "awk '{print \$1, \$2, "0", "0", \$9, \$10," > $TMP
for(i=3;i<=8;i++) { printf ", \$$i" >> $TMP ; } 
printf "}' input-file > output-file" >> $TMP

exec $TMP

hth

Last edited by sea; 02-18-2015 at 10:39 PM.. Reason: Code fix
This User Gave Thanks to sea For This Post:
# 5  
Old 02-18-2015
You are very close to solution try this

Code:
awk '{printf $1 OFS $2 OFS "0" OFS "0" OFS $9 OFS $10 OFS; for(i=3;i<=8;i++) printf "%s ",$i ; print ""}' infile
1 2 0 0 9 10 3 4 5 6 7 8 
1 2 0 0 9 10 3 4 5 6 7 8 
1 2 0 0 9 10 3 4 5 6 7 8 
1 2 0 0 9 10 3 4 5 6 7 8

This User Gave Thanks to Akshay Hegde For This Post:
# 6  
Old 02-18-2015
Thank you guys!
It works!
# 7  
Old 02-18-2015
Try:
Code:
awk '{printf "%s ",$1 OFS $2 OFS "0 0" OFS $9 OFS $10; for(i=3;i<=8;i++) printf "%s ",$i ; print ""}' file

or
Code:
awk '{s=""; for(i=3;i<=8;i++) s=s OFS $i; print $1, $2, "0", "0", $9, $10 s}'  file

or simply:
Code:
awk '{print $1, $2, "0", "0", $9, $10, $3, $4, $5, $6, $7, $8}' file





---
Note: when using printf, it is advisable to not leave the format field unspecified, so instead of
Code:
printf $1 OFS $2 OFS "0" OFS ...

use
Code:
printf %s, $1 OFS $2 OFS "0" OFS ...

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Column manipulation of a file

In a space delimited file how would you go about removing 2 and 3 column of a file and add "word" to the first column of the file in both awk and vi? (2 Replies)
Discussion started by: cokedude
2 Replies

2. Shell Programming and Scripting

Column manipulation

Hallo Team, I have a .csv file. I would like to have another column called Prefix. Under column Prefix i would like to have the first 3 numbers from the theird column. Other Licenced Operator Alpha Code Numbering Per minute Interconnect Fee per rate group (R Excluding VAT) ... (4 Replies)
Discussion started by: kekanap
4 Replies

3. Shell Programming and Scripting

Column manipulation and counter

Hello, I was wondering how I could change this input: cat f1 to produce this output: As you can tell, I have several unsorted repeating strings in col1 of the input file. Each of these strings has an associated word in col2. Col2 has a total of 4 possible words (go, stop,... (3 Replies)
Discussion started by: verse123
3 Replies

4. Shell Programming and Scripting

Column manipulation

Hi, I have an input file in which I need to add a column with the value of part of another column. I.e. I have the following line (example, not the actual data): field1,field2,field3,test12,field5,field6I need to get the last two characters of the 4th field, and add them at the end as a... (5 Replies)
Discussion started by: Subbeh
5 Replies

5. UNIX for Dummies Questions & Answers

Adding a column to a text file based on mathematical manipulation

Hi, I have a tab delimited text file with three different columns. I want to add an extra column to the text file. The extra column will be the second column and it will equal third column - 1. How do I go about doing that? Thanks! Input: chr1 788822 rs11240777 chr1 1008567 rs9442372... (2 Replies)
Discussion started by: evelibertine
2 Replies

6. UNIX for Dummies Questions & Answers

[Solved] Column manipulation

Hi Everyone, I was wondering if someone could help me to transform my data into a format I need. Here is an example of what my data looks like E F G H A 1 2 3 4 B 5 6 7 8 C 9 1 2 3 D 4 5 6 7 and this is what I would need it to look like: AE 1 BE 5 CE 9 DE 4 AF 2 BF 6 CF 1 (6 Replies)
Discussion started by: zajtat
6 Replies

7. Shell Programming and Scripting

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

8. Shell Programming and Scripting

Changing one column of delimited file column to fixed width column

Hi, Iam new to unix. I have one input file . Input file : ID1~Name1~Place1 ID2~Name2~Place2 ID3~Name3~Place3 I need output such that only first column should change to fixed width column of 15 characters of length. Output File: ID1<<12 spaces>>Name1~Place1 ID2<<12... (5 Replies)
Discussion started by: manneni prakash
5 Replies

9. Shell Programming and Scripting

Flat File column manipulation

Hi All, I have a tab delimited input file with say 4 fields (columns) as below : 0000443 1AGPR061 2006 Daiml 0002198 1B3XG0K2 1989 Chdds 0002199 1Bd64J0L 1990 Ch34s 0002275 1B3s4J0K 1989 Chadys 0002276 1B465302 2002 Dageml 0002290 1B45430K 1989 Cays I want the 2nd column in file to... (5 Replies)
Discussion started by: net
5 Replies

10. Shell Programming and Scripting

File Manipulation (Move Column Position)

Hi All, I have a comma separated value (.CSV) file like the one below. The file contains about 20000 lines. FileName EmpNo,Name,Age,Sex,Band,Spouse,Children,Salary, 1000,Arnold,24,M,B,N.A.,No,10000, 1001,Jenny,27,F,C,John,2,20000, ................................... What i need is to... (1 Reply)
Discussion started by: ultimate
1 Replies
Login or Register to Ask a Question