Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

conversion and transpositions of columns and rows

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers conversion and transpositions of columns and rows
# 1  
Old 02-08-2011
conversion and transpositions of columns and rows
Hi,
I am wondering how to do this more efficiently than I am currently doing it:
I have these 3 columns
Code:
3315    E14    1
397      E14    2
321      E14    3
27        E14    4
7          E14    5
2          E14    6

But I like them to be displayed like this:
Code:
            1          2        3      4       5       6      
E14     3315   397    321   27     7        2

I am doing the conversion right now half in unix and half by hand.
I wrote E 14 by hand and 1-6 as well.
To fill in the other numbers I do the following.
Code:
cat my_file.txt |grep E15|cut -f 1|tr '\n' ' '|perl -ne "s/ /\t/g;print"
3315   397    321   27     7        2

Then I cut and paste everything into one file.

Is there a more efficient way to do so? Especially since my file has 30 of these lines that have to be converted. It would be great if I could get this conversion/transposition done automatically.
Thank you! Smilie
Last edited by Franklin52; 02-09-2011 at 03:27 AM.. Reason: Please use code tags, thank you
# 2  
Old 02-08-2011
try:
Code:
echo "3315 E14 1
397 E14 2
321 E14 3
27 E14 4
7 E14 5
2 E14 6" |awk '{for (i=1;i<=NF;i+=2) {a[NR,i]=$i};m=NR;x=$2}END{ {for(j=1;j<=m;j++) printf a[j,3]" "}print "";printf x" ";{for(j=1;j<=m;j++) printf a[j,1]" "}print"" }'
1 2 3 4 5 6
E14 3315 397 321 27 7 2

# 3  
Old 02-08-2011
Code:
awk 'NR==1{line1=$NF;line2=$2 FS $1;next}{line1=line1 FS $NF;line2=line2 FS $1}END{print line1 RS line2}' infile

# 4  
Old 02-09-2011
What do I do if I have:
3315 E14 1
397 E14 2
321 E14 3
27 E14 4
7 E14 5
2 E14 6
2000 E9 3
3000 E9 4
500 E9 6

I do want to transform it into:
 
1 2 3 4 5 6
E14 3315 397 321 27 7 2
E9 2000 3000 500
I tried to do it with the commands above, but didn't manage E9 to be printed on the line below E14 and at the same time have the counts align correctly.
Thank you for your help!
Last edited by danieladna; 02-10-2011 at 12:06 AM..
# 5  
Old 02-10-2011
I was thinking of a table similar to the one in the xls spreadsheet...
Thanks again! Smilie
Table.xls (17.5 KB) 
# 6  
Old 02-10-2011
Try...
Code:
$ cat file1
397 E14 2
3315 E14 1
321 E14 3
27 E14 4
7 E14 5
2 E14 6
2000 E9 3
3000 E9 4
500 E9 6

$ awk ' { d[$2, $3] = $1 }
      ! ($2 in k) { k[$2] = 1 ; h[++c] = $2 }
      m < $3 { m = $3 }
      ! d["", $3] { d["", $3] = $3 }
      END {
            for (x = 0; x <= c; x++) {
                  z = h[x]
                  printf "%s%s", z, OFS
                  for (y = 1; y <= m; y++) {
                        printf "%s%s", d[z, y], (y == m ? ORS : OFS)
                  }
            }
      }
    ' OFS="\t" file1 > file2

$ cat file2
        1       2       3       4       5       6
E14     3315    397     321     27      7       2
E9                      2000    3000            500

$

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Conversion of rows to columns using awk based om column value

HI, My Input file data is dn:adcfgeneral id:13343 Name:xxxxxx Password:iutyerwuitywue wpuwt tuiytruityrutyrwtyrwp dn:cdferwjyyyy id:3875 Name:yyyy Password :hgfdsjkfhdsfkdlshf dshfkldshfdklsfh interset:uiuiufj My output should be ... (6 Replies)
Discussion started by: dineshaila
6 Replies

2. Shell Programming and Scripting

Date conversion DD-MMM-YY to YYYYMMDD for many columns in a file

Hi, Input Col1|col2|col3|col4|col5|col6-------col26 1|2|3|10-Nov-67|10-Nov-97|4|5-------100 1|2|3|11-Feb-01|01-Dec-15|4|5-------2000 Output Col1|col2|col3|col4|col5|col6-------col26 1|2|3|19671110|19971010|4|5-------100 1|2|3|20010211|20151201|4|5-------2000 I want to convert... (3 Replies)
Discussion started by: onesuri
3 Replies

3. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

4. Solaris

Rows to column conversion in Solaris

i am using the command pkginfo -l | /usr/xpg4/bin/grep -e 'VENDOR' -e 'NAME' -e 'VERSION' >> /tmp/test1.txt in order to get name,vendor and version of the applications installed on Solaris machine.The output stored in test1.txt in as shown below: NAME: Solaris Zones (Usr) VERSION: ... (4 Replies)
Discussion started by: omkar.jadhav
4 Replies

5. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

6. Shell Programming and Scripting

Awk: group rows by id and simple conversion

Hi all, I am a newbie to awk and trying to learn by doing examples. I got stuck at this relatively simple conversion. The start file looks like: 1 2 "t1" 1 3 "h1" 2 1 "h1" 2 2 "h2" and I want to convert it into 1 t1:2, h1:3; 2 h1:1, h2:2; Thanks. (9 Replies)
Discussion started by: eagle_fly
9 Replies

7. 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

8. UNIX for Dummies Questions & Answers

Columns to rows conversion in unix

Hi All, My requirement is to convert the rows to columns, please help me how to do in one command. Ex: source file is having data like ABC,XYZ,123,987,KKK,XXX,666 Need output like ABC XYZ 987 KKK XXX 666 Regards, Pavan. (3 Replies)
Discussion started by: madsongtel
3 Replies

9. Shell Programming and Scripting

conversion: 3 columns into matrix

Hi guys, here https://www.unix.com/shell-programming-scripting/193043-3-column-csv-correlation-matrix-awk-perl.html I found awk script converting awk '{ OFS = ";" if (t) { if (l != $1) t = t OFS $1 } else t = OFS $1 x = x ? x OFS $NF : $NF l = $1 }... (2 Replies)
Discussion started by: grincz
2 Replies

10. Shell Programming and Scripting

complicated row to rows conversion

I have a file containing rows with the following format. Field1|Field2|Field3|data1:data data2:data data3:"dataA:data dataB:data" data4:data:data (and so on) I need to format the above row into multiple rows that look like this: Field1|Field2|Field3|data1|data ... (2 Replies)
Discussion started by: newreverie
2 Replies
Login or Register to Ask a Question