0

I have a large datafile with xml type tags. Each row refers to a unique item and contains a different number of attribute fields. I need to sort the rows based on numbered tags in that row from lowest to highest and remove duplicates. Is it possible to do this within each row using awk?

<ITEM ID='81'>,< 1>KWIKSET</1>,< 2>PASS</2>,< 7>KNOB</7>,< 5a>RIGHT</5a>,< 8c>BRASS</8c>,< 2>TYLO</2>,< 2>PASS</2>,< 5a>RIGHT</5a>,< 8c>BRASS</8c>
<ITEM ID='82'>,< 1>KWIKSET</1>,< 4a>PRIVACY</4a>,< 7>KNOB</7>,< 8b>SATIN</8b>,< 8c>CHROME</8c>,< 2>TYLO</2>,< 4a>PRIVACY</4a>,< 8b>SATIN</8b>,< 8c>CHROME</8c>
<ITEM ID='83'>,< 1>KWIKSET</1>,< 8b>POLISHED</8b>,< 8c>BRASS</8c>
  • Ummm ... why do feel you have to make that ugly again (undo my edit)? – tink Jan 10 at 4:06
  • Oh sorry... was trying to figure out how to make it show the code... it's cutting some of what I'm posting... :-| – Mike Jan 10 at 4:08
  • 1
    thanks tink... this looks like exactly what I was looking for. :) – Mike Jan 10 at 4:28
1

I'd use perl for this:

perl -MList::Util=uniq -F, -lane '
    $item = shift @F;
    @fields = uniq sort @F;
    print join ",", $item, @fields;
' file

outputs:

<ITEM ID='81'>,< 1>KWIKSET</1>,< 2>PASS</2>,< 2>TYLO</2>,< 5a>RIGHT</5a>,< 7>KNOB</7>,< 8c>BRASS</8c>
<ITEM ID='82'>,< 1>KWIKSET</1>,< 2>TYLO</2>,< 4a>PRIVACY</4a>,< 7>KNOB</7>,< 8b>SATIN</8b>,< 8c>CHROME</8c>
<ITEM ID='83'>,< 1>KWIKSET</1>,< 8b>POLISHED</8b>,< 8c>BRASS</8c>

It can be made into an even more incomprehensible one-liner:

perl -MList::Util=uniq -F, -lape '$"=","; $_="@{[$F[0], uniq sort @F[1..$#F]]}"' file

I hope the tag contents do not contain commas.

0

Is there any reason you are solely using awk? To solve your problem, you need to first split the data into each unit, sort, remove duplicates, and then rejoin. While you could do this with most any capable programming or scripting language (even C), is it really worth reinventing the wheel when there's already tools captable of doing what you need?

If the data you posted is a real representation of the data you are working with, you can quickly process it with the following commands:

$ cat RAW_DATA
<ITEM ID='81'>,< 1>KWIKSET</1>,< 2>PASS</2>,< 7>KNOB</7>,< 5a>RIGHT</5a>,< 8c>BRASS</8c>,< 2>TYLO</2>,< 2>PASS</2>,< 5a>RIGHT</5a>,< 8c>BRASS</8c>
<ITEM ID='82'>,< 1>KWIKSET</1>,< 4a>PRIVACY</4a>,< 7>KNOB</7>,< 8b>SATIN</8b>,< 8c>CHROME</8c>,< 2>TYLO</2>,< 4a>PRIVACY</4a>,< 8b>SATIN</8b>,< 8c>CHROME</8c>
<ITEM ID='83'>,< 1>KWIKSET</1>,< 8b>POLISHED</8b>,< 8c>BRASS</8c>
$ while read line; do echo "$(cut -d, -f1 <<< "$line"),$(cut -d, -f2- <<< "$line" | tr ',' '\n' | sort -n | uniq | paste -sd,)"; done < RAW_DATA
<ITEM ID='81'>,< 1>KWIKSET</1>,< 2>PASS</2>,< 2>TYLO</2>,< 5a>RIGHT</5a>,< 7>KNOB</7>,< 8c>BRASS</8c>
<ITEM ID='82'>,< 1>KWIKSET</1>,< 2>TYLO</2>,< 4a>PRIVACY</4a>,< 7>KNOB</7>,< 8b>SATIN</8b>,< 8c>CHROME</8c>
<ITEM ID='83'>,< 1>KWIKSET</1>,< 8b>POLISHED</8b>,< 8c>BRASS</8c>

The while loop reads through each line of the file and processes it separately. Then we want to echo back the new line where cut -d, -f1 <<< "$line" extracts just the first field as it's static and cut -d, -f2- <<< "$line" | tr ',' '\n' | sort -n | uniq | paste -sd, extracts the remaining fields, sorts them numerically, filters for unique values, and uses paste -sd, to rejoin as a comma-delimited list.

  • He initially had more than one row of data (a table). With that your tr approach would be .... odd? :) – tink Jan 10 at 4:10
  • I'm not especially attached to awk, if there's a better way to do this... it's just what I know best... but I'm up to learn if there's a better way... :-) – Mike Jan 10 at 4:11
  • I have 20,000+ rows to go through daily... it needs to sort within the row from left to right, but not top to bottom. – Mike Jan 10 at 4:12
  • @Mike Please update your question with a correct example of your data and I can update my answer accordingly – Jonathan Rouleau Jan 10 at 4:13
  • Thanks Jonathan. I'm having problems trying to get it to show up correctly. There are xml tags. The first tag seems deleted, and the rest of the closing tags as well. Is there a better way to format this so it shows up??? I've tried code, html, etc.... it still looks choppy... this is the actual data – Mike Jan 10 at 4:15

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.