Add all 4 column entries for similar column ids


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Add all 4 column entries for similar column ids
# 1  
Add all 4 column entries for similar column ids

Hi,

I want to write a script which will add the entries in all columns for the same column id. I can do it in excel, but I need to do this for 384 columns which will come down to 96 (384/4). How can I do this iteratively

Code:
A A A A B B B B C C C C
1 0 1 0 2 1 4 5 3 4 5 6
2 0 0 2 3 5 70 100 1 0 90 99
5 6 7 8 1 2 3 2 3 5  5 3

output
Code:
A B C
2 12 18
4 208 190
26 8 16

Thanks,
# 2  
An awk approach:
Code:
awk '
        NR == 1 {
                for ( i = 1; i <= NF; i++ )
                {
                        A[i] = $i
                        H[$i]
                }
                for ( k in H )
                        printf "%s ", k
                printf "\n"
        }
        NR > 1 {
                for ( i = 1; i <= NF; i++ )
                        R[A[i]] += $i
                for ( k in R )
                {
                        printf "%s ", R[k]
                        R[k] = 0
                }
                printf "\n"
        }

' file

This User Gave Thanks to Yoda For This Post:
# 3  
Here is a solution in awk:

Code:
awk 'NR==1 {
   for(i=1;i<=NF;i++) C[$i]=C[$i]" "i
   for(col in C) printf col" "
   printf "\n"
   next
}
{for(col in C) {
   T=0
   for(i=split(C[col],v);i;i--) T+=$v[i]
   printf T" "
 }
 printf "\n"
} '

# 4  
Thanks Yoda. It worked but the output is not tab delimited and also it changed the columns order. Can I get it in tab delimited format?

---------- Post updated at 12:51 PM ---------- Previous update was at 12:50 PM ----------

Thanks Chubler_XL. I tried running it using sh script.sh file.txt >out.txt. Its taking forever to complete.
# 5  
I'm a little concerned with your solution Yoda.

Is it guaranteed that the totals printed are going to match the headings, i.e. will for (k in H) be in the same order as for (k in R)?

Also consider the following input:

Code:
A A A A B B B B C C C C
1 0 1 0 2 1 4 5 3 4 5 6
2 0 0 2 3 5 70 100 1 0 90 99
5 6 7 8 1 2 3 2 3 5  5 3
5 6 7 8

---------- Post updated at 07:01 AM ---------- Previous update was at 06:53 AM ----------

Reason my original solution took so long was I forgot to specify the input file!

This adjustment keeps same column order as original file

Code:
awk 'NR==1 {
   for(i=1;i<=NF;i++) {
      G[i]=$i
      if (!($i in C)) {
          C[$i];
          H[++cols]=$i
      }
   }
   for(i=1;i<=cols;i++) printf "%s\t", H[i]
   printf "\n"
   next
}
{
 for(i=1;i<=NF;i++) T[G[i]]+=$i
 for(i=1;i<=cols;i++) {
     printf "%s\t",0+T[H[i]]
     T[i]=0
 }
 printf "\n"
} ' infile


Last edited by Chubler_XL; 12-11-2013 at 06:06 PM.. Reason: Fixed typos and avoid GNU delete command
This User Gave Thanks to Chubler_XL For This Post:
# 6  
Thanks Chubler_XL. It worked as I wanted.(did not change the order and the output is tab delimited). thanks again.
# 7  
With Perl:

Code:
perl -lane'
  $" = "\t";
  do {
    @f = @F;
    $t{$_}++ or push @o, $_ for @F;
    print "@o";
    next 
    } if $. == 1;
  $s{$f[$_]} += $F[$_] for 0..$#F;
  print "@{[map $s{$_}, @o]}"; 
  %s = ();
  ' infile

Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #452
Difficulty: Medium
San Francisco has a Linux-based traffic controller computer that meets the latest Advanced Transportation Controller (ATC) standards.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Add column and multiply its result to all elements of another column

Input file is as follows: 1 | 6 2 | 7 3 | 8 4 | 9 5 | 10 Output reuired (sum of the first column $1*$2) 1 | 6 | 90 2 | 7 | 105 3 | 8 | 120 4 |9 | 135 5 |10 | 150 Please enclose sample input, sample output, and code... (5 Replies)
Discussion started by: Sagar Singh
5 Replies

2. Shell Programming and Scripting

Add Column base on other Column Data

HI Guys, I want add one extra Column base on 3rd Column . Input :- M204 MS204_154 :vsDataUeMe M204 MS204_154 es:sMeasure 0 M204 MS204_154 es:90ilterCoe 9 M204 MS204_154 es:searchE9090ortTime 40 M204 MS204_154 es:servOrPrioI90HoTimer 4000 M204 MS204_154 es:ueMeajllls154545 TRUE... (5 Replies)
Discussion started by: pareshkp
5 Replies

3. Shell Programming and Scripting

Add a character C in a column if that column is blank

I have some files that look as follows. I need to add a character 'C' in the fifth column if that column is blank. I prefer in-place editing. 1 1 B M 0 0 203 0, 0.0 0, 0.0 0, 0.0 0, 0.0 0.000 360.0 360.0 360.0 141.9 15.4 28.8 66.1 2 2 B A ... (21 Replies)
Discussion started by: thejitha
21 Replies

4. UNIX for Dummies Questions & Answers

To find similar items in a column

HI, I have a long file which looks like "1xxx_0_1" "1xxx" 500 5 "ABC*3-DEF*3-LL" "2yyy_0_1" "2yyy" 600 10 "ABC*2-DEF*2-LL" "3ddd_0_1" "3ddd" 150 52 "ABC*3-DEF*3-LL" "1xxx_0_1" "1xxx" 500 5 "ABC*3-DEF*3-LL" "2yyy_0_1" "2yyy" 600 10 "ABC*2-DEF*2-LL" ... (3 Replies)
Discussion started by: XXLMMN
3 Replies

5. UNIX for Dummies Questions & Answers

Find the average based on similar names in the first column

I have a table, say this: name1 num1 num2 num3 num4 name2 num5 num6 num7 num8 name3 num1 num3 num4 num9 name2 num8 num9 num1 num2 name2 num4 num5 num6 num4 name4 num4 num5 num7 num8 name5 num1 num3 num9 num7 name5 num6 num8 num3 num4 I want a code that will sort my data according... (4 Replies)
Discussion started by: FelipeAd
4 Replies

6. Shell Programming and Scripting

Merge two files with similar column entries

Hi , I have few files which contains user name and data transfer rate in MBs and this data is collected for year and for each month report is saved in 12 different files I have to merge all the files to prepare the final report Files are as below Similarly I have 10 more files ... (5 Replies)
Discussion started by: pratapsingh
5 Replies

7. UNIX for Dummies Questions & Answers

Match values/IDs from column and text files

Hello, I am trying to modify 2 files, to yield results in a 3rd file. File-1 is a 8-columned file, separted with tab. 1234:1 xyz1234 blah blah blah blah blah blah 1234:1 xyz1233 blah blah blah blah blah blah 1234:1 abc1234 blah blah blah blah blah blah n/a RRR0000 blah blah blah... (1 Reply)
Discussion started by: ad23
1 Replies

8. Shell Programming and Scripting

to add special tag to a column based on column condition

Hi All, I have following html code <TR><TD>9</TD><TD>AR_TVR_TBS </TD><TD>85000</TD><TD>39938</TD><TD>54212</TD><TD>46</TD></TR> <TR><TD>10</TD><TD>ASCV_SMY_TBS </TD><TD>69880</TD><TD>33316</TD><TD>45698</TD><TD>47</TD></TR> <TR><TD>11</TD><TD>ARC_TBS ... (9 Replies)
Discussion started by: ckwan
9 Replies

9. Shell Programming and Scripting

Need to add letters to a column and add in a new column subtracting from another column

So I have this input 1 10327 rs112750067 T C . PASS DP=65;AF=0.208;CB=BC,NCBI 1 10469 rs117577454 C G . PASS DP=2055;AF=0.020;CB=UM,BC,NCBI 1 10492 rs55998931 C T . PASS DP=231;AF=0.167;CB=BC,NCBI 1 10583 rs58108140 G A ... (3 Replies)
Discussion started by: kellywilliams
3 Replies

10. Shell Programming and Scripting

Parse 1 column and add 2nd column

I'm racking my brain on this one! :( I have a list like this: Paul 20 Paul 25 Paul 30 Frank 10 Julie 15 Julie 13 etc, etc... I've been trying to figure out a way to have the output display the name in the first column ONCE and add the numbers in the second column and display that... (2 Replies)
Discussion started by: sdlennon
2 Replies

Featured Tech Videos