Performing aggregation in File

Shell Programming and Scripting
1

Hi,

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

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

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:

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:

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

2

something along these lines:
awk -f vf.awk myInputFile | sort -t '|' -k1,1 -k2,2 where vf.awk is:

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

Alternative:

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

Thank you so much. I will try out the suggested option.

Again again thanks.

Venkat