ISO 8601 time format for data files?

Use this forum to post Vensim related questions.
Post Reply
billh
Member
Posts: 45
Joined: Tue Jan 10, 2017 11:48 pm
Vensim version: DSS

ISO 8601 time format for data files?

Post by billh »

I've got a data file I'd like to bring in to create a reference mode. Eventually I'd like to use some of the variables as exogenous inputs, and I might even use the data in an optimization.

I've attached an example model. I couldn't figure out how to attach the CSV file to the message, because that's not one of the allowed formats for attachments. While I could change that, my data comes to me that way, and so I wanted to keep it for this example if possible. Here's the data:

Code: Select all

Time,A,B,C
2013-01-01 00:00:00.000,32,66,710
2013-01-01 01:00:00.000,45,39,736
2013-01-01 02:00:00.000,29,18,741
2013-01-01 03:00:00.000,09,00,690
2013-01-01 04:00:00.000,68,19,651
The real data looks similar, except that it's got a total of 9 columns, not 4, and it's got several hundred lines, each line separated by an hour. As you can see, the variable names in the data file match those in the model.

I started by using chapter 20 of the User's Guide as a guide. It presumes the data is in a .dat file, not a .csv file.

I then moved to chapter 9 of the Reference Manual. Ignoring that I had CSV and not TSB data for the moment, I tried Model > Import Dataset..., selected the CSV file, and found the expected dialog box. I selected the Time Down instead of Time Across radio button, and I saw Row 1 values that gave the name of each of the four variables (Time, A, B, C) and Column 1 values that looked plausible: there was a blank line 007, and I couldn't see anything to the right of the colon following the minutes digits. Columns 1-4 had the expected values. That looked promising, as if Vensim had parsed the file appropriately.

Since everything was an ISO 8601 time or a number, I didn't worry about Translate; I simply clicked OK.

That gave me a "Stop from Vensim" dialog that said, "Unable to parse the data file." There was a "Table/Spreadsheet to VDF Messages" window that said
ERROR: Unable to understand @(row 7, col 1) as a time.
Failure - nothing written
Apparently Vensim doesn't like a blank line at the end, so I removed it and tried again. This time I got a "Stop from Vensim" that said "Dat2vdf encountered 12 errors writing data." The "Table/Spreadsheet to VDF Messages" window said
ERROR: Two values found for A at time 2013 - using 68.
You may want to adjust Time Slop in the Options>Settings dialog
ERROR: Two values found for A at time 2013 - using 68.
ERROR: Two values found for A at time 2013 - using 68.
ERROR: Two values found for A at time 2013 - using 68.
Writing 1 values for -A
ERROR: Two values found for B at time 2013 - using 19.
ERROR: Two values found for B at time 2013 - using 19.
ERROR: Two values found for B at time 2013 - using 19.
ERROR: Two values found for B at time 2013 - using 19.
Writing 1 values for -B
ERROR: Two values found for C at time 2013 - using 651.
ERROR: Two values found for C at time 2013 - using 651.
ERROR: Two values found for C at time 2013 - using 651.
ERROR: Two values found for C at time 2013 - using 651.
Writing 1 values for -C
Writing 1 values for time base -Time
Loading ...\refmodeexample.vdf as ...\refmodeexample. Origin is ..
refmodeexample.csv converted to dataset ...\St
In all three cases, the ellipses replace long path names. The first two are identical, and the third appears to be truncated ("St" is the first of one of the directory names, and there's a round filled circle after the "St" on the screen that won't paste into the forum, but Emacs hexl-mode suggests it's a hex '07'.

If I select the Reference Mode tool, click on A, and then click on "Import Vals", I get a "Question from Vensim:
St*...
Attempt to read lookup values???
where "*" replaces the filled circle. Clicking "Yes" gets me a question about "First X value", and increment, and I'm apparently lost.

What do I do now?

Bill
Attachments
refmodeexample.mdl
(1.13 KiB) Downloaded 213 times
billh
Member
Posts: 45
Joined: Tue Jan 10, 2017 11:48 pm
Vensim version: DSS

Re: ISO 8601 time format for data files?

Post by billh »

I should have said: I'm running DSS 6.4E (x32) on Windows 7.

Bill
tomfid
Administrator
Posts: 3804
Joined: Wed May 24, 2006 4:54 am

Re: ISO 8601 time format for data files?

Post by tomfid »

Vensim doesn't support ISO time formats. Time has to be a floating point number. (Ventity does, however.)

The simplest thing to do is compute a time index as an offset from 2013-01-01 00:00:00.000 in an extra column in your spreadsheet. Another option might be to use a tool like Trifacta Wrangler to automate the conversion.
billh
Member
Posts: 45
Joined: Tue Jan 10, 2017 11:48 pm
Vensim version: DSS

Re: ISO 8601 time format for data files?

Post by billh »

Thanks, Tom. That seems to have fixed the problem.

For any R users here who want a start on a conversion, here's the quick and dirty script I used (no warranty included, and you'll no doubt want to modify it):

Code: Select all

library(lubridate)
library(dplyr)
FILEIN <- 'refmodeexample.csv'
FILEOUT <- 'refmodeexample2.csv'

spreadsheet <- read.csv(FILEIN)
spreadsheetout <-
    spreadsheet %>%
    mutate(
        Time = as.character(Time),
        Time = ymd_hms(Time, tz = 'America/Los_Angeles'),
        DeltaTime = as.numeric((Time - Time[1])/3600),
        Timestamp = Time,
        Time = DeltaTime) %>%
    dplyr::select(-DeltaTime)

write.csv(spreadsheetout,
          FILEOUT,
          row.names = FALSE)
Post Reply