Compare two columns of one file to two columns in another file

Question

I have two files as shown below.

First file:

Start State,Next State,Session Count,Transition%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/home,1126,28.600%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/LA_product_view,818,20.780%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/orders/price,21,0.530%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/login/,997,25.330%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/organizations,31,0.780%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/partsVisualizer,167,4.240%

Second file:

/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/LA_product_view,894,13.100%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/home,1184,17.350%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/orders/price,25,0.360%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/searchresults,226,3.310%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/inventories/LA_entry,3,0.040%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/cart,51,0.740%

I need to compare the first two columns of the first file and the first two columns of the second file and if they both match it should print 3,4 columns of both file. If both the files has unique they should be listed with NA from the both files.

My output should be like:

/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/LA_product_view,818,20.780%,894,13.100%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/home,1126,28.600%,1184,17.350%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/orders/price,21,0.530%,25,0.360%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/searchresults,NA,NA,226,3.310%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/inventories/LA_entry,NA,NA,3,0.040%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/cart,NA,NA,51,0.740%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/organizations,31,0.780%,NA,NA
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/partsVisualizer,167,4.240%,NA,NA
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/login/,997,25.330%,NA,NA

Can someone please help me with this?

Can you try something like https://stackoverflow.com/questions/32481877/what-is-nr-fnr-in-awk — Walter A, May 29 '19 at 12:22
There are probably 100+ examples on SO alone for joining two files on multiple columns with awk. — Shawn, May 29 '19 at 13:42
But i could'nt find one as my similar case ...sorry for that could you please help me if you can — Raghu Ram, May 29 '19 at 13:45
Do your files look so messy, or is it just the editing which failed? — kvantour, May 29 '19 at 14:29

score 0 · Answer 1 · answered May 29 '19 at 17:59

awk -f rag.awk file1 file2 where rag.awk is:

    BEGIN {
FS=OFS=","
}
{idx=$1 OFS $2; _34=$3 OFS $4}
FNR==NR {if (FNR>1) f1[idx]=_34; next}
idx in f1 { print idx, f1[idx], _34; delete f1[idx];next}
{print idx, "NA","NA", _34}
END {
  for (i in f1)
     print i, f1[i], "NA", "NA"
}

output:

/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/LA_product_view,818,20.780%,894,13.100%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/home,1126,28.600%,1184,17.350%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/orders/price,21,0.530%,25,0.360%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/searchresults,NA,NA,226,3.310%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/inventories/LA_entry,NA,NA,3,0.040%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/cart,NA,NA,51,0.740%
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/organizations,31,0.780%,NA,NA
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/pages/layout/partsVisualizer,167,4.240%,NA,NA
/ccstoreui/v1/pages/layout/home,/ccstoreui/v1/login/,997,25.330%,NA,NA

Compare two columns of one file to two columns in another file

1 Answers1