Merge files with awk

user_16372109 0
Viewed 96

I am attempting to merge file1 and file2 based on the number in column2 from file2 and the number in column1 from file1 delimited mainly by a comma. There is also a . delimiter between the number I am trying to match in file2

Here is file 1.

1,Mary,24 Fuller Rd
2,Fred,19 St Johns
3,Jonathan,8 Poplar Drive
4,Susan,116 Shepherds Way
5,Michael,4 Nerthern Court

and file 2

Dawning,Order.5.DHL
Hawkins,Order.3.FedEx
Jacob,Order.2.Yodel
Plateu,Order.4.DPD
Martins,Order.1.Hermes

My approach is to extract the key from file2 with split. As a single file, this works, but when working with multiple files, the behaviour is weird and not the expected results.

awk -F, '{{split($2,i,".")}{ print i[2]}' file2
5
3
2
4
1

awk -F, 'NR==FNR{split($2,i,"."); next}{ print i[2]}' file2 file1
1
1
1
1
1

I only get the expected result if I remove split but then have no way of extracting the match.

awk -F, 'NR==FNR{array[$2]}END{ for (i in array) print i}' file2 file1
Order.4.DPD
Order.3.FedEx
Order.1.Hermes
Order.5.DHL
Order.2.Yodel

There are many other steps i have taken and failed at but it may make the question overly bloated so if more information regarding this is needed, please ask.

My expected result would look like this

Mary Martins 24 Fuller Rd
Fred Jacob 19 St Johns
Jonathan Hawkins 8 Poplar Drive
Susan Plateu 116 Shepherds Way
Michael Dawning 4 Nerthern Court

Where column2 from file2 and column1 in file1 match based on the number therefore printing $2 and $NF from file1 and $1 from file2

Here are a few of my failed attempts amongst many

awk -F, 'NR==FNR {M=$1; array[$2]; next}{($1 in array)}END{ for (i in array) print $2, M, $NF}' file2 file1
awk -F, 'NR==FNR {M=$1; array[$2]; for (i in array) split(i,a,"."); next} $1==a[2]{print $2,M, $3}' file2 file1
awk -F, 'NR==FNR {M=$1; array[$2]; next}END { for (i in array) split(i,a,".")}($1~a[2]){ print $2,M}' file2 file1

I have included the perl tag as I'd be interested in solutions with perl but primarily want to do this with awk if possible.

Thank you.

awk
2 Answers
$ awk -F'[.,]' 'NR==FNR{a[$3]=$1; next} {print $2, a[$1], $3}' file2 FS=, file1
Mary Martins 24 Fuller Road
Fred Jacob 19 St Johns
Jonathan Hawkins 8 Poplar Drive
Susan Plateu 116 Shepherds Way
Michael Dawning 4 Nerthern Court
  • -F'[.,]' use . or , as field separator for file2
  • NR==FNR{a[$3]=$1; next} save first field of file2 based on third field as key
  • FS=, before file1 in the argument list will change field separator to , for file1
  • print $2, a[$1], $3 print required data (default OFS is single space character)
user_40820520 
perl -F'[.,]' -ane '$ARGV eq "file2" ? $r{$F[2]} = $F[0] : print "$F[1] $r{$F[0]} $F[2]"' file2 file1
  • -F'[.,]' use . or , as field separator for file2 and file1
  • on file2, $F[0] = last name, $F[2] = id that we want to corelate with file1 id
  • on file1, $F[0] = id, $F[1] = first name, $F[2] = address
  • -ane is for while looping / iterating line and autospliting $_ to @F in given files on the command line
  • this example use ternary operator in Perl which look like CONDITION ? EVALUATE_IF_CONDITION_WAS_TRUE : EVALUATE_IF_CONDITION_WAS_FALSE
  • $ARGV Contains the name of the current file when reading the line
  • $ARGV eq "file2" - this is the CONDITION
  • if the line read from "file2" is true then we store the id as the key and last name as the value into Perl hash ($r)
  • once the line finished on file2, the condition will be false because $ARGV will be 'file1'
  • since we already have the file2 mapping in $r, we can get the last name using $r{$F[0]}
user_160846120
Related