AWK: how can I remove repeated header lines from CSV?

Question

Dealing with the csv produced by the concatenation of several CSVs, I am looking for the possibility to remove repeats of the header lines (present in the each concatunated CSV being identical among them). here is my CSV contained repeats of the first line:

ID(Prot),   ID(lig),    ID(cluster),    dG(rescored),   dG(before), POP(before)
1000,   lig40,  1,  0.805136,   -5.5200,    79
1000,   lig868, 1,  0.933209,   -5.6100,    42
1000,   lig278, 1,  0.933689,   -5.7600,    40
1000,   lig619, 3,  0.946354,   -7.6100,    20
1000,   lig211, 1,  0.960048,   -5.2800,    39
1000,   lig40,  2,  0.971051,   -4.9900,    40
1000,   lig868, 3,  0.986384,   -5.5000,    29
1000,   lig12,  3,  0.988506,   -6.7100,    16
1000,   lig800, 16, 0.995574,   -4.5300,    40
1000,   lig800, 1,  0.999935,   -5.7900,    22
1000,   lig619, 1,  1.00876,    -7.9000,    3
1000,   lig619, 2,  1.02254,    -7.6400,    1
1000,   lig12,  1,  1.02723,    -6.8600,    5
1000,   lig12,  2,  1.03273,    -6.8100,    4
1000,   lig211, 2,  1.03722,    -5.2000,    19
1000,   lig211, 3,  1.03738,    -5.0400,    21
ID(Prot),   ID(lig),    ID(cluster),    dG(rescored),   dG(before), POP(before)
10V1,   lig40,  1,  0.513472,   -6.4600,    150
10V1,   lig211, 2,  0.695981,   -6.8200,    91
10V1,   lig278, 1,  0.764432,   -7.0900,    70
10V1,   lig868, 1,  0.787698,   -7.3100,    62
10V1,   lig211, 1,  0.83416,    -6.8800,    54
10V1,   lig868, 3,  0.888408,   -6.4700,    44
10V1,   lig278, 2,  0.915932,   -6.6600,    35
10V1,   lig12,  1,  0.922741,   -9.3600,    19
10V1,   lig12,  8,  0.934144,   -7.4600,    24
10V1,   lig40,  2,  0.949955,   -5.9000,    34
10V1,   lig800, 5,  0.964194,   -5.9200,    30
10V1,   lig868, 2,  0.966243,   -6.9100,    20
10V1,   lig12,  2,  0.972575,   -8.3000,    10
10V1,   lig619, 6,  0.979168,   -8.1600,    9
10V1,   lig619, 4,  0.986202,   -8.7800,    5
10V1,   lig800, 2,  0.989599,   -6.2400,    20
10V1,   lig619, 1,  0.989725,   -9.2900,    3
10V1,   lig12,  7,  0.991535,   -7.5800,    9
ID(Prot),   ID(lig),    ID(cluster),    dG(rescored),   dG(before), POP(before)
10V2,   lig40,  1,  0.525767,   -6.4600,    146
10V2,   lig211, 2,  0.744702,   -6.8200,    78
10V2,   lig278, 1,  0.749015,   -7.0900,    74
10V2,   lig868, 1,  0.772025,   -7.3100,    66
10V2,   lig211, 1,  0.799829,   -6.8700,    63
10V2,   lig12,  1,  0.899345,   -9.1600,    25
10V2,   lig12,  4,  0.899606,   -7.5500,    32
10V2,   lig868, 3,  0.903364,   -6.4800,    40
10V2,   lig278, 3,  0.913145,   -6.6300,    36
10V2,   lig800, 5,  0.94576,    -5.9100,    35

To post-process this CSV I need to remove repetitions of the header line

ID(Prot),   ID(lig),    ID(cluster),    dG(rescored),   dG(before), POP(before)

keeping the header only in the begining of the fused csv (on the first line!).I have tried to use the following awk one-liner which is looking for the 1st line and then remove its repeates

 awk '{first=$1;gsub("ID(Prot)","");print first,$0}' mycsv.csv > csv_without_repeats.csv

however it did not recognize the header line, meaning that the pattern was not defined correctly.

How my AWK code could be corrected supposed that further it should be piped to sort in other to sort the lines after the filtering of the repeats ?

awk '{first=$1;gsub(/ID(Prot)?(\([-azA-Z]+\))?/,"");print first,$0}' | LC_ALL=C sort -k4,4g input.csv > sorted_and_without_repeats.csv

Obvious suggestion - don't concatenate the CSVs in the first place. — Ed Morton, May 28, 2021 at 12:31
By the way, please stop posting large amounts of sample input/output when it takes a fraction of that to demonstrate your problem. Any time we need a scroll bar to see your sample input or output it's much too big. — Ed Morton, May 28, 2021 at 14:41
@EdMorton. Or at least run all but the first file through tail -n +2 to skip the first row (or all files and add the first row of any file at the beginning with head -n 1). — Graipher, May 30, 2021 at 8:33

terdon · Accepted Answer · 2021-05-28 09:34:16Z

10

Here's an awk script that will skip any lines that start with ID(Prot), unless it is the first line:

awk 'NR==1 || !/^ID\(Prot\)/' file > newFile

Here's the same idea in perl:

perl -ne 'print if $.==1 || !/^ID\(Prot\)/' file > newFile

Or, to edit the original file in place:

perl -i -ne 'print if $.==1 || !/^ID\(Prot\)/' file

answered May 28, 2021 at 9:34

terdon♦

219k58 gold badges411 silver badges617 bronze badges

thank you ! may you specify how it would be possible to pipe your AWK sollution to sort in order to sort the data based on the values in the 4th column for example? I've tried awk 'NR==1 || !/^ID(Prot)/' | LC_ALL=C sort -k4,4g input.csv > output.csv but it did not work:-)
– Hot JAMS
May 28, 2021 at 12:09
1

@HotJAMS you need to give an input file to awk, otherwise it doesn't have anything to work on. Just try awk 'NR==1 || !/^ID(Prot)/' input.csv | LC_ALL=C sort -k4,4g > output.csv
– terdon ♦
May 28, 2021 at 12:10
Fantastics! thank you very much again! chears
– Hot JAMS
May 28, 2021 at 12:14
That pipe to sort relies on specific values in the header coming first in the sort vs the values in the same column in the rest of the file so it's probably OK for the OPs sample input but I wouldn't rely on it in general. It can also shuffle the order of the lines for the same 4th field values which may not be desirable. @HotJams please don't ask multiple questions in one SE question - keep each SE question focused and simple just ask a second question if you have one. That particular "sort with a header" question has been asked and answered many times on SE and SO though,
– Ed Morton
May 28, 2021 at 15:39

Add a comment |

Quasímodo · Accepted Answer · 2021-05-28 13:38:34Z

10

With a POSIX-compliant sed (tested on GNU sed and busybox sed):

sed '1!{/^ID/d;}' data

Delete all line, except the first, when this starts with ID. Some sed implementations have the -i option to enable editing the file in place.

awk:

awk 'NR == 1 {h=$0; print} $0 == h {next}1' data

If we are in the first line save the header and print it, then for each line we process if this is equal to the header skip it, otherwise print it.

Or the same in perl:

perl -lne '$h = $_ if $. == 1; print if($_ ne $h || $. == 1)' data

Add the -i option to let perl edit the file in place.

edited May 28, 2021 at 13:38

Quasímodo

17.5k3 gold badges30 silver badges69 bronze badges

answered May 28, 2021 at 11:25

DanieleGrassini

2,7104 silver badges17 bronze badges

2

+1. And may be shortened to awk 'NR==1{h=$0; print}; ($0 != h) ' input.
– Prabhjot Singh
May 28, 2021 at 13:45
1

@PrabhjotSingh original, you should post it as an ansers
– DanieleGrassini
May 28, 2021 at 13:51
1

Thanks for the sed line. It's simple enough that I could get it to work first time on a table where the undesirable rows each began with a different word (the month). I'm not familiar enough with awk or perl to do that.
– Mockman
May 29, 2021 at 2:56

Add a comment |

guest_7 · Accepted Answer · 2021-05-28 09:38:58Z

7

Here is one simple way to deal with the pbm using the awk utility. But be aware that even if an less/more space is prsent in the headers then they would be included in the output.

awk '
  NR>1&&$0==hdr{next}
  NR==1{hdr=$0}1
' file

The same approach but in the stream editor utility sed:

sed -En '
  1h;1!G;/^(.*)\n\1$/!P
' file

edited May 28, 2021 at 9:38

answered May 28, 2021 at 9:27

guest_7

5,5461 gold badge5 silver badges13 bronze badges

Add a comment |

Kusalananda · Accepted Answer · 2021-05-28 10:02:52Z

6

grep -v -F -x -f <( head -n 1 file.csv | tee file-new.csv ) file.csv >>file-new.csv

This is using a shell that has process substitutions (<(...)), like bash or zsh, to get the header line from the file using head, write that to a new file with tee, and then filter out all header lines from the original file using grep. The filtered lines are appended to the new file, after the header which was previously written there by tee.

This way of doing it does not depend on what the header actually is. It's just extracting all lines from the original file that happen to be different from the first line of the file.

Without the process substitution:

head -n 1 file.csv | tee file-new.csv |
grep -v -F -x -f /dev/stdin file.csv >>file-new.csv

edited May 28, 2021 at 10:02

answered May 28, 2021 at 9:52

Kusalananda♦

276k32 gold badges549 silver badges838 bronze badges

Can we use -f - instead of -f /dev/stdin?
– D. Ben Knoble
May 29, 2021 at 15:38
1

@D.BenKnoble You can with GNU grep, but not with the grep implementations found on some non-GNU systems (e.g. macOS and the other BSD systems). The POSIX standard for grep does not mention this as a possible special value for -f. I'm showing the more portable variation in my answer.
– Kusalananda ♦
May 29, 2021 at 16:02

Add a comment |

Ed Morton · Accepted Answer · 2021-05-28 14:39:44Z

$ awk 'NR==1{h=$0; print} $0!=h' file
ID(Prot),   ID(lig),    ID(cluster),    dG(rescored),   dG(before), POP(before)
1000,   lig40,  1,  0.805136,   -5.5200,    79
1000,   lig868, 1,  0.933209,   -5.6100,    42
1000,   lig278, 1,  0.933689,   -5.7600,    40
1000,   lig619, 3,  0.946354,   -7.6100,    20
1000,   lig211, 1,  0.960048,   -5.2800,    39
1000,   lig40,  2,  0.971051,   -4.9900,    40
1000,   lig868, 3,  0.986384,   -5.5000,    29
1000,   lig12,  3,  0.988506,   -6.7100,    16
1000,   lig800, 16, 0.995574,   -4.5300,    40
1000,   lig800, 1,  0.999935,   -5.7900,    22
1000,   lig619, 1,  1.00876,    -7.9000,    3
1000,   lig619, 2,  1.02254,    -7.6400,    1
1000,   lig12,  1,  1.02723,    -6.8600,    5
1000,   lig12,  2,  1.03273,    -6.8100,    4
1000,   lig211, 2,  1.03722,    -5.2000,    19
1000,   lig211, 3,  1.03738,    -5.0400,    21
10V1,   lig40,  1,  0.513472,   -6.4600,    150
10V1,   lig211, 2,  0.695981,   -6.8200,    91
10V1,   lig278, 1,  0.764432,   -7.0900,    70
10V1,   lig868, 1,  0.787698,   -7.3100,    62
10V1,   lig211, 1,  0.83416,    -6.8800,    54
10V1,   lig868, 3,  0.888408,   -6.4700,    44
10V1,   lig278, 2,  0.915932,   -6.6600,    35
10V1,   lig12,  1,  0.922741,   -9.3600,    19
10V1,   lig12,  8,  0.934144,   -7.4600,    24
10V1,   lig40,  2,  0.949955,   -5.9000,    34
10V1,   lig800, 5,  0.964194,   -5.9200,    30
10V1,   lig868, 2,  0.966243,   -6.9100,    20
10V1,   lig12,  2,  0.972575,   -8.3000,    10
10V1,   lig619, 6,  0.979168,   -8.1600,    9
10V1,   lig619, 4,  0.986202,   -8.7800,    5
10V1,   lig800, 2,  0.989599,   -6.2400,    20
10V1,   lig619, 1,  0.989725,   -9.2900,    3
10V1,   lig12,  7,  0.991535,   -7.5800,    9
10V2,   lig40,  1,  0.525767,   -6.4600,    146
10V2,   lig211, 2,  0.744702,   -6.8200,    78
10V2,   lig278, 1,  0.749015,   -7.0900,    74
10V2,   lig868, 1,  0.772025,   -7.3100,    66
10V2,   lig211, 1,  0.799829,   -6.8700,    63
10V2,   lig12,  1,  0.899345,   -9.1600,    25
10V2,   lig12,  4,  0.899606,   -7.5500,    32
10V2,   lig868, 3,  0.903364,   -6.4800,    40
10V2,   lig278, 3,  0.913145,   -6.6300,    36
10V2,   lig800, 5,  0.94576,    -5.9100,    35

@αғsнιη I don't like using !h instead of NR==1 since what I'm really trying to do is capture the first line of the input, not the most recent line where h is false even if that does happen to almost certainly be true for only the first line of input in this case, and so NR==1 is the clearest way to express that condition. $0!=h; NR==1{h=$0} would be fine though. — Ed Morton, Jun 1, 2021 at 13:11

PauloVlw · Accepted Answer · 2021-05-29 13:01:09Z

2

This awk command should work whatever the header is. It saves the first line as the header, and only prints the following lines if they are different from the saved header. It will work as long as the repeating headers are strictly the same.

awk 'NR==1 && header=$0; $0!=header' originalfile > newfile

answered May 29, 2021 at 13:01

PauloVlw

1214 bronze badges

1

Note that this will omit printing the header if it is empty or can be parsed as the number 0. While not an issue in this case (given the Q), this seems to be an unwanted side effect (and this is why I feel it's worth pointing it out) and it may be relevant to readers dealing with a similar problem/different data.
– fra-san
May 30, 2021 at 12:28
@fra-san you are absolutely right. Given the circumstances of the question, one should only use this code, and mostly all the other answers, if appending different CSV files with their headers.
– PauloVlw
May 30, 2021 at 13:02

Add a comment |

Stéphane Chazelas · Accepted Answer · 2021-05-30 13:15:02Z

2

You could also use grep after having skipped the first line:

{
  head -n1
  grep -v '^ID'
} < file.csv

That assumes file.csv is a regular file (won't work with a pipe with most head implementation) and that head is POSIX compliant in that it will leave the cursor in stdin just after the first line.

To skip all lines that are identical to the first one, you could do:

{
  IFS= read -r header       &&
    printf '%s\n' "$header" &&
    grep -Fxe "$header"
} < file.csv

edited May 30, 2021 at 13:15

answered May 30, 2021 at 10:37

Stéphane Chazelas

475k84 gold badges935 silver badges1385 bronze badges

Add a comment |

Praveen Kumar BS · Accepted Answer · 2021-05-29 19:32:28Z

 awk '{if(!seen[$0]++)print $0}' filename

output

ID(Prot),   ID(lig),    ID(cluster),    dG(rescored),   dG(before), POP(before)
1000,   lig40,  1,  0.805136,   -5.5200,    79
1000,   lig868, 1,  0.933209,   -5.6100,    42
1000,   lig278, 1,  0.933689,   -5.7600,    40
1000,   lig619, 3,  0.946354,   -7.6100,    20
1000,   lig211, 1,  0.960048,   -5.2800,    39
1000,   lig40,  2,  0.971051,   -4.9900,    40
1000,   lig868, 3,  0.986384,   -5.5000,    29
1000,   lig12,  3,  0.988506,   -6.7100,    16
1000,   lig800, 16, 0.995574,   -4.5300,    40
1000,   lig800, 1,  0.999935,   -5.7900,    22
1000,   lig619, 1,  1.00876,    -7.9000,    3
1000,   lig619, 2,  1.02254,    -7.6400,    1
1000,   lig12,  1,  1.02723,    -6.8600,    5
1000,   lig12,  2,  1.03273,    -6.8100,    4
1000,   lig211, 2,  1.03722,    -5.2000,    19
1000,   lig211, 3,  1.03738,    -5.0400,    21
10V1,   lig40,  1,  0.513472,   -6.4600,    150
10V1,   lig211, 2,  0.695981,   -6.8200,    91
10V1,   lig278, 1,  0.764432,   -7.0900,    70
10V1,   lig868, 1,  0.787698,   -7.3100,    62
10V1,   lig211, 1,  0.83416,    -6.8800,    54
10V1,   lig868, 3,  0.888408,   -6.4700,    44
10V1,   lig278, 2,  0.915932,   -6.6600,    35
10V1,   lig12,  1,  0.922741,   -9.3600,    19
10V1,   lig12,  8,  0.934144,   -7.4600,    24
10V1,   lig40,  2,  0.949955,   -5.9000,    34
10V1,   lig800, 5,  0.964194,   -5.9200,    30
10V1,   lig868, 2,  0.966243,   -6.9100,    20
10V1,   lig12,  2,  0.972575,   -8.3000,    10
10V1,   lig619, 6,  0.979168,   -8.1600,    9
10V1,   lig619, 4,  0.986202,   -8.7800,    5
10V1,   lig800, 2,  0.989599,   -6.2400,    20
10V1,   lig619, 1,  0.989725,   -9.2900,    3
10V1,   lig12,  7,  0.991535,   -7.5800,    9
10V2,   lig40,  1,  0.525767,   -6.4600,    146
10V2,   lig211, 2,  0.744702,   -6.8200,    78
10V2,   lig278, 1,  0.749015,   -7.0900,    74
10V2,   lig868, 1,  0.772025,   -7.3100,    66
10V2,   lig211, 1,  0.799829,   -6.8700,    63
10V2,   lig12,  1,  0.899345,   -9.1600,    25
10V2,   lig12,  4,  0.899606,   -7.5500,    32
10V2,   lig868, 3,  0.903364,   -6.4800,    40
10V2,   lig278, 3,  0.913145,   -6.6300,    36
10V2,   lig800, 5,  0.94576,    -5.9100,    35

This may be shortened to awk '(!_[$0]++)' input . This would remove all duplicate lines ( both header or non header). — Prabhjot Singh, May 29, 2021 at 19:47
@PrabhjotSingh never name a variable _ as that removes absolutely ALL information about what that variable represents, it's orders of magnitude worse than even using single character variable names as those usually tell you something. In this case the idiomatic name for the array used to distinguish first vs subsequent occurrences of it's index is seen[] as Praveen used. It should be written just awk '!seen[$0]++' as you suggest of course. — Ed Morton, Jun 1, 2021 at 13:15
but as you correctly point out this approach will remove all duplicate lines, not just the header line, and so it shouldn't be used. — Ed Morton, Jun 1, 2021 at 13:22

Stack Exchange Network

current community

your communities

more stack exchange communities

AWK: how can I remove repeated header lines from CSV?

8 Answers 8

Your Answer

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

Linked

Hot Network Questions

AWK: how can I remove repeated header lines from CSV?

8 Answers 8

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions