动态地为Google折线图枢轴数据 [英] Pivot data dynamically for google line chart

问题描述

我想通过多年在同一条线图中显示各个国家的人口".显示的数据基于多选下拉列表国家/地区"中的选择.基础数据表包含3列:

I want to display "population" of various countries through the years in the same line chart. The data displayed is based on selections from a multi-select dropdown "Countries". Underlying Data Table has 3 columns:

年份，国家，人口
2012，countryA，33
2013，countryA，35
2014年，国家/地区A，40
2012，国家B，65
2013，countryB，70
2014，countryB，75
2012，countryC，15
2013，countryC，20
2014，countryC，25

Year, Country, Population
2012,countryA,33
2013,countryA,35
2014,countryA,40
2012,countryB,65
2013,countryB,70
2014,countryB,75
2012,countryC,15
2013,countryC,20
2014,countryC,25

我正在尝试从基础数据表创建数据透视表我正在使用的代码是:

I am trying to create a pivoted Data View from the underlying Data Table The code I am using is:

  function drawLineChart() {

      var arr = $('#country').val();

      var lineChartJson = $.ajax({
      url: "../json/lineChart.json",
      dataType: "json",
      async: false
      }).responseText;

    var lineChartData = new google.visualization.DataTable(lineChartJson);
    var view = new google.visualization.DataView(lineChartData);

    var viewCols = [0];

    for(var i = 0; i < arr.length; i++) {

           var viewCols1 = [{
                type: 'number',
                label: arr[i],
                calc: function (dt, row) {
                    return (dt.getValue(row, 1) == arr[i]) ? dt.getValue(row, 2) : null;
                        }
                    }];

                    viewCols = viewCols.concat(viewCols1);

            }

 view.setColumns(viewCols);

var aggCols = [{
                    column: 1,
                    type: 'number',
                    label: view.getColumnLabel(1),
                    aggregation: google.visualization.data.sum
                }];

    for(var i = 2; i < 4; i++) {

            var aggCols1 = [{
                    column: i,
                    type: 'number',
                    label: view.getColumnLabel(i),
                    aggregation: google.visualization.data.sum
                }];

            aggCols = aggCols.concat(aggCols1);
        }

    var pivotedData = google.visualization.data.group(view, [0], aggCols);

但是，这似乎并没有达到预期效果，我在图表中仅得到1条线，其中包含了所有国家/地区的值(尽管我可以看到3个国家/地区的图例)

But this does not seem to work as expected and I just get 1 Line in the chart with values for all countries added up (although I can see the legend for 3 countries)

另一方面，如果我按如下所示设置视图"列，则它可以按预期工作.

On the other hand if I set my View columns as below, it works as expected.

    view.setColumns([0, {
    type: 'number',
    label: arr[0],
    calc: function (dt, row) {
        return (dt.getValue(row, 1) == arr[0]) ? dt.getValue(row, 2) : null;
    }
}, {
    type: 'number',
    label: arr[1],
    calc: function (dt, row) {
        // return values of C only for the rows where B = "bar"
        return (dt.getValue(row, 1) == arr[1]) ? dt.getValue(row, 2) : null;
    }
}, {
    type: 'number',
    label: arr[2],
    calc: function (dt, row) {
        return (dt.getValue(row, 1) == arr[2]) ? dt.getValue(row, 2) : null;
    }
}]);

循环中出了什么问题?在创建查看列"的循环中，"concat"有问题吗?我还通过使用console.log看到了viewCols数组，它似乎具有正确的元素

What is going wrong in the loop? Is something wrong with "concat" in the loop where I am creating View Columns? I also saw the viewCols array by using console.log and it seems to have the right elements

我正在尝试关注以下帖子:从现有的google图表DataTable对象创建透视数据视图

I was trying to follow the below post: Creating pivoted DataView from existing google charts DataTable object

推荐答案

问题与范围有关

arr [i] 在 calc:function(dt，row)

这是透视数据的另一种方法...

here is another way to pivot the data...

google.charts.load('current', {
  callback: function () {
    var arr = [
      'countryA',
      'countryB',
      'countryC'
    ];

    var lineChartData = google.visualization.arrayToDataTable([
      ['Year', 'Country', 'Population'],
      [2012,'countryA',33],
      [2013,'countryA',35],
      [2014,'countryA',40],
      [2012,'countryB',65],
      [2013,'countryB',70],
      [2014,'countryB',75],
      [2012,'countryC',15],
      [2013,'countryC',20],
      [2014,'countryC',25]
    ]);

    // sort by year
    lineChartData.sort([{column: 0}]);

    // get unique countries
    var countryGroup = google.visualization.data.group(
      lineChartData,
      [1]
    );

    // build country data table
    var countryData = new google.visualization.DataTable({
      cols: [
        {label: 'Year', type: 'number'},
      ]
    });

    // add column for each country
    for (var i = 0; i < countryGroup.getNumberOfRows(); i++) {
      countryData.addColumn(
        {label: countryGroup.getValue(i, 0), type: 'number'}
      );
    }

    // add row for each year / country
    var rowYear;
    var rowIndex;
    for (var i = 0; i < lineChartData.getNumberOfRows(); i++) {
      if (rowYear !== lineChartData.getValue(i, 0)) {
        rowYear = lineChartData.getValue(i, 0);
        rowIndex = countryData.addRow();
        countryData.setValue(rowIndex, 0, rowYear);
      }
      for (var x = 1; x < countryData.getNumberOfColumns(); x++) {
        if (countryData.getColumnLabel(x) === lineChartData.getValue(i, 1)) {
          countryData.setValue(rowIndex, x, lineChartData.getValue(i, 2));
        }
      }
    }

    // draw agg table
    new google.visualization.ChartWrapper({
      chartType: 'Table',
      containerId: 'table-div',
      dataTable: countryData
    }).draw();

    // draw line chart
    new google.visualization.ChartWrapper({
      chartType: 'LineChart',
      containerId: 'chart-div',
      dataTable: countryData
    }).draw();
  },
  packages: ['corechart', 'table']
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="table-div"></div>
<div id="chart-div"></div>

这篇关于动态地为Google折线图枢轴数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！