merge_csv.sh

#!/bin/bash
# https://stackoverflow.com/a/78062608/240443
# Inspired by glenn jackman's awesome '$(NF-1) == "" {$(NF-1) = $NF} {NF--} 1' trick

file1=$1
file2=$2
key=${3:-name}
sep=${4:-|}

key1=$(head -1 "$file1" | awk -v FS="$sep" -v key="$key" '{ for (i = 1; i <= NF; i++) if ($i == key) print i }')
key2=$(head -1 "$file2" | awk -v FS="$sep" -v key="$key" '{ for (i = 1; i <= NF; i++) if ($i == key) print i }')
output=$(
  awk -v FS="$sep" -v key="$key" '
    NR==1 { for (i = 1; i <= NF; i++) { p[$i] = i } }
    NR!=1 { for (i = 1; i <= NF; i++) { if ($i != key && p[$i]) { o = o ",2." p[$i] } o = o ",1." i } print substr(o, 2) }
  ' <(head -1 "$file2") <(head -1 "$file1")
)

head -1 "$file1" && \
join -t "$sep" -1 "$key1" -2 "$key2" -a 1 -o "$output" \
  <(tail -n +2 "$file1" | sort -t "$sep" -k 1,"$key1") \
  <(tail -n +2 "$file2" | sort -t "$sep" -k 2,"$key2") | \
awk -F "$sep" -v OFS="$sep" '$(NF-1) == "" {$(NF-1) = $NF} {NF--} 1'