6 ms·

CleverCSV: A Drop-In Replacement for Python's CSV Module

154 points by reedwolf 7y ago

r0f1 7y ago
How does this compare to Pandas?
- MapleWalnut 7y ago
  This project appears to have a dependency on pandas. https://github.com/alan-turing-institute/CleverCSV/blob/master/setup.py https://github.com/alan-turing-institute/CleverCSV/blob/mast...
  - donarb 7y ago
    That dependency is merely a wrapper to allow CleverCSV to load data directly into a Pandas data frame. It doesn't require pandas just for loading csv data.
- donarb 7y ago
  Pandas uses the Python csv module under the hood. Most of the code in the read_csv method is used to get the data from the csv file and load it into data frames.
  - urschrei 7y ago
    Are you sure? Wes McKinney rewrote the Pandas CSV parser way back in late 2012 because the batteries-included version was slow and memory-hungry. Did Python upstream his version later on, or replace it with something faster (I'd be surprised if they did either, but I'd be curious to know)
    - evanpw 7y ago
      It's configurable. The default parser is written in C, but it's possible to use a parser based on the csv module if you needs things like separators specified by a regex.
etaioinshrdlu 7y ago
This seems kind of philosophically similar to FTFY, https://github.com/LuminosoInsight/python-ftfy https://github.com/LuminosoInsight/python-ftfy, except for CSV files instead of botched text strings.
- scrollaway 7y ago
  I knew of ftfy already but I would love some more examples of these kinds of data cleanup libraries; it's always super useful.
  - cosmie 7y ago
    Not in any particular order, but here is a list of really useful text butchering utilities, that I keep in the same grab-bag as ftfy: csvclean[1], unidecode[2], transliterate[3], charset-normalizer[4], cchardet[5], phonenumbers[6][7], iconv[8], enca/enconv[9]. [1] https://csvkit.readthedocs.io/en/1.0.3/scripts/csvclean.html https://csvkit.readthedocs.io/en/1.0.3/scripts/csvclean.html [2] https://pypi.org/project/Unidecode/ https://pypi.org/project/Unidecode/ [3] https://pypi.org/project/transliterate/ https://pypi.org/project/transliterate/ [4] https://pypi.org/project/charset-normalizer/ https://pypi.org/project/charset-normalizer/ [5] https://pypi.org/project/cchardet/ https://pypi.org/project/cchardet/ [6] https://pypi.org/project/phonenumbers/ https://pypi.org/project/phonenumbers/ [7] which is a python port of Google's libphonenumber: https://github.com/google/libphonenumber https://github.com/google/libphonenumber [8] https://linux.die.net/man/1/iconv https://linux.die.net/man/1/iconv [9] https://linux.die.net/man/1/enca https://linux.die.net/man/1/enca
  - joshvm 7y ago
    This software (Trifacta Wrangler) was demoed at NeurIPS this year and looked very impressive. https://www.trifacta.com/products/wrangler-editions https://www.trifacta.com/products/wrangler-editions There is also the non-maintaned version that the company spun out from: http://vis.stanford.edu/wrangler/ http://vis.stanford.edu/wrangler/
gregmac 7y ago
> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes interprets non-date values as dates, and when saving, keeps the mangled values, doesn't preserve quotes, and uses regional settings -- to the point in some locales, it becomes a "semi-colon separated file"! Even just opening and then saving a .csv (without editing) can totally mangle it to the point it's unusable. Sometimes Excel can't even open a file saved by Excel without mangling it even more. [1] https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180
- Waterluvian 7y ago
  That is hilarious, amazing, sad, and begging for a blog post of someone walking through the nightmare you described.
  - Maxious 7y ago
    One in five genetics papers contains errors thanks to Microsoft Excel https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...
    - jimnotgym 7y ago
      Blame the calculator...'misuse of Microsoft Excel'
    - bronzecarnage 7y ago
      Nature has the highest percentage of affected papers. Huh.
- Twirrim 7y ago
  At a previous company, someone decided to experiment with putting a comma in a text field submitted to an internal service that for some godforsaken reason spat out CSV files that a lot of other teams consumed. I forget the exact entry in the field but it was something like "I wonder, how does this handle commas". Turns out it didn't handle them well. It didn't escape them or anything. Out spits this line in the csv format that has more columns than expected and everything from the that field onwards was off by one. Most teams were lucky, their code barfed when presented with a line with too many fields. Some teams were unlucky and it didn't. How unlucky _they_ were was somewhat varied based on how much data validation they did. That caused a big headache for a lot of people trying to recover from the actions of the genius who thought doing that against the production endpoint vs the test endpoint. That incident pretty much solidified my reputation in the team as some kind of weird voodoo type of chaos monkey, because my shifts _always_ had a significantly higher sev2 rate caused by things failing that I'd not touched or had any influence over.
emmanueloga_ 7y ago
I learnt a while ago about a way to provide a schema for CSV files. If you publish CSV data, may be useful to provide a schema for it [1]. Sounds like CleverCSV could determine and generate a schema automatically given a CSV file. 1: https://github.com/theodi/csvlint.rb#json-table-schema-support https://github.com/theodi/csvlint.rb#json-table-schema-suppo...
deleted 7y ago
[deleted]
th0ma5 7y ago
The problem mostly with CSV beyond the lack of standards, character encodings, and lack of compression, inability to efficiently write or read, and problems handling multiline freeform text, is that ultimately the structure is inband with the data, which is what makes all problems probable.
- closed 7y ago
  > The problem mostly with CSV... is that ultimately the structure is inband with the data I wonder if for 90% of users this seems like a critical UI feature!
fake-name 7y ago
The only issue I've ever had with python's CSV module is that it's slow. Is this any faster?
- gjjvdburg 7y ago
  Hi, I'm the author of CleverCSV. Although the package was initially written to mainly improve the accuracy of automated dialect detection, we ended up changing some aspects of the C parser as well (most notably increasing the look-ahead to two characters). This has made the parsing somewhat faster than the builtin csv package (around 10-20%).
manor 7y ago
TSV --> Problem Solved! Has anyone ever stumbled upon a cell entry that needed to contain an actual tab character? No!!! The tab character's entire raison-d'etre is to separate fields, whereas the comma separates words. Technically the "C" in CSV denotes "Character" but with TSV the intent is 100% clear. The MIME type is explicit: "Note that fields that contain tabs are not allowable in this encoding." This is a vanishingly small price to pay for a parser a simple as split("\t")... [1] http://www.iana.org/assignments/media-types/text/tab-separated-values http://www.iana.org/assignments/media-types/text/tab-separat...
- dmd 7y ago
  What problem is solved, exactly? How do you parse the messy CSV file you've been handed, in order to convert it to TSV?
  - manor 7y ago
    My point is simply that TSVs are just as straightforward as CSVs but suffer none of the issues. So why are we not making TSVs to begin with?
    - dmd 7y ago
      Nobody (or very few people) processing CSVs is doing it because they like them. You do it because that's how someone else created the data - someone you don't have any control over, like a government agency, financial institution, piece of obscure software, whatever.

154 points by reedwolf 7y ago

r0f1 7y ago

How does this compare to Pandas?

MapleWalnut 7y ago
This project appears to have a dependency on pandas. https://github.com/alan-turing-institute/CleverCSV/blob/master/setup.py https://github.com/alan-turing-institute/CleverCSV/blob/mast...
- donarb 7y ago
  That dependency is merely a wrapper to allow CleverCSV to load data directly into a Pandas data frame. It doesn't require pandas just for loading csv data.
donarb 7y ago
Pandas uses the Python csv module under the hood. Most of the code in the read_csv method is used to get the data from the csv file and load it into data frames.
- urschrei 7y ago
  Are you sure? Wes McKinney rewrote the Pandas CSV parser way back in late 2012 because the batteries-included version was slow and memory-hungry. Did Python upstream his version later on, or replace it with something faster (I'd be surprised if they did either, but I'd be curious to know)
  - evanpw 7y ago
    It's configurable. The default parser is written in C, but it's possible to use a parser based on the csv module if you needs things like separators specified by a regex.

etaioinshrdlu 7y ago

This seems kind of philosophically similar to FTFY, https://github.com/LuminosoInsight/python-ftfy https://github.com/LuminosoInsight/python-ftfy, except for CSV files instead of botched text strings.

scrollaway 7y ago
I knew of ftfy already but I would love some more examples of these kinds of data cleanup libraries; it's always super useful.
- cosmie 7y ago
  Not in any particular order, but here is a list of really useful text butchering utilities, that I keep in the same grab-bag as ftfy: csvclean[1], unidecode[2], transliterate[3], charset-normalizer[4], cchardet[5], phonenumbers[6][7], iconv[8], enca/enconv[9]. [1] https://csvkit.readthedocs.io/en/1.0.3/scripts/csvclean.html https://csvkit.readthedocs.io/en/1.0.3/scripts/csvclean.html [2] https://pypi.org/project/Unidecode/ https://pypi.org/project/Unidecode/ [3] https://pypi.org/project/transliterate/ https://pypi.org/project/transliterate/ [4] https://pypi.org/project/charset-normalizer/ https://pypi.org/project/charset-normalizer/ [5] https://pypi.org/project/cchardet/ https://pypi.org/project/cchardet/ [6] https://pypi.org/project/phonenumbers/ https://pypi.org/project/phonenumbers/ [7] which is a python port of Google's libphonenumber: https://github.com/google/libphonenumber https://github.com/google/libphonenumber [8] https://linux.die.net/man/1/iconv https://linux.die.net/man/1/iconv [9] https://linux.die.net/man/1/enca https://linux.die.net/man/1/enca
- joshvm 7y ago
  This software (Trifacta Wrangler) was demoed at NeurIPS this year and looked very impressive. https://www.trifacta.com/products/wrangler-editions https://www.trifacta.com/products/wrangler-editions There is also the non-maintaned version that the company spun out from: http://vis.stanford.edu/wrangler/ http://vis.stanford.edu/wrangler/

gregmac 7y ago

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes interprets non-date values as dates, and when saving, keeps the mangled values, doesn't preserve quotes, and uses regional settings -- to the point in some locales, it becomes a "semi-colon separated file"! Even just opening and then saving a .csv (without editing) can totally mangle it to the point it's unusable. Sometimes Excel can't even open a file saved by Excel without mangling it even more. [1] https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180

Waterluvian 7y ago
That is hilarious, amazing, sad, and begging for a blog post of someone walking through the nightmare you described.
- Maxious 7y ago
  One in five genetics papers contains errors thanks to Microsoft Excel https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...
  - jimnotgym 7y ago
    Blame the calculator...'misuse of Microsoft Excel'
  - bronzecarnage 7y ago
    Nature has the highest percentage of affected papers. Huh.
Twirrim 7y ago
At a previous company, someone decided to experiment with putting a comma in a text field submitted to an internal service that for some godforsaken reason spat out CSV files that a lot of other teams consumed. I forget the exact entry in the field but it was something like "I wonder, how does this handle commas". Turns out it didn't handle them well. It didn't escape them or anything. Out spits this line in the csv format that has more columns than expected and everything from the that field onwards was off by one. Most teams were lucky, their code barfed when presented with a line with too many fields. Some teams were unlucky and it didn't. How unlucky _they_ were was somewhat varied based on how much data validation they did. That caused a big headache for a lot of people trying to recover from the actions of the genius who thought doing that against the production endpoint vs the test endpoint. That incident pretty much solidified my reputation in the team as some kind of weird voodoo type of chaos monkey, because my shifts _always_ had a significantly higher sev2 rate caused by things failing that I'd not touched or had any influence over.

emmanueloga_ 7y ago

I learnt a while ago about a way to provide a schema for CSV files. If you publish CSV data, may be useful to provide a schema for it [1]. Sounds like CleverCSV could determine and generate a schema automatically given a CSV file. 1: https://github.com/theodi/csvlint.rb#json-table-schema-support https://github.com/theodi/csvlint.rb#json-table-schema-suppo...

deleted 7y ago

[deleted]

th0ma5 7y ago

The problem mostly with CSV beyond the lack of standards, character encodings, and lack of compression, inability to efficiently write or read, and problems handling multiline freeform text, is that ultimately the structure is inband with the data, which is what makes all problems probable.

closed 7y ago
> The problem mostly with CSV... is that ultimately the structure is inband with the data I wonder if for 90% of users this seems like a critical UI feature!

fake-name 7y ago

The only issue I've ever had with python's CSV module is that it's slow. Is this any faster?

gjjvdburg 7y ago
Hi, I'm the author of CleverCSV. Although the package was initially written to mainly improve the accuracy of automated dialect detection, we ended up changing some aspects of the C parser as well (most notably increasing the look-ahead to two characters). This has made the parsing somewhat faster than the builtin csv package (around 10-20%).

manor 7y ago

TSV --> Problem Solved! Has anyone ever stumbled upon a cell entry that needed to contain an actual tab character? No!!! The tab character's entire raison-d'etre is to separate fields, whereas the comma separates words. Technically the "C" in CSV denotes "Character" but with TSV the intent is 100% clear. The MIME type is explicit: "Note that fields that contain tabs are not allowable in this encoding." This is a vanishingly small price to pay for a parser a simple as split("\t")... [1] http://www.iana.org/assignments/media-types/text/tab-separated-values http://www.iana.org/assignments/media-types/text/tab-separat...

dmd 7y ago
What problem is solved, exactly? How do you parse the messy CSV file you've been handed, in order to convert it to TSV?
- manor 7y ago
  My point is simply that TSVs are just as straightforward as CSVs but suffer none of the issues. So why are we not making TSVs to begin with?
  - dmd 7y ago
    Nobody (or very few people) processing CSVs is doing it because they like them. You do it because that's how someone else created the data - someone you don't have any control over, like a government agency, financial institution, piece of obscure software, whatever.