substracting common row names from two files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting substracting common row names from two files
# 1  
Old 12-16-2009
substracting common row names from two files

Hi,

I have two files with with similar row names. For the names that match, I want to substract the values from each column.

eg (files are tab separeted).

FILE1:

Code:
hhh  2  4  5
KKL  2  3  2
LLP   2  1  2


FILE2:

Code:
hhh  3  1  1
KKL  2  1  1
UUI  1  1  1


Output (result of subtracting matching file names from file1 and 2):

Code:
hhh -1  3  4
KKL  0  2  1


thanks
# 2  
Old 12-16-2009
Code:
gawk '
FNR==NR{
    for(i=2;i<=NF;i++){
        a[$1,i]=$i
    }
    next
}
(a[$1,2]){
   printf "%s " ,$1
   for(o=2;o<=NF;o++){
        printf "%d ", a[$1,o]-$o
   }
   print ""
}
' file1 file2


Last edited by ichigo; 12-17-2009 at 12:29 AM..
# 3  
Old 12-16-2009
Code:
paste a.txt b.txt | awk '{
cnt=NF/2
tmp=1+cnt
if($1==$tmp){
  printf $1
  for(i=2;i<=cnt;i++){
    tcnt=i+cnt
    val=$i-$tcnt
    printf(" %s",val)
  }
  print ""
}
}'

# 4  
Old 12-17-2009
Hey Summer cherry it does not work.

Hi ichigo, that makes sense but i forgot to mention that my file contains 200 columns so writing it out might be kinda long. I only put a sample up.
# 5  
Old 12-17-2009
Code:
my %hash;


open my $f1, "<", "file1" || die "$!";
while(<$f1>){
        chomp;
        my ($word,@arr) = split("\t");
        $hash{$word} = \@arr;
}
close $f1;

open my $f2, "<", "file2" || die "$!";
while(<$f2>){
        chomp;
        my ($word,@arr) = split("\t");
        if (exists $hash{$word}){
                print "$word\t";
                my $i=0;
                foreach (@{$hash{$word}}){
                        print $hash{$word}[$i] - $arr[$i] ."\t";
                        $i++;
                }
                print "\n";
        }
     }
close $f2;

Note you are loading the file 1 in memory . so keep in mind the file size and its feasibility

HTH,
PL

Last edited by daptal; 12-17-2009 at 12:38 AM.. Reason: removed else part
# 6  
Old 12-17-2009
Quote:
Originally Posted by kylle345
Hi ichigo, that makes sense but i forgot to mention that my file contains 200 columns so writing it out might be kinda long. I only put a sample up.
https://www.unix.com/302380993-post2.html
# 7  
Old 12-17-2009
Try:

Code:
paste <(sort file1) <(sort file2) | awk '$1==$5 { print $1,$2-$6,$3-$7,$4-$8 }'

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Comparing two files and list the difference with common first line content of both files

I have two file as given below which shows the ACL permissions of each file. I need to compare the source file with target file and list down the difference as specified below in required output. Can someone help me on this ? Source File ************* # file: /local/test_1 # owner: own #... (4 Replies)
Discussion started by: sarathy_a35
4 Replies

2. Shell Programming and Scripting

Exclude certain file names while selectingData files coming in different names in a file name called

Data files coming in different names in a file name called process.txt. 1. shipments_yyyymmdd.gz 2 Order_yyyymmdd.gz 3. Invoice_yyyymmdd.gz 4. globalorder_yyyymmdd.gz The process needs to discard all the below files and only process two of the 4 file names available ... (1 Reply)
Discussion started by: dsravanam
1 Replies

3. Shell Programming and Scripting

Comparing two files on row by row and send the report

Comparing two files on row by row File1 ecount~100 dcount~200 ccount~300 zxcscount~5000 and so on. File2 ecount~100 dcount~203 ccount~300 zxcscount~5000 and so on. If i use diff command (1 Reply)
Discussion started by: onesuri
1 Replies

4. Shell Programming and Scripting

Compare multiple files, and extract items that are common to ALL files only

I have this code awk 'NR==FNR{a=$1;next} a' file1 file2 which does what I need it to do, but for only two files. I want to make it so that I can have multiple files (for example 30) and the code will return only the items that are in every single one of those files and ignore the ones... (7 Replies)
Discussion started by: castrojc
7 Replies

5. Shell Programming and Scripting

Print unique names in each row of a specific column using awk

Is it possible to remove redundant names in the 4th column? input cqWE 100 200 singapore;singapore AZO 300 400 brazil;america;germany;ireland;germany .... .... output cqWE 100 200 singapore AZO 300 400 brazil;america;germany;ireland (4 Replies)
Discussion started by: quincyjones
4 Replies

6. Shell Programming and Scripting

nawk time substracting

Nawk ‘BEGIN {printf “0t%d=Y\n”,srand () -1800}' | adb I know that printf is printing the value in %d which is for a day of the month and subtracting the value by -1800 (30 minutes). So if date is 14:25:45 subtracting from 1800 then time will equal to 13:55:45 I also know that srand () is... (1 Reply)
Discussion started by: INHF
1 Replies

7. Shell Programming and Scripting

How to remove common file names from text files

I'm running on freebsd -- with a default shell of csh. I have two files named A and B. Each line of each file contains a file name. How can I write a script that removes all the file names in file B from A. I tried to use perl to create a huge regular expression with "|" separating the file... (2 Replies)
Discussion started by: siegfried
2 Replies

8. Shell Programming and Scripting

awk command : row by row merging of two files

I want to write a scrpit to merge files row wise (actually concatinating) main.txt X Y Z file 1 A B C file 2 1 2 3 now i want the script to check if the file1 is empty or not, if empty then make it like A B C 1 2 3 again to check if second file is empty if not do as done... (0 Replies)
Discussion started by: shashi792
0 Replies

9. AIX

Substracting time between two columns

I've start and end time in two columns. How can I substract time from column 2 and column 1 and receive output in another file 'd' ? Code: $ cat c 12:55:04 2:03:56 2:03:56 3:20:17 14:00:00 13:05:00 (1 Reply)
Discussion started by: alps0206
1 Replies

10. Shell Programming and Scripting

Merge files of differrent size with one field common in both files using awk

hi, i am facing a problem in merging two files using awk, the problem is as stated below, file1: A|B|C|D|E|F|G|H|I|1 M|N|O|P|Q|R|S|T|U|2 AA|BB|CC|DD|EE|FF|GG|HH|II|1 .... .... .... file2 : 1|Mn|op|qr (2 Replies)
Discussion started by: shashi1982
2 Replies
Login or Register to Ask a Question