Aftere reading all the links I gave (several times), I finally worked this out. It confused me why so many tables seemed to be made. Here's the sequence of events:
1) Import the data into a datatable 2) Create a new table view with the grouping data and averages . As all the data is being used, the grouping data is all the same (in this case an empty string). Then use then aggregation to calculate the averages. 3) Create the averages variables from the above table view - they are all going to be the same. 4) Make a new view from the original datatable and add the the calcuated averages to new columns. What I did was start from scratch to make things clearer: <script src="https://www.gstatic.com/charts/loader.js";></script> <script> google.charts.load('current', {'packages':['corechart']}); google.charts.setOnLoadCallback(drawChart); function drawChart() { var queryOptions = { // Define the CSV data columns csvColumns: ['date', 'number', 'number', 'number', 'number'], // This should be false if your CSV file doesn't have a header csvHasHeader: true } // Create the query giving the path and name of the CSV file var query = new google.visualization.Query('jose.csv', queryOptions); query.send(handleQueryResponse); } function handleQueryResponse(response) { if (response.isError()) { alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage()); return; } var dataTable = response.getDataTable();dataTable //Create the averages using aggregation var averages = google.visualization.data.group( dataTable, // The first column is what to group on, in this case an empty string [{column: 0, type: 'string', modifier: function () {return '';}}], [ {column: 1, type: 'number', aggregation: google.visualization.data.avg}, {column: 2, type: 'number', aggregation: google.visualization.data.avg}, {column: 3, type: 'number', aggregation: google.visualization.data.avg}, {column: 4, type: 'number', aggregation: google.visualization.data.avg} ] ); // Put the calculated averages into variables var diastolAvg = averages.getValue(0, 1); var sistolAvg = averages.getValue(0, 2); var pulsaAvg = averages.getValue(0, 3); var saturaAvg = averages.getValue(0, 4); // Create a new view from the table and add the averages var dataView = new google.visualization.DataView(dataTable); dataView.setColumns([0, 1, 2, 3, 4, { label: 'diastolAvg', type: 'number', calc: function (dt, row) { return diastolAvg; } }, { label: 'sistolAvg', type: 'number', calc: function (dt, row) { return sistolAvg; } }, { label: 'pulsaAvg', type: 'number', calc: function (dt, row) { return pulsaAvg; } }, { label: 'saturaAvg', type: 'number', calc: function (dt, row) { return saturaAvg; } }]); var options = { series: { 0:{type: 'area'}, 1:{type: 'area'}, 2:{type: 'area'}, 3:{type: 'area'}, 4:{type: 'line'}, 5:{type: 'line'}, 6:{type: 'line'}, 7:{type: 'line'} } }; var joseChart = new google.visualization.ComboChart(document.getElementById('joseChart-div')); joseChart.draw(dataView, options); } </script> <div id="joseChart-div" style="width:100%; height:900px;"></div> You should be able to add all your options back into this. You can see this simplified chart at https://brisray.com/test/jose-simple.htm Just so I don't forget how to do this, I've also started a new page - https://brisray.com/google-charts/calcs.htm - where it should permanently along with some other bits and pieces. Like most things, it's easy once you understand what's going on. On Tuesday, May 2, 2023 at 2:16:31 PM UTC-4 José Vicente Zahonero wrote: > I have modified my php file and added the averages at the last line of > the csv. Now I don't know how I can add this data to my chart. > Can you help me? > > El martes, 2 de mayo de 2023 a las 6:07:58 UTC+2, José Vicente Zahonero > escribió: > >> Hi, I have just read the documentation in the link you sent me and it >> didn't clarify much, to be honest. When I convert the table to csv the >> columns are specified in the database, moreover, then I convert that csv to >> Excel and there is no problem when it comes to differentiate the dates from >> the numbers to calculate the averages. >> Anyway, what I want to embed is a line with the average of each column, >> not the trend it has. I don't know if I've made myself clear. >> I'm sorry if I'm wasting your time. I thank you for the trouble you are >> taking with my problem. >> >> Translated with www.DeepL.com/Translator (free version) >> >> El martes, 2 de mayo de 2023 a las 1:34:17 UTC+2, Ray Thomas escribió: >> >>> When CSV files are used by the API, it guesses the data type of each >>> column. It's reading the number columns properly but I think it is >>> importing the date as a string and so cannot graph it on a trendline. >>> >>> From >>> https://developers.google.com/chart/interactive/docs/reference#google.visualization.arraytodatatable >>> you >>> can change the header of the CSV file to {label: 'fecha', type: >>> 'date'},sistolica,diastolica,pulsaciones,saturacion >>> >>> On Monday, May 1, 2023 at 7:59:37 AM UTC-4 José Vicente Zahonero wrote: >>> >>>> Hi, I have modified my HTML-PHP file with your specifications and I >>>> still can't see the lines with the averages. The only difference between >>>> your proposal and mine is the way to create the csv file, I create it from >>>> a Postgres query and you do not. I attach the code I have and a screenshot >>>> of what it returns. Best regards.[image: Captura_001.png] >>>> >>>> El lunes, 1 de mayo de 2023 a las 12:50:00 UTC+2, Ray Thomas escribió: >>>> >>>>> I used a LineChart because Trendlines only work on Scatter Charts, Bar >>>>> Charts, Column Charts, and Line Charts. >>>>> >>>>> Sorry, I should have explained that in my answer. >>>>> >>>>> I also don't have PHP installed so what I did was use a query to open >>>>> the CSV file after I rewrote the dates. The complete code I used is at >>>>> https://brisray.com/test/jose.htm (which is where the screenshot came >>>>> from) and the CSV file is at https://brisray.com/test/jose.csv >>>>> <https://brisray.com/test/jose.csv> >>>>> >>>>> On Monday, May 1, 2023 at 2:12:51 AM UTC-4 José Vicente Zahonero wrote: >>>>> >>>>>> Hi, I have tried changing the format of the dates to 'yyyy-MM-dd' in >>>>>> the csv and I have added the code you have indicated but it doesn't show >>>>>> me >>>>>> any average line in the graph. >>>>>> [image: Captura_001.png] >>>>>> >>>>>> El lunes, 1 de mayo de 2023 a las 1:41:03 UTC+2, Ray Thomas escribió: >>>>>> >>>>>>> The quickest way is to add a trendline to the graph. Add this >>>>>>> something like this under your series statements in the options secion: >>>>>>> >>>>>>> trendlines: { >>>>>>> 0:{type: 'linear', color: 'yellow', visibleInLegend: true, >>>>>>> lineWidth:2}, >>>>>>> 1:{type: 'linear', color: 'yellow', visibleInLegend: true, >>>>>>> lineWidth:2}, >>>>>>> 2:{type: 'linear', color: 'yellow', visibleInLegend: true, >>>>>>> lineWidth:2}, >>>>>>> 3:{type: 'linear', color: 'yellow', visibleInLegend: true, >>>>>>> lineWidth:2} >>>>>>> }, >>>>>>> >>>>>>> Trendlines do not work on discrete data such as strings, so you have >>>>>>> to change your CSV file to the preferred date format which for a CSV >>>>>>> file >>>>>>> is yyyy-MM-dd or do it in the datatable. The trendline help is at >>>>>>> https://developers.google.com/chart/interactive/docs/gallery/trendlines >>>>>>> >>>>>>> [image: jose-trendlines.jpg] >>>>>>> >>>>>>> Trendlines are not averages, so if you really need those you need to >>>>>>> create new columns in the datatable and calculate them yourself. The >>>>>>> example in the ComboChart help at >>>>>>> https://developers.google.com/chart/interactive/docs/gallery/combochart >>>>>>> uses the averages of the row data but you need it for the column data. >>>>>>> So >>>>>>> you'll need 4 new columns, one for each of the current columns, and >>>>>>> each of >>>>>>> the new columns will contain all the same number, the column averages. >>>>>>> >>>>>>> Another method of getting the averages is to use the aggregation >>>>>>> method. The help for that is at >>>>>>> https://developers.google.com/chart/interactive/docs/reference#data-manipulation-methods. >>>>>>> >>>>>>> Because that depends on grouping the data, you are going to need a new >>>>>>> column containing a single value in all the rows so they can be grouped >>>>>>> together. How to use this properly has come up fairly often so there >>>>>>> are >>>>>>> examples on Stack Overflow. Here are some: >>>>>>> >>>>>>> >>>>>>> https://stackoverflow.com/questions/58417507/how-to-draw-an-average-column-in-google-charts-bar-chart >>>>>>> >>>>>>> https://stackoverflow.com/questions/50004379/draw-a-line-to-show-the-average-distribution-in-google-scatter-plot >>>>>>> >>>>>>> https://stackoverflow.com/questions/35918308/average-line-for-current-data-in-a-google-combochart >>>>>>> >>>>>>> I don't know who WhiteHat is, but they certainly know their way >>>>>>> around the API. >>>>>>> >>>>>>> On Saturday, April 29, 2023 at 3:59:39 AM UTC-4 José Vicente >>>>>>> Zahonero wrote: >>>>>>> >>>>>>>> Hi, I have an area chart in which I represent some values taken >>>>>>>> from a csv file. I would like to be able to add the averages >>>>>>>> corresponding >>>>>>>> to each column to the graph, can you help me? >>>>>>>> My code is in the attached file. Thanks for you help. >>>>>>>> >>>>>>> -- You received this message because you are subscribed to the Google Groups "Google Visualization API" group. To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/1e7824be-18c6-4ea9-b5c8-616efebe86c1n%40googlegroups.com.
