1

My data.frame uses the scientific notation, when parsing files like 3.007530e+07.

I definitely like to use it in R, however, for this analysis I have to transfer my data to csv and open it in excel(German Version), which cannot handle this notation.

My df looks sth like that:

df <- c(6.402000e+05,9.312903e+05,1.007800e+06,1.142000e+06,1.298500e+06,1.511700e+06,1.749000e+06,1.869357e+06)

I tried changing my global options such as options(scipen=999), which does not work, because then I have problems with my fread function.

Therefore, my question:

How to change the notation in a data.frame before, using write.csv()?

I appreciate your replies!

alko989
  • 7,688
  • 5
  • 39
  • 62
Carol.Kar
  • 4,581
  • 36
  • 131
  • 264
  • 1
    What kind of problem do you have with `fread`? For your sample data setting `scipen` works fine for me and as you want it to work. – alko989 Jan 30 '15 at 15:33
  • I can't reproduce this at all. `df <- c(6.402000e+05,9.312903e+05,1.007800e+06,1.142000e+06,1.298500e+06,1.511700e+06,1.749000e+06,1.869357e+06; write.csv(df, file = "tst.csv")` produces a file without scientific notation. – Ista Jan 30 '15 at 15:38
  • I get when I use `scipen` a 64bit integer coercion. This error is coming from the base64 package which fread uses. – Carol.Kar Jan 30 '15 at 15:39
  • 1
    why don't you just change your options when you want to write to csv, then change them back immediately, so that they don't interfere with `fread`? You could even write a little function to wrap this procedure. – Ben Bolker Jan 30 '15 at 15:45
  • Consider serializing properly rather than going via error-prone csv files. For R-to-R use cases, `saveRDS()` is excellent. – Dirk Eddelbuettel Jan 30 '15 at 15:46
  • @DirkEddelbuettel, the OP states (for better or worse) that they need to have Excel in their pipeline ("I have to transfer my data to csv and open it in excel") – Ben Bolker Jan 30 '15 at 21:39

1 Answers1

2

As an alternative to altering the R format (since you want to keep scientific notation in R), could you change how Excel imports your file?

For example, naming your csv file with a non-standard extension to trigger the manual importing process (import wizard), instead of automatically opening the file in the wrong format?

I tried a simple test with a csv formatted file of numbers in scientific notation, saved with a ".sci" filename. My version of Excel launched the wizard, then imported the file and handled the scientific notation correctly [MS Excel Starter 2010, English version].

Edit: I found the reference to using an unrecognized file extension to trigger Excel's import wizard: http://excelribbon.tips.net/T012201_Avoiding_Scientific_Notation_on_File_Imports.html

[The article suggests using .DAT, which I wouldn't use for an ASCII file, but I wanted to give credit where it's due for the idea.]

Lantana
  • 496
  • 3
  • 6