How to use timestamps when reading data from csv?

Questions
,
1

Hey,

I have a large csv file in the following format containing different sensor outputs per timestamp.
The header looks like this:
timestamp;DeviationID ValueY;CuStepNo ValueY;LevelMainTank ValueY;YC10001_MV ValueY;[…]

And values look like this:
2018-10-10 14:53:19;1;1;149.985778808594;100;

So everything is either int or float, except for the timestamp column.
I now want to read this csv file and plot it with lilaq, using the timestamp column as my y-Axis.
I believe I need lq.load-txt with a converters dict, but I haven’t been able to get it to work correctly.

So far I’ve tried:

#import "@preview/lilaq:0.5.0" as lq

#let data = lq.load-txt(read("./data/SmA-Four-Tank-Batch-Process_V2.csv"), header: true, delimiter: ";", converters: (
  "timestamp": datetime,
  "rest": float,
))

#let timestamp = data["timestamp"]
#let LevelMainTank_ValueY = data["LevelMainTank ValueY"]

#lq.diagram(
  lq.plot(timestamp, LevelMainTank_ValueY),
)

And typst compile returns:

error: assertion failed: Found converter that doesn't map to a header: "timestamp"
   ┌─ @preview/lilaq:0.5.0\src\loading\txt.typ:72:8
   │
72 │         assert(name in header, message: "Found converter that doesn't map to a header: " + repr(name))
   │         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

help: error occurred in this call of function `load-txt`
  ┌─ \\?\C:\Users\hansm\Documents\WA_Master\plot.typ:3:12
  │
3 │   #let data = lq.load-txt(read("./data/SmA-Four-Tank-Batch-Process_V2.csv"), header: true, delimiter: ";", converters: (
  │ ╭─────────────^
4 │ │   "timestamp": datetime,
5 │ │   "rest": float,
6 │ │ ))
  │ ╰──^

Any help is very much appreciated!

2

Could you please provide an example of the code you have been trying, that in order for us to help you faster? Something we can copy/paste or a link to a typst.app project?

1 Like
3

Thanks for the tip! I’ve added my current code to my post above.

1 Like
4

You are welcome. Could you also please post a subset of the CSV file you are using?

1 Like
5

Sure, here’s a subset from the first 200 lines (it includes more columns than the example I posted above).
Link (since new users can’t upload directly on here): 82.4 KB file on MEGA

6

Thanks. Here it is.
subset_200_lines.csv (82.4 KB)

1 Like
7

You have to use the name of the column instead:

#let data = lq.load-txt(read("./data/SmA-Four-Tank-Batch-Process_V2.csv"), header: true, delimiter: ";", converters: (
  "DeviationID": datetime, //Change this to the real name of column 1
  "rest": float,
))

Or the column number. See load-txt − Lilaq

8

Hmmmm… the subset I uploaded seems to be missing the first column named “timestamp” from the header…
But this then wouldn’t change my code where I already use “timestamp” for the column name.

9

Yes, this is why I mentioned that.

I have used a modified version of How to transform a date input format into an output format? - #3 by TheJanzap and am suggesting you use something like this:

#import "@preview/lilaq:0.5.0" as lq

#let timestamp(t) = {
  let txt = str(t)  // Expected datetime format is "2018-10-21 14:53:54"
  let date = datetime(
    year: int(txt.slice(0, count: 4)),
    month: int(txt.slice(5, count: 2)),
    day: int(txt.slice(8, count: 2)),
    hour: int(txt.slice(11, count:2)),
    minute: int(txt.slice(14, count:2)),
    second: int(txt.slice(17, count:2)),
  )
  date
}

#let csv = ```csv
A,B,C,D
2019-10-01 15:00:00, 0, 2, "A"
2019-10-01 15:30:00, 1, 4, "B"
2019-10-01 16:00:00, 2, 6, "C"
```.text

#let data = lq.load-txt(
  csv,
  header: true,
  converters: (
    A: v => timestamp(v),
    D: v => str(v),
  ),
)

#lq.diagram(
    lq.plot(data.A, data.B),
)

Note that Data loading − Lilaq recommends:

JSON data is typed, i.e., there are strings, ints, floats, arrays, and objects (dictionaries) and the Typst function json automatically converts these types to Typst types (unlike with CSV).

2 Likes
10

Thanks for the code! I’ll have to take a closer look at this. I just noticed when viewing my csv in in hex editor that I have some weird bytes just before timestamp in my header…

11

Here is an example with a subset of your values. I have added the missing header for column 1.

Working Solution 
#import "@preview/lilaq:0.5.0" as lq

#let timestamp(t) = {
  let txt = str(t)  // Expected datetime format is "2018-10-21 14:53:54"
  let date = datetime(
    year: int(txt.slice(0, count: 4)),
    month: int(txt.slice(5, count: 2)),
    day: int(txt.slice(8, count: 2)),
    hour: int(txt.slice(11, count:2)),
    minute: int(txt.slice(14, count:2)),
    second: int(txt.slice(17, count:2)),
  )
  date
}

#let csv = ```csv
Timestamp; DeviationID ValueY;CuStepNo ValueY;LevelMainTank ValueY;YC10001_MV ValueY;PI12002_PV_Out ValueY;PI12003_PV_Out ValueY;YC14001_MV ValueY;FIC14002_MV ValueY;FIC14002_PV_Out ValueY;FIC14002_SP ValueY;YC14006_MV ValueY;PI14012_PV_Out ValueY;YC23001_MV ValueY;YC22001_MV ValueY;YC21001_MV ValueY;FIC23002_PV_Out ValueY;FIC23002_MV ValueY;FIC23002_SP ValueY;FIC22002_MV ValueY;FIC22002_PV_Out ValueY;FIC22002_SP ValueY;FIC21002_MV ValueY;FIC21002_PV_Out ValueY;FIC21002_SP ValueY;YS23004_Ctrl ValueY;YS22004_Ctrl ValueY;YS21004_Ctrl ValueY;LIC23002_MV ValueY;LIC23002_PV_Out ValueY;LIC23002_SP ValueY;LIC22002_MV ValueY;LIC22002_PV_Out ValueY;LIC22002_SP ValueY;LIC21002_MV ValueY;LIC21002_PV_Out ValueY;LIC21002_SP ValueY;PIC14007_MV ValueY;PIC14007_PV_Out ValueY;PIC14007_SP ValueY;YC14008_Ctrl ValueY;PI10003_PV_Out ValueY
2018-10-10 14:53:19;1;1;149.985778808594;100;0.0706018209457397;0.0879629850387573;100;100;0;2000;12.5;0.0758101940155029;0;0;0;0;0;0;0;0;0;0;0;0.6119384765625;0;0;0;0;1.94632518291473;2;0;1.96621811389923;2;0.6119384765625;1.97974538803101;15;10;0.127007722854614;0.200000002980232;0;0.00171031872741878
2018-10-10 14:53:20;1;1;149.985778808594;100;0.0706018209457397;0.0868055820465088;100;100;0;2000;12.5;0.0752314329147339;0;0;0;0;0;0;0;0;0;0;0;1.1514892578125;0;0;0;0;1.94552946090698;2;0;1.96780955791473;2;1.1514892578125;1.97974538803101;15;10;0.127020925283432;0.200000002980232;0;0.00171468942426145
```.text

#let data = lq.load-txt(
  csv,
  header: true,
  delimiter: ";",
  converters: (
    Timestamp: v => timestamp(v),
  ),
)

#lq.diagram(
    lq.plot(data.Timestamp, data.at("LevelMainTank ValueY"))),
)
1 Like
12

Thanks, the code works perfectly now!

13

I massage CSVs with QSV a lot, prior to pulling it into typst…

1 Like