Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I am handling large numerical data in Mathematica. In smaller problems everything worked fine using Export and Import with the parameter csv and nothing more.

Now I am facing a much larger data volume and plain Export and Import is way too slow for CSV format.

What I want to do: First, exporting a numerical list of approximately $1400 \cdot 260$. Then I perform some calculations outside of mathematica and finally I import a csv file back using Import.

In this question I read how to improve the speed of Export with CSV.

I tried

Export["data.csv", 
  ExportString[Transpose[temp], "CSV", "FieldSeparators" -> ", "], 
  "Table"];

for a toy-example of dimension $9 \cdot 6$. This could be

temp = RandomReal[{0, 1000}, {9, 6}];

The problem is that I got additional blank lines in my CSV file. How can I avoid those blank lines? I did not have them using plain Export.

Second part of the question: can I use a similar approach to speed up Import for CSV files?

I work in English locale in Windows 7 and Mathematica 8. My data should be comma-separated.

The result of the above looks like this:

155.9418457227914,427.72566448956945,462.4370455183139,434.230107096781,377.73423736605037,457.7044624877774,229.5721937681028,453.6973831247924,827.5146478718962

656.9573702857699,975.1048399942904,716.715190156526,67.07781324817643,168.78248854317894,863.1953962590844,997.7580107302701,427.94798294100747,565.2955778916687

192.3648037459477,435.0418975785194,126.17228368369842,772.0737083559297,453.73573640921836,957.9178360741387,920.4158275934401,234.75353158374764,162.82606110943834

841.7132070637356,799.1268178998612,931.2448706410551,950.7753472229233,114.01596316796622,145.0771999411104,287.47149951303663,786.9008107323455,99.09420650484662

116.9916885502289,715.7594598282562,970.6252946068753,654.1742185278038,262.3778046629968,200.13980161337577,347.24862854841354,314.5612015073982,241.11046402342163

203.65015448763597,952.1236458849723,578.2673369638862,527.2990305555661,655.1228742370724,318.81372163827496,311.2738362265584,315.97629887850667,514.7676854642548

EDIT: A small example of the data that I want to import (2nd step) can be found here. The true problem size is here.

share|improve this question
    
Can you give a short sample of what your input and output files look like? –  RunnyKine Nov 4 '13 at 17:37
    
First I would like to takle the export. For this one could use the random number example provided. I would like to combine Export and ExportString in order to speed up the export. If it is correct for the toy example then it should be alright for the big data. Is this enough info for you? –  Richard Nov 4 '13 at 17:40
    
I actually was looking to use a different approach especially for the Import aspect since ReadList is much faster than Import. I was going to write something from scratch that will be much faster than anything Import and Export can achieve. So for me to see a sample file will be helpful. –  RunnyKine Nov 4 '13 at 17:45
1  
Why don't you have a look at this? –  Leonid Shifrin Nov 4 '13 at 17:57
1  
Are you required to use csv? Exchanging data in binary is typically much faster, though obviously your external program needs to be modified to read it. –  george2079 Nov 4 '13 at 18:46

2 Answers 2

up vote 8 down vote accepted

Here's a much faster, purely Mathematica way than using Import to import your data:

UPDATE

As Leonid mentioned the previous code doesn't exactly replicate Import. The truth is I was only trying to retrieve the numerical part. Here's an updated version that tries to replicate the output from Import.

readYourCSV2[file_String?FileExistsQ, n_Integer] := Module[{str = OpenRead[file], data}, 
  data = ReadList[str, Table[Record, {n}], RecordSeparators -> {",", "\n"}]; 
  Close[str]; 
  ReleaseHold[ToExpression[data, InputForm, Hold] /. {Plus[Times[x_, E | e], y_] :> x * 10 ^ y}] 
 ]

Here, n is the number of columns.

UPDATE 2

Now for the Export, here's a fast, again, purely Mathematica way to export in CSV format.

writeYourCSV[file_String, list_List?MatrixQ] := 
 With[{str = OpenWrite[file, PageWidth -> Infinity], len = Length[ list[[1]] ]},
      Scan[Write[str, Sequence @@ (Flatten[Table[{FortranForm[ #[[i]] ], OutputForm[","]}, 
              {i, len - 1}]]) ~ Join ~ { FortranForm[ #[[len]] ] }] &, list]; Close[str];
]

This takes less than 10 seconds to write your large data back to CSV format:

writeYourCSV["testcsv.csv", databig] // AbsoluteTiming

{9.921969, Null}

share|improve this answer
    
This does not give the same result as Import["~/Downloads/returns_out_small.csv"], even if we compare purely numerical part. –  Leonid Shifrin Nov 4 '13 at 18:49
    
But the large file contains many more columns (1419, to be precise). –  Leonid Shifrin Nov 4 '13 at 19:09
    
I did compare this to my method, calling your code as DeleteCases[readYourCSV["~/Downloads/returns_out.csv", 1419], {(Null | EndOfFile) ..}]. The strange thing was that your result was one row less (missed the very first one, it seems), and also that the results agreed only to 10^-3. –  Leonid Shifrin Nov 4 '13 at 20:11
    
@LeonidShifrin. Thanks for checking it. I think the reason is I forgot to add the \n RecordSeparator. I've updated my code to include this and also include the first row (heading) of the .CSV file –  RunnyKine Nov 4 '13 at 20:42
    
Yes, I also meant the numerics only. I did not mean the headers - I ignore them too. –  Leonid Shifrin Nov 4 '13 at 21:53

Here is a Java-based solution, pretty fast but valid only when all your columns are numerical (double) values.

First, grab and run the code for the Java reloader (The linked version should work on Windows and probably Linux, but was reported to have issues for OS X. So, Mac users may try this one instead: Import["https://gist.github.com/lshifr/7307845/raw/SimpleJavaReloader.m"] - not yet tested this version for other platforms). Then, compile this class:

JCompileLoad["public class DoubleParser{
   public static double[] parseDouble(String[] strdub){
      double[] res = new double[strdub.length];
      int i = 0;
      for(;i < strdub.length;i++){
         try{
            res[i]= Double.parseDouble(strdub[i]);
         } catch (NumberFormatException e){
            res[i] = 0;
         }
      }
      return res;
   }
}"] 

Then, here is the Mathematica counterpart:

ClearAll[importDoubleCSV];
Options[importDoubleCSV]={"Headers"->True};
importDoubleCSV[file_String?FileExistsQ, opts : OptionsPattern[]]:=
  With[{fn=If[TrueQ[OptionValue["Headers"]],Rest,Identity]},
    Transpose[
      DoubleParser`parseDouble/@
        Transpose[
          DeleteCases[                
            StringSplit[fn[StringSplit[FromCharacterCode[BinaryReadList[file]],"\n"]],","],
            {s_String/;StringMatchQ[s,Whitespace]}
          ]
        ]
    ]
  ]

For your small file, the result agrees with what you get by using Import, after you remove empty rows:

res1=Rest[DeleteCases[Import["~/Downloads/returns_out_small.csv"],{""..}]];
res2=importDoubleCSV["~/Downloads/returns_out_small.csv"];
res1==res2

(* True *)

Your large file gets processed on my machine in about 4 seconds:

(resLrg=importDoubleCSV["~/Downloads/returns_out.csv"])//Short//AbsoluteTiming

(* 
    {4.789668,
     {{0.000449449,0.000418204,<<1415>>,0.000064701,0.000045972},
        <<1417>>,{<<1>>}}
    }
*)

which doesn't look bad to me. I wasn't patient enough to wait until Import["~/Downloads/returns_out.csv"] finishes, so did not compare results in this case - but the reader is most welcome to do that (and the timings too).

An added advantage here is that we get the results packed:

Developer`PackedArrayQ @ resLrg

(* True *)

Note that Java parsing code adopts a convention to replace all non-parsable strings with zeros. It is possible to improve on this, by returning also the positions of non-parsable strings, separately. Note also that the UTF-8 encoding is implicitly assumed.

share|improve this answer
    
Wow! No we have Javareloader for Mac! Cool! –  Murta Nov 5 '13 at 2:17
    
I actually think that the speed achieved here is more or less (up to a factor of 2 or 3) as good as one gets. Further qualitative improvements can only be achieved by switching to a binary format to store the data - which was already suggested in comments. –  Leonid Shifrin Nov 5 '13 at 3:20
    
@Murta You bet :). This post was done using a Mac, and I am on a Mac right now. –  Leonid Shifrin Nov 5 '13 at 3:23
    
I tested the new version (using direct import from github link) in MMA 9 for Mac and in MMA 10 beta for windows. Worked wonderfully! Now I'm more motivated to study some Java in the comfort of mathematica environment. –  Murta Nov 8 '13 at 1:30
    
@Murta I also do have the syntax highlighter and some other tools for Java, will hopefully publish those some time soon. –  Leonid Shifrin Nov 8 '13 at 9:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.