Summation of Columns accross multiple files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Summation of Columns accross multiple files
# 1  
Old 12-09-2009
Summation of Columns accross multiple files

I have around 1000 files in a directory (file1.txt, file2.txt, ... file1000.txt), each file is 2 columns, the first colums are same for all of them, second one is different.

file1.txt
1 10
2 20
3 30
4 40
5 50

file2.txt
1 100
2 400
3 500
4 600
5 900

what I need is , I want output.txt such as for only 2 above files, it will give
output.txt
1 110
2 420
3 530
4 640
5 950

well if there was only two files i could do
Code:
pr -m -t -s\  *txt | gawk '{print  $1,$2+$4}'

but I have thousand files , I need sth like
Code:
pr -m -t -s\  *txt | gawk '{print  $1,$2+$4+...+$2000}'

also I want to make it dynamic (number of files may change)

what I tried is

Code:
#!/bin/bash

numfiles=$(ls *.txt | wc -l)
command=""

for (( c=1; c<=$numfiles; c++ ))
do  
command=" ${command}\$$[2*${c}]+"
pr -m -t -s\  *trace | gawk '{print $1,${command}}'
done


Last edited by scriptie; 12-09-2009 at 11:16 PM..
# 2  
Old 12-09-2009
Code:
join file file2| awk '{t=0;for(i=2;i<=NF;i++){t+=$i};print $1,t}'

# 3  
Old 12-09-2009
Not sure how are you going to distinguish the two files.Or is it going to be file1 and file2 all the time??

Anyway, try the below logic to add the second column

Code:
awk '{ A[$1]+=$2;} END{ for (i in A) print i" "A[i]; }' file1 file2 | sort -nk1 -o out.txt

# 4  
Old 12-09-2009
thanks guys
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Removing carriage returns from multiple lines in multiple files of different number of columns

Hello Gurus, I have a multiple pipe separated files which have records going over multiple Lines. End of line separator is \n and records going over multiple lines have <CR> as separator. below is example from one file. 1|ABC DEF|100|10 2|PQ RS T|200|20 3| UVWXYZ|300|30 4| GHIJKL|400|40... (7 Replies)
Discussion started by: dJHa
7 Replies

2. Shell Programming and Scripting

Merging Multiple Columns between two files

Hello guys, I have 2 CSV files which goes like this: CSV1: Breaking.csv: UTF-8 "Name","Description","Occupation","Email" "Walter White","","Chemistry Teacher","w.w@bb.com" "Jessie Pinkman","","Junkie","j.p@bb.com" "Hank Schrader","","DEA Agent","h.s@bb.com" CSV2: Bad.csv... (7 Replies)
Discussion started by: jeffreybsu
7 Replies

3. Shell Programming and Scripting

Merging multiple files from multiple columns

Hi guys, I have very basic linux experience so I need some help with a problem. I have 3 files from which I want to extract columns based on common fields between them. File1: --- rs74078040 NA 51288690 T G 461652 0.99223 0.53611 3 --- rs77209296 NA 51303525 T G 461843 0.98973 0.60837 3... (10 Replies)
Discussion started by: bartman2099
10 Replies

4. Shell Programming and Scripting

Compare multiple files with multiple number of columns

Hi, input file1 abcd 123 198 xyz1:0909090-0909091 ghij 234 999 xyz2:987654:987655 kilo 7890 7990 xyz3:12345-12357 prem 9 112 xyz5:97-1134 input file2 abcd 123 198 xyz1:0909090-0909091 -9.122 0 abed 88 98 xyz1:98989-090808 -1.234 1.345 ghij 234 999 xyz2:987654:987655 -10.87090909 5... (5 Replies)
Discussion started by: jacobs.smith
5 Replies

5. UNIX for Dummies Questions & Answers

cutting multiple columns into multiple files

Hypothetically, suppose that file1 id v1 v2 v3 v4 v5 v6 v7..........v100 1 1 1 1 1 1 2 2 .....50 2 1 1 1 1 1 2 2 .....50 3 1 1 1 1 1 2 2 .....50 4 1 1 1 1 1 2 2 .....50 5 1 1 1 1 1 2 2 .....50 I want to write a loop such that I take the id# and the first 5 columns (v1-v5) into the... (3 Replies)
Discussion started by: johnkim0806
3 Replies

6. Shell Programming and Scripting

List and Compare Files accross different servers.

Hi all, This is my situation. First thing is I cannot use rsync to accomplish this. I don't have on my systems and we can't put it on. I run HP-UX 11v3. I have a list of files generated every day which tells me which files are not in sync with the rest of the servers.I want to ls -l the... (1 Reply)
Discussion started by: zixzix01
1 Replies

7. Shell Programming and Scripting

Adding Multiple Files via Columns

I have a number of files with multiple rows that I need to add together. Let say I have 10 files: Each file has a great number of rows and columns. I need to add these files together the following way. In other words, If, for example, file A occupies Columns 1 to 19, I want to add file B... (7 Replies)
Discussion started by: Ernst
7 Replies

8. Shell Programming and Scripting

calculating column summation in a directory of flat files

Hello Guru s I need your kind help to solve my below issue I have a directory of flat files and have to calculate sum of some columns from the flat file . Say for flat file 302 I need the column summation of 2 and 3 rd column For flat file 303 I need the column summation of 5 and... (2 Replies)
Discussion started by: Pratik4891
2 Replies

9. Shell Programming and Scripting

need help with post:extract multiple columns from multiple files

hello, I will would be grateful if anyone can help me reply to my post extract multiple cloumns from multiple files; skip rows and include filenames; awk Please see this thread. Thanks manishabh (0 Replies)
Discussion started by: manishabh
0 Replies

10. Shell Programming and Scripting

Combine multiple columns from multiple files

Hi there, I was wondering if someone can help me with this. I am trying the combine multiple columns from multiple files into one file. Example file 1: c0t0d0 c0t2d0 # hostname vgname c0t0d1 c0t2d1 # hostname vgname c0t0d2 c0t2d2 # hostname vgname c0t1d0 c0t3d0 # hostname vgname1... (5 Replies)
Discussion started by: martva
5 Replies
Login or Register to Ask a Question