I have a huge csv file with 10 fields separated by commas. Unfortunately, some lines are malformed and do not contain exactly 10 commas (what causes some problems when I want to read the file into R). How can I filter out only the lines that contain exactly 10 commas?
8 Answers
Another POSIX one:
awk -F , 'NF == 11' <file
If the line has 10 commas, then there will be 11 fields in this line. So we simply make awk
use ,
as the field delimiter. If the number of fields is 11, the condition NF == 11
is true, awk
then performs the default action print $0
.
-
5That's actually the first thing that came to my mind on this question. I thought it was overkill, but looking at the code...it sure is clearer. For the benefit of others:
-F
sets the field separator andNF
refers to the number of fields in a given line. Since no code block{statement}
is appended to the conditionNF == 11
, the default action is to print the line. (@cuonglm, feel free to incorporate this explanation if you like.)– WildcardJan 13, 2016 at 9:16 -
5+1: Very elegant and readable solution that is also very general. I can e.g. found all malformed lines with
awk -F , 'NF != 11' <file
– sitemsJan 13, 2016 at 10:08 -
@gardenhead: It's easy to get it, as you see the OP said in his comment. I sometime answer from my mobile, so it's difficult to add the details explanation.– cuonglmJan 13, 2016 at 10:15
-
1@mikeserv: No, sorry if I made you confused, it's just my bad English. You can't have 11 fields with 1-9 commas.– cuonglmJan 13, 2016 at 10:27
-
1
Using egrep
(or grep -E
in POSIX):
egrep "^([^,]*,){10}[^,]*$" file.csv
This filters out anything not containing 10 commas: it matches full lines (^
at the start and $
at the end), containing exactly ten repetitions ({10}
) of the sequence "any number of characters except ',', followed by a single ','" (([^,]*,)
), followed again by any number of characters except ',' ([^,]*
).
You can also use the -x
parameter to drop the anchors:
grep -xE "([^,]*,){10}[^,]*" file.csv
This is less efficient than cuonglm's awk
solution though; the latter is typically six times faster on my system for lines with around 10 commas. Longer lines will cause huge slowdowns.
The simplest grep
code that will work:
grep -xE '([^,]*,){10}[^,]*'
Explanation:
-x
ensures that the pattern must match the entire line, rather than just part of it. This is important so you don't match lines with more than 10 commas.
-E
means "extended regex", which makes for less backslash-escaping in your regex.
Parentheses are used for grouping, and the {10}
afterwards means there must be exactly ten matches in a row of the pattern within the parantheses.
[^,]
is a character class—for instance, [c-f]
would match any single character that is a c
, a d
, an e
or an f
, and [^A-Z]
would match any single character that is NOT an uppercase letter. So [^,]
matches any single character except a comma.
The *
after the character class means "zero or more of these."
So the regex part ([^,]*,)
means "Any character except a comma any number of times (including zero times), followed by a comma" and the {10}
specifies 10 of these. Then [^,]*
to match the rest of the non-comma characters to the end of the line.
sed -ne's/,//11;t' -e's/,/&/10p' <in >out
That first branches out any line with 11 or more commas, and then prints of what remains only those that match 10 commas.
Apparently I answered this before... Here's a me-plagiarism from a question looking for exactly 4 occurrences of some pattern:
You can target
[num]
th occurrence of a pattern with a seds///
ubstitution command by just adding the[num]
to the command. When yout
est for a successful substitution and don't specify a target:
label, thet
est branches out of the script. This means all you have to do is test fors///5
or more commas, then print what remains.Or, at least, that handles the lines which exceed your maximum of 4. Apparently you also have a minimum requirement. Luckily, that is just as simple:
sed -ne 's|,||5;t' -e 's||,|4p'
...just replace the 4th occurrence of
,
on a line with itself and tack yourp
rint on to thes///
ubstitution flags. Because any lines matching,
5 or more times have already been pruned, the lines containing 4,
matches contain only 4.
-
1@cuonglm - that is what I had actually, at first, but people are always telling me i should write more readable code. since i can read the stuff others dispute as unreadable im not sure what to keep and what to drop...? so i put the second comma.– mikeservJan 13, 2016 at 10:04
-
@cuonglm - you can mock me - it wont hurt my feelings. i can take a joke. if you were mocking me it was a little funny. its ok - i just wasn't sure and wanted to know. in my opinion, people should be able to laugh at themselves. anyway, i still dont get it!– mikeservJan 13, 2016 at 10:34
-
Haha, right, it's a very positive thinking. Anyway, it's very funny to chat with you and sometimes, you stress my brain.– cuonglmJan 13, 2016 at 10:42
-
It's interesting that in this answer, if I replace
s/hello/world/2
withs//world/2
, GNU sed work fine. With twosed
from heirloom,/usr/5bin/posix/sed
raise segfault,/usr/5bin/sed
goes into infinitive loop.– cuonglmJan 14, 2016 at 4:23 -
@mikeserv, in reference to our earlier discussion about
sed
andawk
(in comments)—I like this answer and upvoted it, but notice the translation of the acceptedawk
answer is: "Print lines with 11 fields" and the translation of thissed
answer is: "Attempt to remove the 11th comma; skip to next line if you fail. Attempt to replace the 10th comma with itself; print line if you succeed." Theawk
answer gives the instructions to the computer just the way you would express them in English. (awk
is good for field based data.)– WildcardJan 14, 2016 at 6:24
Throwing some short python
:
#!/usr/bin/env python2
with open('file.csv') as f:
print '\n'.join(line for line in f if line.count(',') == 10)
This will read each line and check if the number of commas in the line is equal to 10 line.count(',') == 10
, if so print it will the line.
And here's a Perl way:
perl -F, -ane 'print if $#F==10'
The -n
causes perl
to read its input file line by line and execute the script given by -e
on each line. The -a
turns on automatic splitting: each input line will be split on the value given by -F
(here, a comma) and saved as the array @F
.
The $#F
(or, more generally $#array
), is the highest index of the array @F
. Since arrays start at 0
, a line with 11 fields will have an @F
of 10
. The script, therefore, prints the line if it has exactly 11 fields.
-
1You could also do
print if @F==11
as an array in a scalar context returns the number of elements.– SobriqueJan 14, 2016 at 9:34
If fields can contain commas or newlines your code needs to understand csv. Example (with three columns):
$ cat filter.csv
a,b,c
d,"e,f",g
1,2,3,4
one,two,"three
...continued"
$ cat filter.csv | python3 -c 'import sys, csv
> csv.writer(sys.stdout).writerows(
> row for row in csv.reader(sys.stdin) if len(row) == 3)
> '
a,b,c
d,"e,f",g
one,two,"three
...continued"
I suppose that most solutions so far would discard the second and fourth row.
To display those lines NOT containing the exact number of delimiters :
[ $(awk -F, '!(NF == 11)' <myfile.csv | wc -l) -gt 0 ] && printf "\nPlease maintain the comma separator for the following line(s) in the myfile.csv: \n\n" && awk -F, '!(NF == 11)' <myfile.csv && echo && exit 1
Useful for those looking to validate their input data files.
This one liner is taken from my open source project called Automated_Quiz, which is hosted here : https://sourceforge.net/projects/automated-quiz/
sed
does here) only as far as one more match than is looked for, though this question does. You should not have closed this.grep
answer there is not an acceptable answer for either question...