Here is my take on this:
values
+-----+--+
|alpha|3 |
+-----+--+
|alpha|14|
+-----+--+
|alpha|7 |
+-----+--+
|beta |73|
+-----+--+
|delta|21|
+-----+--+
|delta|15|
+-----+--+
|delta|6 |
+-----+--+
|delta|33|
+-----+--+
|gamma|5 |
+-----+--+
|gamma|17|
+-----+--+
|omega|37|
+-----+--+
NB. Check the datatypes
datatype each values
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
|literal|floating|
+-------+--------+
NB. Not necessary but for readability, get the primary keys
[primarykeys=. ,{."1 values
+-----+-----+-----+----+-----+-----+-----+-----+-----+-----+-----+
|alpha|alpha|alpha|beta|delta|delta|delta|delta|gamma|gamma|omega|
+-----+-----+-----+----+-----+-----+-----+-----+-----+-----+-----+
NB. Again, not necessary but doing so for readability
[datavals=. ,>}."1 values
3 14 7 73 21 15 6 33 5 17 37
NB. Here is the actual computation (just using sum across and key)
primarykeys +/ /. datavals
24 73 75 22 37
NB. Here is the result formatted with the primary key column stitched to the
data
(~.primarykeys),. < every primarykeys +/ /. datavals
+-----+--+
|alpha|24|
+-----+--+
|beta |73|
+-----+--+
|delta|75|
+-----+--+
|gamma|22|
+-----+--+
|omega|37|
+-----+--+
r/Alex
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of PackRat
Sent: Saturday, June 06, 2009 5:41 AM
To: Programming forum
Subject: [Jprogramming] Summing subsets
I'm stumped again, seeking the collective wisdom. I'm sure this is a
very common situation for those of you who work with entered data and
thus has probably been solved already long ago:
I have a table where each row has a key and a value. The rows are in
sorted order by key, and each key occurs one or more times. My goal is
to create a new table where each key appears only once, and the values
for each key are summed into a single value associated with each key.
Here's an example:
Starting condition: End result:
(t1) (t2)
+-----+--+ +-----+--+
|alpha|3 | |alpha|24|
+-----+--+ +-----+--+
|alpha|14| |beta |73|
+-----+--+ +-----+--+
|alpha|7 | |delta|75|
+-----+--+ +-----+--+
|beta |73| |gamma|22|
+-----+--+ +-----+--+
|delta|21| |omega|37|
+-----+--+ +-----+--+
|delta|15|
+-----+--+
|delta|6 |
+-----+--+
|delta|33|
+-----+--+
|gamma|5 |
+-----+--+
|gamma|17|
+-----+--+
|omega|37|
+-----+--+
I've puttered around with some previous code I had written and/or found
or obtained elsewhere and came up with the following statements, which
may or may not be useful at all:
NB. count how many elements are within each cut of unique elements:
b1=. # ;.1 ~: t1
NB. values in second column (to be summed):
c1=. _999999 ". > 1 {"1 t1
NB. nub sieve (positions of each first unique key):
d1=. ~: t1
However, I don't know where to go from here. I had originally thought
that maybe b1 and c1 could be manipulated together in some way or that
+/ could be used in some way, but I couldn't figure out anything. It
sure seems as if something ought to be possible, since this would be
such a common occurrence with gathered data. Thanks in advance for
your assistance and insights!
Harvey
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
