I have a following dataframe:
+--------+------+---------+---------+
| Col1 | col2 | values1 | Values2 |
+--------+------+---------+---------+
| item1 | A1 | 5 | 11 |
| item1 | A2 | 5 | 25 |
| item1 | A3 | 5 | 33 |
| item1 | na | | 18 |
| item2 | A1 | 6 | 12 |
| item2 | A2 | 6 | 26 |
| item2 | A3 | 6 | 34 |
| item2 | na | 6 | |
+--------+------+---------+---------+
which can be created with this code
df = Seq(
(item1, A1,5 ,11),
(item1, A2,5 ,25),
(item1, A3,5 ,33),
(item1, na,0,18),
(item2, A1,6 ,12),
(item2, A2,6 ,26),
(item2, A3,6 ,34),
(item2, na,6 ,0)).toDF('Col1', 'col2', 'values1', 'Values2');
I want to skip the adding of column values1 for all the records when doing rollup or cube on it.
My Desired OutPut:
+-------+------+---------+---------+
| Col1 | col2 | values1 | values2 |
+-------+------+---------+---------+
| null | null | 17 | 159 |
| item1 | null | 5 | 87 |
| item1 | A1 | 5 | 11 |
| item1 | A2 | 5 | 25 |
| item1 | A3 | 5 | 33 |
| item1 | na | 0 | 18 |
| item2 | null | 12 | 72 |
| item2 | A1 | 6 | 12 |
| item2 | A2 | 6 | 26 |
| item2 | A3 | 6 | 34 |
| item2 | na | 6 | |
+-------+------+---------+---------+
How can I get a rollup or cube Function applied to this dataset so that sum of values1 to Col1 should sum up the values for either (A1/A2/A3)+na= so for eg:
the second row shows
values1 =5= 5+0 and values2= 87=11+25+33+18 and the 6th row values1 =12=6+6 and values2 =12+26+34+0=72
But what I get now by doing the rollup operation is Adds up all the agg which I don't want to happen for values1 column.
df.rollup("Col1","col2").agg(sum("values1") as "values1",sum("values2") as "values2");
Current Output:
+-------+------+---------+---------+
| Col1 | col2 | values1 | values2 |
+-------+------+---------+---------+
| null | null | 39 | 159 |
| item1 | null | 15 | 87 |
| item1 | A1 | 5 | 11 |
| item1 | A2 | 5 | 25 |
| item1 | A3 | 5 | 33 |
| item1 | na | 0 | 18 |
| item2 | null | 24 | 72 |
| item2 | A1 | 6 | 12 |
| item2 | A2 | 6 | 26 |
| item2 | A3 | 6 | 34 |
| item2 | na | 6 | |
+-------+------+---------+---------+
(The link which was posted as dup is not the actual ask here. The desired output is different than the answers in the link )
