Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Performing aggregation in File

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Performing aggregation in File
# 1  
Old 07-25-2013
Performing aggregation in File
Hi,

I need help in UNIX shell script to handle the following:

I have file called File1.txt which contains data something like below:

Code:
LibraryName|BookName|Flag-Indicator|COUNT

LIB1|BOOK1|A|12
LIB1|BOOK1|A|2
LIB2|BOOK2|I|1
LIB2|BOOK1|I|4
LIB1|BOOK2|A|12
LIB1|BOOK1|I|22
LIB1|BOOK2|I|8
LIB1|BOOK1|I|4
LIB1|BOOK2|I|3
LIB1|BOOK2|I|2
LIB2|BOOK1|A|12
LIB2|BOOK1|A|2
LIB2|BOOK1|A|22
LIB2|BOOK2|A|2
LIB2|BOOK2|I|3
LIB2|BOOK2|I|2
LIB1|BOOK1|I|3

First i need sort the file based on LibraryName, BookName and Flag-Indicator and sum the book count value based on Flag value.
The end result should be saved in the called File2.txt.

Sorted File:
Code:
LIB1|BOOK1|A|12
LIB1|BOOK1|A|2
LIB1|BOOK1|I|22
LIB1|BOOK1|I|3
LIB1|BOOK1|I|4
LIB1|BOOK2|A|12
LIB1|BOOK2|I|2
LIB1|BOOK2|I|3
LIB1|BOOK2|I|8
LIB2|BOOK1|A|12
LIB2|BOOK1|A|2
LIB2|BOOK1|A|22
LIB2|BOOK1|I|4
LIB2|BOOK2|A|2
LIB2|BOOK2|I|1
LIB2|BOOK2|I|2
LIB2|BOOK2|I|3


Final File2.txt should look like this:

Code:
LibraryName|BookName|A-Flag-COUNT|I-Flag-COUNT

LIB1|BOOK1|14|29
LIB1|BOOK2|12|13
LIB2|BOOK1|36|4
LIB2|BOOK2|2|16

Thanks in advance for your help.

Thanks,
Venkat
Last edited by Corona688; 07-25-2013 at 01:06 PM..
# 2  
Old 07-25-2013
something along these lines:
awk -f vf.awk myInputFile | sort -t '|' -k1,1 -k2,2 where vf.awk is:
Code:
BEGIN {
  FS=OFS="|"
  flags="A|I"
  n=split(flags, flagsA, FS)
}

{
  idx= $1 OFS $2
  lb[idx]
  sum[idx,$3]+=$4
}
END {
  for (i in lb) {
    printf i
    for(j=1;j<=n;j++)
      printf("%s%s%s", OFS, sum[i,flagsA[j]], (j==n)?RS:"")
  }
}

# 3  
Old 07-25-2013
Alternative:

Code:
awk '$3~"A"{A[$1"|"$2]+=$4}$3~"I"{I[$1"|"$2]+=$4}END{for(a in A)print a,A[a],I[a]}' FS="|" OFS="|" file \
| sort -t"|" -k1,1 -k2,2

# 4  
Old 07-25-2013
Thank you so much. I will try out the suggested option.

Again again thanks.

Venkat
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Solaris

How can I test link aggregation?

Hi, I have Solaris-10 server and link aggregation is configured on this in below way # dladm show-aggr key: 1 (0x0001) policy: L4 address: 3c:d9:2b:ee:a8:a (auto) device address speed duplex link state bnx1 3c:d9:2b:ee:a8:a... (8 Replies)
Discussion started by: solaris_1977
8 Replies

2. IP Networking

Link Aggregation

Hi ihave three link of internet and iwant to put one linux front of a firewall that this three linux speard firewall such az load balance and fialover but dont close any port and protocol and only firewall have been internet what way can i use for it ? are there any script and services do that... (0 Replies)
Discussion started by: mnnn
0 Replies

3. Shell Programming and Scripting

Aggregation of huge data

Hi Friends, I have a file with sample amount data as follows: -89990.3456 8788798.990000128 55109787.20 -12455558989.90876 I need to exclude the '-' symbol in order to treat all values as an absolute one and then I need to sum up.The record count is around 1 million. How... (8 Replies)
Discussion started by: Ravichander
8 Replies

4. Programming

Performing a while for two files

Hi, This is for Perl. I have a while loop, and would like to process two files, the formats are given below : access access.<previousday>-* How can perform a while loop on both logs, while creating the logic for access.<previousday>-* to get format like "access.20130615-124139" when... (11 Replies)
Discussion started by: susankoperna1
11 Replies

5. HP-UX

Break Link Aggregation in HP UX.

Hi, I want to Break the Link Aggregation. My aggregation are lan0+lan1 = lan900. Now I want to break this and put the IP in lan0. But i have cluster environment and this is the main database server. So It need to change in cluster script. But I dont know where I have to change it. Please... (1 Reply)
Discussion started by: mkiron
1 Replies

6. Solaris

Link aggregation

Me again :) I'm trying to find a page describing the L2, L3 und L4 modes of dladm. It's nice to read "hashed by ip header", but how should I use that? On the file-server it's ok to have the six interfaces serving six clients each on it's own. But an rsync connection via switch between two... (8 Replies)
Discussion started by: PatrickBaer
8 Replies

7. HP-UX

Link Aggregation HPUX

Hi, Hoping someone can offer some advice on something i have not dealt with before. We have a server that seems to have some very strange configuration done on it. It has 2 physical NIC's and rather than both be aggregated into LAN900 we have 1 in LAN900 and 1 in LAN901? (See Below)... (2 Replies)
Discussion started by: Andyp2704
2 Replies

8. Shell Programming and Scripting

Conditional aggregation and print of a column in file

Hi My input file looks like field1 field2 field3 field4 field5 field1 field2 field3 field4 field5 field1 field2 field3 field4 field5 :::::::::::: :::::::::::: There may be one space of multiple spaces between fields and no fields contains spaces in them. If field 1 to 4 are equal for... (3 Replies)
Discussion started by: bittoo
3 Replies

9. Solaris

Link Aggregation

Hi there I have a requirement to provide failover to our customer boxes in case of interface / switch failure, I have been looking at Solaris Link Aggregation with LACP and I wanted to ask a question Ive seen multiple websites that say the following Does this also mean that if the... (2 Replies)
Discussion started by: hcclnoodles
2 Replies

10. UNIX for Dummies Questions & Answers

performing queries on file data

Can anyone help.. I have a .dat file which contains 5 columns of data. I need to select one row containing just 3 of the columns. I'm not sure how to select a single row? I then need perform arithmetic on on two out of the three columns and display the output in a new column. For example.. ... (1 Reply)
Discussion started by: computersaysno
1 Replies
Login or Register to Ask a Question