2
$\begingroup$

R has excellent header supports such as read.table with header options. I don't want to mess up with text-processing or stream-processing. I am looking for a similar tool as in R because it saves a lot of tedious work.

How can I import files with headers like 4 lines header where identifier on lines 1-2?

Example

Import["https://dl.dropboxusercontent.com/u/96742826/Mathematica/henris_data_s3.csv"]

enter image description here

where four rows in the header and unique identifier is first row+second row like K3_INJ+GOPR. Numerical values start from the fifth row.

Improve this question
$\endgroup$
2
  • $\begingroup$ Btw I noticed that this file is a ragged, starting on the col position with empty column name 4526-16144, irritating. I think I will just preprocess the file first in my editor and then just read in Mathematica. $\endgroup$
    – hhh
    Nov 11, 2014 at 4:16
  • $\begingroup$ David Z in chat: "Ah, well I was going to say Map[{#[[1]] <> #[[2]], #[[3 ;;]]} &, list]. There should be a nicer way to do it with pattern substitution though, anyway." $\endgroup$
    – hhh
    Nov 11, 2014 at 6:31

3 Answers 3

Reset to default
2
$\begingroup$

This method will work with the ragged data array provided.

Example 1

data = Import["henris_data_s3.csv"];
vnames = Transpose[data[[{1, 2}]]];
vnames[[99]]

{"L1_INJ", "GWIR"}

Above is the 'name' of the 99th record.

Table[vdata[vnames[[i]]] = DeleteCases[Drop[Map[
      Quiet[Check[Part[#, i], Null]] &, data], 4], Null],
  {i, Length[vnames]}];

This is the data in the 99th record.

vdata[{"L1_INJ", "GWIR"}]

{0, 0, 0, 581, 0, 0, 0, 0, 0, 0, 96, 0, 0, 0, ...

Note, this method ignores columns without header information.

Example 2

data = Import[
   "https://dl.dropboxusercontent.com/u/96742826/Mathematica/henris_data_s3.csv"];
vnames = Transpose[data[[{1, 2}]]];
Table[vdata[vnames[[i]]] = 
   DeleteCases[Drop[Map[Quiet[Check[Part[#, i], Null]] &, data], 4], 
    Null], {i, Length[vnames]}];

vnames[[1]]
vdata[vnames[[1]]]
vdata[{"TIME", ""}]
vdata[{"K3_PROD", "GOPR"}]

enter image description here

Improve this answer
$\endgroup$
7
  • $\begingroup$ I cannot understand vdata[{"L1_INJ", "GWIR"}], can you explain what it is doing? How does vdata[{"L1_INJ"}] work if you have non-unique match (how is collision-resolution handled)? $\endgroup$
    – hhh
    Nov 12, 2014 at 13:37
  • $\begingroup$ [{"L1_INJ", "GWIR"} is a unique column identifier. You can reformat it as you please. The identifier is used as a data function argument in vdata[...], which is populated in the Table. $\endgroup$
    – Chris Degnen
    Nov 12, 2014 at 14:16
  • $\begingroup$ Thank you, can you explain how to get data by name of the field? Cases such as vdata[{TIME}]; vdata[{"TIME",}]; vdata[{"TIME"}] are not working for me -- I got it! vdata[{"TIME", ""}] works, testing! :D $\endgroup$
    – hhh
    Nov 12, 2014 at 14:31
  • $\begingroup$ Is there some easy way to match all entries with parts such as PR in the name? I am planning this "regexp match all fields having 'PR' like 'FOPR' and 'GOPR'". $\endgroup$
    – hhh
    Nov 12, 2014 at 14:54
  • 1
    $\begingroup$ You can use vnames2 = Select[vnames, MemberQ[StringMatchQ[#, "*PR*"], True] &] $\endgroup$
    – Chris Degnen
    Nov 12, 2014 at 15:52
2
$\begingroup$

Leonid provided the following demo in chat: the basic idea is to use R's read.table header management before coming back to Mathematica.

file = URLSave["http://samplecsvs.s3.amazonaws.com/TechCrunchcontinentalUSA.csv"];

Needs["RLink`"]
InstallR[];
RSet["testfile", file];
REvaluate["testdata <- read.table(testfile, header=TRUE)"]
Improve this answer
$\endgroup$
0
$\begingroup$

I don't know the answer to this question with a header of many lines and unique identifier specified by many lines. I am trying to create discussion about this in chat.

Alternative solution with *ix tools

  1. Edit the original file to simpler form with instructions here so simplified datadump here

  2. and now read the simplified CSV into Mathematica where only first row has the unique identifier, wuola!

Mathematica solution, perhaps?

  1. Skip header lines on import

  2. ReadList with header and foot lines

  3. Stream CSV or TSV files

  4. Reading specific entries from a CSV file

Puzzles

String manipulation

I. combine rows 1-2

enter image description here

data = {{a, a, c}, {i, j, k}, {1, 2, 3}};
Transpose[data]

Stream manipulation

I. specify the header so that ignore rows 3-4 and make rows 1-2 into an identifier

Improve this answer
$\endgroup$
1
  • $\begingroup$ @Öskå Chris's answer does not return correct output :/ Alerted him now in chat. $\endgroup$
    – hhh
    Nov 11, 2014 at 13:42

Your Answer

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

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