3

Consider the following csv file:

A,3300   
B,8440   
B,8443   
B,8444 
C,304
C,404  
M,5502   
M,5511

The real csv file is really big (around 60,000 rows) but I only included a small version for describing purposes.

I need create a script to parse the file and filter rows basing on the second field to group in a single row those with a matching set of characters (replacing the second field with the matching set of characters).

In other words, I expect the following output from the given csv file above:

A,3300   
B,844  
C,304
C,404 
M,55   

Please note that ONLY the content on the second csv field is relevant for the purpose of the script, so any matching/un-matching occurrence in the other fields need to remain on the file as they are.

Would awk be useful to accomplish this task? or any other built-in function? Any help will be much appreciated.

  • 1
    Welcome to U/L! Is it only the first characters of the second field that you would look at? What about C,404 and C,304? What would the result be? Please do not answer in comments, but edit your question to clarify. (You can then tag me in the comments so I am notified, by mentioning @Sparhawk ). – Sparhawk Aug 28 '19 at 7:06
  • 1
    Thanks @Sparhawk , I will check your proposal later today and come back to you with some feedback. The question has been edited as you suggested. – Marco Aug 28 '19 at 13:51
0

I wrote a small awk function to find common starting characters between 2 strings:

awk '
BEGIN{OFS=FS=","}
function common_chars(a,b, o){
    split(a,asplit,"")
    split(b,bsplit,"")
    n=1
    while (asplit[n]==bsplit[n]){
        o=o""asplit[n]
        n++
    }
    return o
}
s[$1] {v[$1]=common_chars(v[$1],$2)}
!s[$1] {v[$1]=$2;s[$1]=1 }
END {for(a in v){print a,v[a]}}
' file

If $1 has not been seen (state is saved in s[$1]), save $2 in array v[$1]=$2. If it has been seen, set v[$1] with the return of the function between itself and $2. The function simply runs a while loop on the single characters until it finds the first characters not matching.

For C,404 and C,304 it will print C,

Output:

A,3300   
B,844
C,
M,55
  • Thanks @pLumo I will check the proposal and come back to you with some feedback. – Marco Aug 28 '19 at 13:57
  • Hey @pLumo , I just tested your suggestion and indeed it gives the result you posted above, but that is not the desired output, sorry I should have stated that clearly on my original post. If there are no matching occurrences on the second csv field then I need to preserve the entire row as it is (no matter what the other field is). I edited the original post to clarify. Also I'm trying to understand your code so that I can try to modify as needed, but I am not that good on Linux, so if you have some time to take a look and help it would be great. – Marco Aug 28 '19 at 15:32
0

This will likely be somewhat slow for 60,000 rows, but seems to work. Do not put quotes around $line here!

I still have this odd feeling that there's a mistake somewhere in that script, that would show with more data to work on...

$ sort -u testfile | datamash -t, -g1 collapse 2  \
| tr ',' ' ' | while read line ; do ./my_filter $line ; done
A,3300
B,844
C,304
C,404
M,55

Preprocessing the data with datamash and gets me sorted data i can feed to my_filter line by line:

$ sort -u testfile | datamash -t, -g1 collapse 2 
A,3300
B,8440,8443,8444
C,304,404
M,5502,5511

Now here is my_filter:

$ cat my_filter
#!/bin/bash
_longest_match () {
  if ((${#1}>${#2})); then
    long="$1" short="$2"
  else
    long="$2" short="$1"
  fi

  lshort=${#short}
  score=0
  for ((l=score+1;l<=lshort;++l)); do
    sub="${short:0:l}"

    [[ $long != $sub* ]] && break
    subfound="$sub" score="$l"
  done

  if ((score)); then
    printf '%s\n' "$subfound"
  fi
} # ----------  end of function _longest_match  ----------


_output () {
  for item in $(echo "$@"|tr ' ' '\n' | sort -u) ; do
    printf '%s,%s\n' "$key" "$item"
  done
} # ----------  end of function _output  ----------

declare -A matches
declare -A no_matches

key=$1
shift

for item in $( printf '%s\n' "$@"| sort -nr ); do
  if [ -z "$one" ]; then
    one=$1
    two=${2:-$1}
    shift 2
  else
    two=$1
    shift
  fi

  three=$(_longest_match $one $two)

  [ ${#three} -gt 0 ] && matches[$key]+="$three " || no_matches[$key]+="$one $two "
  [ ${#three} -gt 0 ] && one="$three" || one="$two"
done

  _output "${matches[@]} ${no_matches[@]}" | sort -u

Found some inspiration for the _longest_match at https://stackoverflow.com/a/23297950

I did some additional testing with double entries in the testfile:

$ cat testfile.new 
A,3300
B,8440
B,8440
U,3
U,7
U,7
U,73
B,8440
B,8443
B,8444
B,976
C,304
C,404
M,5502
M,5511

And the result was:

$ sort -u testfile | datamash -t, -g1 collapse 2  \
| tr ',' ' ' | while read line ; do ./my_filter $line ; done
A,3300
B,844
B,976
C,304
C,404
M,55
U,3
U,7

Does that look like your expected result?

  • Yes, at first glance it definitely does. It looks perfect to me. Let me try your code here and give you feedback later on. Thanks @markgraf – Marco Aug 28 '19 at 22:22
  • Just noticed this is faster if testfile gets sorted an uniq-ed first. Updating script... – markgraf Aug 29 '19 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.