1

I have many different csv files in a folder (megadrive.txt, snes.txt) like this:

Aerial Assault (USA);Aerial Assault (USA);Sega Master System;;;;;;;;;0;;;;;
Aerial Assault (USA);Aerial Assault (USA);Sega Master System;;1990;Sega;Shooter;;;;;0;;;;;
After Burner (World);After Burner (World);Sega Master System;;;;;;;;;;;;;;
After Burner (World);After Burner (World);Sega Master System;;1988;Sega;Flying;;;;;0;;;;;
Air Rescue (Europe);Air Rescue (Europe);Sega Master System;;1992;Sega;Shooter;;;;;0;;;;;
Aladdin (Europe);Aladdin (Europe);Sega Master System;;1994;Sega;Platform;;;;;0;;;;;

In these CSVs, I have many, many lines and many have the same first field. I want to batch-process these files and, in each file, only keep the longest line for each first field. For example, the output should be:

Aerial Assault (USA);Aerial Assault (USA);Sega Master System;;1990;Sega;Shooter;;;;;0;;;;;
After Burner (World);After Burner (World);Sega Master System;;1988;Sega;Flying;;;;;0;;;;;
Air Rescue (Europe);Air Rescue (Europe);Sega Master System;;1992;Sega;Shooter;;;;;0;;;;;
Aladdin (Europe);Aladdin (Europe);Sega Master System;;1994;Sega;Platform;;;;;0;;;;;

In particular

Aerial Assault (USA);Aerial Assault (USA);Sega Master System;;;;;;;;;0;;;;;
Aerial Assault (USA);Aerial Assault (USA);Sega Master System;;1990;Sega;Shooter;;;;;0;;;;;

both records have the first field duplicated but the second entry is longer, so I would like to keep the second entry end remove all the shorter lines with the same first field.

How can I do this?

2 Answers 2

1

I am assuming your fields are defined by ;. And that there can be no ; inside a field. If those assumptions are true, you can do:

$ awk -F';' '{if(!a[$1]||length($0)>length(a[$1])){a[$1]=$0}}END{for(i in a){print a[i]}}' file.txt
Aerial Assault (USA);Aerial Assault (USA);Sega Master System;;1990;Sega;Shooter;;;;;0;;;;;
After Burner (World);After Burner (World);Sega Master System;;1988;Sega;Flying;;;;;0;;;;;
Aladdin (Europe);Aladdin (Europe);Sega Master System;;1994;Sega;Platform;;;;;0;;;;;
Air Rescue (Europe);Air Rescue (Europe);Sega Master System;;1992;Sega;Shooter;;;;;0;;;;;

However, this has the drawback of needing to store one line per 1st field in memory and that might be an issue for huge files. If so, you can try this instead:

$ awk '{print length($0)";"$0}' file.txt | sort -t';' -rnk1,1 | awk -F';' '++a[$2]==1' | cut -d';' -f2-
Aerial Assault (USA);Aerial Assault (USA);Sega Master System;;;;;;;;;0;;;;;
After Burner (World);After Burner (World);Sega Master System;;;;;;;;;;;;;;
Air Rescue (Europe);Air Rescue (Europe);Sega Master System;;1992;Sega;Shooter;;;;;0;;;;;
Aladdin (Europe);Aladdin (Europe);Sega Master System;;1994;Sega;Platform;;;;;0;;;;;

You can apply either solution to all of your files with a simple shell loop:

for f in *txt; do 
    awk -F';' '{if(!a[$1]||length($0)>length(a[$1])){a[$1]=$0}}END{for(i in a){print a[i]}}' "$f" > "$f".fixed
done

Or

for f in *txt; do 
    awk '{print length($0)";"$0}' file.txt | sort -t';' -rnk1,1 | 
        awk -F';' '++a[$2]==1' | cut -d';' -f2- > "$f".fixed
done
3
  • 1
    @EdMorton absolutely right about the sort, thanks.Should be fixed now. As for tabs, though, what if the 2nd field had a tab? And sure, !++seen[$2]++ might be more idiomatic, but it's also more cryptic to those not familiar with the idiom, so I don't see any benefit in using it here. I've done so often enough in other cases.
    – terdon
    Apr 4, 2020 at 15:44
  • Good point about the possibility of the 2nd field containing a tab, I hadn't considered that. wrt the idiom - IMHO idioms are good for everyone to become familiar with if they're going to be using a tool/language so if we use one and someone isn't familiar with it and investigates or asks then it's still a win.
    – Ed Morton
    Apr 4, 2020 at 15:50
  • I deleted my answer and upvoted yours since they're now both so similar. Talking of cryptic though {if(!a[$1]||length($0)>length(a[$1])){a[$1]=$0}}END{for(i in a){print a[i]}} could stand a few newlines and indenting and maybe a temp var or 2 :-).
    – Ed Morton
    Apr 4, 2020 at 15:54
0

Try with sort(1):

sort -rt';' filename | sort -t';' -usk1,1

Aerial Assault (USA);Aerial Assault (USA);Sega Master System;;1990;Sega;Shooter;;;;;0;;;;;
After Burner (World);After Burner (World);Sega Master System;;1988;Sega;Flying;;;;;0;;;;;
Air Rescue (Europe);Air Rescue (Europe);Sega Master System;;1992;Sega;Shooter;;;;;0;;;;;
Aladdin (Europe);Aladdin (Europe);Sega Master System;;1994;Sega;Platform;;;;;0;;;;;

Both sorts will use the ; as the field delimiter (-t';'). The first will reverse sort (-r), so that the empty fields come after the non-empty fields, and the second sort will sort by the first field (-k1,1), and remove extra lines with the same first field (-u = uniq), but will otherwise keep to order set by the first sort (-s = stable).

This assumes that instead of the "longest" line as the title says, you actually want the "most complete", ie. between two lines with the same first field, the shorter one has always a subset of the fields of the longer one (which is the only case where discarding the shorter lines can make any sense IMHO). It also assumes that your sort implementation has a -s (stable) option: both the GNU (Linux) and BSD sort do.

If you want to do it on a batch of files, you should use find:

find dir -type f -name '*.txt' \
    -exec sh -c 'for f; do sort -rt";" "$f" |
    sort -t";" -usk1,1 > "$f.new" && echo mv "$f.new" "$f"; done' sh {} +

Adjust the find's predicates (-name, etc) and only remove the echo from before mv if you're ready to clobber your existing files.

2
  • That won't keep the longest lines since sort -rt';' would output foo;z; before foo;bar;lots;of;extras.
    – Ed Morton
    Apr 4, 2020 at 15:23
  • I was kind of second guessing the OP, my bad, I have clarified the answer. If the challenge really is to sort by first field then by length, then preprocessing the input to prepend the length is probably the way to go, something like awk '{print length";"$0}' file | sort -t';' -k2,2 -k1,1rn | sed 's/[^;]*;//' | sort -st';' -k1,1 -u
    – user313992
    Apr 4, 2020 at 21:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .