Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need help to subtract columns from 2 files and output to new file
# 1  
Old 12-09-2010
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 80 DDD

Results.txt (InFile1.txt - InFile2.txt)
AAA 50 140 BBB
CCC 230 320 DDD
# 2  
Old 12-09-2010
Yes awk can get the job done.

Code:
paste data1.txt data2.txt | awk '{ printf("%s %d %d %s\n", $1, ($2-$6), ($3-$7), $8); }'

If you have any questions let me know.
# 3  
Old 12-09-2010
Code:
awk '{a=$2;b=$3;getline<f;$2-=a;$3-=b}1' f=file1 file2

If the files are not one-on-one:
Code:
awk 'NR==FNR{A[$1]=$2;B[$1]=$3;next}{$2-=A[$1];$3-=B[$1]}1' file2 file1

Last edited by Scrutinizer; 12-09-2010 at 10:42 AM..
These 3 Users Gave Thanks to Scrutinizer For This Post:
cabrao gc_sw MrTrigger
# 4  
Old 12-09-2010
Quote:
Originally Posted by Scrutinizer
Code:
awk '{a=$2;b=$3;getline<f;$2-=a;$3-=b}1' f=file1 file2

If the files are not one-on-one:
Code:
awk 'NR==FNR{A[$1]=$2;B[$1]=$3;next}{$2-=A[$1];$3-=B[$1]}1' file2 file1

Well done my friend.
# 5  
Old 12-10-2010
Works great, thanks! Smilie

The files are one-to-one so I'm using
Code:
awk '{a=$2;b=$3;getline<f;$2-=a;$3-=b}1' f=file1 file2

Forgot that I also need to skip the first line, any thoughts?
# 6  
Old 12-10-2010
I think join is good for the purpose here
Code:
join file1 file2  | awk '{print $1,$2-$5,$3-$6,$4}' -

# 7  
Old 12-10-2010
To leave the first line unchanged:
Code:
awk '{a=$2;b=$3;getline<f}NR>1{$2-=a;$3-=b}1' f=file1 file2

To leave the first line out:
Code:
awk '{a=$2;b=$3;getline<f;$2-=a;$3-=b}NR>1' f=file1 file2

This User Gave Thanks to Scrutinizer For This Post:
MrTrigger
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

Add new column which is subtract of 2 columns.

Hi below is a file Date Category Time Attempts Success 2/17/2014 PayFlow ATB 0.999988 4039104 4039057 2/18/2014 PayFlow ATB 0.999912 4620964 4620558 2/19/2014 PayFlow ATB 0.999991 4380836 4380796 2/20/2014 PayFlow ATB 0.999988 5031047 5030985 2/21/2014 ... (5 Replies)
Discussion started by: villain41
5 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

Add values in 2 columns and subtract from third

Hi All, I have a file with thousands of lines in the following format, where Field1=First 8 characters Field2-9-16 characters Field3=17-26 characters I need to add Field 1 and Field2 and subtract the result from Field 3. Field3=Field3 - (Field1 + Field2) 0012.00 0010.00 0001576.53... (4 Replies)
Discussion started by: nua7
4 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

Subtract 2 date columns in .csv file and get output as number of days

Hi, I have one .csv file. I have 2 date columns present in file, column 2 and column 3. I need to calculate how many days exist between 2 dates. I am trying to subtract date column 2 from date column 3. Eg: my file look likes s.no, Start_date,End_Date 1, 7/29/2012,10/27/2012 2,... (9 Replies)
Discussion started by: Dimple
9 Replies

7. Shell Programming and Scripting

Help need to subtract the data from 2 columns

space_used.lst /dev/vx/dsk/A06487-S01-c4e3s-ORACLE-dg/oradata01 505G 318G 175G 65% /dborafiles/nethealth21/PV/oradata01 /dev/vx/dsk/A06487-S01-c4e3s-ORACLE-dg/oradata02 505G 433G 67G 87% /dborafiles/nethealth21/PV/oradata02 /dev/vx/dsk/A06487-S01-c4e3s-ORACLE-dg/oradata03 507G 422G 79G 85%... (4 Replies)
Discussion started by: sathik
4 Replies

8. UNIX for Dummies Questions & Answers

Sum of all columns in all files in one output file

If I have say 4 files like this: File1: 1 3 4 7 7 0 5 7 5 9 1 2 7 4 8 File2: 1 4 6 2 5 7 1 2 3 6 0 3 0 3 8 File3: (5 Replies)
Discussion started by: cosmologist
5 Replies

9. Shell Programming and Scripting

How to subtract a number from all columns?

Hi, I want to subtract a number from all columns except the first column. I have a number of files each having different columns around 60/70. How to do that in awk or any other command? Thanks Input Col 1 Col 2 Col3 - - - - Col55 1 .0123 .098 - - - 0.6728 2 - -... (3 Replies)
Discussion started by: Surabhi_so_mh
3 Replies

10. Shell Programming and Scripting

AWK solution to subtract multiple columns of numbers

Hope somebody is happy. NR==1 { num_columns=split( $0, menuend ); next; } { split( $0, substrend ); for ( i=1; i<=NF; i++ ) { minuend -= substrend; } } END { print "Result:"; for ( i=1; i<=num_columns; i++ ) { printf(... (3 Replies)
Discussion started by: awkward
3 Replies
Login or Register to Ask a Question