Import vs ReadList CSV file with Currency Values

Question

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 ?

Why exactly do you want to use ReadList instead of Import ? — SquareOne, Feb 2 '16 at 12:29
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. — Athanassios, Feb 2 '16 at 12:39
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 ? — SquareOne, Feb 2 '16 at 12:58
@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 ;-) — Athanassios, Feb 2 '16 at 13:20

Community · Accepted Answer · 2017-04-13 12:55:39Z

2

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} *)

edited Apr 13 '17 at 12:55

Community♦

1

answered Feb 2 '16 at 15:56

Jason B.

53.2k3 gold badges97 silver badges204 bronze badges

$\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

add a comment |

Community · Accepted Answer · 2017-04-13 12:56:06Z

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

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

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

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.

great answer @SquareOne regarding to 3. I though it would be easier, faster perhaps to use ReadLine, is that right ? — Athanassios, Feb 2 '16 at 18:49
@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. — SquareOne, Feb 2 '16 at 20:45
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. — Athanassios, Feb 2 '16 at 21:11
@Athanassios For example you might find interesting these posts: 1, 2, 3, 4, ... — SquareOne, Feb 2 '16 at 22:08

Berg · Accepted Answer · 2016-02-02 11:16:53Z

1

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 *)

answered Feb 2 '16 at 11:16

Berg

1,0195 silver badges14 bronze badges

$\begingroup$ I am looking for a better answer ;-) $\endgroup$ – Athanassios Feb 2 '16 at 12:40

add a comment |

Athanassios · Accepted Answer · 2016-02-03 11:56:11Z

0

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

edited Feb 3 '16 at 11:56

answered Feb 3 '16 at 10:16

Athanassios

1,1918 silver badges19 bronze badges

$\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

| show 3 more comments

Stack Exchange Network

current community

your communities

more stack exchange communities

Import vs ReadList CSV file with Currency Values

4 Answers 4

1. `ReadList` with formats

2. `ReadList` combined with `ToExpression`

3.`ReadList` with your original dataset

4. Lazy lists

Your Answer

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

Linked

Hot Network Questions

Import vs ReadList CSV file with Currency Values

4 Answers 4

1. ReadList with formats

2. ReadList combined with ToExpression

3.ReadList with your original dataset

4. Lazy lists

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

1. `ReadList` with formats

2. `ReadList` combined with `ToExpression`

3.`ReadList` with your original dataset