X-Git-Url: https://zdv2.bktei.com/gitweb/BK-2020-03.git/blobdiff_plain/dd9f30387a6f3e81b56ea3e86bef174422b37c77..fdf917e1ee70b612202fe10fab2e73d0ea077017:/unitproc/bkcsvjoin?ds=inline diff --git a/unitproc/bkcsvjoin b/unitproc/bkcsvjoin new file mode 100755 index 0000000..beb1fbf --- /dev/null +++ b/unitproc/bkcsvjoin @@ -0,0 +1,206 @@ +# Date: 2020-02-21T21:23Z; + +# Author: Steven Baltakatei Sandoval + +# License: This bash script, 'bkcsvjoin.sh', is licensed under GPLv3 or +# later by Steven Baltakatei Sandoval: +# +# 'bkcsvjoin.sh' sorts and joins two CSV files based on first field +# Copyright (C) 2020 Steven Baltakatei Sandoval (baltakatei.com) +# +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# A copy of the GNU General Public License may be found at +# . + +# Description: Sorts and joins two CSV files based upon the contents +# of the first field ("field1") in each line of CSV file. It assumes +# the first line is a header labelling field names. It outputs several +# files into a new directory in the working directory. Set theory +# characters in output file names indicate file 1 elements with "A" +# and file 2 elements with "B". Input assumes UNIX-style line +# endings. The output files use the following numbers and definitions: +# +# 1. A⋃B The union of A and B. Contains all field1 elements from file 1 and 2. +# +# 2. A⋃(A⋂B) Contains all field1 elements of file 1 plus the matches between +# file 1 and 2. +# +# 3. B⋃(B⋂A) Contains all field1 elements of file 2 plus the matches between +# file 1 and 2. +# +# 4. A⋂B Contains only field1 elements present in both file 1 and file 2. +# +# 5. A-B Contains all field1 elements of file 1 except when a matching +# element is present in file 2. +# +# 6. B-A Contains all field1 elements of file 2 except when a matching +# element is present in file 1. +# +# 7. A⊖B Contains all field1 elements of file 1 and file 2 except for +# elements which are present in both file 1 and 2. + +# Usage: bkcsvjoin.sh [ file1 ] [ file2 ] > fileOut.csv + +# Dependencies: GNU Coreutils 8.30-3 (bash, join, sort, head, tail, +# mkdir) (see end of file for license details). Note: only tested on +# Debian GNU/Linux 10. + +echoerr() { echo "$@" 1>&2; } # Function for outputing text to stderr. + +SCRIPT_DATE=$(date +%Y%m%dT%H%M%S%z) +SCRIPT_PATH=$(pwd) + +# Create temporary working directory +TEMP_DIRPATH="$SCRIPT_PATH"/"$SCRIPT_DATE"..temp +mkdir "$TEMP_DIRPATH" + +# Read file names as input arguments +FILE1_PATH="$1" +FILE2_PATH="$2" +FILE1_BASENAME=$(basename "$FILE1_PATH") +FILE2_BASENAME=$(basename "$FILE2_PATH") +#[[ -f "$FILE1_PATH" ]] && echoerr "DEBUG:$FILE1_PATH is file with basename $FILE1_BASENAME." +#[[ -f "$FILE2_PATH" ]] && echoerr "DEBUG:$FILE2_PATH is file with basename $FILE2_BASENAME." + +# Warn and prompt if carriage return character is used. +if [[ $(grep -Uc $'\x0D' "$FILE1_PATH") -gt 0 ]] || [[ $(grep -Uc $'\x0D' "$FILE2_PATH") -gt 0 ]]; then + echoerr "ERROR: Carriage Return character(s) detected. Windows text file?" + read -p "Do you wish to attempt to convert DOS-style line endings to UNIX-style by removing all carriage returns? (probably okay) [y/n]" choice + case "$choice" in + y|Y ) echoerr "yes" + OPTION_DOS2UNIX="true" + ;; + n|N ) echoerr "no" + OPTION_DOS2UNIX="false" + echoerr "WARNING:Output may contain mix of DOS-style (CRLF) and UNIX-style (LF) line endings!" + echoerr " See https://stackoverflow.com/a/2613834 " + sleep 2 + ;; + * ) echo "Invalid selection." + echoerr "Exiting." && exit 1;; + esac +fi + +# Sort files on field1 and write to temporary directory. See [1], and [2]. +FILE1_SORTED_PATH="$TEMP_DIRPATH"/"$FILE1_BASENAME"_SORTED.csv +FILE2_SORTED_PATH="$TEMP_DIRPATH"/"$FILE2_BASENAME"_SORTED.csv +( head -n1 $FILE1_PATH && tail -n+2 $FILE1_PATH | sort -t, -k1,1 | uniq ) > $FILE1_SORTED_PATH +( head -n1 $FILE2_PATH && tail -n+2 $FILE2_PATH | sort -t, -k1,1 | uniq ) > $FILE2_SORTED_PATH + +# 1. A⋃B. Join files into Union of (file 1) + (file 2) (i.e. throwing nothing out). See [3], [6]. +FILES_JOINED_COMPLETE="$( join -t, -a1 -a2 -o auto --header "$FILE1_SORTED_PATH" "$FILE2_SORTED_PATH" )" + +# 2. A⋃(A⋂B). Join files, file 1 uniques only (omit unmatched field1 entries from file 2). See [6]. +FILES_JOINED_F1UO="$( join -t, -a1 -o auto --header "$FILE1_SORTED_PATH" "$FILE2_SORTED_PATH" )" + +# 3. B⋃(A⋂B). Join files, file 2 uniques only (omit unmatched field1 entries from file 1). See [6]. +FILES_JOINED_F2UO="$( join -t, -a2 -o auto --header "$FILE1_SORTED_PATH" "$FILE2_SORTED_PATH" )" + +# 4. A⋂B. Join files, only entries present in both file 1 and file 2 ( file 1 ⋂ file 2 ). See [6]. +FILES_JOINED_INTERSECTION="$( join -t, -o auto --header "$FILE1_SORTED_PATH" "$FILE2_SORTED_PATH" )" + +# 5. A-B. Join files into Relative Complement of (file 1) - (file 2) field1 entries. See See [5], [6]. +FILES_JOINED_DIFFERENCE_AB="$( join -t, -v1 -o auto --header "$FILE1_SORTED_PATH" "$FILE2_SORTED_PATH" )" + +# 6. B-A. Join files into Relative Complement of (file 2) - (file 1) field1 entries. See [5], [6]. +FILES_JOINED_DIFFERENCE_BA="$( join -t, -v2 -o auto --header "$FILE1_SORTED_PATH" "$FILE2_SORTED_PATH" )" + +# 7. A⊖B. Join files into Symmetric Difference of (file 2) ⊖ (file 1) field1 entries. See [5], [6]. +FILES_JOINED_DIFFERENCE_SYM="$( join -t, -v1 -v2 -o auto --header "$FILE1_SORTED_PATH" "$FILE2_SORTED_PATH" )" + +# Attempt to restore to UNIX-style line endings by removing all carriage return (CR) characters OPTION_DOS2UNIX set to true. See [7]. +if [[ $OPTION_DOS2UNIX == "true" ]]; then + FILES_JOINED_COMPLETE="$(echo "$FILES_JOINED_COMPLETE" | tr -d '\r')" + FILES_JOINED_F1UO="$(echo "$FILES_JOINED_F1UO" | tr -d '\r')" + FILES_JOINED_F2UO="$(echo "$FILES_JOINED_F2UO" | tr -d '\r')" + FILES_JOINED_INTERSECTION="$(echo "$FILES_JOINED_INTERSECTION" | tr -d '\r')" + FILES_JOINED_DIFFERENCE_AB="$(echo "$FILES_JOINED_DIFFERENCE_AB" | tr -d '\r')" + FILES_JOINED_DIFFERENCE_BA="$(echo "$FILES_JOINED_DIFFERENCE_BA" | tr -d '\r')" + FILES_JOINED_DIFFERENCE_SYM="$(echo "$FILES_JOINED_DIFFERENCE_SYM" | tr -d '\r')" +fi + +# Output CSV files to temp directory with carriage returns ('\r') removed. +echo "$FILES_JOINED_COMPLETE" > "$TEMP_DIRPATH"/"1.A⋃B..UNION.csv" && echoerr "1.A⋃B..UNION.csv written to $TEMP_DIRPATH" +echo "$FILES_JOINED_F1UO" > "$TEMP_DIRPATH"/"2.A⋃(A⋂B)..FILE-1-UNIQUES-ONLY.csv" && echoerr "2.A⋃(A⋂B)..FILE-1-UNIQUES-ONLY.csv written to $TEMP_DIRPATH" +echo "$FILES_JOINED_F2UO" > "$TEMP_DIRPATH"/"3.B⋃(A⋂B)..FILE-2-UNIQUES-ONLY.csv" && echoerr "3.B⋃(A⋂B)..FILE-2-UNIQUES-ONLY.csv written to $TEMP_DIRPATH" +echo "$FILES_JOINED_INTERSECTION" > "$TEMP_DIRPATH"/"4.A⋂B..INTERSECTION.csv" && echoerr "4.A⋂B..INTERSECTION.csv written to $TEMP_DIRPATH" +echo "$FILES_JOINED_DIFFERENCE_AB" > "$TEMP_DIRPATH"/"5.A-B..DIFFERENCE_A_MINUS_B.csv" && echoerr "5.A-B..DIFFERENCE_A_MINUS_B.csv written to $TEMP_DIRPATH" +echo "$FILES_JOINED_DIFFERENCE_BA" > "$TEMP_DIRPATH"/"6.B-A..DIFFERENCE_B_MINUS_A.csv" && echoerr "6.B-A..DIFFERENCE_B_MINUS_A.csv written to $TEMP_DIRPATH" +echo "$FILES_JOINED_DIFFERENCE_SYM" > "$TEMP_DIRPATH"/"7.A⊖B..DIFFERENCE_SYM.csv" && echoerr "7.A⊖B..DIFFERENCE_SYM.csv written to $TEMP_DIRPATH" + +# ==References== +# [1]: How to sort a csv file while retaining the first line. https://stackoverflow.com/a/14562674 +# [2]: How to sort a csv file by sorting on a single field. https://stackoverflow.com/a/44744800 +# [3]: How to join multiple sorted csv files into one. https://stackoverflow.com/a/27606199 +# [4]: How to join a csv file retaining the first line, sorting by certain fields, and not omitting nonmatching fields. https://superuser.com/a/1527095 +# [5]: How to determine symmetric difference between two sorted files. https://www.gnu.org/software/coreutils/manual/html_node/Set-operations.html +# [6]: Set theory symbols https://www.rapidtables.com/math/symbols/Set_Symbols.html +# [7]: How to convert DOS-style to UNIX-style line endings. https://stackoverflow.com/a/2613834 + +# ==Dependencies== + + # GNU bash, version 5.0.3(1)-release (x86_64-pc-linux-gnu) + # Copyright (C) 2019 Free Software Foundation, Inc. + # License GPLv3+: GNU GPL version 3 or later + + # This is free software; you are free to change and redistribute it. + # There is NO WARRANTY, to the extent permitted by law. + + + # join (GNU coreutils) 8.30 + # Copyright (C) 2018 Free Software Foundation, Inc. + # License GPLv3+: GNU GPL version 3 or later . + # This is free software: you are free to change and redistribute it. + # There is NO WARRANTY, to the extent permitted by law. + + # Written by Mike Haertel. + + + # sort (GNU coreutils) 8.30 + # Copyright (C) 2018 Free Software Foundation, Inc. + # License GPLv3+: GNU GPL version 3 or later . + # This is free software: you are free to change and redistribute it. + # There is NO WARRANTY, to the extent permitted by law. + + # Written by Mike Haertel and Paul Eggert. + + + # head (GNU coreutils) 8.30 + # Copyright (C) 2018 Free Software Foundation, Inc. + # License GPLv3+: GNU GPL version 3 or later . + # This is free software: you are free to change and redistribute it. + # There is NO WARRANTY, to the extent permitted by law. + + # Written by David MacKenzie and Jim Meyering. + + + # tail (GNU coreutils) 8.30 + # Copyright (C) 2018 Free Software Foundation, Inc. + # License GPLv3+: GNU GPL version 3 or later . + # This is free software: you are free to change and redistribute it. + # There is NO WARRANTY, to the extent permitted by law. + + # Written by Paul Rubin, David MacKenzie, Ian Lance Taylor, + # and Jim Meyering. + + + # mkdir (GNU coreutils) 8.30 + # Copyright (C) 2018 Free Software Foundation, Inc. + # License GPLv3+: GNU GPL version 3 or later . + # This is free software: you are free to change and redistribute it. + # There is NO WARRANTY, to the extent permitted by law. + + # Written by David MacKenzie. + +# ==Metadata== +# - Created in task 20200220_90eb0c7d +