Display data from csv table to a chart

[SaidJADA]

Hello everyone,

I a beginner in NodeRed and would like to get some support please.

I have a csv file that contains 18 columns with data and would like to display some of the series on a chart and display in the Dashboard.

Could you please help me ?

Thank you,
Said

[zenofmud]

Jada, welcome to the forum!

I'm unclear about what you want to do. You say:

@SaidJADA

would like to display some of the series

so do you mean you want to pull some of the columns out and ignore the rest or do you want to ignore some complete rows of the data?

I notice in your flow, you haven't added the ui_chart node. Have you read the 'Help' tab for the ui_chart yet?

To help you get an idea of what you can do, here is a flow with many different types of charts so you can examine how they work:

[{"id":"7a7104.afd9b6fc","type":"ui_chart","z":"ab1173bd.4d246","name":"","group":"2ed90642.e002ba","order":2,"width":0,"height":0,"label":"bar chart","chartType":"bar","legend":"true","xformat":"HH:mm:ss","interpolate":"linear","nodata":"No Data","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":2,"x":700,"y":720,"wires":[[],[]]},{"id":"d03fb8c.16c7248","type":"function","z":"ab1173bd.4d246","name":"","func":"var m={};\nm.labels = [\"January\", \"February\", \"March\", \"April\", \"May\", \"June\", \"July\"];\nm.series = ['Series A', 'Series B', 'Series C'];\nm.data = [\n    [65, 59, 80, 81, 56, 55, 40],\n    [28, 48, 40, 19, 86, 27, 90],\n    [38, 28, 20, 49, 45, 60, 20]\n  ];\nreturn {payload:[m]};","outputs":1,"noerr":0,"x":430,"y":920,"wires":[["4468770d.ed449","7a7104.afd9b6fc","32e862c6.b9ca56","51d7ea90.948c94"]]},{"id":"4468770d.ed449","type":"ui_chart","z":"ab1173bd.4d246","name":"","group":"2ed90642.e002ba","order":1,"width":0,"height":0,"label":"line chart","chartType":"line","legend":"true","xformat":"auto","interpolate":"linear","nodata":"No Data","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":2,"x":700,"y":660,"wires":[[],[]]},{"id":"dc8df7a3.3aec3","type":"function","z":"ab1173bd.4d246","name":"","func":"var chart = [{\n    \"series\":[\"A\",\"B\",\"C\"],\n    \"data\":[[{\"x\":1504029632890,\"y\":5},{\"x\":1504029636001,\"y\":4},{\"x\":1504029638656,\"y\":2}],[{\"x\":1504029633514,\"y\":6},{\"x\":1504029636622,\"y\":7},{\"x\":1504029639539,\"y\":6}],[{\"x\":1504029634400,\"y\":7},{\"x\":1504029637959,\"y\":9},{\"x\":1504029640317,\"y\":7}]],\n    \"labels\":[\"\"]\n}];\nmsg.payload = chart;\n\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":760,"wires":[["4468770d.ed449"]]},{"id":"4ee46f7a.1c65d","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":1,"width":0,"height":0,"passthru":false,"label":"Time Line 1","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":290,"y":760,"wires":[["dc8df7a3.3aec3"]]},{"id":"91fa8fcd.f51c2","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":5,"width":0,"height":0,"passthru":false,"label":"array","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":270,"y":920,"wires":[["d03fb8c.16c7248"]]},{"id":"f390f8d.6631d88","type":"random","z":"ab1173bd.4d246","name":"","low":"1","high":"10000000","inte":"true","x":460,"y":1120,"wires":[["4468770d.ed449"]]},{"id":"853ab516.3cef08","type":"inject","z":"ab1173bd.4d246","name":"","repeat":"","crontab":"","once":false,"topic":"","payload":"","payloadType":"str","x":290,"y":1120,"wires":[["f390f8d.6631d88"]]},{"id":"ae514a3f.3648c","type":"function","z":"ab1173bd.4d246","name":"","func":"var m={};\nm.labels = [10,20,30,40,50,60,70];\nm.series = ['Series A', 'Series B', 'Series C', 'Series D'];\nm.data = [\n    [65, 59, 80, 81, 56, 55, 40],\n    [28, 48, 40, 19, 86, 27, 90],\n    [38, 28, 20, 49, 45, 60, 20],\n    [58, 18, 40, 29, 15, 30, 60]\n  ];\nreturn {payload:[m]};","outputs":1,"noerr":0,"x":430,"y":960,"wires":[["4468770d.ed449","7a7104.afd9b6fc","32e862c6.b9ca56","51d7ea90.948c94"]]},{"id":"9356aadb.9590e","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":6,"width":0,"height":0,"passthru":false,"label":"array2","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":270,"y":960,"wires":[["ae514a3f.3648c"]]},{"id":"8ea67535.6ff6a","type":"function","z":"ab1173bd.4d246","name":"","func":"var chart = [{\n    \"series\":[\"A\",\"B\",\"C\"],\n        \"data\":[[{\"x\":1,\"y\":5},{\"x\":2,\"y\":4},{\"x\":3,\"y\":2}],[{\"x\":4,\"y\":6},{\"x\":5,\"y\":7},{\"x\":6,\"y\":6}],[{\"x\":7,\"y\":7},{\"x\":8,\"y\":9},{\"x\":9,\"y\":7}]],\n    \"labels\":[\"\"]\n}];\nmsg.payload = chart;\n\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":800,"wires":[["4468770d.ed449"]]},{"id":"703350ed.7b4a2","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":2,"width":0,"height":0,"passthru":false,"label":"Time Line 2","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":290,"y":800,"wires":[["8ea67535.6ff6a"]]},{"id":"48204f2e.f9aa38","type":"function","z":"ab1173bd.4d246","name":"","func":"var m = {};\nm.labels = [\"Download\", \"In-Store\", \"Mail-Order\"];\nm.data = [[300, 500, 100]];\nm.series = [\"Sales\"];\nreturn {payload:[m],topic:msg.topic};","outputs":1,"noerr":0,"x":450,"y":1160,"wires":[["51d7ea90.948c94","7a7104.afd9b6fc","4468770d.ed449","83a75824.c1bf1","ecb9f291.f90c8","32e862c6.b9ca56"]]},{"id":"51d7ea90.948c94","type":"ui_chart","z":"ab1173bd.4d246","name":"pie chart","group":"2ed90642.e002ba","order":3,"width":0,"height":0,"label":"pie chart","chartType":"pie","legend":"true","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":"30","useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":2,"x":740,"y":900,"wires":[[],[]]},{"id":"b8bf6023.f1958","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":10,"width":0,"height":0,"passthru":false,"label":"array for pie","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"Pie 4 T","x":290,"y":1160,"wires":[["48204f2e.f9aa38"]]},{"id":"4043f991.33bac","type":"function","z":"ab1173bd.4d246","name":"","func":"var chart = [{\n    \"series\":[\"A\",\"B\",\"C\"],\n        \"data\":[[{\"x\":1000000,\"y\":5},{\"x\":2000000,\"y\":4},{\"x\":3000000,\"y\":2}],[{\"x\":4000000,\"y\":6},{\"x\":5000000,\"y\":7},{\"x\":6000000,\"y\":6}],[{\"x\":7000000,\"y\":7},{\"x\":8000000,\"y\":9},{\"x\":9000000,\"y\":7}]],\n    \"labels\":[\"\"]\n}];\nmsg.payload = chart;\n\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":840,"wires":[["4468770d.ed449"]]},{"id":"9f78ee29.e3e7c8","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":3,"width":0,"height":0,"passthru":false,"label":"Time Line 3","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":290,"y":840,"wires":[["4043f991.33bac"]]},{"id":"f11989da.d40538","type":"function","z":"ab1173bd.4d246","name":"","func":"var chart = [{\n    \"series\":[\"A\",\"B\",\"C\"],\n        \"data\":[[{\"x\":1000000000,\"y\":5},{\"x\":2000000000,\"y\":4},{\"x\":3000000000,\"y\":2}],[{\"x\":4000000000,\"y\":6},{\"x\":5000000000,\"y\":7},{\"x\":6000000000,\"y\":6}],[{\"x\":7000000000,\"y\":7},{\"x\":8000000000,\"y\":9},{\"x\":9000000000,\"y\":7}]],\n    \"labels\":[\"\"]\n}];\nmsg.payload = chart;\n\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":880,"wires":[["4468770d.ed449"]]},{"id":"87a13772.ef19d8","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":4,"width":0,"height":0,"passthru":false,"label":"Time Line 4","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":290,"y":880,"wires":[["f11989da.d40538"]]},{"id":"844f93d8.cebff","type":"function","z":"ab1173bd.4d246","name":"","func":"var m={\n    \"series\":[\"X\",\"Y\",\"Z\"],\n    \"data\":[[5,6,9],[3,8,5],[6,7,2]],\n    \"labels\":[\"Jan\",\"Feb\",\"Mar\"]\n};\nreturn {payload:[m]};","outputs":1,"noerr":0,"x":430,"y":1080,"wires":[["4468770d.ed449","7a7104.afd9b6fc","51d7ea90.948c94","83a75824.c1bf1","ecb9f291.f90c8","32e862c6.b9ca56"]]},{"id":"108063d9.b8ee2c","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":9,"width":0,"height":0,"passthru":false,"label":"array3c","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":280,"y":1080,"wires":[["844f93d8.cebff"]]},{"id":"cb5da5be.3a6878","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":11,"width":0,"height":0,"passthru":false,"label":"array 2 for pie","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"Pie Hole","x":300,"y":1200,"wires":[["7abdc605.58ebf"]]},{"id":"7abdc605.58ebf","type":"function","z":"ab1173bd.4d246","name":"","func":"var m = {};\nm.labels = ['2006', '2007', '2008', '2009', '2010', '2011', '2012'];\nm.data = [[28, 48, 40, 19, 86, 27, 90]];\nm.series = ['Series A'];\nreturn {payload:[m],topic:msg.topic};","outputs":1,"noerr":0,"x":450,"y":1200,"wires":[["51d7ea90.948c94","7a7104.afd9b6fc","ecb9f291.f90c8","83a75824.c1bf1","4468770d.ed449","32e862c6.b9ca56"]]},{"id":"dcbe88cb.64cd08","type":"function","z":"ab1173bd.4d246","name":"topic","func":"var m = [\n    {topic:\"X\", payload:22},\n    {topic:\"Y\", payload:66},\n    {topic:\"Z\", payload:42}\n    ];\nreturn [m];","outputs":1,"noerr":0,"x":430,"y":720,"wires":[["7a7104.afd9b6fc","51d7ea90.948c94","4468770d.ed449","32e862c6.b9ca56"]]},{"id":"b2b0022b.dc6e88","type":"inject","z":"ab1173bd.4d246","name":"","repeat":"","crontab":"","once":false,"topic":"","payload":"","payloadType":"date","x":280,"y":720,"wires":[["dcbe88cb.64cd08"]]},{"id":"63c3e876.0d1d48","type":"function","z":"ab1173bd.4d246","name":"label","func":"var m = [\n    {label:\"A\", payload:22},\n    {label:\"B\", payload:66},\n    {label:\"C\", payload:42},\n    ];\nreturn [m];","outputs":1,"noerr":0,"x":430,"y":680,"wires":[["7a7104.afd9b6fc","51d7ea90.948c94","4468770d.ed449","32e862c6.b9ca56"]]},{"id":"f2a0cff5.27739","type":"inject","z":"ab1173bd.4d246","name":"","repeat":"","crontab":"","once":false,"topic":"","payload":"","payloadType":"date","x":280,"y":680,"wires":[["63c3e876.0d1d48"]]},{"id":"a73907f6.75def8","type":"function","z":"ab1173bd.4d246","name":"","func":"var m={\n    \"series\":[\"X\",\"Y\",\"Z\"],\n    \"data\":[[5],[3],[6]],\n    \"labels\":[\"Jan\"]\n};\nreturn {payload:[m]};","outputs":1,"noerr":0,"x":430,"y":1000,"wires":[["7a7104.afd9b6fc","83a75824.c1bf1","ecb9f291.f90c8","4468770d.ed449","32e862c6.b9ca56","51d7ea90.948c94"]]},{"id":"2ba7dc59.06a284","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":7,"width":0,"height":0,"passthru":false,"label":"array3a","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":280,"y":1000,"wires":[["a73907f6.75def8"]]},{"id":"81617557.e3c69","type":"function","z":"ab1173bd.4d246","name":"","func":"var m={\n    \"series\":[\"X\"],\n    \"data\":[[5,3,6]],\n    \"labels\":[\"Jan\",\"Feb\",\"Mar\"]\n};\nreturn {payload:[m]};","outputs":1,"noerr":0,"x":430,"y":1040,"wires":[["7a7104.afd9b6fc","4468770d.ed449","ecb9f291.f90c8","83a75824.c1bf1","32e862c6.b9ca56","51d7ea90.948c94"]]},{"id":"29b27958.20b53e","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":8,"width":0,"height":0,"passthru":false,"label":"array3b","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":280,"y":1040,"wires":[["81617557.e3c69"]]},{"id":"32e862c6.b9ca56","type":"ui_chart","z":"ab1173bd.4d246","name":"","group":"57b5b387.4c6594","order":2,"width":0,"height":0,"label":"horizontal bar","chartType":"horizontalBar","legend":"true","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":"30","useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":2,"x":720,"y":780,"wires":[[],[]]},{"id":"83a75824.c1bf1","type":"ui_chart","z":"ab1173bd.4d246","name":"","group":"57b5b387.4c6594","order":3,"width":0,"height":0,"label":"polar chart","chartType":"polar-area","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":"30","useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":2,"x":750,"y":1020,"wires":[[],[]]},{"id":"ecb9f291.f90c8","type":"ui_chart","z":"ab1173bd.4d246","name":"","group":"57b5b387.4c6594","order":4,"width":0,"height":0,"label":"radar chart","chartType":"radar","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":"30","useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":2,"x":750,"y":1080,"wires":[[],[]]},{"id":"7f0039e9.9a03f","type":"function","z":"ab1173bd.4d246","name":"label","func":"var m = [\n    {label:\"A\", payload:22, series:\"X\"},\n    {label:\"B\", payload:66, series:\"X\"},\n    {label:\"C\", payload:42, series:\"X\"},\n    {label:\"A\", payload:33, series:\"Y\"},\n    {label:\"B\", payload:72, series:\"Y\"},\n    {label:\"C\", payload:12, series:\"Y\"},\n    {label:\"A\", payload:44, series:\"Z\"},\n    {label:\"B\", payload:32, series:\"Z\"},\n    {label:\"C\", payload:80, series:\"Z\"}\n    ];\nreturn [m];","outputs":1,"noerr":0,"x":430,"y":640,"wires":[["51d7ea90.948c94","7a7104.afd9b6fc","4468770d.ed449","32e862c6.b9ca56"]]},{"id":"4afd374f.630008","type":"inject","z":"ab1173bd.4d246","name":"","repeat":"","crontab":"","once":false,"topic":"","payload":"","payloadType":"date","x":280,"y":640,"wires":[["7f0039e9.9a03f"]]},{"id":"c2761135.35b09","type":"ui_button","z":"ab1173bd.4d246","name":"","group":"d93b56e7.6518b","order":11,"width":0,"height":0,"passthru":false,"label":"Clear All","color":"","bgcolor":"","icon":"","payload":"[]","payloadType":"json","topic":"","x":420,"y":600,"wires":[["ecb9f291.f90c8","83a75824.c1bf1","51d7ea90.948c94","32e862c6.b9ca56","7a7104.afd9b6fc","4468770d.ed449"]]},{"id":"2ed90642.e002ba","type":"ui_group","name":"Charts","tab":"211dd179.12ba1e","order":2,"disp":false,"width":"6"},{"id":"d93b56e7.6518b","type":"ui_group","name":"Inputs","tab":"211dd179.12ba1e","order":1,"disp":false,"width":"6"},{"id":"57b5b387.4c6594","type":"ui_group","name":"Group 3","tab":"211dd179.12ba1e","order":3,"disp":false,"width":"6"},{"id":"211dd179.12ba1e","type":"ui_tab","name":"Charts","icon":"dashboard","order":2}]

[SaidJADA]

Thank you very much for your quick reply,

Yes, I would like to pull some of the columns out and ignore the rest.

I haven't added yet the ui-chart because i'm struggling to prepare the input of the chart.

I would like for example to display in X axis : the values of column 3 (col3)

and in Y axis the values of the column 5 (col5)

Thanks again,
Said

[zenofmud]

Are you going to pull that data from every row or just certain ones?

[zenofmud]

Since you have a join node, I assume you are outputting each row of the CSV as a separate object. That makes things easier because you can add a change node between the csv and join node to remove the extra objects in the payload.

Here is an example flow showing you how:

[{"id":"f8d63116.fee3e","type":"inject","z":"cf1da9ab.8fd48","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"col1\":\"C60-9-Auto\",\"col2\":\"2021/4/9\",\"col3\":\"22:30:0\",\"col4\":904,\"col5\":19,\"col6\":4499,\"col7\":86}","payloadType":"json","x":180,"y":120,"wires":[["8de689bd.46a4f8","14cfe9de.3f264e"]]},{"id":"8de689bd.46a4f8","type":"debug","z":"cf1da9ab.8fd48","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":410,"y":260,"wires":[]},{"id":"14cfe9de.3f264e","type":"change","z":"cf1da9ab.8fd48","name":"","rules":[{"t":"delete","p":"payload.col1","pt":"msg"},{"t":"delete","p":"payload.col4","pt":"msg"},{"t":"delete","p":"payload.col6","pt":"msg"},{"t":"delete","p":"payload.col7","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":120,"wires":[["83f16c55.1a4668"]]},{"id":"83f16c55.1a4668","type":"debug","z":"cf1da9ab.8fd48","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":670,"y":120,"wires":[]}]

Now to add it all into a chart in one go, you are going to have to format it as described here:

node-red/node-red-dashboard/blob/master/Charts.md

and look at the flows so you can see how the data needs to be formatted.

You will probably have to use a function node with a loop after the join node to format the data correctly. Now the question is the date/time - do you control how the CSV data is created? If you do, I would add a column with a timestamp in milliseconds and use that other wisa you will have to concatinate the data and time and then use a Date.parse() function (do a google search 'javascript date.parse' to read about it) to get the string data/time into milliseconds to use.