3

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
4
  • 7
    Obvious suggestion - don't concatenate the CSVs in the first place.
    – Ed Morton
    May 28 '21 at 12:31
  • 1
    Related: unix.stackexchange.com/q/257819/117549
    – Jeff Schaller
    May 28 '21 at 14:27
  • 2
    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 '21 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 '21 at 8:33
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 
4
  • 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 '21 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 '21 at 12:10
  • Fantastics! thank you very much again! chears
    – Hot JAMS
    May 28 '21 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 '21 at 15:39
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.

3
  • 2
    +1. And may be shortened to awk 'NR==1{h=$0; print}; ($0 != h) ' input. May 28 '21 at 13:45
  • 1
    @PrabhjotSingh original, you should post it as an ansers May 28 '21 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 '21 at 2:56
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
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
2
  • Can we use -f - instead of -f /dev/stdin? May 29 '21 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 '21 at 16:02
5
$ 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
2
  • 1
    +1 for being the most succinct.
    – guest_7
    May 28 '21 at 15:11
  • 1
    @αғ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 '21 at 13:11
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

2
  • 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 '21 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. May 30 '21 at 13:02
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
0
 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
3
  • 4
    This may be shortened to awk '(!_[$0]++)' input . This would remove all duplicate lines ( both header or non header). May 29 '21 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 '21 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 '21 at 13:22

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.