There are two input files; both are formatted as TSV.File1: treated.bam.tsv File2: untreated.bam.tsvBoth files have the same fields, as listed below.(I numbered then for presentation purposes)  each file has 23 fields.1chrom           9mismatches_pp       17C_pp2pos             10deletions          18T3ref             11deletions_pp       19T_pp4reads_all       12insertions         20G5reads_pp        13insertions_pp      21G_pp6matches         14A                  22N7matches_pp      15A_pp               23N_pp8mismatches      16CIf values in the 1st and 2nd columns (chrom, pos) are the same in both files, I want to extract some of the fields in the records, then make a new output file like this below. The output file has 15 fields, combining data from the two input files, as  below.From file1:  1chrom  2pos  3ref  4reads_all  8mismatches  10deletions  12insertions  pct_file1 (the values from file1: (8mismatches+10deletions+12insertions)/4reads_all)From file2:  3ref  4reads_all  8mismatches  10deletions  12insertions  pct_file2 (the values from file2: (8mismatches+10deletions+12insertions)/4reads_all)-New values from extractions.  pct_sub  (the values from pct_file1 - pct_file2: ((8mismatches+10deletions+12insertions)/4reads_all) - ((8mismatches+10deletions+12insertions)/4reads_all))In the output file, the first eight columns come from File1, treated.bam.tsv (the 8th column is the value calculated with 8mismatches, 10deletions, 12insertions and 4reads_all from File1).The rest of them come from File2, untreated.bam.tsv, and 13th column is also the value calculated with 8mismatches, 10deletions, 12insertions and 4reads_all from File2.The last field, pct_sub, is calculated from the subtraction value with fields from File1 ((8mismatches+10deletions+12insertions)/4reads_all) and from File2 ((8mismatches+10deletions+12insertions)/4reads_all).And how can I add new column names in output files, like pct_file1, pct_file2, pct_sub?This is what I made for the above output file. (Input and output files have all same format: TSV.)awk 'FNR==NR{array[$1,$2]=$0;next} { if ( $1 $2 in array ) print $1, $2, array[$3], array[$4], array[$8], array[$10], array[$12], (array[$8]+array[$10]+array[$12])/array[$4], $3, $4, $8, $10, $12, ($8+$10+$12)/$4, ((array[$8]+array[$10]+array[$12])/array[$4])-(($8+$10+$12)/$4) > awkoutput.bam.tsv }' treated.bam.tsv untreated.bam.tsv(Actually, $1, $2 are not a problem from File1 or File2)FILE1 (treated)chrom pos ref reads_all reads_pp matches matches_pp mismatches mismatches_pp deletions deletions_pp insertions insertions_pp A A_pp C C_pp T T_pp G G_pp N N_ppchrY 59363551 G 8 0 7 0 0 0 1 0 5 0 0 0 0 0 0 0 7 0 0 0chrY 59363552 G 7 0 7 0 0 0 0 0 0 0 0 0 0 0 0 0 7 0 0 0chrY 59363553 T 7 0 7 0 0 0 0 0 0 0 0 0 0 0 7 0 0 0 0 0chrY 59363554 G 7 0 7 0 0 0 0 0 0 0 0 0 0 0 0 0 7 0 0 0chrY 59363555 T 7 0 7 0 0 0 0 0 0 0 0 0 0 0 7 0 0 0 0 0FILE2 (untreated)chrom pos ref reads_all reads_pp matches matches_pp mismatches mismatches_pp deletions deletions_pp insertions insertions_pp A A_pp C C_pp T T_pp G G_pp N N_ppchrY 59363551 G 2 0 2 0 0 0 0 0 1 0 0 0 0 0 0 0 2 0 0 0chrY 59363552 G 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0chrY 59363553 T 1 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0chrY 59363554 G 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0chrY 59363555 T 1 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0OUTPUTchrom pos ref reads_all mismatches deletions insertions pct_file1 ref reads_all mismatches deletions insertions pct_file2 pct_subchrY 59363551 G 8 0 1 5 0.75 G 2 0 0 1 0.5 0.25chrY 59363552 G 7 0 0 0 0 G 1 0 0 0 0 0chrY 59363553 T 7 0 0 0 0 T 1 0 0 0 0 0chrY 59363554 G 7 0 0 0 0 G 1 0 0 0 0 0chrY 59363555 T 7 0 0 0 0 T 1 0 0 0 0 0
How to compare 2 files with common columns and then get the output file with columns from each file
awk
if ( $1 $2 in array ) doesnt work;you have to do if (($1,$2) in array).You cant use array[$3] and array[$4] like that.Your array looks likearray[chrY,59363551]=chrY 59363551 G 8 0 7 0 0 0 1 0 5 0 0 0 0 0 0 0 7 0 0 0array[chrY,59363552]=chrY 59363552 G 7 0 7 0 0 0 0 0 0 0 0 0 0 0 0 0 7 0 0 0             and when you say array[$3] and array[$4],youre referring to array[G] and array[2], etc.,which do not exist.The ability to specify > filename in awk codeis a great feature when you want to write to multiple files.Its not so useful when you have only one output file why dont you just redirect the output from the awk command?Long lines are bad.Break long commands into short lines.Reduce duplication by reusing variables.Dont use an array called array.Thats like having a variable called variable,a file called file, a person called Person, etc.Use descriptive names.OK, that said,awk 'FNR==NR {file1data[$1,$2]=$0; next}        {       if (($1,$2) in file1data) {                        # Save desired values from file2.                        file2arg03=$3                        file2arg04=$4                        file2arg08=$8                        file2arg10=$10                        file2arg12=$12                        pct_file2=($8+$10+$12)/$4                        # Get data from file1.                        $0=file1data[$1,$2]                        pct_file1=($8+$10+$12)/$4                        print $1, $2, $3, $4, $8, $10, $12, pct_file1, \                                file2arg03, file2arg04, file2arg08, file2arg10, file2arg12, \                                pct_file2, pct_file1-pct_file2                } else printf (%s,%s) in file2 but not file1.%s, $1, $2, ORS        }' treated.bam.tsv untreated.bam.tsv > awkoutput.bam.tsvLike your version, this saves the file1 data in an arrayand then does all the work/output while its reading file2.Upon receiving a line from file2,it saves the desired fields from that line into named variables(we could also use another array, five elements long)and then it restores the data from the corresponding line in file1.By assigning the entire line to $0,it causes $1, $2, $3, $4, etc.,to be restored to their original values.Are you really having a problem writing a header line in the output?Try:        {       if (FNR == 1) {                        print chrom pos ref reads_all mismatches deletions insertions pct_file1                 } else if (($1,$2) in file1data ) {                        file2arg03=$3                              OK, heres a version thats closer in spirit to your attempt,and that handles the header line:awk 'FNR==NR {file1line[$1,$2]=$0; next}        {       if (FNR == 1) {                        print chrom pos ref reads_all mismatches deletions insertions pct_file1 ref reads_all mismatches deletions insertions pct_file2 pct_sub                 } else if (($1,$2) in file1line ) {                        # Get data from file1.                        split(file1line[$1,$2], file1arg)                        pct_file1=(file1arg[8]+file1arg[10]+file1arg[12])/file1arg[4]                        pct_file2=($8+$10+$12)/$4                        print $1, $2, file1arg[3], file1arg[4], file1arg[8], \                                file1arg[10], file1arg[12], pct_file1, \                                $3, $4, $8, $10, $12, pct_file2, pct_file1-pct_file2                } else printf (%s,%s) in file2 but not file1.%s, $1, $2, ORS        }' treated.bam.tsv untreated.bam.tsv > awkoutput.bam.tsvThis retrieves the line from file1 (from file1line)and passes it to split to break it down into its constituent 23 values,which get stored in the array file1arg.Then it is able to use file1arg[3], file1arg[4], ,the way you were using array[$3], array[$4], 