Repository navigation
Automatic detection of dec=',' in Europe #2431
Description
I'm not sure what base and readr do in this regard, but currently in fread, dec=='.' by default and needs manually setting to ',' in Europe for numerics with comma as the decimal separator. It could instead be detected automatically like sep already is. Please +1 this issue if you'd like this.
Further, dec could be automatically detected per-column for files where some numeric columns use ',' and other columns use '.'. But does anyone need that?
Activity
GegznaV commentedon Oct 20, 2017
It would be enough, if
decwould be chosen correctly for the whole file.clarkdk commentedon Oct 22, 2017
+1 for auto-
dec.decby column world not be needed. I don’t think a mixeddeccsv could be written or read by Excel.Boyoron commentedon Nov 23, 2018
pls.
s-fleck commentedon Nov 11, 2019
fread()also sometimes ignores a manual setdec = ,. This happens randomly and quite infrequently and is therefore hard to reproduce. Restarting the R session fixes this issue. I've been having this problem for years, but only occasioannly so I did not report it before.jangorecki commentedon Nov 11, 2019
It sounds like a different issue than the one here
MichaelChirico commentedon May 22, 2020
Any sample files for this issue?
jangorecki commentedon May 22, 2020
MichaelChirico commentedon May 22, 2020
A real-world example would be better 😄
I saw a 🇫🇷 government website using
sep=';'as well, is that common in such files? Orsep='\t'maybe?jangorecki commentedon May 22, 2020
AFAIR this is how excel produces csv files in France and Poland.
MichaelChirico commentedon May 22, 2020
Here are some nightmarishly bad ones 😂
https://datos.gob.es/en/catalogo/ea0010587-porcentaje-de-gastos-en-i-d-respecto-al-pib-a-precios-de-mercado-por-comunidades-autonomas-serie-2000-2017-estadistica-sobre-actividades-en-i-d-en-el-sector-empresas-identificador-api-t14-p057-a2017-l0-02007-px
MichaelChirico commentedon May 24, 2020
@cderv @dmpe @thohan88 @GabijaSakalyte @Boyoron @IndreSakalauskaite @Amygdalae @AndriusJasinevicius @dvaitkus @Katazyna-Stankevic @labutytegreta @rasainsodaite @ievajuozapaityte @gertrudam @RPrakapaite @Grazvile @bugampo @raugulis @Ignnn @iurbon @EvitaJ @rutele13 @jstonkus @pociuteagne @Kaamile @LinaAnu @zyginta @evelina11101 @silvimi @Auguste11 @1075353 @Andrealek @esadausk @vaiiva @supermenas @ramintares @viktorija-romovaite @egle-lele @RokasStat @ema-malinauskaite @1611003 @zyle1 @tokotrienoliai @DanasKl @danielius-mockus @Gabriele-gif @domasrupkus @GerdaSkin @emyliuxe @GegznaV @clarkdk @s-fleck
Sorry for the wide ping. I have a PR addressing this issue in #4482 -- it would be great if anyone could provide some "real world" sample CSVs rather than testing on my toy examples. Thanks in advance!
GegznaV commentedon May 25, 2020
@MichaelChirico, here are some examples of data: data.zip. Inside the ZIP:
kojos.csv– data with various measurements of leg parts. Three columns with European numbers.16.0001.trt– spectroscopic data created by the software of a spectrometer. The data of interest start at line 9. Two semicolon-separated columns with European numbers.ezerai– two tab-separated columns with European numbers and UTF-8 encoding. Data from Wikipedia. (I do not expectfread()to read this datasetezeraicorrectly with the default settings).GegznaV commentedon May 25, 2020
Wouldn't it be more logical to automatically choose
\ttab assepwhen it is present instead of some other character? (See the example datasetezerai)jangorecki commentedon May 25, 2020
AFAIR excel uses
sep=";"when writing to csv (in those countries wheredec=",").MichaelChirico commentedon May 25, 2020
@GegznaV
freadmay choose\t-- there is some logic to determine whatfread"thinks" is the correct separator among,|;\tandMichaelChirico commentedon May 25, 2020
Thanks a bunch for the data sets Vilmantas.
fread('16.0001.trt')fails because of the extraneous info in the first 6 lines (auto-skiplogic not up to the task);fread('16.00001.trt', skip=7L)andfread('16.00001.trt', skip=8L)both work automatically (both get the column names wrong, which are on line 7 but have a subheader column on line 8)fread('kojos.csv')works greatfread('ezerai')is not working "automagically". The issue is, both,and\tasseplead to 3 columns (which matches the header), and the logic for selectingsepis agnostic to column types -- there is a priority order, and,comes first. I filed fread sep='auto' could try using detect_types in the event of a tie #4487 -- I'm hopefulfread's logic could actually detectsep='\t'on its own.fread('ezerai', sep='\t')works.jangorecki commentedon May 25, 2020
Remember not to try to handle every possible input. For example
Sounds that fread would need to skip first 6 lines, then read seventh, skip 8th, and read the rest. Hnadling that is doable but it impose maintainance overhead, can introduce new bugs, etc. It might be better to provide a more general interface where skip can be a vector, so user need to understand what is wrong with their files, and then just
skip=c(1:6,8). I recall someone already asked for possibility to read particular lines of file.MichaelChirico commentedon May 25, 2020
Yea I don't think there's an automatic way on this one that's not a fragile house of cards to support.
skip=vectorwould be awesome anyway.GegznaV commentedon May 25, 2020
However, if we return to the original issue
Automatic detection of dec=',' in Europe, it seems that PR #4482 does what it is expected.When could one expect these changes to be on CRAN?
jangorecki commentedon May 25, 2020
@GegznaV probably not very soon. Note that we provide windows binaries so Rtools/compilation is not needed. If you are on R 3.6 you can just
If you are on other version you can try
Note that soon those 3.6 will move to 4.0.
GegznaV commentedon May 25, 2020
Ok, can I expect the new version of data.table on CRAN somewhere around mid-August? (Before the new school year in September). Or your dates are even further?
jangorecki commentedon May 25, 2020
We don't have any fixed release dates. New version on CRAN might eventually be just a patch release not having new features like this.