Message Boards Message Boards

GROUPS:

[✓] Convert list of dates for use in time series?

Posted 7 months ago
959 Views
|
7 Replies
|
5 Total Likes
|

I've been scouring the forums and I've found many ways to transform dates. However, I'm stuck trying to figure out how to import a set of dates in a format that I can use in a time series. I'm not sure why Mathematica won't automatically recognize the format as that is were it originally came from via an exported DataDrop TSV file. I'm able to import the dates, can remove the last part of the string, but when I try DateObject it does not work. Yet, entering a single value as noted below working.

hwd = Import["hw.tsv", {"Data", All, 1}];
rep = StringReplace[Rest[hwd], " GMT +0" -> ""] // InputForm;
DateObject[rep]

Ultimately I want to be able to import the file and be able to do a TimeSeries with the Date and the temperature without the degrees and plot it as a DateListPlot.

Attachments:
7 Replies
Posted 7 months ago

Hi Michael,

Try this:

hwd = Import["hw.tsv", {"Data", All, 1}, "HeaderLines" -> 1];
rep = StringReplace[hwd, " GMT +0" -> ""];
DateObject /@ rep
Posted 7 months ago

To further answer your question, I think it would be easier to import everything, then do a bit of manipulation on the columns. I'm not sure why you want to remove the temperature units, I'd just convert these to a Quantity and preserve the unit value. This works fine with TimeSeries/DateListPlot:

hwd = Import["hw.tsv", "Data", "HeaderLines" -> 1];
hwd = MapAt[DateObject[StringReplace[#, " GMT +0" -> ""]] &, hwd, {All, 1}];
hwd = MapAt[Quantity, hwd, {All, 2}];
ts = TimeSeries[hwd]
DateListPlot[ts]

If you really want to remove the units, change the third line to :

hwd = MapAt[ToExpression[StringReplace[#, " " ~~ __ -> ""]] &, hwd, {All, 2}];
Posted 7 months ago

I have five different data exports. I had a workbook that allowed me to compare the means, and do trend analysis and comparisons for different temperature sensors in my house. However, DataDrop dropped the Degrees F from a value in a couple of the datasets and I'm not able to run them as it see the 62 differently that the rest of the data. The same process that works for the hw file, does not work for the basement file. That is why I'm trying to find a way to import, but normalize on the value only and use the Dates to create a new dataset that is easier to use going forward.

Does that help?

Attachments:
Posted 7 months ago

Some of the values are not strings, so StringReplace is complaining.. An easy fix would be changing the 3rd line to

hwd = MapAt[ToExpression[StringReplace[ToString[#], " " ~~ __ -> ""]] &, hwd, {All, 2}];
Posted 7 months ago

Thanks for the help. This worked well. I was able to process the data I have and am working on getting a clean export for additional analysis.

Posted 7 months ago

Hi Michael,

Try this:

hwd = Import["hw.tsv", {"Data", All, 1}, "HeaderLines" -> 1];
rep = StringReplace[hwd, " GMT +0" -> ""];
DateObject /@ rep

Michael:

There are a few issues going on with your data (at least the 2 attachments). If the source of the data is WL's own DataDrop, I believe there are built in functions to retrieve such data. see "guide/UsingTheWolframDataDrop" in the WL documentation. I believe there are some Unicode encoding issues going on with the Degree symbol. I get an inkling view of your workflow. Some automated data collection perhaps from IOT (manually?) uploaded to WL Data Drop. Either the upload to the Drop has some issues or the download (retrieval process) has some issues. You do state that you think the issue is with drop, but I am not sure if you mean upload or download. I came up with the following:

ClearAll[clean];
clean[x_] := 
 Module[{}, {If[Head[x[[1]]] == String, 
    DateObject[StringReplace[x[[1]], "+0" -> ""], TimeZone -> 0]], 
   If[Head[x[[2]]] == String, 
    ToExpression[
     StringReplace[x[[2]], {"F" -> "", "\[Degree]" -> ""}]],  
    ToExpression[
     StringReplace[
      ToString[x[[2]]], {"F" -> "", "\[Degree]" -> ""}]]]}];

DateListPlot[
 TimeSeries[
  Map[Function[clean[#]], 
   Import["hw.tsv", "Data", HeaderLines -> 1] ]]]

DateListPlot[
 TimeSeries[
  Map[Function[clean[#]], 
   Import["basement.tsv", "Data", 
    HeaderLines -> 1] ]]]

Returns values and no errors in both files. You may try the other 5 (or is it 3 more) using or following the above code style.

To convert the timestamp into a DateObject remove the "+0" and the time is converted to local time zone, so we add or set the time zone to 0. The temperature data has "F" and "[Degree] symbol. The WL treats "[Degree]" in a special way. Please see degree Go to the Encodings section and select "More" a few times. The capital A hat that shows up in your files are a side effect of encoding issues and how the degree symbol is treated in WL and Unicode as well as many other systems. See more degree When left alone Import seems to have taken care of this "degree" issue. If you import the data as plaintext using "Text" option the CapAhat returns.

Slight change in the clean function can produce a dataset that is also compatible with TimeSeries and also good to upload back to Data Drop

cleanq[x_] := 
  Module[{}, {If[Head[x[[1]]] == String, 
     DateObject[StringReplace[x[[1]], "+0" -> ""], TimeZone -> 0]], 
    If[Head[x[[2]]] == String, 
     Quantity[
      ToExpression[
       StringReplace[x[[2]], {"F" -> "", "\[Degree]" -> ""}]], 
      "Fahrenheit"], 
     Quantity[
      ToExpression[
       StringReplace[
        ToString[x[[2]]], {"F" -> "", "\[Degree]" -> ""}]], 
      "Fahrenheit"]]}];
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract