Issue
I am having two CSV files each having 2 columns with same column name. 1.csv has generated first and 2.csv has generated after 1 hour. S o I want to see the Profit % increment and decrement for each Business unit comparing to last report.
For example: Business unit B has increment of 50% (((15-10)/10)*100). However for C it has decrease of 50%. Some new business unit(AG & JK) is also added in new hour report which can be considered only for new one. However few business unit(D) also removed from next hour which can be considered not required.
So basically I need to know: how can I compare and extract this data?
Business Profit %
A 0
B 10
C 10
D 0
E 0
F 1615
G 0
Business profit %
A 0
B 15
C 5
AG 5
E 0
F 1615
G 0
JK 10
Updated requirement:
Business Profits% Old profit % new Variation
A 0 0 0
B 10 15 50%
C 10 5 -50%
D 0 cleared
AG 5 New
E 0 0 0
F 1615 1615 0%
G 0 0 0%
JK 10 New
Solution
I'd use awk for the job, something like this:
$ awk 'NR==FNR{ # process file2
a[$1]=$2 # hash second column, key is the first column
next # process the next record of file2
}
{ # process file1
if($1 in a==0) # if company not found in hash a
p="new" # it must be new
else
p=($2-a[$1])/(a[$1]==0?1:a[$1])*100 # otherwise calculate p%
print $1,p # output company and p%
}' file1 file2
A 0
B 50
C -50
AG new
E 0
F 0
G 0
JK new
One-liner version with appropriate semicolons:
$ awk 'NR==FNR{a[$1]=$2;next}{if($1 in a==0)p="new";else p=($2-a[$1])/(a[$1]==0?1:a[$1])*100;print $1,p}' file1 file2
Answered By - James Brown Answer Checked By - Marie Seifert (WPSolving Admin)