Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Arrange output based on rows into columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Arrange output based on rows into columns
# 1  
Old 10-25-2010
Arrange output based on rows into columns
Hi All,

I would like to ask help on how can i achieve below output.

Inputfile:
Code:
 
Oct11,apa1-daily,01:25:01
Oct11,apa2-daily,01:45:23
Oct12,apa1-daily,02:30:11
Oct12,apa2-daily,01:55:01
Oct13,apa1-off,01:43:34
Oct13,apa2-off,01:22:04

Desired output:
Code:
 
Clients             Oct11      Oct12         Oct13
apa1-daily       01:25:01   02:30:11
apa1-off                                        01:43:34
apa2-daily       01:45:23   01:55:01
apa2-off                                        01:22:04

Thanks in advance
Mars
# 2  
Old 10-25-2010
not quite what you're after, but this snippet should be close - you'll need to tweak the final formatting a little:

Code:
#  nawk -F, '
{
cl[$2]++
dl[$1]++
el[$2" "$1]=$3
}
END {
dd="Clients";for (d in dl){dd=dd","d}
print dd
for (c in cl){t=c;for (d in dl){t=t","el[c" "d]} print t}
}
' infile  
Clients,Oct11,Oct12,Oct13
apa1-daily,01:25:01,02:30:11,
apa1-off,,,01:43:34
apa2-daily,01:45:23,01:55:01,
apa2-off,,,01:22:04

HTH
This User Gave Thanks to Tytalus For This Post:
mars101
# 3  
Old 10-25-2010
Code:
awk -F, '
{a[$1 FS $2]=$3;b[$1]=$1;c[$2]=$2}
END { asort(b);asort(c);
      printf "Clients\t\t"; for (i in b) printf b[i]"\t\t";printf "\n"
       for (i in c)
         {  printf c[i]"\t";
            { for (j in b)
               printf (a[b[j] FS c[i]]=="")?"\t\t":a[b[j] FS c[i]]"\t"
            }
            printf "\n"
         }
    }
' infile |sort -n

Clients         Oct11           Oct12           Oct13
apa1-daily      01:25:01        02:30:11
apa1-off                                        01:43:34
apa2-daily      01:45:23        01:55:01
apa2-off                                        01:22:04

Not sure ,why asort(c) doesn't work as my expect, and I have to add |sort -n to sort it
This User Gave Thanks to rdcwayx For This Post:
mars101
# 4  
Old 10-26-2010
This is really what i need. Thank you so much. Both code works and just need for me to do some adjustment on format.

Really appreaciated your help.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Keep only columns in first two rows based on partial header pattern.

I have this code below that only prints out certain columns from the first two rows (doesn't affect rows 3 and beyond). How can I do the same on a partial header pattern “G_TP” instead of having to know specific column numbers (e.g. 374-479)? I've tried many other commands within this pipe with no... (4 Replies)
Discussion started by: aachave1
4 Replies

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

3. Shell Programming and Scripting

Convert rows to columns based on key and count

Team, I am having requirement to convert rows to columns Input is: key ,count, id1, pulse1, id2, pulse2 ,id3, pulse3 12, 2 , 14 , 56 , 15, 65 13, 3, 12, 32, 14, 23, 18, 54 22, 1 , 32, 42 Expected Out put: key, id,pulse 12, 14, 56 12, 15, 65 13 ,12, 32 13, 14 ,23 13, 18 ,54 22 ,32,... (3 Replies)
Discussion started by: syam1406
3 Replies

4. Shell Programming and Scripting

Convert rows to columns based on condition

I have a file some thing like this: GN Name=YWHAB; RC TISSUE=Keratinocyte; RC TISSUE=Thymus; CC -!- FUNCTION: Adapter protein implicated in the regulation of a large CC spectrum of both general and specialized signaling pathways GN Name=YWHAE; RC TISSUE=Liver; RC ... (13 Replies)
Discussion started by: raj_k
13 Replies

5. Shell Programming and Scripting

Extracting rows and columns in a matrix based on condition

Hi I have a matrix with n rows and m columns like below example. i want to extract all the pairs with values <200. Input A B C D A 100 206 51 300 B 206 100 72 48 C 351 22 100 198 D 13 989 150 100 Output format A,A:200 A,C:51 B,B:100... (2 Replies)
Discussion started by: anurupa777
2 Replies

6. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on the values of two columns (given ranges)

Hi, I have a tab delimited text file with multiple columns. The second and third columns include numbers that have not been sorted. I want to extract rows where the second column includes a value between -0.01 and 0.01 (including both numbers) and the first third column includes a value between... (1 Reply)
Discussion started by: evelibertine
1 Replies

7. Shell Programming and Scripting

Skipping rows based on columns

Hi, suppose I have the following file and certain rows have missing columns, how do i skip these rows and create an output file which has all the columns in it E/N Ko_exp %err Ko_calc %err diff diff- diff+ 0.95 ======== ======= ==== ======= ==== ===== ===== =====... (12 Replies)
Discussion started by: ramky79
12 Replies

8. Shell Programming and Scripting

Selecting rows based on values in columns

Hi My pipe delimited .txt file contains rows with 10 columns. Can anyone advise how I output to file only those rows with the letters ‘ci' as the first 2 characters in the 3rd column ? Many thanks (4 Replies)
Discussion started by: malts18
4 Replies

9. Shell Programming and Scripting

sql output from multiple rows and columns as variables in a script

This is for an Oracle journal import. I was using a pl/sql package and oracle API's. Oracle added invoker rights to their API's and now my package won't run. I didn't want to use their API's anyway. The only reason i was using pl/sql and the API's (just a package) was to utilize a cursor. How... (2 Replies)
Discussion started by: lmu
2 Replies

10. Shell Programming and Scripting

want to print output if u have rows and columns

U have a file File 1 0.3 0.2 0.4 0.3 0.8 0.11 0.22 0.4 0.23 0.45 0.56 0.78 0.98 0.11 0.32 0.2 0.4 0.45 0.54 0.2 0.33 0.44 0.21 0.22 0.98 0.8 0.2 0.34 0.54 0.98 0.12 0.1 0.22 0.32 0.34 0.89 0.22 File 2 rows columns 3 1 2 3 4 2 5 ... (8 Replies)
Discussion started by: cdfd123
8 Replies
Login or Register to Ask a Question