How to sum two API JSON data ?

Hey saurav, try this out 😎

You can go to https://laravelplayground.com/ and paste this code to see it in action

<?php
/**
*
* method to convert api data to a better array format 
* Following is the sample code of transformed data

	[
    	date1 => [
        	productName1 => qty,
            productName2 => qty,
            productName3 => qty
        ],
        
        date2 => [...],
        ...
    ]
*
*/
function transformedData(array $data)
{
  	
    $transoformedData = [];

    $columns = $data[0];

    foreach(array_slice($data,1) as $row)
    {
    	$temp = [];

        foreach(array_slice($columns,1) as $i => $productName)
        {
            $temp[$productName] = $row[$i+1];
        }

        $transoformedData[$row[0]] = $temp;
    }
  
  	return $transoformedData;
}


/**
*
* method to combine two arrays by summing the values with common keys, if exist
*
*/
function sumArrays(array $a1, array $a2)
{
    $a3 = $a1;

    foreach($a2 as $k => $v) {
        if(array_key_exists($k, $a3)) {
        	$a3[$k] += $v;
        } else {
           	$a3[$k] = $v; 
        }
    }
  
  return $a3;
}


/**
*
* method to combine data came from two apis
*
*/
function combineAPIData($data1,$data2)
{
  	//transform data
	$data1 = transformedData($data1);
	$data2 = transformedData($data2);

	
	$sumData = $data1;

	foreach($data2 as $date => $sales)
	{
	  	$sumData[$date] = (array_key_exists($date,$sumData))
    	  	? sumArrays($sumData[$date], $sales)
	      	: $sales;
	
	}
  
  	return $sumData;
}



/****** TRY IT OUT *******/


// sample data from 1st api
$data1 = [
    ["Month","Apple","Orange","Guava","Banana"],
    ["Nov-16", 9 , 11 , 6, 10],
    ["Nov-18 " , 5 , 0 , 0, 4],
    ["Nov-13 " , 8 , 2 , 10, 2]
];

// sample data from 2nd api
$data2 = [
    ["Month", "Apple", "Banana", "Guava"],
    ["Nov-16" , 4  , 7 , 0], 
    ["Nov-13 " , 12 , 8 , 9],
    ["Nov-15 " , 12 , 8 , 19]
]; 


dd(combineAPIData($data1, $data2));

/* sample output


array:4 [
  "Nov-16" => array:4 [
    "Apple" => 13
    "Orange" => 11
    "Guava" => 6
    "Banana" => 17
  ]
  "Nov-18 " => array:4 [
    "Apple" => 5
    "Orange" => 0
    "Guava" => 0
    "Banana" => 4
  ]
  "Nov-13 " => array:4 [
    "Apple" => 20
    "Orange" => 2
    "Guava" => 19
    "Banana" => 10
  ]
  "Nov-15 " => array:3 [
    "Apple" => 12
    "Banana" => 8
    "Guava" => 19
  ]
]
**/

@gitwithravish OP is seeking for a JavaScript solution. (see the channel next to the post title)

@rodrigo.pedra Oh damn, I was so much into the logic building that I did not even notice it. Thanks 😅

Will post a javascript code once I sit on my desk.

(Dare anyone convert my logic into javascript 😡😄)

My take in JavaScript:

var data1 = {
    'Product sales': [
        ['Month', 'Apple', 'Banana', 'Guava'],
        ['Nov-16', 9, 11, 6],
        [' Nov-18 ', 5, 0, 0]
    ]
};

var data2 = {
    'Product sales': [
        ['Month', 'Apple', 'Banana', 'Guava'],
        ['Nov-16', 4, 7, 0],
        [' Nov-20 ', 12, 8, 9]
    ]
};

const normalize = function (data) {
    if (data.length < 1) return [];

    const headers = data[0];

    return data.slice(1).map(function (record) {
        return headers.reduce(function (result, key, index) {
            result[key] = record[index];

            return result;
        }, {});
    });
};

const denormalize = function (data, field) {
    const entries = Object.entries(data);

    if (entries.length === 0) return [];

    const header = [field, ...Object.keys(entries[0][1])]

    const values = entries.map(function ([key, values]) {
        return [key, ...Object.values(values)];
    })

    return [header, ...values];
};

const consolidate = function (field, groupBy, ...sources) {
    const summary = sources
        .flatMap(function (source) {
            return normalize(source[field]);
        })
        .reduce(function (dictionary, record) {
            const key = record[groupBy];
            delete record[groupBy];

            const current = dictionary[key];

            if (!current) {
                dictionary[key] = record;

                return dictionary;
            }

            dictionary[key] = Object.entries(record).reduce(function (current, [field, value]) {
                if (!current[field]) {
                    current[field] = 0;
                }

                current[field] += Number(value);

                return current;
            }, current);

            return dictionary;
        }, {});

    return {[field]: denormalize(summary, groupBy)};
};

console.log(consolidate('Product sales', 'Month', data1, data2));

Output:

{
  'Product sales': [
    [ 'Month', 'Apple', 'Banana', 'Guava' ],
    [ 'Nov-16', 13, 18, 6 ],
    [ ' Nov-18 ', 5, 0, 0 ],
    [ ' Nov-20 ', 12, 8, 9 ]
  ]
}

Updated to take account for different columns in each dataset, including different column order.

Note the difference in the example input:

var data1 = {
    'Product sales': [
        ['Month', 'Apple', 'Banana', 'Guava', 'Potato'],
        ['Nov-16', 9, 11, 6, 5],
        [' Nov-18 ', 5, 0, 0, 15]
    ]
};

var data2 = {
    'Product sales': [
        ['Month', 'Apple', 'Guava', 'Banana', 'Tomato'],
        ['Nov-16', 4, 0, 7, 10],
        [' Nov-20 ', 12, 9, 8, 20]
    ]
};

const normalize = function (data) {
    if (data.length < 1) return [];

    const headers = data[0];

    return data.slice(1).map(function (record) {
        return headers.reduce(function (result, key, index) {
            result[key] = record[index];

            return result;
        }, {});
    });
};

const denormalize = function (data, field) {
    const entries = Object.entries(data);

    if (entries.length === 0) return [];

    const header = [field, ...Object.keys(entries[0][1])];

    const values = entries.map(function ([key, values]) {
        return [key, ...Object.values(values)];
    });

    return [header, ...values];
};

const extractFields = function (dataset, except) {
    return dataset
        .flatMap(function (record) {
            return Object.keys(record);
        })
        .map(String)
        .filter(function (field) {
            return field !== String(except);
        })
        .filter(function (field, index, fields) {
            return fields.indexOf(field) === index;
        })
        .sort(function (field, other) {
            return field.localeCompare(other);
        });
};

const consolidate = function (field, groupBy, ...sources) {
    const dataset = sources.flatMap(function (source) {
        return normalize(source[field]);
    });

    const fields = extractFields(dataset, groupBy);

    const summary = dataset.reduce(function (dictionary, record) {
        const key = record[groupBy];

        delete record[groupBy];

        dictionary[key] = fields.reduce(function (current, field) {
            if (!current[field]) {
                current[field] = 0;
            }

            current[field] += Number(record[field] || 0);

            return current;
        }, current = dictionary[key] || {});

        return dictionary;
    }, {});

    return {[field]: denormalize(summary, groupBy)};
};

console.log(consolidate('Product sales', 'Month', data1, data2));

@gitwithravish @rodrigo.pedra hey, thanks a lot for responding. I have not tried your solution but I will try and give you a message whether it will work or not

You're welcome! Hope it work out for you, or at least inspire you towards to the solution

Error coming with

Cannot read property 'Product sales' of undefined

@rodrigo.pedra

all of my data are dynamic even Product name and date. I just need to sum the data when the product name and date matches

@rodrigo.pedra

return [header, ...values];
what I should have to put in value?

like I have created two-variable productSales and productSales1.All data from the first URL are in productSales and data from the second URL are in productSales1 . I am just a beginner. Please don't be mad at me

@saurav77

of course data is expected to be dynamic, that is why the consolidate function accepts every moving part as a parameter.

If you copy the code from both my responses and paste on your browser's console both will work.

Show how are you calling the consolidate function. Including how any variable passed as a parameter is previously set.

For example, in your code you can have something like this:

Promise.all([
    fetch('http://127.0.0.1:8000/api/productSales').then(function (response) {
        return response.json();
    }),
    fetch('http://127.0.0.1:8000/api/productRe-Sales').then(function (response) {
        return response.json();
    }),
]).then(function (sources) {
    console.log(consolidate('Product sales', 'Month', ...sources));
});

The code samples were meant to illustrate how to handle the hard part (consolidate, map, filter, etc...)

If your data is not keyed by 'Product sales', use a different parameter then.

If your data has a different shape, use the code samples as a starting point and modify them to fit this different shape.

Not mad at all =)

What happens when you try this?

// ... previous function definitions

// comment this out
// console.log(consolidate('Product sales', 'Month', data1, data2));

console.log(consolidate('Product sales', 'Month', productSales, productSales1));

@rodrigo.pedra

let url="http://127.0.0.1:8000/api/productSales";
 fetch(url)
            .then(response => response.json())
            .then(data => {
                console.log(data.product-sales);
                google.charts.load('current', {'packages': ['line']});
                google.charts.setOnLoadCallback(drawSalesChart);
        });
let url1="http://127.0.0.1:8000/api/productRe-Sales";
 fetch(url1)
            .then(response => response.json())
            .then(data => {
                console.log(data.product-sales);
        });

I had fetch data like this. Is it the right way?

@rodrigo.pedra okay I will take time to use this method.

I am not at my computer anymore, but you can try tweaking to use the Promise.all from my example above and then moving the google chart part to the callback inside the Promise.all().then(...) part.

Hope it helps.

Maybe @gitwithravish can help further, I will be away for a while

@rodrigo.pedra it didn't work. Maybe I did not understand. I didn't do anything. I just copied and paste only your code to see whether it is working or not. Is there anything I am missing?

let url="http://127.0.0.1:8000/api/productSales";
let url1="http://127.0.0.1:8000/api/productRe-Sales";

Promise.all([
            fetch(url).then(function (response) {
                return response.json();
            }),
            fetch(url1).then(function (response) {
                return response.json();
            }),
        ]).then(function (sources) {
      const normalize = function (data) {
                if (data.length < 1) return [];

                const headers = data[0];

                return data.slice(1).map(function (record) {
                    return headers.reduce(function (result, key, index) {
                        result[key] = record[index];

                        return result;
                    }, {});
                });
            };
            const denormalize = function (data, field) {
                const entries = Object.entries(data);

                if (entries.length === 0) return [];

                const header = [field, ...Object.keys(entries[0][1])];

                const values = entries.map(function ([key, values]) {
                    return [key, ...Object.values(values)];
                });

                return [header, ...values];
            };

            const extractFields = function (dataset, except) {
                return dataset
                    .flatMap(function (record) {
                        return Object.keys(record);
                    })
                    .map(String)
                    .filter(function (field) {
                        return field !== String(except);
                    })
                    .filter(function (field, index, fields) {
                        return fields.indexOf(field) === index;
                    })
                    .sort(function (field, other) {
                        return field.localeCompare(other);
                    });
            };
            const consolidate = function (field, groupBy, ...sources) {
                const dataset = sources.flatMap(function (source) {
                    return normalize(source[field]);
                });

                const fields = extractFields(dataset, groupBy);

                const summary = dataset.reduce(function (dictionary, record) {
                    const key = record[groupBy];

                    delete record[groupBy];

                    dictionary[key] = fields.reduce(function (current, field) {
                        if (!current[field]) {
                            current[field] = 0;
                        }

                        current[field] += Number(record[field] || 0);

                        return current;
                    }, current = dictionary[key] || {});

                    return dictionary;
                }, {});

                return {[field]: denormalize(summary, groupBy)};
            }
          })

Hi @saurav77

First:

Did you ever tried copying my code, pasting into the browser console, and hitting enter to see the output?

Keeping saying "it doesn't work" does not make clear which part is not working.

Second:

From your other responses it became clearer what you want, you want to consume an endpoint and plot a google chart.

Don't take me wrong, but in your opening post you just asked how to merge two data structures.

Can you show a code sample where you got it working for a single data source?

I mean the whole process:

• fetching a single external data
• creating a google chart

But a code where you can assert it is working. I am not familiar with google charts, and maybe you were using another way to fetch your data, so I will try to adapt my answer to your working code sample.

Please, it is really important that you answer the first question, don't skip the first question (if you tested the code I shared as-is before trying to use mix it into your work).

Thanks in advance.

So I spin a new laravel app to test it out:

1 - Routes

My ./routes/web.php file content

<?php

use Illuminate\Support\Facades\Route;

Route::get('/api/productSales', function () {
    $data = [
        'Product sales' => [
            ['Month', 'Apple', 'Banana', 'Guava'],
            ['Nov-16', 9, 11, 6],
            [' Nov-18 ', 5, 0, 0],
        ],
    ];

    return response()->json($data);
});

Route::get('/api/productRe-Sales', function () {
    $data = [
        'Product sales' => [
            ['Month', 'Apple', 'Banana', 'Guava'],
            ['Nov-16', 4, 7, 0],
            [' Nov-20 ', 12, 8, 9],
        ],
    ];

    return response()->json($data);
});

// this will render the blade file at 
// ./resources/views/test.blade.php
Route::view('/', 'test');

2 - JavaScript Chart utilities

I created a JavaScript file called chart-utils.js and placed only the functions from my sample code there.

I tweaked the consolidate function to not need the 'Product Sales' parameter so it can be used more easily.

I placed this file at ./public/js/chart-utils.js

const normalize = function (data) {
    if (data.length < 1) return [];

    const headers = data[0];

    return data.slice(1).map(function (record) {
        return headers.reduce(function (result, key, index) {
            result[key] = record[index];

            return result;
        }, {});
    });
};

const denormalize = function (data, field) {
    const entries = Object.entries(data);

    if (entries.length === 0) return [];

    const header = [field, ...Object.keys(entries[0][1])];

    const values = entries.map(function ([key, values]) {
        return [key, ...Object.values(values)];
    });

    return [header, ...values];
};

const extractFields = function (dataset, except) {
    return dataset
        .flatMap(function (record) {
            return Object.keys(record);
        })
        .map(String)
        .filter(function (field) {
            return field !== String(except);
        })
        .filter(function (field, index, fields) {
            return fields.indexOf(field) === index;
        })
        .sort(function (field, other) {
            return field.localeCompare(other);
        });
};

const consolidate = function (groupBy, ...sources) {
    const dataset = sources.flatMap(function (source) {
        return normalize(source);
    });

    const fields = extractFields(dataset, groupBy);

    const summary = dataset.reduce(function (dictionary, record) {
        const key = record[groupBy];

        delete record[groupBy];

        dictionary[key] = fields.reduce(function (current, field) {
            if (!current[field]) {
                current[field] = 0;
            }

            current[field] += Number(record[field] || 0);

            return current;
        }, current = dictionary[key] || {});

        return dictionary;
    }, {});

    return denormalize(summary, groupBy);
};

3 - Blade template

This is my blade template placed on ./resources/views/test.blade.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Sales Chart</title>

    <script src="https://www.gstatic.com/charts/loader.js"></script>
    <script src="{{ asset('/js/chart-utils.js') }}"></script>
</head>
<body>
<div id="sales-chart"></div>

<script>
google.charts.load('current', {'packages': ['line']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
    Promise.all([
        fetch('/api/productSales').then((response) => response.json()),
        fetch('/api/productRe-Sales').then((response) => response.json()),
    ]).then(function (sources) {
        return consolidate('Month', ...sources.map((source) => source['Product sales']));
    }).then(function (dataset) {
        var data = google.visualization.arrayToDataTable(dataset);

        var options = {
            chart: {
                title: 'Sales chart',
                subtitle: 'consolidated'
            },
            width: 900,
            height: 500
        };

        var chart = new google.charts.Line(document.getElementById('sales-chart'));
        chart.draw(data, google.charts.Line.convertOptions(options));
    });
}
</script>
</body>
</html>

4 - Testing

Then I run in console/terminal:

php artisan serve

And navigated to http://127.0.0.1:8000/

5 - Results

Full image resolution at: https://ibb.co/dQBjzL6

Hope it helps

I can’t thank you enough. I appreciate your help. Thanks a million.

You're welcome, have a nice day =)

@rodrigo.pedra Can I ask you one more question if you don't mind?

@rodrigo.pedra Is there anyway so I can sort these data according to monthly

["Month", "Apple", "Banana", "Mango"]
1: (4) ["Apr-08", 30, 0, 0]
2: (4) ["Apr-16", 26, 0, 21]
3: (4) ["Jul-08", 16.25, 9.25, 0]
4: (4) ["Feb-07", 58, 0, 0]
5: (4) ["Feb-08", 11, 0, 0]
6: (4) ["Jun-07", 4, 0, 0]
7: (4) ["Jul-07", 2.25, 0, 0]

@saurav77 Why do you change the best reply to yourself?! Because @rodrigo.pedra didn't reply to you?

@michaloravec No, it was by mistake. I just have an account friend of mine. I copy this mine into a friend account.I accidentally click.I apologize

@michaloravec No, it was just a mistake. I just ask my problem mine in my friend's account. I accidentally click. I apologize

As the month on your data is not normalized, due to:

• Some of your samples have spaces around the month data
• Month are represented by name, so August would be sorted before January, as A comes first than J in alphabetical order
• Years are represented in two digits, not a big deal in recent years, but on the century turn it was a problem as 01 comes before 99 for example
• Months come before year, so when sorting alphabetically all "January"s would be grouped together and so on

Sorting by month requires a bit of data handling, but sure is possible.

Adding to my last code sample

google.charts.load('current', {'packages': ['line']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
    // helps converting month names to an integer
    const MONTHS = ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'];

    Promise.all([
        fetch('/api/productSales').then((response) => response.json()),
        fetch('/api/productRe-Sales').then((response) => response.json()),
    ]).then(function (sources) {
        return consolidate('Month', ...sources.map((source) => source['Product sales']));
    }).then(function (dataset) {
        // separating headers and records (we want to keep headers first
        const [headers, ...records] = dataset;

        const sorted = records
            // normalizing month
            .map(function (record) {
                const [month, year] = String(record[0]).trim().toLowerCase().split('-');

                return {month: MONTHS.indexOf(month), year: Number(year), record};
            })
            // sorting
            .sort(function (oneRecord, otherRecord) {
                if (oneRecord.year === otherRecord.year) {
                    return otherRecord.month - otherRecord.month;
                }

                return oneRecord.year - otherRecord.year;
            })
            // getting only the data back
            .map(({record}) => record);
        
        // reuniting headers and records, now sorted
        return [headers, ...sorted];
    }).then(function (dataset) {
        var data = google.visualization.arrayToDataTable(dataset);

        var options = {
            chart: {
                title: 'Sales chart',
                subtitle: 'consolidated'
            },
            width: 900,
            height: 500
        };

        var chart = new google.charts.Line(document.getElementById('sales-chart'));
        chart.draw(data, google.charts.Line.convertOptions(options));
    });
}

Hope it helps.