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

UNIX for Beginners Q & A
1

I Have two files as below,

first file:

Start State |Next State |Session Count |Transition%

LA_product_view |home |694 |28.660%

LA_product_view | searchresults |54 |2.230%

home | 1101260 | 2 | 0.050%

second file:

Start State Next State Session Count Transition%

LA_product_view |home | 618 | 27.560%

LA_product_view |searchresults | 59 | 2.630%

home | price | 25 | 0.360%

i need to compare first two coloumns of first file and first two coloumns of second file and if they both match it should print 3,4 coloums of both file.if both the files has unique they should be listed with NA from the both files. my output should be like:

LA_product_view | home | 694 |28.660% |618 |27.560%

LA_product_view | searchresults |54 | 2.230% | 59 |2.630%

home | 1101260 | 2 |0.050% |N/A | N/A

home | price | N/A | N/A | 25 | 0.360%

can someone please help me in this..!

2

If you got a recent bash or ksh with "process substitution", and

join --version
join (GNU coreutils) 8.30

, try

join  -t"|" -a1 -a2 -e"N/A" -o auto --nocheck-order --header  <(sed  's/|/#/' file1) <(sed 's/|/#/' file2) | sed 's/#/|/'
Start State|Next State Session | Count |Transition%|Session Count|Transition%
LA_product_view|home|694|28.660%|618|27.560%
LA_product_view|searchresults|54|2.230%|59 |2.630%
home|price |N/A|N/A|25|0.360%
home|view|2|0.050%|N/A|N/A
3

@RudiC
mine is join (GNU coreutils) 8.22
i tried above it didnt work...any awk solution if possible?

--- Post updated at 01:16 PM ---

@RudiC
please help me if my files are in comma separated can u check will this work
file1.txt:

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%

file2.txt:

/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%

can u check if this is like this

4

What keeps you from checking it yourself, reading the join man page if need be?

5

@rudic
i checked with this
join -t"," -a1 -a2 -e"N/A" -o auto --nocheck-order --header <(sed 's/,/#/' file1.txt) <(sed 's/,/#/' file2.txt) | sed 's/#/,/'

it didnt worked...!

6

You may need to pre-process your files; sorting them but keeping the header in line 1. Then, join on field 2.

7

a bit verbose, but try: 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, "NA", "NA"
}

You didn't provide the expected output for your latest sample files, but... the above produces - not sure if it's what you'd expect:

/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
1 Like
8

@vgersh99
Thank God...!Thanks alot...!
it worked like a charm..!

--- Post updated at 05:52 PM ---

@vgersh99
Thanks... it worked like a charm...!
Thanks alot again...!