Group Array Data on One Column and Sum Data from Another Column

Group rows of data and in each group sum one column and concatenate another column

I would create an intermediary array which groups into array keys by id first, then use that to call combinations of array_column() with array_sum() and implode() to produce your sum value and combined name string.

$temp_array = [];
foreach ($the_array as $init) {
// Initially, group them on the id key as sub-arrays
$temp_array[$init['id']][] = $init;
}

$result = [];
foreach ($temp_array as $id => $arr) {
// Loop once for each id ($temp_array has 2 elements from your sample)
// And add an element to $result
$result[] = [
'id' => $id,
// Sum the value subkeys
// array_column() returns just the key 'value' as an array
// array_sum() adds them
'value' => array_sum(array_column($arr, 'value')),
// implode the name subkeys into a string
'name' => implode(',', array_column($arr, 'name'))
];
}

print_r($result);
Array
(
[0] => Array
(
[id] => 1
[value] => 60
[name] => apple,orange,banana
)

[1] => Array
(
[id] => 2
[value] => 300
[name] => car,bicycle
)

)

Group array data on one column and sum data from another column

<?php

// array of bank structure
$banks = array();
$banks[] = array('name'=>'Bank BRI','amount'=>rand());
$banks[] = array('name'=>'Bank BRI','amount'=>rand());
$banks[] = array('name'=>'Bank BCA','amount'=>rand());
$banks[] = array('name'=>'Bank CIMB','amount'=>rand());
$banks[] = array('name'=>'Bank BRI','amount'=>rand());
$banks[] = array('name'=>'Bank CIMB','amount'=>rand());
$banks[] = array('name'=>'Bank BRI','amount'=>rand());
$banks[] = array('name'=>'Bank BNI','amount'=>rand());
$banks[] = array('name'=>'Bank CIMB','amount'=>rand());
$banks[] = array('name'=>'Bank BCA','amount'=>rand());
$banks[] = array('name'=>'Bank Mandiri','amount'=>rand());
$banks[] = array('name'=>'Bank BCA','amount'=>rand());
$banks[] = array('name'=>'Bank BCA','amount'=>rand());
$banks[] = array('name'=>'Bank Permata','amount'=>rand());

// begin the iteration for grouping bank name and calculate the amount
$amount = array();
foreach($banks as $bank) {
$index = bank_exists($bank['name'], $amount);
if ($index < 0) {
$amount[] = $bank;
}
else {
$amount[$index]['amount'] += $bank['amount'];
}
}
print_r($amount); //display

// for search if a bank has been added into $amount, returns the key (index)
function bank_exists($bankname, $array) {
$result = -1;
for($i=0; $i<sizeof($array); $i++) {
if ($array[$i]['name'] == $bankname) {
$result = $i;
break;
}
}
return $result;
}

php sum value group by other column in two dimensional array

Smart solution for PHP> = 7.4 with Null Coalesce Assignment ??= :

foreach($arr as $row){
$sum[$row['dog']] ??= 0;
$sum[$row['dog']] += $row['value'];
}

var_export($sum);
//array ( 'beagl' => 5, 'york' => 3, )

The same using an array class (you find the class here).

$sum = TableArray::create($arr)
->filterGroupAggregate(['value' => 'sum'],['dog'])
->fetchKeyValue('dog','value')
;

How to group and merge array entries and to sum-up values on multiple common (but not all) keys?

Just test both properties in the find() callback, and add both to the new object when pushing into acc.

const arr = [
{'ID':'1','Parent':'1','Member': '1','Code': '123','Subject': 'Org A','value': 0.3},
{'ID':'2','Parent':'1','Member': '1','Code': '124','Subject': 'Org A','value': 0.25},
{'ID':'3','Parent':'1','Member': '1','Code': '123','Subject': 'Org B','value': 0.45},
{'ID':'4','Parent':'1','Member': '2','Code': '125','Subject': 'Org A','value': 0.8},
{'ID':'5','Parent':'1','Member': '2','Code': '211','Subject': 'Org C','value': 0.3},
{'ID':'6','Parent':'1','Member': '3','Code': '221','Subject': 'Org B','value': 0.3},
{'ID':'7','Parent':'1','Member': '3','Code': '221','Subject': 'Org C','value': 0.25},
{'ID':'8','Parent':'1','Member': '3','Code': '234','Subject': 'Org A','value': 0.45},
{'ID':'9','Parent':'1','Member': '4','Code': '123','Subject': 'Org A','value': 0.8},
{'ID':'10','Parent':'2','Member': '5','Code': '123','Subject': 'Org D','value': 0.3},
{'ID':'11','Parent':'2','Member': '5','Code': '123','Subject': 'Org E','value': 0.3},
{'ID':'12','Parent':'2','Member': '6','Code': '125','Subject': 'Org E','value': 0.25},
{'ID':'13','Parent':'2','Member': '6','Code': '211','Subject': 'Org F','value': 0.45},
{'ID':'14','Parent':'2','Member': '6','Code': '221','Subject': 'Org F','value': 0.8},
{'ID':'15','Parent':'2','Member': '6','Code': '123','Subject': 'Org G','value': 0.3},
{'ID':'16','Parent':'3','Member': '7','Code': '124','Subject': 'Org H','value': 0.3},
{'ID':'17','Parent':'3','Member': '8','Code': '124','Subject': 'Org H','value': 0.25},
{'ID':'18','Parent':'3','Member': '9','Code': '123','Subject': 'Org I','value': 0.45},
{'ID':'19','Parent':'3','Member': '10','Code': '123','Subject': 'Org J','value': 0.8},
{'ID':'20','Parent':'3','Member': '10','Code': '211','Subject': 'Org I','value': 0.3},
{'ID':'21','Parent':'4','Member': '11','Code': '221','Subject': 'Org K','value': 0.3},
{'ID':'22','Parent':'4','Member': '11','Code': '234','Subject': 'Org K','value': 0.25},
{'ID':'23','Parent':'4','Member': '12','Code': '234','Subject': 'Org K','value': 0.45},
{'ID':'24','Parent':'4','Member': '12','Code': '123','Subject': 'Org L','value': 0.8},
{'ID':'25','Parent':'4','Member': '13','Code': '211','Subject': 'Org M','value': 0.3}
];

const summed = arr.reduce((acc, cur) => {
const item = acc.length > 0 && acc.find(({
Code, Parent
}) => Code === cur.Code && Parent == cur.Parent)
if (item) {
item.value += cur.value
} else acc.push({
Code: cur.Code,
Parent: cur.Parent,
value: cur.value
});
return acc
}, [])
console.log(arr); // not modified
console.log(summed)

Group rows on one column and create nested array from another column

First group elements to subarrays, then output each subarray:

$groups = [];
foreach($a as $value){
$groups[$value['id']][] = $value['reason'];
}

foreach ($groups as $key => $value) {
echo $key . ' | ' . implode(', ', $value);
}

How to sum values in one column based on values in other columns R?

library(dplyr); library(tidyr)

# this will give the count of each kind of response in its own column
df %>%
count(Question, Participant, Control) %>%
pivot_wider(names_from = Control, values_from = n)

#if you just want Yes's counted
df %>%
group_by(Question, Participant) %>%
summarize(Summed_Yes_Responses = sum(Control == 1, na.rm = TRUE))

Bigquery query to get sum of values of one column based on another column

Try this:

WITH sample AS (
SELECT * FROM UNNEST([
STRUCT('abc' AS company, 'http://www.abc.net1' AS link, 1 AS full_count),
('abc', 'http://www.abc.net1/page1', 2),
('abc', 'http://www.abc.net1/page1/folder1', 3),
('abc', 'http://www.abc.net1/page1/folder2', 4),
('abc', 'http://www.abc.net1/page2', 5),
('xyz', 'http://www.xyz.net1/', 6),
('xyz', 'http://www.xyz.net1/page1/', 7),
('xyz', 'http://www.xyz.net1/page1/file1', 8)
])
)
SELECT first.company, first.link, SUM(second.full_count) AS starts_with_count
FROM sample first, sample second
WHERE STARTS_WITH(second.link, first.link)
GROUP BY 1, 2
;

output:

Sample Image

How to find sum of arrays in a column which is grouped by another column values in a spark dataframe using scala

It is not very complicated. As you mention it, you can simply group by "c1" and aggregate the values of the array index by index.

Let's first generate some data:

val df = spark.range(6)
.select('id % 3 as "c1",
array((1 to 5).map(_ => floor(rand * 10)) : _*) as "Value")
df.show()
+---+---------------+
| c1| Value|
+---+---------------+
| 0|[7, 4, 7, 4, 0]|
| 1|[3, 3, 2, 8, 5]|
| 2|[2, 1, 0, 4, 4]|
| 0|[0, 4, 2, 1, 8]|
| 1|[1, 5, 7, 4, 3]|
| 2|[2, 5, 0, 2, 2]|
+---+---------------+

Then we need to iterate over the values of the array so as to aggregate them. It is very similar to the way we created them:

val n = 5 // if you know the size of the array
val n = df.select(size('Value)).first.getAs[Int](0) // If you do not
df
.groupBy("c1")
.agg(array((0 until n).map(i => sum(col("Value").getItem(i))) :_* ) as "Value")
.show()
+---+------------------+
| c1| Value|
+---+------------------+
| 0|[11, 18, 15, 8, 9]|
| 1| [2, 10, 5, 7, 4]|
| 2|[7, 14, 15, 10, 4]|
+---+------------------+


Related Topics



Leave a reply



Submit