13
$\begingroup$

I need to load data from the CSV file shown below.

Mathematica graphics

Import[ ] drops the leading zeros in column 3 when it imports the CSV file, but they are security identifiers, and I need them. ReadList[ ] keeps the leading zeros, but I can't get it to break the columns into separate elements. Each record ends up as long string. The options: Word, WordSeparators, ect. don't do what they are supposed to do. I could probably save the file as a TSV, or go into the CSV file and prepend a "'" and the appropriate number of zeros, or use a function to count the number of characters in column three and prepend the missing zeros, but that's just klugy(sp). What is the right way to ReadList[ ] the file?

Here are the first few records in the file (copied by hand). The leading zeros in column three don't appear when the CSV file is loaded in Excel, but they do show up when the file is loaded in Notes++.

date,secid,cus_sed,tick,repno,dis_to_df,impl_rate,prob_df
01/31/2004,753,01381710,AA,0323N,3.20387,BBB,0.00068
01/31/2004,756,00431F10,AABC,34341,2.88794,BB,0.00194
01/31/2004,757,03237110,ANCPA,0591N,3.18587,BBB,0.00072
01/31/2004,759,00440310,AACE,A02B8,3.23057,BBB,0.00062 01/31/2004,767,45814710,FILM,A06F0,2.04099,B-,0.02063

Improve this question
$\endgroup$
2
  • $\begingroup$ Thanks for the sample. I posted a simple ReadList example. Is it your intention to keep everything as Strings or would you prefer to have each column in an optimized format? $\endgroup$
    – Mr.Wizard
    Oct 22, 2012 at 20:42
  • $\begingroup$ Strings are ok for what I'm doing now, but I would really like to know how to specify the format for each field. I've tried to figure out how to do that from the documentation, but couldn't. $\endgroup$
    – George Wolfe
    Oct 22, 2012 at 20:45

4 Answers 4

Reset to default
12
$\begingroup$

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
]

Mathematica graphics

Import

ReadList is usually quite a bit faster, but if speed is not paramount you may find Import more convenient. It attempts to auto-detect the content of each field. Since you state that your cus_sed field will always be eight digits we can assume that any number less than eight digits has had zeros stripped from the left side, and pad it accordingly.

pad = IntegerString[#, 10, 8] &;

dat = Import["sample.csv"];

dat[[All, 3]] = dat[[All, 3]] /. n_Integer :> pad@n;

dat // Grid

Mathematica graphics

The issue here is that data types are not consistent within a single column, but depending on your needs that may not matter. Here is the same as above but shown in InputForm to reveal the actual data:

Mathematica graphics

Convoluted ReadList

I held off posting this because I don't remember handling commas in ReadList being this ugly. Perhaps I'm still missing an option. Anyway, you can get ReadList to read each field as a different type. Here is a rather badly convoluted example where I read a comma as Character and then drop it at the end with Part.

stream = OpenRead["sample.csv"];  (* open the file as a stream *)

Skip[stream, Record] (* skip the first line of the file *)

dat =
 ReadList[stream,
   {Record, Character, Number, Record, Record, Record, Character, 
    Number, Character, Record, Character, Number},
   RecordSeparators -> {"\n", ",", " "}
 ];

dat = dat[[All, {1, 3, 4, 5, 6, 8, 10, 12}]];  (* remove commas *)

Types are now uniform in each column:

Grid[dat]

Mathematica graphics

Improve this answer
$\endgroup$
7
  • $\begingroup$ This works great. I was carelessly using "\," as the word separator. $\endgroup$
    – George Wolfe
    Oct 22, 2012 at 20:48
  • $\begingroup$ @George I'm still working on another option. Let me ask, are all cus_sed items eight digits long? $\endgroup$
    – Mr.Wizard
    Oct 22, 2012 at 20:57
  • $\begingroup$ Yes. There are CUSIPS, which always have 8 characters (or 9 if the check digit is included). $\endgroup$
    – George Wolfe
    Oct 22, 2012 at 21:02
  • 3
    $\begingroup$ The big advantage to using Import of "CSV" is that it understands quoted strings with embedded commas, if you have any of those, and removes the quotes while keeping the comma in the string. (For instance: 12,300 is 2 entries, while "12,300" is a single one.) The main problem with Import is that it gives you no way to provide "hints" to override its auto-detection: you don't want 03237110 to be an Integer even though it looks like one. $\endgroup$
    – librik
    Oct 22, 2012 at 22:07
  • 1
    $\begingroup$ To get your commas discarded automatically, try these definitions: ignore[_String] := Sequence[] and igc := ignore[Character]. Then you can use igc in your specifier list in place of Character. The character will be read as a String, placed in an ignore[] head, and then evaluation will remove it. $\endgroup$
    – librik
    Oct 22, 2012 at 22:55
6
$\begingroup$

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@#&
, Import["data.csv", "CSV", "Numeric" -> False, "HeaderLines" -> 1]
, {DateList, FromDigits, Identity, Identity, Identity, ToExpression, Identity, ToExpression}
, List
]
Improve this answer
$\endgroup$
1
  • $\begingroup$ This is very nice -- you can specify exactly what format you expect each field to be in. Note that, since you have disabled all numeric auto-detection and are reading everything in as text, you could equally well have used ReadList of Word with WordSeparators->",". You would still need to skip the header line, of course. $\endgroup$
    – librik
    Oct 22, 2012 at 22:31
2
$\begingroup$

A bit more detailed solution for mixed data. First the CSV data is loaded as a list of records by ReadList (like Mr.Wizard's answer) and then the appropriate columns are converted to expressions, except for the first line that is expected to provide the column labels.

columnNumber = 8;
expressionColumns = {2, 6, 8};

data = ReadList["sample.csv", Table[Record, {columnNumber}], RecordSeparators -> {",", " ", "\n"}];

InputForm@Join[List@First@data, 
  Transpose@
   MapAt[ToExpression, Transpose@Rest@data, List /@ expressionColumns]]

Mathematica graphics

Improve this answer
$\endgroup$
1
  • $\begingroup$ This is helpful, too. Thanks $\endgroup$
    – George Wolfe
    Oct 22, 2012 at 20:59
1
$\begingroup$

Import["temp.csv","Table"] should do what you want.

For my test .csv file I had:

1,0022002,3
0,0003101,0

and the following doesn't strip the leading zeros as desired:

Import["temp.csv","Table"]
(* {{"1,0022002,3"}, {"0,0003101,0"}} *)
Improve this answer
$\endgroup$
2
  • $\begingroup$ I would probably also add a StringSplit[#, ","] & /@ before the Import call so that the values are split, otherwise you get each row as a single string. $\endgroup$
    – rm -rf ♦
    Oct 22, 2012 at 20:34
  • $\begingroup$ Import[fname,"Table"] creates the same result as ReadList[ ]. I'll have to use StringSplit[ ]. I thought I should be able to get our data loading functions to to what I needed them to do, but I guess not. The is one place where I miss Fortran. Thanks, all. $\endgroup$
    – George Wolfe
    Oct 22, 2012 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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