Hot answers tagged

20

Analysis Reading the data is not the issue. I can read the data as strings quite fast. str = OpenRead["train-7000.csv"]; (data = ReadList[str, String]); // AbsoluteTiming//First (* 0.453251 *) Memory use is modest too: data // ByteCount (* 48550344 *) It's only slightly larger than the file on disk: FileByteCount["train-7000.csv"] (* 46483707 *) ...


17

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] := ...


16

Here is a function which may help: Clear[readRows]; readRows[stream_, n_] := With[{str = ReadList[stream, "String", n]}, ImportString[StringJoin[Riffle[str, "\n"]], "Table"] /; str =!= {}]; readRows[__] := $Failed; I tested on your file and it works all right (it may make sense to read rows in batches, this is much faster): n=0; str = OpenRead["...


15

This is a complementary answer, which shows mostly how to reduce memory use rather than speed (although later I might update it to address the speed issue as well). This answer is based on an undocumented functionality, so the usual warning applies: there is no guarantee that the method suggested below will work in future versions. Using undocumented ...


14

A while ago I had to do some work on large amounts of timestamped magnetometer data. Although my work only recently reached the level of 500MB, you might be able to use some of these techniques on your files. Here's a sample of my CSV file with timestamp,x,y,z values 2015-06-03T22:21:30.827Z,10.5767,2.2233,-51.9933 2015-06-03T22:21:30.313Z,10.5833,2.2233,-...


13

If your data is Imported say in data then you can use data[[All,{1,2}]] to use the first column as x and the second as y value. Plotting all three data sets would be ListPlot[{data[[All, {1, 2}]], data[[All, {1, 3}]], data[[All, {1, 4}]]}] If you want to print against row index, like in yopur figure you can use ListPlot[{data[[All, 2]], data[[All, 3]], ...


12

ReadList Assuming that you want to read all fields as strings, you could set up ReadList like this: ReadList["sample.csv", Word, WordSeparators -> {",", " "}, RecordSeparators -> {"\n"}, RecordLists -> True ] Import ReadList is usually quite a bit faster, but if speed is not paramount you may find Import more convenient. It attempts to ...


12

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/...


11

I worked on Interpreter. As far as the implentation is now, the DelimitedSequence parser does not support quoting, so what you want can't be done. We'll try to add it in a future version.


11

Here is one option: ds = Dataset[<|"A" -> 3757, "B" -> 426, "C" -> 193, "D" -> 1|>] Now, to create the CSV you can use: Export["text.csv", List@@@Normal@Normal@ds]


10

Update Inspired by Andy Ross header = First @ iris; header = StringReplace[header,"."->""](Because Dot is protected in Mathematica) data = Rest @ iris; assign[name_, value_] := Evaluate[ToExpression[name]] = value; Thread[ assign[header, Transpose@data] ] ==================================================================== If you ...


10

Using SemanticImport["testdata.csv"] I get on the exported data you provided which is the dataset you seek. But SemanticImport has been reported to have a few bugs, maybe that's why you can't get it to work. In the meantime, and in between time, you can use the much cleaner approach to obtain your dataset after using Import on your file: data = {{"...


8

This is an ideal use case for SemanticImport, but unfortunately it has issues getting the commas right in version 10.0. Luckily, version 10.0.1 has already fixed this bug:


7

Export["test.csv", Join[{"sep = ,"}, RandomInteger[{-5, 5}, {5, 3}]], "TextDelimiters"->None] (With the obvious issue if you also have strings that should be quoted in the data )


7

(*Testing ... First we generate some points*) points = Flatten[Table[{x, y, PDF[BinormalDistribution[{0, 0}, {1, 2}, .5], {x, y}]}, {x, -3, 3, .1}, {y, -3, 3, .1}], 1]; (* Now we export it as a csv *) Export["c:\\points.csv", points]; (* The file looks like this: -3.,-3.,0.0010207851317789406 -3.,-2.9,0.0010190852401957891 -3.,-2.8,0....


6

We can use the "Numeric" option to have Import interpret all data as strings: Import["data.csv", "CSV", "Numeric" -> False] The resulting table will only contain strings. If desired, we could post-process the imported strings by applying a different parsing function to each input column (ignoring the header line for simplicity): Inner[ #2@#& , ...


6

When I use Import to import a test file consisting of the following 4 lines 10/03/13 19:04, 4, N 28/11/12 03:41, 6.2, Y # Records added 01/04/12 10/03/11 19:04, 4, N using "CSV" as the specified format Import["C:\\Users\\Sjoerd\\Desktop\\test.dat", "CSV"] I get {{"10/03/13 19:04", 4, " N"}, {"28/11/12 03:41", 6.2, " Y"}, {"# Records added 01/04/12"}...


6

ListPlot requires a list of {x,y} pairs for each data set. What Markus is showing is how to get that in the right format. For styling, read through the options for PlotStyle and PlotMarkers. For legends, you have several options: Try to use the PlotLegend Package (bleh) Roll your own with the help of MMA.SE Most helpful: Creating legends for plots with ...


6

I think that the existing comma-separated-values, fields, containing "new lines" are the offending elements in your file. If you do Length /@ Import["data.csv"] {64, 64, 64, 64, 64, 64, 1, 1, 25, 64, 64, 8, 1, 1, 1, 1, 1, 25, 7, 1, 1, 1, 1, 25, 1, 2, 2, 25, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, 64, ...


6

Here's one way to resolve the CRLF problem. data = Import[fname, "Text"]; StringReplacePart[data, "\[Wolf]", StringPosition[data, "\n" ~~ DigitCharacter]]; StringReplace[%, "\n" -> ""]; StringReplace[%, "\[Wolf]" -> "\n"]; data2 = ImportString[%, "CSV"]; First, I import the file as a single text string. I notice in the file that all of the '...


6

Assumptions No fields contain a \n or \r (true for first 7000 rows) everything is encoded in ASCII (true for first 7000 rows) Observations Some of your fields contain a , (not a problem) Some of your fields contain a \ (big problem) Your data contains the following field (in FullForm, i.e. the \ is not escaped) "PSYCHOLOGIST\CLINICAL PSYCHOLOGIST"...


5

Summary This is generalized piece of code for reading FAST rows terminated with a newline from a CSV or TSV text stream with UTF8 encoding. You may specify options for the filetype, the starting and ending line number, as well as a string to search for a position in the file (find). By default the result includes the header which can be omitted. The ...


5

If the OutputForm of your expressions is acceptable for export you might use WriteString as follows: dat = RandomReal[{-9, 9}, {261000, 30}]; str = OpenWrite["speedtest.csv"]; WriteString[str, Row[#, ","], "\n"] & ~Scan~ dat // AbsoluteTiming Close[str]; {15.9369116, Null} The file looks like: 1.18859,3.64721,2.2051,-3.76912,-8.36245,1.8706,-0....


5

This function converts serial dates only since March 1st, 1990, due to the Excel bug described here: Converting Excel serial dates with NETLink convertDate[serialdate_] := If[serialdate < 61, "N/A", DatePlus[{1901, 1, 0}, serialdate - 366]] convertDate[40969.00069] {2012, 3, 1, 0, 0, 59.616} DateString@Round@% Thu 1 Mar 2012 00:01:00


5

Previous Answers Below Here is a more general approach but somewhat slower on my machine: readSkipCSVGen[file_String?FileExistsQ, first_Integer, step_Integer, cols_: {3, 4, 5, 8, 12}] := Module[{str = OpenRead[file], temp, data, ta}, Skip[str, String, first - 1]; data = Reap[ While[ Or[ta =!= EndOfFile, Not@ValueQ[ta]], Sow[temp = ...


5

The simplest thing to try would be to just import one sheet at a time. Do you see crashes even when only importing one sheet at a time? If that still crashes I see several things you could try and am not sure which would work best for you: Use .NET/Link and COM You could use .NET/Link to access excel via COM if you are on Windows to do what you want. But ...


5

Riffle Riffle[Range[10], CharacterRange["a", "j"]] {1, "a", 2, "b", 3, "c", 4, "d", 5, "e", 6, "f", 7, "g", 8, "h", 9, "i", 10, "j"} Transpose Flatten Flatten@Transpose@{Range[10], CharacterRange["a", "j"]} {1, "a", 2, "b", 3, "c", 4, "d", 5, "e", 6, "f", 7, "g", 8, "h", 9, "i", 10, "j"} Flatten (Thanks @J.M.) Flatten[{Range[10], ...


4

If I try to create a CSV file without the first line indicating the seperator, the resulting file is readable by Excel. Export["test2.csv",RandomInteger[{-5,5},{5,3}]] But if you still want the first line, I would do something like this Export["test3.csv", "sep = ,\n" <> ExportString[RandomInteger[{-5, 5}, {5, 3}], "CSV"] , "Text"]


4

Because your expression v is a vector, it's being interpreted as one line per number. The following gives your desired result. Export["v.csv", {v}, "csv"]


4

This is a job for Transpose. table = Take[Import["~/myfile.csv"]]; {x, y, z, time, bool} = Transpose[table]; (* do stuff *) output = Transpose[{x, y, z, time, bool}]; Export["newfile.csv", output, "CSV"]



Only top voted, non community-wiki answers of a minimum length are eligible