Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sum pairs of values and per level and state which one is absent

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sum pairs of values and per level and state which one is absent
# 1  
Old 08-02-2015
Sum pairs of values and per level and state which one is absent
There are 3 values (cols 3,4,5) for each name (col 1) and level (col2 ). Some levels for some of the names do not exist. Files are space delimited

Code:
SSGG765  L1  1 2 3
SSGG765  L2  4 5 6
GUHJHJJ7  L1  7 8 9
GUHJHJJ7   L5  10 12 13
FFRTGGG   L1  11 1 3

Given a list of pairwise names, I want to sum the 3 values , level-wise and also if some name is absent/missing for a particular level, I want to state that in the last column

Code:
SSGG765 GUHJHJJ7
GUHJHJJ7 FFRTGGG
FFGGGFF  TTYYRRRR

Looking for output

Code:
SSGG765  GUHJHJJ7 L1 8 10 12 None
SSGG765  GUHJHJJ7 L2 4  5   6  GUHJHJJ7
SSGG765  GUHJHJJ7  L5  10 12 13  SSGG765
GUHJHJJ7 FFRTGGG   L1  18 9 12 None
GUHJHJJ7 FFRTGGG   L5  10 12 13 FFRTGGG
FFGGGFF  TTYYRRRR  Na Na Na Na Both


Here is what I am fighting with

Code:
awk 'NR=FNR{ a[$1]=$3; b[$1]=$4; c[$1]=$5; next } 
       if (($1 in a) && !($2 in a )) { $3=a[$1] ; $4 = b[$1]; $5=c[$1];$6=$2}
       if (!($1 in a) && ($2 in a )) { $3=a[$1] ; $4 = b[$1]; $5=c[$1];$6=$1}
       if (($1 in a) && ($2 in a )) { $3+=a[$1] ; $4+ = b[$1]; $5+=c[$1];$6="None"}
       if (!($1 in a) && !($2 in a )) { $3="Na" ; $4 = "Na"; $5="Na";$6="Both"}1' file1 file2

Please assist, and also let me know if i can explain any better
# 2  
Old 08-02-2015
This may be close but I'm unable to determine how you get the 3rd line of you sample output.

Code:
awk '
FNR==1{file++}
file==1 { O[$1]=$2; S[$2]=$1; L=$1; next}
file==2{
   T1[$1,$2] += $3
   T2[$1,$2] += $4
   T3[$1,$2] += $5
   T1[S[$1],$2] += $3
   T2[S[$1],$2] += $4
   T3[S[$1],$2] += $5
}
file==3 {
   if ($1 in O) {
       if ($1 SUBSEP $2 in T1)
         if (O[$1] SUBSEP $2 in T1) v="None"
         else v=O[$1]
       else
          if (O[$1] SUBSEP $2 in T1) v=$1
          else v="Both"
       print $1,O[$1],$2,T1[$1,$2],T2[$1,$2],T3[$1,$2],v
   } else print L,O[L],"Na","Na","Na","Both"
}' file2 file1 file1

This User Gave Thanks to Chubler_XL For This Post:
jalaj841
# 3  
Old 08-02-2015
Hi Chubler_XL,

Did you mean this line?

Code:
SSGG765  GUHJHJJ7  L5  10 12 13  SSGG765


This is because this line exists
Code:
GUHJHJJ7  L5  10 12 13

but for L5, SSGG765 does not have any value..

So you can think of it as the sum of the following rows

Code:
SSGG765 L5 0 0 0
GUHJHJJ7  L5  10 12 13

with
Code:
SSGG765

as the last column (as it is missing).. does this make sense?
# 4  
Old 08-03-2015
OK, I think I have the requirement now. Try this

Code:
awk '
FNR==NR{ keys[++numkey]=$1; O[$1]=$2; next}
{
    L[$2]
    T1[$1,$2]+=$3
    T2[$1,$2]+=$4
    T3[$1,$2]+=$5
}
END {
   for(i=1;i<=numkey;i++) {
       key=keys[i]
       prn=0
       for(lvl in L) {
           if ((key SUBSEP lvl) in T1) {
                  V1=T1[key,lvl]
                  V2=T2[key,lvl]
                  V3=T3[key,lvl]
                  miss=O[key]
           } else V1=V2=V3= x

           if ((O[key] SUBSEP lvl) in T1) {
                  if(V1==x) miss=key
                  else miss="None"
                  V1 += T1[O[key],lvl]
                  V2 += T2[O[key],lvl]
                  V3 += T3[O[key],lvl]
           }
           if (V1 != x) {
             prn=1
             print key,O[key],lvl, V1, V2, V3, miss
           }
       }
       if (!prn) print key,O[key],"Na", "Na", "Na", "Na", "both"
   }
}' file2 file1

This User Gave Thanks to Chubler_XL For This Post:
jalaj841
# 5  
Old 08-03-2015
Hi Chubler_XL,

The code is working perfectly with the sample data,but if i add a couple of more levels, it seems to miss some rows in the output

Sample 2

Code:
SSGG765  L1  1 2 3
SSGG765  L2  4 5 6
GUHJHJJ7  L1  7 8 9
GUHJHJJ7   L5  10 12 13
GUHJHJJ7   L15  0 1 3
FFRTGGG   L1  11 1 3
SSGG765  L11  11 12 23


Output is

Code:
SSGG765 GUHJHJJ7 L1 8 10 12 None
SSGG765 GUHJHJJ7 L2 4 5 6 GUHJHJJ7
SSGG765 GUHJHJJ7 L11 11 12 23 GUHJHJJ7
SSGG765 GUHJHJJ7 L5 10 12 13 SSGG765
GUHJHJJ7 FFRTGGG L1 18 9 12 None
GUHJHJJ7 FFRTGGG L5 10 12 13 FFRTGGG
FFGGGFF TTYYRRRR Na Na Na Na both

which is missing the rows in green

Code:
SSGG765 GUHJHJJ7 L1 8 10 12 None
SSGG765 GUHJHJJ7 L2 4 5 6 GUHJHJJ7
SSGG765 GUHJHJJ7 L11 11 12 23 GUHJHJJ7
SSGG765 GUHJHJJ7 L5 10 12 13 SSGG765
SSGG765 GUHJHJJ7 L15  0 1 3 SSGG765
GUHJHJJ7 FFRTGGG L15  0 1 3 FFRTGGG
GUHJHJJ7 FFRTGGG L1 18 9 12 None
GUHJHJJ7 FFRTGGG L5 10 12 13 FFRTGGG
FFGGGFF TTYYRRRR Na Na Na Na both

# 6  
Old 08-03-2015
The reported problem was because count was zero for the V1 value. Updates in red should support zero counts correctly:

Code:
awk '
FNR==NR{ keys[++numkey]=$1; O[$1]=$2; next}
{
    L[$2]
    T1[$1,$2]+=$3
    T2[$1,$2]+=$4
    T3[$1,$2]+=$5
}
END {
   for(i=1;i<=numkey;i++) {
       key=keys[i]
       prn=0
       for(lvl in L) {
           if ((key SUBSEP lvl) in T1) {
                  V1=T1[key,lvl]
                  V2=T2[key,lvl]
                  V3=T3[key,lvl]
                  miss=O[key]
           } else V1=V2=V3= x

           if ((O[key] SUBSEP lvl) in T1) {
                  if(length(V1)) miss="None"
                  else miss=key
                  V1 += T1[O[key],lvl]
                  V2 += T2[O[key],lvl]
                  V3 += T3[O[key],lvl]
           }
           if (length(V1)) {
             prn=1
             print key,O[key],lvl, V1, V2, V3, miss
           }
       }
       if (!prn) print key,O[key],"Na", "Na", "Na", "Na", "both"
   }
}' file2 file1

These 2 Users Gave Thanks to Chubler_XL For This Post:
jalaj841 RavinderSingh13
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to sum the value with negative values?

Hi Gurus, I have requirement need to sum the value, the logic is if the value is negative then time -1, I tried below two ways. one is failed, another one doesn't work. awk -F"," '{if($8< 0 $8*-1 else $8) sum+=$8}{print sum, $8} END{printf("%.2f\n",sum)}' awk -F","... (4 Replies)
Discussion started by: ken6503
4 Replies

2. UNIX for Dummies Questions & Answers

Sum up values followed by pattern

I have a file with data merged from multiple files. File contains header, data and trailer of multiple files. The trailer starts with 99 and delimiter is ~. Trailer 99~120 99~30 As it is a merged file we i have multiple lines followed by 99~. Need help to find sum of values which are there... (4 Replies)
Discussion started by: santoshdrkr
4 Replies

3. UNIX for Dummies Questions & Answers

sum values based on ID

Hi, I would like to be able to sum up the counts of a column by the ID of another column. Example (although the actual file I have has thousands of IDs): Input file: A1BG-AS1:001 3 A1BG-AS1:002 0 A1BG-AS1:003 2 A1CF:001 1038 A1CF:002 105 A1CF:003 115 A1CF:004 137 Desired output... (3 Replies)
Discussion started by: fadista
3 Replies

4. UNIX for Dummies Questions & Answers

awk code to process column pairs and find those with more than 1 set of possible values

Hi, I have a very wide dataset with pairs of columns starting from column 7 onwards (see example below). 0 123456 -1 0 0 -9 0 0 1 2 2 2 1 1 1 1 2 2... 0 123457 -1 0 0 -9 1 2 1 1 2 2 0 0 0 0 2 2... 0 123458 -1 0 0 -9 0 0 1 2 2 2 1 1 2 2 1 2... 0 123459 -1 0 0 -9 1 2 0 0 2 2 1 1 1 2 1 1...... (2 Replies)
Discussion started by: kasan0
2 Replies

5. Shell Programming and Scripting

Getting a sum of column values

I have a file in the following layout: 201008005946873001846130058030701006131840000000000000000000 201008006784994001154259058033001009527844000000000000000000 201008007323067002418095058034801002418095000000000000000000 201008007697126001722141058029101002214158000000000000000000... (2 Replies)
Discussion started by: jclanc8
2 Replies

6. Shell Programming and Scripting

How to sum up two decimal values?

I am running the following script : cat ind_sls_extr_UX.out_sorted | while read each_rec do count=`echo "${each_rec}" | cut -c1-2` if then final_amount=0 amount=`echo "${each_rec}" | cut -c280-287` echo "${amount}" final_amount=`expr ${amount} + ${amount}` ... (7 Replies)
Discussion started by: mady135
7 Replies

7. Shell Programming and Scripting

How to sum values from top

Hi. Im looking for way to sum numbers from top. For example i have such command top -b -n | grep Cpu | cut -c 35 - 39 which give me output 97.0 . Ho can i do with that value any arithmetic actions (for example 97.0 +1)? Using c = $((top -b -n | grep Cpu | cut -c 35 - 39)) gives me... (8 Replies)
Discussion started by: qdf
8 Replies

8. Shell Programming and Scripting

how to sum values from 2 different files?

Hi I am trying to add count values from two different files into one file. Could any body please suggest me best command to do this? My problem was as follows: a.txt b.txt c.txt 10 20 30(needed) i tried cat a.txt b.txt > c.txt (its not adding the values) Thanks in advance.. Praveen (8 Replies)
Discussion started by: npk2210
8 Replies

9. Shell Programming and Scripting

How to sum column 1 values

I have a file file like this. I want to sum all column 1 values. input A 2 A 3 A 4 B 4 B 2 Out put A 9 B 6 (3 Replies)
Discussion started by: suresh3566
3 Replies
Login or Register to Ask a Question