1
$\begingroup$

I import data from excel in the following format:

data // MatrixForm

data format

After converting the data to a time series, the "NA" values are converted to "0. + NA", when I try to access them at a specific date. E.g.:

tsdata = TimeSeries[data[[;; , 2;;]], {data[[1 , ;;]]}]
tsdata["March 29, 2012"]

{3.87874, 5.35429, 0. + "NA", 1.14632}

What I would like/expect to obtain is simply:

{3.87874, 5.35429, "NA", 1.14632}

I tried using different combinations of MissingDataMethod, which did not show any impact, e.g.:

tsdata = TimeSeries[data[[;; , 2;;]], {data[[1 , ;;]]}, MissingDataMethod -> None]

Here is a MWE, based on this csv DATA:

data = Import["https://pastebin.com/raw/Xj6x3PSe"];
dates = Table[DateList[{StringTake[data[[k, 1]], 10], {"Day", "Month","Year"}}], {k, 2, Length[data]}];
tsdata = TimeSeries[data[[2 ;;, 2 ;;]], {dates}];
tsdata["August 26, 2008"]

And the output is:

{6.67345,6.55845,7.46281,7.32023,6.89391,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,1.82743,1.77073,1.65871,1.56591,1.52516,1.46369,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,1.47368,1.57085,1.65731,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,1.18979,1.02756,1.08294,1.12263,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA,0. +NA}

However,

tsdata["Values"][[7, ;;]]

yields:

{6.67345,6.55845,7.46281,7.32023,6.89391,NA,NA,NA,NA,NA,NA,1.82743,1.77073,1.65871,1.56591,1.52516,1.46369,NA,NA,NA,NA,NA,NA,NA,1.47368,1.57085,1.65731,NA,NA,NA,NA,NA,NA,1.18979,1.02756,1.08294,1.12263,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA}

$\endgroup$
  • $\begingroup$ Might want to do a preliminary data[[;; , 2;;]] /. "NA" -> Missing[]. $\endgroup$ – J. M.'s discontentment Aug 14 '17 at 15:17
  • $\begingroup$ I tried that as well, producing: {3.87874, 5.35429, 0. + Missing[], 1.14632}. Interestingly enough, when accessing the data via tsdata["Values"], both "NA" and Missing[] work just fine. $\endgroup$ – CFW Aug 14 '17 at 15:22
  • $\begingroup$ Then, can you try tsdata = TimeSeriesMap[Chop, TimeSeries[(* stuff *)]]? $\endgroup$ – J. M.'s discontentment Aug 14 '17 at 15:29
  • $\begingroup$ @J.M. you mean TimeSeriesMap[Chop, tsdata]["March 28, 2012"]? This raises: "InterpolatingFunction::dmval: Input value {3541881600} lies outside the range of data in the interpolating function. Extrapolation will be used." Possibly there is something wrong with the dates? $\endgroup$ – CFW Aug 14 '17 at 15:53
  • $\begingroup$ That's weird. Can you maybe export the Excel file as *.csv and put it on Pastebin? $\endgroup$ – J. M.'s discontentment Aug 14 '17 at 16:09
1
$\begingroup$
ASOFDATA[tsdata_, date_?((StringQ[#] || ListQ[#]) &)] := 
tsdata @ DateObject @ date;
(* there is a long-standing annoying design oversight in Mathematica: 
of course  0. + x should give x, but only  0 + x simplifies x. 
*) 
(* But for a symbol like NA, oder, better Missing[], 
   we can educate WL in a one-liner:*)
Unprotect[Missing]; Missing /: 0. + Missing[] = Missing[]; Protect[Missing];
data = Import["https://pastebin.com/raw/Xj6x3PSe"] /. 
   "NA" -> Missing[];
dates = Table[
   DateList[{StringTake[data[[k, 1]], 10], {"Day", "Month", 
      "Year"}}], {k, 2, Length[data]}];
tsdata = TimeSeries[data[[2 ;;, 2 ;;]], {dates}];

{6.67345,6.55845,7.46281,7.32023,6.89391,Missing[],Missing[],Missing[],Missing[],Missing[],Missing[],1.82743,1.77073,1.65871,1.56591,1.52516,1.46369,Missing[],Missing[],Missing[],Missing[],Missing[],Missing[],Missing[],1.47368,1.57085,1.65731,Missing[],Missing[],Missing[],Missing[],Missing[],Missing[],1.18979,1.02756,1.08294,1.12263,Missing[],Missing[],Missing[],Missing[],Missing[],Missing[],Missing[],Missing[],Missing[],Missing[],Missing[]}

| improve this answer | |
$\endgroup$
  • $\begingroup$ If 0.+x yielded x, then 0.+1 would be different than 0.+x/.x->1. Inconsistent. $\endgroup$ – John Doty Oct 15 '17 at 20:15
  • $\begingroup$ @JohnDoty Nobody in the applied sciences cares. Anybody getting things like 0.+x or 1.*x from, say NDSolve or so, is doing numerical calculations to finite precision. It can be (and has been, in real life) quite inefficient in a larger expression to let NDSolve calculate 0.*somebigfunction[x] only to do by hand at the end 0. -> 0 My argment is that people who know what they are doing do not care about a minor "inconsistency" induced by a non-strongly-type language, but the majority of users even do not understand or do not care about the difference between infinite and finite precision. $\endgroup$ – Rolf Mertig Oct 16 '17 at 11:08
  • 1
    $\begingroup$ I guess I'm "nobody" then. If I'm doing numerical calculations to finite precision, one thing I don't want is for Mathematica to spuriously switch to exact calculation. Constants with 1000 terms of special functions of Root objects are unwieldy. If you "do not understand or do not care about the difference between infinite and finite precision" in Mathematica, you don't understand the tool, and will run into problems using it. $\endgroup$ – John Doty Oct 17 '17 at 1:40
  • $\begingroup$ Do you work in applied science? All those engineers I helped over the years never work with more that MachinePrecision. $\endgroup$ – Rolf Mertig Oct 17 '17 at 8:48
  • 1
    $\begingroup$ I'm a scientific instrument designer. For numerical work, MachinePrecision usually suffices. But imprecision can be toxic to symbolic methods, and mathematical perfection can be toxic to numerical methods. Mathematica's strength is that it can do both. This comes at a price: mixing approaches can cause all kinds of trouble if you're not careful. These problems are at the root of many issues on this forum. When should you use 0.3, and when should you use 1/3? They are profoundly different mathematical objects. $\endgroup$ – John Doty Oct 17 '17 at 16:53
2
$\begingroup$

The following function mimics the desired functionality:

ASOFDATA[tsdata_, date_] := Module[{position, data},
  (*date accepts: {2012,02,23} or "February 23, 2012"*)
  position = Flatten[Position[tsdata["Dates"], #] & /@ {DateObject[date]}];
  data = Flatten[tsdata["Values"][[position, ;;]]];
  Return[data]
  ]

data = Import["https://pastebin.com/raw/Xj6x3PSe"];
dates = Table[
   DateList[{StringTake[data[[k, 1]], 10], {"Day", "Month", 
      "Year"}}], {k, 2, Length[data]}];
tsdata = TimeSeries[data[[2 ;;, 2 ;;]], {dates}];

ASOFDATA[tsdata, "August 26, 2008"]

{6.67345,6.55845,7.46281,7.32023,6.89391,NA,NA,NA,NA,NA,NA,1.82743,1.77073,1.65871,1.56591,1.52516,1.46369,NA,NA,NA,NA,NA,NA,NA,1.47368,1.57085,1.65731,NA,NA,NA,NA,NA,NA,1.18979,1.02756,1.08294,1.12263,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA}

Still, I don't understand where the issue is with:

tsdata["August 26, 2008"]
| improve this answer | |
$\endgroup$
0
$\begingroup$

It may be useful to define NA as a symbol that collapses numeric expressions to just itself.

First, define a helper to identify numeric functions:

NumericFunctionQ[f_Symbol] := MemberQ[Attributes[f], NumericFunction] NumericFunctionQ[_] := False

Then, use an upvalue for NA.

(f_ /; NumericFunctionQ[f])[___, NA, ___] ^:= NA

0.0 + NA (* NA *) Sin[NA] (* NA *)

| improve this answer | |
$\endgroup$

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.