-1
$\begingroup$

A testing file with the specified format can be downloaded from here

Let's read a few lines from this file

(recLines = ReadList[csvFileName, Record, 3]) // TableForm

SalesOrderID,ProductID,ProductName,OrderDate,ShipDate,Revenue
51217,779,Soft Cushion,5/7/2014,11/7/2014,"$2,400"
51217,788,Soft Cushion,5/7/2014,11/7/2014,"$2,400"

ls[[2]] // FullForm
"51217,779,Soft Cushion,5/7/2014,11/7/2014,\"$2,400\""

Had I used the Import I would have got

recData = Import[csvFileName];
recDate[[1;;3]]//TableForm

SalesOrderID    ProductID   ProductName OrderDate   ShipDate    Revenue
51217   779 Soft Cushion    5/7/2014    11/7/2014   2400
51217   788 Soft Cushion    5/7/2014    11/7/2014   2400

Head /@ csvData[[3]]
{Integer, Integer, String, String, String, Integer}

This is exactly the output I would like to get, but instead of using Import I want to use ReadList.

I have read various posts related to ReadList and this is the closest I have found to an answer that will use ReadList

rl = ReadList[csvFileName, Word, WordSeparators->{","}, RecordSeparators->{"\n"}, RecordLists->True]

rl[[1 ;; 3]] // TableForm

 SalesOrderID   ProductID   ProductName OrderDate   ShipDate    Revenue 
  51217 779     Soft Cushion    5/7/2014        11/7/2014       "$2	400"
  51217	788	Soft Cushion	5/7/2014   	11/7/2014	"$2      400"

rl[[3]] // FullForm

List["51217","788","Soft Cushion","5/7/2014","11/7/2014","\"$2","400\""]

Head /@ rl[[3]]
{String, String, String, String, String, String, String}

Could you please continue this to a full answer or perhaps share a better solution with ReadList ? For example, is it possible to read and transform at the same time with ReadList, by specifying the header, i.e. types of the record items, and get exactly the same result as that of Import ?

I also have this challenge question for the experts of the language:

ReadList is a memory efficient and fast method of reading and parsing CSV files. But it cannot detect automatically the data types of the record items, something that Import does AUTOMAGICALLY. Wouldn't be possible to implement somehow a ReadList that can also detect the header format?

If not, suppose we do know what is the format of the records, and suppose it does not change. There are all these cryptic types and options of ReadList to assist you in getting the parsing of data items right. Can somebody explain me in the file I specified how I can use these types and options to achieve reading lists same way like Import function ?

$\endgroup$
  • 1
    $\begingroup$ Why exactly do you want to use ReadList instead of Import ? $\endgroup$ – SquareOne Feb 2 '16 at 12:29
  • $\begingroup$ ReadList is much faster for big CSV files than Import. I am using this fileas an exercise to learn more on how to use ReadList. $\endgroup$ – Athanassios Feb 2 '16 at 12:39
  • $\begingroup$ In order to use ReadList with your CSV file in the most efficient way, the entries in your file should be formatted more friendly for Mathematica. Do you have a way to modify your csv file to add some formatting ? $\endgroup$ – SquareOne Feb 2 '16 at 12:58
  • $\begingroup$ Thank you, yes ReadList, I have just edited my comments $\endgroup$ – Athanassios Feb 2 '16 at 13:06
  • $\begingroup$ @SquareOne I can modify my CSV file, but I thought ReadLine could detect the format of each record and transform the fields, if you specify the correct types, something that Import does automagically ;-) $\endgroup$ – Athanassios Feb 2 '16 at 13:20
2
$\begingroup$

As long as you are using ReadList to read in the entire file, and then acting on each line, you may as well just feed the result of ReadLine into ImportString.

test = ImportString[
    StringJoin[
     Riffle[ReadList["shoes_revenue.csv", String], "\n"]
     ],
    "CSV"]; // AbsoluteTiming
(*{2.7918, Null}*)

which is marginally faster than this answer

conv = {ToExpression@#[[1]], 
    ToExpression@#[[2]], #[[3]], #[[4]], #[[5]], 
    ToExpression@StringTake[StringJoin@#[[6 ;;]], {3, -2}]} &;
(wrdlst = 
   conv /@ (ReadList["shoes_revenue.csv", Word, 
       WordSeparators -> {","}, RecordSeparators -> {"\n"}, 
       RecordLists -> True])[[2 ;;]];
  PrependTo[wrdlst, 
   Flatten@StringSplit[ReadList["shoes_revenue.csv", Record, 1], ","]];
  ) // AbsoluteTiming
(* {3.00567, Null} *)
$\endgroup$
  • $\begingroup$ This is so far the fastest method with ReadList but I checked the timing on Import and I found that it reads the file faster than your answer. Memory allocation is also about the same with that of Import. $\endgroup$ – Athanassios Feb 3 '16 at 12:07
  • 1
    $\begingroup$ That is definitely true. I think that you can really save some overhead by using Read or ReadList when the data is formatted very simply. The fact that you have to do postprocessing on each line is going to remove any benefit from these stream operations over Import. If you could preprocess these files to go so far as to change the "$2,400" to 2400 then you could benefit from ReadList. $\endgroup$ – Jason B. Feb 3 '16 at 12:58
  • 1
    $\begingroup$ I have found that simply Import["shoes_revenue.csv"]alone is 2 times faster than your best solution, and the result is the same. There is something I don't understand at all $\endgroup$ – andre314 Feb 3 '16 at 17:51
  • $\begingroup$ I think Import and ImportString functions have been optimized to automatically detect the format of data rows in a file. Something that you have to do in a suboptimal way in ReadList. That is also why JasonB solution is also the fastest here. But generally speaking for in-memory efficient and fast processing you have to switch on a different kind of data structure, data model, I.e. single instance, associative. See also this question and my comment there $\endgroup$ – Athanassios Feb 3 '16 at 21:29
  • 1
    $\begingroup$ @andre, In this case, ImportString is essentially writing the string to a temporary file and then calling Import on it, so it's not very efficient. You can see the temporary streams used with the following: DeleteDuplicates[Flatten[Trace[ImportString["a,b,c\n1,2,3\n4,5,6\n", "CSV"], Except[List | SameQ][___, (InputStream | OutputStream)[_String, ___], ___]]]]. $\endgroup$ – rhennigan Feb 6 '16 at 18:52
3
$\begingroup$

Here are some approaches with slightly modified versions of your input CSV file. If you are looking for an efficient way to read the data, the input data should be as friendly as possible to MMA.

However in the last example, I will give one possible way to process your original data.

1. ReadList with formats

With this approach, the most problematic in your data is the Revenue field where values contain a comma ($2,400). So let's say you don't have this problem (I directly changed this to a numeric value but I could have left it as a string with just the comma removed)

Let's say your file is :

csv = "51217,779,Soft Cushion,5/7/2014,11/7/2014,2400\n51217,788,Soft Cushion,5/7/2014,11/7/2014,3200";

Print@csv

Blockquote

str = StringToStream@csv;
rl = ReadList[str, 
{Number, Character, Number, Character, Word, Word, Word, Character, Number},
RecordSeparators -> {"\n"}, WordSeparators -> {","}, RecordLists -> False]
Close@str;

{{51217, ",", 779, ",", "Soft Cushion", "5/7/2014", "11/7/2014", ",", 2400},

{51217, ",", 788, ",", "Soft Cushion", "5/7/2014", "11/7/2014", ",", 3200}}

and finally you extract the parts you want (you get rid of the commas) :

rl[[All, {1, 3, 5, 6, 7, 9}]]

{{51217, 779, "Soft Cushion", "5/7/2014", "11/7/2014", 2400},

{51217, 788, "Soft Cushion", "5/7/2014", "11/7/2014", 3200}}

and you can check that :

Head /@ rl[[All, {1, 3, 5, 6, 7, 9}]][[2]]

{Integer, Integer, String, String, String, Integer}

2. ReadList combined with ToExpression

(I learned this powerful method from @Jakob Akkerboom in this post)

This approach will fit data like these (here it is better not to have commas inside the fields, so for the sake of simplicity i removed these):

csv = "51217,779,\"Soft Cushion\",\"5/7/2014\",\"11/7/2014\",2400\n\
51217,788,\"Soft Cushion\",\"5/7/2014\",\"11/7/2014\",3200";

Print@csv

Blockquote

then just

str = StringToStream@csv;
rl = ReadList[str, String, RecordSeparators -> {"\n"}] // 
Map[ToExpression["{" ~~ # ~~ "}"] &, #] &
Close@str;

{{51217, 779, "Soft Cushion", "5/7/2014", "11/7/2014", 2400},

{51217,788, "Soft Cushion", "5/7/2014", "11/7/2014", 3200}}

and you can verify:

Head /@ rl[[1]]

{Integer, Integer, String, String, String, Integer}

3.ReadList with your original dataset

The idea is to import each line of the CSV file as string, then to do the processing to suit your needs. If you deal with very big files, this approach could be adapted to read the file by chunks (using low level Read function instead of ReadList).

r1 = ReadList[csvFileName, String];
r2 = Map[StringReplace[#, 
       "\"$" ~~ pat : (DigitCharacter .. ~~ "," ~~ DigitCharacter ..) ~~"\"" :> 
StringDelete[pat, ","]] &, r1] // Map[StringSplit[#, ","] &, #] &;
r2[[2 ;;, {1, 2, 6}]] = r2[[2 ;;, {1, 2, 6}]] // ToExpression;

r2 // TableForm

Blockquote

and

Head /@ r2[[2]]

{Integer, Integer, String, String, String, Integer}

4. Lazy lists

But if you have to deal with very big files, maybe you should convert your data to @Leonid Shifrin's lazy lists.

$\endgroup$
  • $\begingroup$ great answer @SquareOne regarding to 3. I though it would be easier, faster perhaps to use ReadLine, is that right ? $\endgroup$ – Athanassios Feb 2 '16 at 18:49
  • $\begingroup$ @Athanassios You mean ReadList, not ReadLine $\endgroup$ – andre314 Feb 2 '16 at 18:54
  • $\begingroup$ @Athanassios Yes ReadList is fast, but if you need also to filter/parse the data after reading the file (like in 3.) then the whole computation is longer, but probably less than with Import. $\endgroup$ – SquareOne Feb 2 '16 at 20:45
  • $\begingroup$ This time @andre I really meant to use ReadLine, I.e. parse string lines, line by line. Wouldn't be faster and more memory efficient this way ? Please also see the end of my question about comparing it with the import function. This part has NOT been answered. $\endgroup$ – Athanassios Feb 2 '16 at 21:11
  • 1
    $\begingroup$ @Athanassios For example you might find interesting these posts: 1, 2, 3, 4, ... $\endgroup$ – SquareOne Feb 2 '16 at 22:08
1
$\begingroup$

I am not sure what exactly you are looking for, but you could apply ToExpression to those columns that should be Integers instead of Strings:

 Head@ToExpression["1234"]
 (* Integer *)

Similarly, you can combine the last two columns, drop the Dollar symbol and convert to integer:

ToExpression@StringDrop["$2" <> "843", 1]
(*2843 *)
$\endgroup$
  • $\begingroup$ I am looking for a better answer ;-) $\endgroup$ – Athanassios Feb 2 '16 at 12:40
0
$\begingroup$

ReadList with Word

It seems to me that the best you can do with ReadList in this case is to parse each line of the file and get the field values back as strings. For the monetary comma value, you get a list with two string values and this is the major obstacle to tackle.

Note that this ReadList use has already been added to my question above, but nobody continued this for a full answer

We know the format of the fields, two integers, followed by a string, followed by two dates, followed by the monetary value.

conv = {
        ToExpression@#[[1]], ToExpression@#[[2]],
        #[[3]], 
        #[[4]], #[[5]],
        ToExpression@StringTake[StringJoin@#[[6 ;;]], {3, -2}]} &;

Map the Pure Function above to the output of ReadList

   wrdlst = conv /@ (ReadList[csvFileName, Word, WordSeparators -> {","}, RecordSeparators -> {"\n"}, RecordLists -> True])[[2 ;;]];

   PrependTo[wrdlst, Flatten@StringSplit[ReadList[csvFileName, Record, 1], ","]];

   Head /@ wrdlst[[3]]
   {Integer, Integer, String, String, String, Integer}

In my machine it is about 1.7sec faster than the answer provided by @SquareOne. It is also close to Jason's answer

$\endgroup$
  • $\begingroup$ This answer is also very close to the "ReadList combined with ToExpression" demonstration provided by @SquareOne $\endgroup$ – Athanassios Feb 3 '16 at 10:26
  • $\begingroup$ This doesn't work for me on the file you provided: i.stack.imgur.com/P6L13.png $\endgroup$ – Jason B. Feb 3 '16 at 10:38
  • $\begingroup$ Yes, I downloaded the Google spreadsheet and it does not work $\endgroup$ – Jason B. Feb 3 '16 at 10:59
  • $\begingroup$ Jason please try now with this link $\endgroup$ – Athanassios Feb 3 '16 at 11:02
  • $\begingroup$ That fixed it, just asking google docs to download it from the editing window added an extra "\r" to the end of each line. I think you would be better served by using StringReplace[StringJoin@#[[6 ;;]], "$" -> ""] for the last conversion instead of StringTake, as it is more versatile, i.e. it would work on values over $10,000. $\endgroup$ – Jason B. Feb 3 '16 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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