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:
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
How to Stop My PHP Form Resubmitting Input After Page Refresh
Showing Image Binary Data Using JavaScript
How to Resolve MySQL Port 3306 Error on Wamp
Combining 'Where' and 'Like' Statements by Using the Ci Activerecords
How to Update Multiple Select Box Values in PHP MySQL
Assign Jquery Value to PHP Variable Inside Ajax Sussess
How to Sends Two Values in Select Dropdown Using PHP
Open Modal from Another Page Laravel
Auto Update Cart on Quantity Change in Woocommerce Cart Page
Send File Via Curl from Form Post in PHP
404 Not Found When Passing Variable to Route in Laravel
Retrieve MySQL Value from PHP for Radio Buttons
Remove Useless Zero Digits from Decimals in PHP
404 Not Found; Apache Server Can't Load Simple PHP File
Php Session Lost After Redirect
Build a Tree from a Flat Array in PHP