CODEWITHSUNDEEP
shellcsvunix
user2642751
user2642751

Reputation: 43

Comparison of 2 csv files having same column names with different data

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

Upvotes: 0

Views: 44

Answers (1)

James Brown
James Brown

Reputation: 37404

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

Upvotes: 1

Related Questions