How to outer-join two CSV files, using shell script?

Question

I have two CSV files, like the following:

file1.csv

label,"Part-A"
"ABC mn","2.0"
"XYZ","3.0"
"PQR SN","6"

file2.csv

label,"Part-B"
"XYZ","4.0"
"LMN Wv","8"
"PQR SN","6"
"EFG","1.0"

Desired Output.csv

label,"Part-A","Part-B"
"ABC mn","2.0",NA
"EFG",NA,"1.0"
"LMN Wv",NA,"8"
"PQR SN","6","6"
"XYZ","3.0","4.0"

Currently with the below awk command i am able to combine the matching one's which have entries for label in both the files like PQR and XYZ but unable to append the ones that are not having label values present in both the files:

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

Answer 1

I would like to introduce Miller to you. It is a tool that can do a few things with a few file formats and that is available as a stand-alone binary. You just have to download the archive, put the mlr executable somewhere (preferably in your PATH) and you're done with the installation.

mlr --csv \
    join -f file1.csv -j 'label' --ul --ur \
    then \
    unsparsify --fill-with 'NA' \
    then \
    sort -f 'label' \
    file2.csv

Command parts:

• mlr --csv
  means that you want to read CSV files and output a CSV format. As an other example, if you want to read CSV files and output a JSON format it would be mlr --icsv --ojson
• join -f file1.csv -j 'label' --ul --ur ...... file2.csv
  means to join file1.csv and file2.csv on the field label and emit the unmatching records of both files
• then is Miller's way of chaining operations
• unsparsify --fill-with 'NA'
  means to create the fields that didn't exist in each file and fill them with NA. It's needed for the records that had a uniq label
• then sort -f 'label'
  means to sort the records on the field label

---

Regarding the updated question: mlr handles the CSV quoting on its own. The only difference with your new expected output is that it removes the superfluous quotes:

label,Part-A,Part-B
ABC mn,2.0,NA
EFG,NA,1.0
LMN Wv,NA,8
PQR SN,6,6
XYZ,3.0,4.0

Answer 2

This solution prints exactly the wished result with any AWK. Please note that the sorting algorithm is taken from the mawk manual.

# SO71053039.awk

#-------------------------------------------------
# insertion sort of A[1..n]
function isort( A,A_SWAP,           n,i,j,hold ) {
  n = 0
  for (j in A)
    A_SWAP[++n] = j
  for( i = 2 ; i <= n ; i++)
  {
    hold = A_SWAP[j = i]
    while ( A_SWAP[j-1] "" > "" hold )
    { j-- ; A_SWAP[j+1] = A_SWAP[j] }
    A_SWAP[j] = hold
  }
  # sentinel A_SWAP[0] = "" will be created if needed
  return n
}

BEGIN {
  FS = OFS = ","
  out = "Output.csv"

  # read file 1
  fnr = 0
  while ((getline < ARGV[1]) > 0) {
    ++fnr
    if (fnr == 1) {
      for (i=1; i<=NF; i++)
        FIELDBYNAME1[$i] = i # e.g. FIELDBYNAME1["label"] = 1
    }
    else {
      LABEL_KEY[$FIELDBYNAME1["label"]]
      LABEL_KEY1[$FIELDBYNAME1["label"]] = $FIELDBYNAME1["\"Part-A\""]
    }
  }
  close(ARGV[1])

  # read file2
  fnr = 0
  while ((getline < ARGV[2]) > 0) {
    ++fnr
    if (fnr == 1) {
      for (i=1; i<=NF; i++)
        FIELDBYNAME2[$i] = i # e.g. FIELDBYNAME2["label"] = 1
    }
    else {
      LABEL_KEY[$FIELDBYNAME2["label"]]
      LABEL_KEY2[$FIELDBYNAME2["label"]] = $FIELDBYNAME2["\"Part-B\""]
    }
  }
  close(ARGV[2])

  # print the header
  print "label" OFS "\"Part-A\"" OFS "\"Part-B\"" > out

  # get the result
  z = isort(LABEL_KEY, LABEL_KEY_SWAP)
  for (i = 1; i <= z; i++) {
    result_string = sprintf("%s", LABEL_KEY_SWAP[i])
    if (LABEL_KEY_SWAP[i] in LABEL_KEY1)
      result_string = sprintf("%s", result_string OFS LABEL_KEY1[LABEL_KEY_SWAP[i]] OFS (LABEL_KEY_SWAP[i] in LABEL_KEY2 ? LABEL_KEY2[LABEL_KEY_SWAP[i]] : "NA"))
    else
      result_string = sprintf("%s", result_string OFS "NA" OFS LABEL_KEY2[LABEL_KEY_SWAP[i]])
    print result_string > out
  }
}

Call:

awk -f SO71053039.awk file1.csv file2.csv
=> result file Output.csv with content:
label,"Part-A","Part-B"
"ABC mn","2.0",NA
"EFG",NA,"1.0"
"LMN Wv",NA,"8"
"PQR SN","6","6"
"XYZ","3.0","4.0"