3

I have a large amount of auto-generated CSV files that look like this:

1603145914502,48.12,0.085,s
1603145914815,48.12,0.020,s
1603145914941,48.12,0.019,s
1603145915404,48.12,0.031,s
1603145915612,48.12,0.033,s
1603145915899,48.12,0.019,s

one integer, two floats and a letter.

some of the files are corrupted due to a threading issue when they were generated:

1603145914502,48.12,0.085,s
1603145914815,48.12,0.020,s
1603145914941,48.12,0.019,s
1603145915404,48.12,0.031,s
1603145915612,48.12,0.033,s
1603145915899,48.12,0.019,s
1603145914502,48.12,0.085,s915899,48.12,0.019,s
1603145914815,48.12,0.020,s
1603145914941,48.12,0.019,s
1603145915404,48.12,0.031,s
1603145915612,48.12,0.033,s
1603145915899,48.12,0.019,s
1459143
1603145914815,48.12,0.020,s
1603145914941,48.12,0.019,s
1603145915404,48.12,0.031,s

Is there a way to find and remove lines that do not fit the format? it looks like something that awk could do really well, but I have absolutely no clue how to use it :)

If there is a way to do it, I would really appreciate if the command could be explained as well so I can learn something from it.


Edit: I'm clarifying the format:

INT,FLOAT,FLOAT,CHAR

There is never a space after the comma. The values can be anything that fit the format above.

  • It might be possible that a line was broken but it still follows the format. – choroba Oct 26 at 16:23
  • Please edit your question and add more details about what "lines that do not fit the format" means. Lines that have more than the expected number of columns? Lines that don't have s in the 4th column? etc. – Bodo Oct 26 at 16:24
  • @Bodo, I've clarified the format; so you can see that only 2 lines in the example do not fit it. – Thomas Oct 26 at 16:27
  • So, could things like -12,Inf,-1e-20,, which match that format occur in the input? – Stéphane Chazelas Oct 26 at 16:34
  • 1
    awk would be fine but the natural solution in awk would still be a regexp that matches the line. I added that and sed equivalent to my answer. – Ed Morton Oct 26 at 17:30
3

Aeither of these should be all you need to match simple/basic formats (e.g. no signs, no exponents) of INT,FLOAT,FLOAT,CHAR:

grep -E '^[0-9]+,([0-9]+\.[0-9]+,){2}[[:alpha:]]$' file

sed -En '/^[0-9]+,([0-9]+\.[0-9]+,){2}[[:alpha:]]$/p' file

awk '/^[0-9]+,([0-9]+\.[0-9]+,){2}[[:alpha:]]$/' file
| improve this answer | |
  • 2
    (assuming the INTs are positive (looks like a unix time with miliseconds so should be fine), and the FLOAT as well and there's no 1.2e-6, NaN, Inf, and CHAR is meant to be a letter). – Stéphane Chazelas Oct 26 at 16:33
2

Is there a way to find and remove lines that do not fit the format?

Many ways, here's one:

$ perl -n -i.bak -e 'print if /\d{13},\d\d.\d\d,\d\.\d\d\d,s$/' t.dat

$ diff t.dat.bak t.dat
7d6
< 1603145914502,48.12,0.085,s915899,48.12,0.019,s
13d11
< 1459143

$ cat t.dat
1603145914502,48.12,0.085,s
1603145914815,48.12,0.020,s
1603145914941,48.12,0.019,s
1603145915404,48.12,0.031,s
1603145915612,48.12,0.033,s
1603145915899,48.12,0.019,s
1603145914815,48.12,0.020,s
1603145914941,48.12,0.019,s
1603145915404,48.12,0.031,s
1603145915612,48.12,0.033,s
1603145915899,48.12,0.019,s
1603145914815,48.12,0.020,s
1603145914941,48.12,0.019,s
1603145915404,48.12,0.031,s
$

I tend to reach for perl before awk/sed but the same operation is doable using awk in much the same way


I would really appreciate if the command could be explained as well so I can learn something from it.

Explanation

  • -n loop over lines from file but do not print them to STDOUT
  • -i do an in-place edit to the file
  • -i.bak and keep a backup copy with specified filename extension in case I make a mistake!
  • -e 'script' run commands in script (on each line of input since -n option)
  • print if ... print the line if it matches the conditional expression
  • / ... / - use this regular expression for pattern-matching
  • ^ at the start of a line ...
  • \d match a digit
  • {3} match exactly three of the previously specified character
  • , match a literal comma character
  • \. match a literal stop character (otherwise . is a wildcard metacharacter)
  • s match a literal s character
  • $ match end of line (i.e. there must be no further characters on the line.

A more flexible expression would be `^\d+,\d+.\d+,\d+.\d+,[a-zA-Z]$

  • + at least one of the previous character
  • [...] one of this specified set
  • [a-z] any lowercase ASCII character between a and z inclusive
  • [[:alpha:]] any character in the POSIX alphabetic set
  • \p{Lowercase_Letter} any Unicode character with the lowercase letter property

Perl regular expressions are a little different from regular expressions used in awk/grep. I think modern versions of awk/grep have options to use perl style regular expressions. See man page for grep's -P option

| improve this answer | |
1
# expect
#          1         2
# 123456789012345678901234567
# 160314591xxxx,48.12,0.0xx,s

grep -Ex '160314591[0-9]{4},48\.12,0\.0[0-9]{2},s' < file.csv

Would do a strict matching. You can make more or less strict as to what exactly you want it to match by adjusting that regular expression.

| improve this answer | |

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.