Compare two files and print a column output with totals 1

[madasafish]

I have tried getline and am going round in circles.
The simplest way I can put it is as follows:

Given the following files:

file1:
1,user1
2,user2
3,user3
4,user4
5,user5

file2:
1,user1
3,user3
5,user5
7,user7
9,user9

Display the following output with totals

Common File1 File2
1 2 7
3 4 9
5

=== === ===
3 2 2
=== === ===

It's not easy! (well for me anyway!)
The icing on the cake would be a flag to change output to..

Common File1 File2
user1 user2 user7
user3 user4 user9
user5
=== === ===
3 2 2
=== === ===

Note: "user" could be any name like bert fred martin etc

Any help, will go into my awk tool arsenal

madasafish2

Madasfish2

 

[PHV]

A starting point, in an *nix environment:

comm file1 file2 | awk -F'\t' '
{a[NF,++i[NF]]=$NF}
END{
 print "Common\tFile1\tFile2"
 for(j=1;j<=i[1]||j<=i[2]||j<=i[3];++j)
  print a[3,j]"\t"a[1,j]"\t"a[2,j]
 print "===\t===\t==="
 print i[3]"\t"i[1]"\t"i[2]
}'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[madasafish]

As expected, a millennium distance from me in awk skills and very close.

I get exactly the results I asked for in the previous post. Thank-you

Can I move the goal posts?

Given the files:

File1:
dsc001,Sophie Lutzeier
adsc002,Stephanie Colclough
adsc003,Gill Wallace
adsc004,David Everett
fin6114,Andrew Hall
fin6115,Louise Wheatman
fin6142,Fiona Skipper
fin6189,Nicola Adam
fin6202,Darren Priddey
fin6203,Deirdre Cotter

File2:
admin
ahmedf
alderd
aldermank
allsoppm
baconc
batch604
batch605
batch607
batch608
fin6203

Can I get the following output

Common File1 File2
fin6203,Deirdre Cotter adsc001,Sophie Lutzeier admin
adsc002,Stephanie Colclough ahmedf
adsc003,Gill Wallace alderd
adsc004,David Everett aldermank
fin6114,Andrew Hall allsoppm
fin6115,Louise Wheatman baconc
fin6142,Fiona Skipper batch604
fin6189,Nicola Adam batch605
fin6202,Darren Priddey batch607
batch608
=== === ===
1 9 10


The format above (on here)is abit skew-wiff, take note of the one common file in both files.

the code I used is:
file1=mplist
file2=dpfile

comm $file1 $file2 | awk -F'\t' '
{a[NF,++i[NF]]=$NF}
END{
printf "%30-s %-30s %30-s\n","Common","File1","File2"
for(j=1;j<=i[1]||j<=i[2]||j<=i[3];++j)
printf "%30-s %-30s %30-s\n",a[3,j],a[1,j],a[2,j]
printf "%30-s %-30s %30-s\n","===","===","==="
printf "%30-s %-30s %30-s\n",i[3],i[1],i[2]
}'

Once again, thank-you for your help with this.

madasafish

 

[madasafish]

Can someone please explain to me what red code is doing?

{a[NF,++i[NF]]=$NF}
END{
print "Common\tFile1\tFile2"
for(j=1;j<=i[1]||j<=i[2]||j<=i[3];++j) [/color red]
print a[3,j]"\t"a[1,j]"\t"a[2,j]
print "===\t===\t==="
print i[3]"\t"i[1]"\t"i[2]