Sum of all columns in all files in one output file


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Sum of all columns in all files in one output file
# 1  
Old 07-04-2011
Sum of all columns in all files in one output file

If I have say 4 files like this:

File1:

Code:
1 3 4
7 7 0
5 7 5
9 1 2
7 4 8

File2:

Code:
1 4 6 
2 5 7 
1 2 3 
6 0 3
0 3 8

File3:

Code:
1 4 7
4 2 6 
1 2 4
9 0 8
8 5 3

File4:

Code:
2 6 4 
2 3 8
7 3 5
6 3 0
8 9 4

And I want an output that sums all the columns in one file:

sum_col1_file1 sum_col2_file1 sum_col3_file1
sum_col1_file2 sum_col2_file2 sum_col3_file2
sum_col1_file3 sum_col2_file3 sum_col3_file3
sum_col1_file4 sum_col2_file4 sum_col3_file4

so the output of the files above would be:

Code:
29  14  19
10  13  27
23  24  28
25  22  21

Any idea how I can get that (using awk maybe?) ? Smilie
# 2  
Old 07-04-2011
This will handle multiple files and multiple columns. Each file can have a different number of columns.

Code:
#!/usr/bin/env ksh
awk '
    function show_totals(   i )
    {
            for( i = 1; i <= max_seen; i++ )
                printf( "%d ", count[i] );
            printf( "\n" );
    }

    {
        if( last && last != FILENAME )
        {
            show_totals();
            delete count;          # if using nawk use: split( "", count, "." )
            max_seen = 0;
        }
        last = FILENAME;

        for( i = 1; i <= NF; i++ )
            count[i] += $(i);

        if( max_seen < NF )
            max_seen = NF;
    }
    END {
        show_totals();
    }
' file1 file2 file3 file4    # or as many as you have
exit

This User Gave Thanks to agama For This Post:
# 3  
Old 07-05-2011
Quote:
Originally Posted by agama
This will handle multiple files and multiple columns. Each file can have a different number of columns.

I missed mentioning that in the real files I have decimal numbers. How can I modify this code to be used for all numbers not just integers? (I mean so that I get decimal numbers in the output file as well)
# 4  
Old 07-05-2011
Check printf and %f.

Here a simple shell loop and awk code to accomplish the same :
file[1-4] matches file1 to file4.
Code:
for i in file[1-4]; do awk ' { sum1+=$1;sum2+=$2;sum3+=$3} END { printf ("%f,%f,%f\n",sum1,sum2,sum3) }  ' $i >> out.file; done

This User Gave Thanks to Peasant For This Post:
# 5  
Old 07-05-2011
Code:
for f in File*; do
	awk '{for(i=1;i<=NF;i++)A[i]+=$i}END{for(i in A)printf A[i]" ";print ""}' "$f"
done

# 6  
Old 07-05-2011
Hi.

From the Debian repository as well as the home for num-utils:
Code:
#!/usr/bin/env bash

# @(#) s1	Demonstrate numsum from num-utils.
# http://suso.suso.org/programs/num-utils/
# Version: 0.5-9

# Utility functions: print-as-echo, print-line-with-visual-space, debug.
# export PATH="/usr/local/bin:/usr/bin:/bin"
pe() { for _i;do printf "%s" "$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "$*"; }
db() { ( printf " db, ";for _i;do printf "%s" "$_i";done;printf "\n" ) >&2 ; }
db() { : ; }
C=$HOME/bin/context && [ -f $C ] && $C

for file in data*
do
  pe
  pe " Sample data line and column sum for file $file:"
  head -n1 $file
  /usr/bin/numsum -c $file
done

exit 0

producing:
Code:
% ./s1

Environment: LC_ALL = C, LANG = C
(Versions displayed with local utility "version")
OS, ker|rel, machine: Linux, 2.6.26-2-amd64, x86_64
Distribution        : Debian GNU/Linux 5.0.8 (lenny) 
GNU bash 3.2.39

 Sample data line and column sum for file data1:
1 3 4
29 22 19

 Sample data line and column sum for file data2:
1 4 6 
10 14 27

 Sample data line and column sum for file data3:
1 4 7
23 13 28

 Sample data line and column sum for file data4:
2 6 4 
25 24 21

 Sample data line and column sum for file data5:
1.1 3.2 4.3
30.7 24.2 21.7

See the web page noted in the script for details ... cheers, drl
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help to subtract columns from 2 files and output to new file

Hi, I have 2 files in below formats File1_Stored.txt ABC:100, 83 ABC:84, 53 ABC:14, 1222And File2_Stored.txt ABC:100 , 83 ABC:84 , 1553 ABC:524 , 2626I am trying to get the 3rd file in below format. So, whenever difference is 0 it shouldn't appear but if the difference is not 0 then... (2 Replies)
Discussion started by: Abhayman
2 Replies

2. Shell Programming and Scripting

Sum of columns and format the output

Input file: 011100020100 0.00 1 20000 30000 20000 011110000025 0.00 1 000 240000 10000 011100020100 0.00 1 200000 2324000 403500 032200030025 0.00 1 2077500 3077500 250000 032200030025 0.00 1 2565000 25536400 320000 022220000005 0.00 1 10000 300000 300000 022220000005 0.00 1 200050... (7 Replies)
Discussion started by: vinus
7 Replies

3. Shell Programming and Scripting

Compare columns of multiple files and print those unique string from File1 in an output file.

Hi, I have multiple files that each contain one column of strings: File1: 123abc 456def 789ghi File2: 123abc 456def 891jkl File3: 234mno 123abc 456def In total I have 25 of these type of file. (5 Replies)
Discussion started by: owwow14
5 Replies

4. Shell Programming and Scripting

Sum numeric columns contained in a plain text file

Hi everyone, Here are the contents of a plain text file created by a SQL query: SUM(T.TRNQTY) COUNT(D.TRNSEQ) ---------------- ---------------- 1380 46 1393 59 2680 134 740 37 ... (5 Replies)
Discussion started by: gacanepa
5 Replies

5. Shell Programming and Scripting

Combining columns from multiple files into one single output file

Hi, I have 3 files with one column value as shown File: a.txt ------------ Data_a1 Data_a2 File2: b.txt ------------ Data_b1 Data_b2 Data_b3 Data_b4 File3: c.txt ------------ Data_c1 Data_c2 Data_c3 Data_c4 Data_c5 (6 Replies)
Discussion started by: vfrg
6 Replies

6. Shell Programming and Scripting

Sum of two columns in a file

Hi, I need to do a sum of two columns in a file where delimiter is |^ input 1|^2|^3|^4|^50|^2|^3|^100 2|^3|^4|^6|^100|^7|^2|^50 3|^4|^2|^3|^50|^6|^3|^50 4|^2|^5|^7|^25|^2|^8|^25 Output required: Sum of 2 columns 5 & 8 which is 450 (11 Replies)
Discussion started by: Jram
11 Replies

7. Shell Programming and Scripting

Sum up values of columns in 4 files using shell script

I am new to shell script.I have records like below in 4 different files which have about 10000 records each, all records unique and sorted based on column 2. 1 2 3 4 5 6 --------------------------- SR|1010478|000044590|1|0|0| SR|1014759|000105790|1|0|0| SR|1016609|000108901|1|0|0|... (2 Replies)
Discussion started by: reach.sree@gmai
2 Replies

8. Shell Programming and Scripting

sum the columns of files

I have several csv files like this: file1.csv 1 12 1 2 8 9 3 9 2 4 5 9 ... file2.csv 1 0 1 2 2 3 3 4 1 ... file3.csv 1 0 1 2 4 0 ... I want the result like this 1 12 3 2 14 12 3 13 3 (10 Replies)
Discussion started by: frewise
10 Replies

9. Shell Programming and Scripting

Need help to subtract columns from 2 files and output to new file

Hi, I need some help figuring this out, I think it can be done using awk but I don't know how. So, I want to take two input files, subtract some columns with each other and then output to a new results file. InFile1.txt AAA 100 200 BBB CCC 300 400 DDD InFile2.txt AAA 50 60 BBB CCC 70... (7 Replies)
Discussion started by: MrTrigger
7 Replies

10. Shell Programming and Scripting

Sum of three columns - in 4N columns file

Hi All, happy new year. I have a file with 4xN columns like 0.0000e+00 0.0000e+00 7.199E+07 7.123E+07 6.976E+07 6.482E+07 5.256E+07 2.523E+07 0.0000e+00 0.0000e+00 8.641E+07 8.550E+07 8.373E+07 7.780E+07 6.309E+07 3.028E+07... (8 Replies)
Discussion started by: f_o_555
8 Replies
Login or Register to Ask a Question