Creating a Chart from A CSV File and JSON File In Node-Red

hello and welcome to another video uh this video is another video about the chart node and it's actually a video in response to an earlier video i did and someone asked me uh how do you take data from a csv file and display it in a chart node so i created a flow for that and i'll show you the flow and i'll show you how to use it and in addition i also while i was doing it i took data from a log file a log file i produced using a python script i'll explain that to you later on and i created a chart from that data here you can see both both charts here one's called broker latency and the other ones just simply called chart this is actually data that was sent to me to create the flow in the first place so let's first take a look at the this one here which is taken from a csv file and we take a look at the csv file first and this is the data coming in you can see it's got a header here and this is describes the fields that are appearing down here now what they wanted to display was the actual temperature there's various temperature sensors there and you can see there's 26 and you can see here i think it's 45 there's a temperature so and there's 25 so there are actually four temperature readings in in this data and we go and display that now the timestamp here is actually interesting it's not a unix timestamp you can see here it's 1430. so one of the first things i had to do is convert this timestamp into a unix formatted a timestamp there's the time there's the date you can see the date here so if i show you the flow for this one so this is a flow here it's actually quite simple we have the inject node to start it we read the file using the file in node pass it to the csv node and we pass it into a function which does just all the work really and that passes on to the the chart node to display it now i'm using the debug node here and it's very useful especially when you're creating the flow because you're going to have to process the data so you can have to know what the data looks like and that's what the debug node's there so we can actually see the data itself the csv node we're passing out a single array and the first row contains the column names which i showed you earlier so let's read our data and we can see we've got three there which is one two three so if i look at the output of the csv which is the second one here we can see we got an array with 40 objects and you see here we've got the data it's taken the column errors and it's actually created key value pairs you can see the temperatures here now now what i've got to do now is use the function node to extract these temperatures and display them on the graph and you can see here that the time there's the the day and there's the error minutes on seconds and what i've got to do is convert this into a unix timestamp before i actually pass it on to the chart node so let's have a look at our function okay this bit of code here is just me testing out the the two timestamp uh function this is the one that converts it into a unix formatted uh timestamp which we'll need for sending on to the graph node uh we start really here we create five arrays data out is the array that's going out and these are four arrays here are temporary arrays where we put our data in now we loop through the array we got 43 elements if i look over on the right hand side you can see there's 43 elements and we loop we'd loop through each element of the array and we put it into a object and you can see here the x is the timestamp and the y is the temperature now i got four temperatures so i have four values here temp one temp two tem three temp four now these are objects now i put those objects into an array a1.push so i push them into their array there and that loops around as many times it needs to in this case 43 times and we finish up with four arrays now i combine those four arrays into a single array data out and then i construct the payload using the format we saw in earlier videos where i create the series which is the title the labels and then it most importantly the data out and then i simply return it you can again ignore that bit there again that's just me testing i'll remove those from the code when i i actually publish it so the version you'll get will be minus these things here and that produces the the graph you can see here okay so pretty straightforward and we're going to do the same this time this is for a log file and this is what the log file looks like now this was created using a python script now there is on the site and there is also a github version of it a logger script where you can log data in json format using python you can log it by topic as well as a topic blogger and there's a straight data logger and i use that to create this this log it's json formatted data so this is all json so i have to convert it when i i read it in and what it is is a a script that basically measures the broker latency so he tests the broker sends a message to the broker the message comes back and it times the the round-trip time and that's the latency and it does it for quality of service zero one and two and then it repeats it it's i think it's i'm not sure it's five second intervals i've got it here but then it repeats the test okay uh this is the graph we produce and you can see it here quality serves 0 1 and 2 and you can see the differences and it's i created this really to illustrate for a tutorial the fact that using different quality of service so either is quicker or slower so quality of service zero is is usually a lot quicker than quality of service uh one and certainly quality of service too you can see quality of service too is consistently slower than quality of service zero which is the dark blue okay so again same structure we read it in from a file using the file in node and we pass it into a function that generates the graph this time we've got a regular expression right at the top which basically removes all the current return line feeds from the data as soon as he doesn't remove them it actually replaces all the carriage recent return line feeds with a single known carriage return line fit because it the data could come tongues all in a twist uh the data could come from unix or could come from windows or it could come from mac so we need to have a proper format or a known format so we do that and then we split it based on the new line character again i create three arrays quality server zero one and two and basically i then loop through all the data and do the same as i did in the the last example and that's this full loop here and i see i'm creating an object using the x is the time and why is the time taken and i'm storing it in into an array i push that into quantity of service zero push that one into quality of service one and that one into quality service two and again at the end i combine them data the data out is quality service 0 1 and 2. so this is an array of an array just like in the last example and again the labels and the series and i'll just return the message so basically this is identical to the the last example the way we actually structure the data the only difference is we don't need to work with the time stamp because we're using already a unix formatted timestamp but we do need to do some work on the input data to remove the new lines and replace them okay so that's it really how to get data from a csv file now to get data from a log file you can do the same for a database and i'll just show you quickly one i i've done for a client i can't make this available but i can actually show you uh what it looks like so here's the actual flow itself you can see the database nodes being used here this part of the flow here is basically collecting the uh the request is going to create the request for the database and then you can see it's called create query then it feeds it on into the database the database sends out the results into a function node in the function node again does the work to display it into the the graph node here okay we've seen this when i showed you how to do uh read data from a sql database and store data for in a sql database in in previous videos and i'll put links those videos below but that's basically you'll see that in those videos as well and you can see the the form here so i can select the data i can select the end time i can select the customer i can select the center and i can select the number of records i'm going to use this is an interesting one you might find useful is this here can show it in a table format if i do it i use a table format and i just submit the query and you can see coming down here or it can be in a graph format so go on to graph and it shows you the the graph view here using the chart node and if i want to i can enter a file name here and i can export the results off into into that file name but okay so that brings us the end of the video i hope you found it useful if you liked the video then please click on the like button below if you got comments on the video then use the comments form below if you like to get notified of new videos to the channel then you can always subscribe to the channel and click on the notification button and until next time goodbye

Channel: Steve Cope

Views: 22,109

Keywords: node-red, chart node, csv, create graph, create chart, dashboard, video, stevesinternetguide, tutorial, json, log file

Id: rq3fukD4RJY

Length: 11min 51sec (711 seconds)

Published: Fri Sep 11 2020