4
$\begingroup$

Lets assume, i have this data.txt file.

s1/km  t1/h   s2/km    t2/h
20     1      40       1

40     2      80       2

60     3

I define myself a function for processing

deleteRows[expr_] := Replace[expr, x_List :> DeleteCases[x, {}], {0, Infinity}]

Then import the file:

data = deleteRows@Import["data.txt", "Table"][[2 ;;]]

Everythings works fine till now. But if I want to select the 3rd column it breaks.

data[[All, 3]]

How do I deal with such situations?

|improve this question
$\endgroup$
5
$\begingroup$

Maybe this:

dataraw = Import["data.txt", "Table", 
  "IgnoreEmptyLines" -> True, "HeaderLines" -> 1]

{{20, 1, 40, 1}, {40, 2, 80, 2}, {60, 3}}

Then for example:

data = PadRight[dataraw, {Automatic, Automatic}, "NA"]

{{20, 1, 40, 1}, {40, 2, 80, 2}, {60, 3, "NA", "NA"}}

Finally,

data[[All, 3]]

{40, 80, "NA"}

|improve this answer
$\endgroup$
  • $\begingroup$ This is great! I am going to modify my answer to just have the R commands. $\endgroup$ – Anton Antonov Oct 4 '15 at 17:34
  • $\begingroup$ Thank you very much. There remain only some questions: PadRight[dataraw, Automatic, "NA"] works as well. Why do you make a list of the Automatic option? $\endgroup$ – mcocdawc Oct 5 '15 at 17:28
  • $\begingroup$ Does one have to use "NA"? Are there some list handling built in functions in mathematica that treat "NA" in a special way? Or could one use also "any string"? At least for ListPlot[] the "any string" works. Because of the answer by @AntonAntonov I tried PadRight[dataraw, Automatic, Missing[]] which also works and to me this seems the cleanest solution, or whats the purpose of Missing[]? $\endgroup$ – mcocdawc Oct 5 '15 at 17:35
  • $\begingroup$ Missing[__] has documentation and handling in Mathematica. See the function pages for Missing, DeleteMissing, MissingQ. $\endgroup$ – Anton Antonov Oct 5 '15 at 17:42
  • $\begingroup$ @mcocdawc Yes, Missing is certainly the way to go with mma versions >=10, for the reasons you say (see the Details section of the doc). And yes, one Automatic is sufficient here (the double {Automatic, Automatic} just reminds that you pad a 2d array (but this is useless of course here to pad with new lines containing Missing data. $\endgroup$ – SquareOne Oct 5 '15 at 17:50
3
$\begingroup$

Using R/RLink

Since this scenario happens fairly often when doing data analysis here is a solution using R (through RLink):

Needs["RLink`"]
InstallR[]

rres = REvaluate[
  "read.table( file = \"./data.txt\", quote = \"\", header = TRUE, fill = TRUE, stringsAsFactors = FALSE)"]

(* RDataFrame[RNames["s1.km", "t1.h", "s2.km", "t2.h"], 
 RData[{20, 40, 60}, {1, 2, 3}, {40, 80, Missing[]}, {1, 2, 
   Missing[]}], RRowNames[1, 2, 3]] *)

data = Transpose[ List @@ rres[[2]] ];
data[[All, 3]]

(* {40, 80, Missing[]} *)

Note the parallels with the solution by SquareOne.

|improve this answer
$\endgroup$
  • $\begingroup$ Shouldn't the data be transposed ? $\endgroup$ – SquareOne Oct 4 '15 at 17:48
  • $\begingroup$ @SquareOne Yes, thank you! I forgot that data frames in R are column-wise. $\endgroup$ – Anton Antonov Oct 4 '15 at 18:12
  • $\begingroup$ Thank you very much for your answer, but since I am not familiar with RLink the answer by SquareOne is of better use for me. $\endgroup$ – mcocdawc Oct 5 '15 at 17:23
  • $\begingroup$ @mcocdawc No problem! The solution of SqureOne was also unknown to me -- I use/used a different, less convenient approach. (It was in the first version of my post.) I kept the R/RLink solution because I have to execute that command often when using R and because it is analogous to using Import with the proposed options. $\endgroup$ – Anton Antonov Oct 5 '15 at 17:28

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.