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?

$\endgroup$

2 Answers 2

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"}

$\endgroup$
5
  • $\begingroup$ This is great! I am going to modify my answer to just have the R commands. $\endgroup$ Oct 4, 2015 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, 2015 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, 2015 at 17:35
  • $\begingroup$ Missing[__] has documentation and handling in Mathematica. See the function pages for Missing, DeleteMissing, MissingQ. $\endgroup$ Oct 5, 2015 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, 2015 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.

$\endgroup$
4
  • $\begingroup$ Shouldn't the data be transposed ? $\endgroup$
    – SquareOne
    Oct 4, 2015 at 17:48
  • $\begingroup$ @SquareOne Yes, thank you! I forgot that data frames in R are column-wise. $\endgroup$ Oct 4, 2015 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, 2015 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$ Oct 5, 2015 at 17:28

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.