Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.soft-sys.math.mathematica > #1269 > unrolled thread

read CSV

Back to article view | Back to comp.soft-sys.math.mathematica

Contents

  read CSV Alan <alan.isaac@gmail.com> - 2011-03-30 09:13 +0000
    Re: read CSV David <dlkeith@comcast.net> - 2011-03-31 09:03 +0000
    Re: read CSV David Bailey <dave@removedbailey.co.uk> - 2011-03-31 09:03 +0000
    Re: read CSV Peter Breitfeld <phbrf@t-online.de> - 2011-03-31 09:04 +0000
    Re: read CSV annetts729 <annetts729@gmail.com> - 2011-03-31 11:04 +0000

#1269 — read CSV

I've received some files formatted like below.
How can I read selected columns into a list?
I'm coming from a background where I'd read in each line,
discarding the first, then (in this case at least)
split on the comma, and coerce the type.  So I'm happy
to do that if I can figure out how.  (Naturally I could
preprocess the data into a form that say ReadList
likes better, but I'm looking for a pure Mathematica solution.)

Thanks,
Alan Isaac

"x","y","color","pen down?"
"0","0.26161459854014585","105","true"
"1","0.2665251497682828","105","true"
"2","0.29244385081680907","105","true"
"3","0.31995416628471895","105","true"

[toc] | [next] | [standalone]

#1336

On Mar 30, 2:13 am, Alan <alan.is...@gmail.com> wrote:
> I've received some files formatted like below.
> How can I read selected columns into a list?
> I'm coming from a background where I'd read in each line,
> discarding the first, then (in this case at least)
> split on the comma, and coerce the type.  So I'm happy
> to do that if I can figure out how.  (Naturally I could
> preprocess the data into a form that say ReadList
> likes better, but I'm looking for a pure Mathematica solution.)
>
> Thanks,
> Alan Isaac
>
> "x","y","color","pen down?"
> "0","0.26161459854014585","105","true"
> "1","0.2665251497682828","105","true"
> "2","0.29244385081680907","105","true"
> "3","0.31995416628471895","105","true"

Hello Alan,

You can do this using Import to read the data into a list structure,
and then work with the list structure to extract and format the data
as you please.

I copied your data and made it into a text file. I then used Import to
read the text file, forcing CSV format, and extracted the XY column
data.

Below is a notebook expression. Copy the notebook expression as text.
The open a new Mathematica notebook and paste the expression into it.
Say yes to interpret the notebook. When you execute the notebook it
will open a dialog box allowing you to select your data file. It will
then import the file and process it.

Kind regards,

David

-----------------------------------------------

Notebook[{

Cell[CellGroupData[{
Cell["Choose the file using a dialog", "Subsection",
 CellChangeTimes->{{3.510498132049691*^9, 3.510498154778931*^9}}],

Cell[CellGroupData[{

Cell[BoxData[
 RowBox[{"file", "=",
  RowBox[{
  "SystemDialogInput", "[", "\"\<FileOpen\>\"", "]"}]}]], "Input",
 CellChangeTimes->{{3.510497968155803*^9, 3.5104980119450803`*^9}}],

Cell[BoxData["\<\"C:\\\\Users\\\\David\\\\Documents\\\\Tools\\\\\
Mathematica\\\\read\\\\data.txt\"\>"], "Output",
 CellChangeTimes->{{3.510498006703471*^9, 3.510498022787099*^9}}]
}, Open  ]]
}, Open  ]],

Cell[CellGroupData[{

Cell["Set the directory to that of the selected file", "Subsection",
 CellChangeTimes->{{3.510498158819338*^9, 3.5104981918757963`*^9}}],

Cell[CellGroupData[{

Cell[BoxData[
 RowBox[{"SetDirectory", "[",
  RowBox[{"DirectoryName", "[", "file", "]"}], "]"}]], "Input",
 CellChangeTimes->{{3.510498027108307*^9, 3.5104980559839573`*^9}}],

Cell[BoxData["\<\"C:\\\\Users\\\\David\\\\Documents\\\\Tools\\\\\
Mathematica\\\\read\"\>"], "Output",
 CellChangeTimes->{3.51049805759076*^9}]
}, Open  ]]
}, Open  ]],

Cell[CellGroupData[{

Cell["Import the file as a CSV", "Subsection",
 CellChangeTimes->{{3.510498201516613*^9, 3.5104982177406416`*^9}}],

Cell[CellGroupData[{

Cell[BoxData[
 RowBox[{"raw1", "=",
  RowBox[{"Import", "[",
   RowBox[{"file", ",", "\"\<CSV\>\""}], "]"}]}]], "Input",
 CellChangeTimes->{{3.510498065453174*^9, 3.510498079383999*^9}, {
   3.510498523516779*^9, 3.510498526917585*^9}, {
   3.5104985870424905`*^9, 3.5104985881812925`*^9},
   3.5104986942770786`*^9}],

Cell[BoxData[
 RowBox[{"{",
  RowBox[{
   RowBox[{"{",
    RowBox[{"\<\"x\"\>", ",", "\<\"y\"\>", ",", "\<\"color\"\>",
     ",", "\<\"\\\"pen down?\\\" \"\>"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{
    "0", ",", "0.26161459854014585`", ",", "105",
     ",", "\<\"\\\"true\\\" \"\>"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{
    "1", ",", "0.2665251497682828`", ",", "105",
     ",", "\<\"\\\"true\\\" \"\>"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{
    "2", ",", "0.29244385081680907`", ",", "105",
     ",", "\<\"\\\"true\\\" \"\>"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{
    "3", ",", "0.31995416628471895`", ",", "105",
     ",", "\<\"\\\"true\\\" \"\>"}], "}"}], ",",
   RowBox[{"{", "\<\"\"\>", "}"}], ",",
   RowBox[{"{", "\<\"\"\>", "}"}], ",",
   RowBox[{"{", "\<\"\"\>", "}"}], ",",
   RowBox[{"{", "\<\"\"\>", "}"}]}], "}"}]], "Output",
 CellChangeTimes->{
  3.5104980844072075`*^9, 3.510498527853586*^9, {3.510498562737648*^9,
    3.5104985900844955`*^9}, 3.5104986954470806`*^9}]
}, Open  ]]
}, Open  ]],

Cell[CellGroupData[{

Cell["\<\
Select all records with length 4\
\>", "Subsection",
 CellChangeTimes->{{3.5104987265535355`*^9, 3.5104987458819695`*^9}, {
  3.5104989656159573`*^9, 3.510498975849575*^9}}],

Cell[CellGroupData[{

Cell[BoxData[
 RowBox[{"raw2", "=",
  RowBox[{"Select", "[",
   RowBox[{"raw", ",", " ",
    RowBox[{
     RowBox[{
      RowBox[{"Length", "[", "#", "]"}], "\[Equal]", "4"}], "&"}]}],
   "]"}], " ",
  RowBox[{"(*", " ",
   RowBox[{"read", " ", "about", " ", "pure", " ", "functions"}], " ",
    "*)"}]}]], "Input",
 CellChangeTimes->{{3.5104986985826864`*^9, 3.510498721140326*^9}, {
  3.5104987519035797`*^9, 3.5104987663336053`*^9}}],

Cell[BoxData[
 RowBox[{"{",
  RowBox[{
   RowBox[{"{",
    RowBox[{"\<\"x\"\>", ",", "\<\"y\"\>", ",", "\<\"color\"\>",
     ",", "\<\"\\\"pen down?\\\" \"\>"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{
    "0", ",", "0.26161459854014585`", ",", "105",
     ",", "\<\"\\\"true\\\" \"\>"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{
    "1", ",", "0.2665251497682828`", ",", "105",
     ",", "\<\"\\\"true\\\" \"\>"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{
    "2", ",", "0.29244385081680907`", ",", "105",
     ",", "\<\"\\\"true\\\" \"\>"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{
    "3", ",", "0.31995416628471895`", ",", "105",
     ",", "\<\"\\\"true\\\" \"\>"}], "}"}]}], "}"}]], "Output",
 CellChangeTimes->{3.510498721920327*^9}]
}, Open  ]]
}, Open  ]],

Cell[CellGroupData[{

Cell["\<\
Select rows 2 through the last and the XY columns only\
\>", "Subsection",
 CellChangeTimes->{{3.510498237474676*^9, 3.510498300498787*^9}, {
  3.510498775678022*^9, 3.5104987838836365`*^9}, {
  3.5104988263469105`*^9, 3.5104988476877484`*^9}}],

Cell[CellGroupData[{

Cell[BoxData[
 RowBox[{"data", "=",
  RowBox[{"raw2", "[",
   RowBox[{"[",
    RowBox[{
     RowBox[{"2", ";;"}], ",",
     RowBox[{"{",
      RowBox[{"1", ",", "2"}], "}"}]}], "]"}], "]"}], "  ",
  RowBox[{"(*", " ",
   RowBox[{
   "look", " ", "up", " ", "Part", " ", "to", " ", "see", " ", "what",
     " ", "this", " ", "means"}], " ", "*)"}]}]], "Input",
 CellChangeTimes->{{3.5104983063175974`*^9, 3.5104983592640905`*^9}, {
  3.5104984602274675`*^9, 3.5104984635502734`*^9}, {
  3.5104986662438297`*^9, 3.5104986664310303`*^9}, {
  3.510498792198451*^9, 3.5104988078140783`*^9}}],

Cell[BoxData[
 RowBox[{"{",
  RowBox[{
   RowBox[{"{",
    RowBox[{"0", ",", "0.26161459854014585`"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{"1", ",", "0.2665251497682828`"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{"2", ",", "0.29244385081680907`"}], "}"}], ",",
   RowBox[{"{",
    RowBox[{"3", ",", "0.31995416628471895`"}], "}"}]}],
  "}"}]], "Output",
 CellChangeTimes->{
  3.5104983620252953`*^9, 3.5104984642054744`*^9,
   3.5104986160897417`*^9, 3.5104986673202314`*^9, {
   3.510498786754041*^9, 3.510498808375679*^9}}]
}, Open  ]]
}, Open  ]],

Cell[CellGroupData[{

Cell["Plot the strokes", "Subsection",
 CellChangeTimes->{{3.5104988800734053`*^9, 3.5104988889654207`*^9}}],

Cell[CellGroupData[{

Cell[BoxData[
 RowBox[{"ListLinePlot", "[", "data", "]"}]], "Input",
 CellChangeTimes->{{3.510498861134972*^9, 3.5104988735837936`*^9}}],

Cell[BoxData[
 GraphicsBox[{{}, {},
   {Hue[0.67, 0.6, 0.6],
    LineBox[{{0., 0.26161459854014585`}, {1., 0.2665251497682828}, {
     2., 0.29244385081680907`}, {3., 0.31995416628471895`}}]}},
  AspectRatio->NCache[GoldenRatio^(-1), 0.6180339887498948],
  Axes->True,
  AxesOrigin->{0, 0.262},
  PlotRange->{{0., 3.}, {0.26161459854014585`, 0.31995416628471895`}},
  PlotRangeClipping->True,
  PlotRangePadding->{
    Scaled[0.02],
    Scaled[0.02]}]], "Output",
 CellChangeTimes->{{3.510498867530983*^9, 3.5104988755337973`*^9}}]
}, Open  ]]
}, Open  ]]
},
WindowSize->{707, 647},
WindowMargins->{{24, Automatic}, {Automatic, 30}},
FrontEndVersion->"8.0 for Microsoft Windows (64-bit) (February 23, \
2011)",
StyleDefinitions->"Default.nb"
]

[toc] | [prev] | [next] | [standalone]

#1338

On 30/03/2011 10:13, Alan wrote:
> I've received some files formatted like below.
> How can I read selected columns into a list?
> I'm coming from a background where I'd read in each line,
> discarding the first, then (in this case at least)
> split on the comma, and coerce the type.  So I'm happy
> to do that if I can figure out how.  (Naturally I could
> preprocess the data into a form that say ReadList
> likes better, but I'm looking for a pure Mathematica solution.)
>
> Thanks,
> Alan Isaac
>
> "x","y","color","pen down?"
> "0","0.26161459854014585","105","true"
> "1","0.2665251497682828","105","true"
> "2","0.29244385081680907","105","true"
> "3","0.31995416628471895","105","true"
>

Here is the code you need. Since you are new to Mathematica, I'll 
explain a bit.

1)  The Import command will read data from files using the filename 
suffix to decide the format it expects. Under Windows, a full pathname 
uses backslash characters, which need to be doubled in strings.

2)   The data is read in as a 2D array, which can be thought of as a 
list of rows - you need to drop one row.

3)   Assuming you want the string "true" to become a Mathematica 
boolean, you need the /. operator to perform the swap, as shown


In[2]:= data = Import["c:\\ttt\\test.csv"]

Out[2]= {{"x", "y", "color", "pen down?"}, {0, 0.261615, 105,
   "true"}, {1, 0.266525, 105, "true"}, {2, 0.292444, 105, "true"}, {3,
    0.319954, 105, "true"}}

In[5]:= data =
  Drop[data, 1] /. "true" -> True

Out[5]= {{2, 0.292444, 105, True}, {3, 0.319954, 105, True}}

David Bailey
http://www.dbaileyconsultancy.co.uk

[toc] | [prev] | [next] | [standalone]

#1344

Alan wrote:

> I've received some files formatted like below.
> How can I read selected columns into a list?
> I'm coming from a background where I'd read in each line,
> discarding the first, then (in this case at least)
> split on the comma, and coerce the type.  So I'm happy
> to do that if I can figure out how.  (Naturally I could
> preprocess the data into a form that say ReadList
> likes better, but I'm looking for a pure Mathematica solution.)
>
> Thanks,
> Alan Isaac
>
> "x","y","color","pen down?"
> "0","0.26161459854014585","105","true"
> "1","0.2665251497682828","105","true"
> "2","0.29244385081680907","105","true"
> "3","0.31995416628471895","105","true"
>

Try this:

(Import["file.dat","CVS"]//Rest)/."true"->True

Output:
{{0, 0.261615, 105, True}, {1, 0.266525, 105, True},
 {2, 0.292444, 105, True}, {3, 0.319954, 105, True}}


-- 
_________________________________________________________________
Peter Breitfeld, Bad Saulgau, Germany -- http://www.pBreitfeld.de

[toc] | [prev] | [next] | [standalone]

#1362

Hi Alan,

On Mar 30, 5:13 pm, Alan <alan.is...@gmail.com> wrote:
> I've received some files formatted like below.
> How can I read selected columns into a list?
> I'm coming from a background where I'd read in each line,
> discarding the first, then (in this case at least)
> split on the comma, and coerce the type.  So I'm happy
> to do that if I can figure out how.  (Naturally I could
> preprocess the data into a form that say ReadList
> likes better, but I'm looking for a pure Mathematica solution.)
>
> Thanks,
> Alan Isaac
>
> "x","y","color","pen down?"
> "0","0.26161459854014585","105","true"
> "1","0.2665251497682828","105","true"
> "2","0.29244385081680907","105","true"
> "3","0.31995416628471895","105","true"

Something like
irawd = Import["Isaac.txt", "Table"] (* read data *);
ihedr = irawd[[1]] (*  header *);
idata = Rest@irawd (* siphon off from rest of data *);
idata = StringReplace[#, {"\"" -> "", "true" -> "True"}] & /@  idata
(* string processing *);
idata = StringSplit[#, ","] & /@ idata (* split line into strings *);
idata = Map[Flatten, idata, 1] (* and flatten appropriately *)

will read data in the file which is cut from your post.

Now we want to assign variables to columns.  Use

xcord = ToExpression@idata[[All, 1]] ;
ycord = ToExpression@idata[[All, 2]];

or simply

cords =  ToExpression@idata[[All, {1, 2}]];
colour =  ToExpression@idata[[All, 3]];
pen = idata[[All, 4]];

which we can use like
rawg = If[pen[[#]],
     {Directive[Hue[colour[[#]]/255], PointSize[.01]],
Point[cords[[#]]]},
     {Directive[White], Point[cords[[#]]]}
     ] & /@ Range@Length@colour;
Show[Graphics@rawg, Frame -> True, AspectRatio -> .75]

D.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.soft-sys.math.mathematica

csiph-web