How to subtract 2 numbers in 2 different files?

user_7167600 0
Viewed 232

I have 2 files that are reports of size of the Databases (1 file is from yesterday, 1 from today). I want to see how the size of each database changed, so I want to calculate the difference.

File looks like this:

"DATABASE","Alloc MB","Use MB","Free MB","Temp MB","Hostname"

"EUROPE","9133508","8336089","797419","896120","server3"
"ASIA","3740156","3170088","570068","354000","server5"
"AFRICA","4871331","4101711","769620","318412","server4"

Other file is the same, only the numbers are different.

I want to see how the database size changed (so ONLY column "Use MB").

I guess I cannot use "diff" or "awk" options since numbers may change dramatically each day. The only good 'algoritm' I can think of is to subtract numbers between 5th and 6th double quote ("), how do I do that?

shell unix bash
2 Answers

You can do this (using awk): 

paste file1 file2 -d ',' |awk -F ',' '{gsub(/"/, "", $3); gsub(/"/, "", $9); print $3 - $9}'

paste puts the two files next to another, separated by a comma (-d ','). So you will have :

"DATABASE","Alloc MB","Use MB","Free MB","Temp MB","Hostname","DATABASE","Alloc MB","Use MB","Free MB","Temp MB","Hostname"

"EUROPE","9133508","8336089","797419","896120","server3","EUROPE","9133508","8336089","797419","896120","server3"
...

gsub(/"/, "", $3) removes the quotes around column 3

And finally we print column 3 minus column 9

user_80020010

Maybe I missed something, but I don't get why you could not use awk as it can totally do

The only good 'algoritm' I can think of is to subtract numbers between 5th and 6th double quote ("), how do I do that?

Let's say that file1 is :

"DATABASE","Alloc MB","Use MB","Free MB","Temp MB","Hostname"

"EUROPE","9133508","8336089","797419","896120","server3"
"ASIA","3740156","3170088","570068","354000","server5"
"AFRICA","4871331","4101711","769620","318412","server4"

And file2 is :

"DATABASE","Alloc MB","Use MB","Free MB","Temp MB","Hostname"

"EUROPE","9133508","8335089","797419","896120","server3"
"ASIA","3740156","3170058","570068","354000","server5"
"AFRICA","4871331","4001711","769620","318412","server4"

Command

awk -F'[",]' 'NR>2&&NR==FNR{db[$2]=$8;next}FNR>2{print $2, db[$2]-$8}' file1 file2

gives you result : 

EUROPE 1000
ASIA 30
AFRICA 100000

You can also use this answer to deal more properly with quotechars on awk.

If your awk version cannot support multiple field delimiters, you can try this : 

awk -F, 'NR>2&&NR==FNR{db[$1]=$3;next}FNR>2{print $1, db[$1]-$3}' <(sed 's,",,g' file1) <(sed 's,",,g' file2)
user_42893800
Related