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

$\endgroup$
  • $\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 '14 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 '14 at 6:31
1
$\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

$\endgroup$
  • $\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 '14 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 '14 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 '14 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 '14 at 14:54
  • 1
    $\begingroup$ You can use vnames2 = Select[vnames, MemberQ[StringMatchQ[#, "*PR*"], True] &] $\endgroup$ – Chris Degnen Nov 12 '14 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)"]
$\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

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

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.