Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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 Help to subtract columns from 2 files and output to new file
# 1  
Old 09-04-2017
Help to subtract columns from 2 files and output to new file
Hi,

I have 2 files in below formats

Code:
File1_Stored.txt
ABC:100, 83
ABC:84, 53
ABC:14, 1222

And
Code:
File2_Stored.txt
ABC:100 , 83
ABC:84 , 1553
ABC:524 , 2626

I 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 it should appear like

Code:
ABC:84,53,1553, -1500
ABC:14, 1222 , 0, 1222
ABC:524 ,0,2626,-2626

I tried doing it using awk to get the difference but it failing.
Code:
awk 'NR==FNR{A[$1]=$2;B[$1]=$2}{$2-=A[$1]}1' File1_Stored.txt  File2_Stored.txt

Any help is much appriciated.

Regards,



Moderator's Comments:
Mod Comment Posting "Does not work" without explanation does not help you or anyone. If a command does not work for you, please show the exact circumstances you used it, and the exact error or malfunction you received. Do not paraphrase errors, or post the text as links, images, or attachments if you can avoid it: Paste the exact message, in code tags, like [code] text [/code] or by selecting the text and using the Image button.

Thank you.

The UNIX and Linux Forums
Last edited by RudiC; 09-04-2017 at 11:48 AM.. Reason: changed awk
# 2  
Old 09-04-2017
Please be aware that you're not too consistent with the spaces in your input files. You'll either have to correct those or take care of it in the script. I did the former.

Try

Code:
awk -F, '
NR==FNR         {A[$1] = $2
                 next
                }

$2 - A[$1]      { print $0, A[$1]+0, $2 - A[$1]
                }
$1 in A         {delete A[$1]
                }
END             {for (a in A) print a, 0, A[a], -A[a]
                }
' OFS="," file2  file1
ABC:84, 53,1553,-1500
ABC:14, 1222,0,1222
ABC:524,0, 2626,-2626

This User Gave Thanks to RudiC For This Post:
Abhayman
# 3  
Old 09-04-2017
Thank you so much
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

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

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