Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Transpose, Group, and Total

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Transpose, Group, and Total
# 1  
Old 07-22-2011
Transpose, Group, and Total
Need help with some data below.
I would like to transpose rows of column c, h to columns with those labels(c,h.)
I would then like to sort and group by the values in columns c then h.
For column h I would like to further sort by the parenthesis values.
After the sort I would like an additional column with the total for the sorted groups.
Finally below that I would like a sum of all the sorted types.
Please see below for example output.

HTML Code:
a:SOA
 b:250
 c:250
 d:FS
 e:TR
f: 17
 g:250 (02)
 h:00 
 i:10
a: SOA
 b:250
 c:250
 TR
 TR
 17
 3250 (03)
 00 
 10
 SOA
 250
 250
 FS
 TR
 17
 250
 00
 10
 SOA
 250
 250
 TR
 TR
 19
 3250 (01)
 00 (



Sample Output

HTML Code:
C                                                  H                                                                  Total
250                                       3250 (03)                                                                2
250                                       3250 (01)                                                                5
300                                       2000 (11)                                                              12
300                                        2050(25)                                                                4
Total                                                                                                                    23
Thnx!
Last edited by ravzter; 07-22-2011 at 10:14 PM..
# 2  
Old 07-23-2011
I don't have a complete solution, but it still may be of help to you; a more general way to tackle complex problems:

Break it up into simpler tasks:
1. extract just the lines starting with c: or h:
Code:
sed -n '/^c:/ p ' input

or
Code:
egrep '^c:' input > cfile

2. get rid of the "header":
Code:
sed -i 's/^c://'  cfile

3. paste them together two have two columns:
Code:
paste cfile hfile

4. sort
Code:
sort -nk1,2

5. add counts
Code:
awk '{cntC[$1]++; cntH[$2]++}END{...}'

and put it all together. Use temporary files for more simplicity and easier debugging, or pipe it all:
Code:
paste  <(sed -n '/^c:/ s/^c://p ' input) <(sed -n '/^h:/ s/^h://p ' input) | 
  sort -nk1,2 | awk ..

.

This could all be done by awk also, but breaking it up into separate steps helps debugging and gives you a better grasp of what each tool does.
This User Gave Thanks to mirni For This Post:
ravzter
# 3  
Old 07-23-2011
Quote:
Originally Posted by ravzter
Need help with some data below.
I would like to transpose rows of column c, h to columns with those labels(c,h.)
I would then like to sort and group by the values in columns c then h.
For column h I would like to further sort by the parenthesis values.
After the sort I would like an additional column with the total for the sorted groups.
Finally below that I would like a sum of all the sorted types.
[...]

Please post a bigger sample of your input and the expected output based on that exact sample of input.
# 4  
Old 07-25-2011
Hi Radoulov,

Thanks for the response! I will get some data to you, however I want to take it couple steps back. I'm actually extracting the data with a combination of grep and awk commands. I think it might be a good idea to provide that raw data, which I should available to you in a couple of hours.

Thanks for the help!
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Programming

Sql ORA-00937: not a single-group group function

I'm trying to return only one row with the highest value for PCT_MAX_USED. Any suggestions? When I add this code, I get the ORA-00937 error. trunc(max(decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100))) pct_max_used This is the original and returns all rows. select (select... (3 Replies)
Discussion started by: progkcp
3 Replies

2. Shell Programming and Scripting

need a one liner to grep a group info from /etc/group and use that result to search passwd file

/etc/group tiadm::345:mk789,po312,jo343,ju454,ko453,yx879,iy345,hn453 bin::2:root,daemon sys::3:root,bin,adm adm::4:root,daemon uucp::5:root /etc/passwd mk789:x:234:1::/export/home/dummy:/bin/sh po312:x:234:1::/export/home/dummy:/bin/sh ju454:x:234:1::/export/home/dummy:/bin/sh... (6 Replies)
Discussion started by: chidori
6 Replies

3. Shell Programming and Scripting

Sort the file contents in each group....print the group title as well

I've this file and need to sort the data in each group File would look like this ... cat file1.txt Reason : ABC 12345-0023 32123-5400 32442-5333 Reason : DEF 42523-3453 23345-3311 Reason : HIJ 454553-0001 I would like to sort each group on the last 4 fileds and print them... (11 Replies)
Discussion started by: prash184u
11 Replies

4. Shell Programming and Scripting

Merge group numbers and add a column containing group names

Hi All I do have a file like this with 6 columns. Groups of data merge together and the group number is indicated above each group. 1 1 12 26 289 3.2e-027 GCGTATGGCGGC 2 12 26 215 6.7e+006 TTCCACCTTTTG 3 9 26 175 ... (1 Reply)
Discussion started by: Lucky Ali
1 Replies

5. Shell Programming and Scripting

Calculate total space, total used space and total free space in filesystem names matching keyword

Good afternoon! Im new at scripting and Im trying to write a script to calculate total space, total used space and total free space in filesystem names matching a keyword (in this one we will use keyword virginia). Please dont be mean or harsh, like I said Im new and trying my best. Scripting... (4 Replies)
Discussion started by: bigben1220
4 Replies

6. Shell Programming and Scripting

Merge group numbers and add a column containing group names

I have a file in the following format. Groups of data merge together and the group number is indicated above each group. 1 adrf dfgr dfg 2 dfgr dfgr 3 dfef dfr fd 4 fgrt fgr fgg 5 fgrt fgr (3 Replies)
Discussion started by: Lucky Ali
3 Replies

7. Shell Programming and Scripting

Get the total of a field in all the lines of a group

Hi I have a Fixed format data file where I need to to get the total of the field at certain position in a file for a group of lines. In this data file I need the total of all the field ats position 30:39 for each line starting with 6 and for each group startign with 5. Which means for... (27 Replies)
Discussion started by: appsguy616
27 Replies

8. UNIX for Dummies Questions & Answers

grep running total/ final total across multiple files

Ok, another fun hiccup in my UNIX learning curve. I am trying to count the number of occurrences of an IP address across multiple files named example.hits. I can extract the number of occurrences from the files individually but when you use grep -c with multiple files you get the output similar to... (5 Replies)
Discussion started by: MrAd
5 Replies
Login or Register to Ask a Question