How to join two CSV files by a temporary common column in awk?

user_943340 0
Viewed 94

I have two CSV files in the form of

file1

A,44
A,21
B,65
C,79

file2

A,7
B,4
C,11

I used awk as

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

producing

A,44,7
A,21,7
B,65,4
C,79,11

a[$1] prints the entire line from file1. How can I omit the first columns in both files (the first column is only used to match the second columns) to produce:

44,7
21,7
65,4
79,11

In other words, how can I pass the columns from the first file to the print block, as $2 does for the second file?

awk
2 Answers

Could you please try following, tested and written on shown samples only.

awk 'BEGIN{FS=OFS=","} FNR==NR{a[$1]=$2;next} ($1 in a){print $2,a[$1]}' file2 file1

Explanation: Adding detailed explanation for above.

awk '                     ##Starting awk program from here.
BEGIN{                    ##Starting BEGIN section from here.
  FS=OFS=","              ##Setting field and output field separator as comma here.
}
FNR==NR{                  ##Checking condition FNR==NR which will be TRUE when file2 is being read.
  a[$1]=$2                ##Creating array a with index $1 and value is $2 from current line.
  next                    ##next will skip all further statement from here.
}
($1 in a){                ##Statements from here will be executed when file1 is being read and it's checking if $1 is present in array a then do following.
  print $2,a[$1]          ##Printing 2nd field and value of array a with index $1 here.
}
' file2 file1             ##Mentioning Input_file names here.

Output will be as follows for shown samples.

44,7
21,7
65,4
79,11

2nd solution: More Generic solution, where considering that your both Input_files could have duplicates in that case it will print 1st value of A in Input_file1 to first value of Input_file2 and so on.

awk '
BEGIN{
  FS=OFS=","
}
FNR==NR{
  a[$1]
  b[$1,++c[$1]]=$2
  next
}
($1 in a){
  print $2,b[$1,++d[$1]]
}
' file2 file1
user_58665800

You can join them using the join command and chose which fields you want to have in the output:

kent$  join -t',' -o 1.2,2.2 file1 file2
44,7                                         
21,7
65,4
79,11
user_1648350
Related