<div dir="ltr">Excellent question indeed. <div><br></div><div>The first quick comment I have is to always be aware that directly using the functions np.sum and np.mean in aggregation will be orders of magnitude faster than calling the `average()` function that was defined in the original post. That is because in those special cases the numpy `reduceat` method is called and everything gets done in the C layer. Thus Andrew's suggestion for a workaround in this case is the right way to go if the data tables are large.</div><div><br></div><div>About the more generalized problem of getting access to the other table columns within the aggregation function, that is unfortunately not possible in the current release code. I have an idea for doing this, which is now an astropy issue (<a href="https://github.com/astropy/astropy/issues/4513">https://github.com/astropy/astropy/issues/4513</a>).</div><div><br></div><div>As for what to do right now with astropy 1.1, the following illustrates how to do generalized aggregation in the way that is needed for this example. It will be relatively slow and possibly memory intensive, but if the tables are not huge that won't be a problem:</div><div><font face="monospace, monospace"><br></font></div><div><div><font face="monospace, monospace">from __future__ import division, print_function</font></div><div><font face="monospace, monospace">from astropy import table</font></div><div><font face="monospace, monospace">from astropy.table import Table</font></div><div><font face="monospace, monospace">from collections import OrderedDict</font></div><div><br></div><div><font face="monospace, monospace">t = Table([['a', 'a', 'a', 'b', 'b', 'c'],</font></div><div><font face="monospace, monospace"> [1, 2, 3, 4, 5, 6],</font></div><div><font face="monospace, monospace"> [2, 2, 1, 2, 1, 1]],</font></div><div><font face="monospace, monospace"> names=('name', 'value', 'weight'))</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace">grouped = t.group_by('name')</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace">def transform_table(tbl):</font></div><div><font face="monospace, monospace"> """</font></div><div><font face="monospace, monospace"> Generalized function that takes table ``tbl`` as input</font></div><div><font face="monospace, monospace"> and returns a new Table ``out``. Note that ``out`` does not</font></div><div><font face="monospace, monospace"> necessarily need to have the same types or columns.</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace"> The example is just the identity transform. Be aware that</font></div><div><font face="monospace, monospace"> in-place operations will affect the input table.</font></div><div><font face="monospace, monospace"> """</font></div><div><font face="monospace, monospace"> out = tbl</font></div><div><font face="monospace, monospace"> return out</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace">out_tables = []</font></div><div><font face="monospace, monospace">for group in grouped.groups:</font></div><div><font face="monospace, monospace"> out_tables.append(transform_table(group))</font></div><div><font face="monospace, monospace">result = table.vstack(out_tables)</font></div><div><font face="monospace, monospace">print('transform_table')</font></div><div><font face="monospace, monospace">print(result)</font></div><div><font face="monospace, monospace">print()</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace">def average_weighted(tbl, name):</font></div><div><font face="monospace, monospace"> col = tbl[name]</font></div><div><font face="monospace, monospace"> if name == 'weight':</font></div><div><font face="monospace, monospace"> value = col.sum()</font></div><div><font face="monospace, monospace"> else:</font></div><div><font face="monospace, monospace"> weight = tbl['weight']</font></div><div><font face="monospace, monospace"> value = (col * weight).sum() / weight.sum()</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace"> return value</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace">def transform_table_to_row(tbl, func):</font></div><div><font face="monospace, monospace"> """</font></div><div><font face="monospace, monospace"> Generalized function that takes table ``tbl`` as input</font></div><div><font face="monospace, monospace"> and returns a new table row as an OrderedDict. It applies</font></div><div><font face="monospace, monospace"> function ``func`` to each column.</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace"> The example computes the weighted average of each field (where</font></div><div><font face="monospace, monospace"> possible) assuming the weights are in column ``weight``.</font></div><div><font face="monospace, monospace"> """</font></div><div><font face="monospace, monospace"> out = OrderedDict()</font></div><div><font face="monospace, monospace"> for name in t.colnames:</font></div><div><font face="monospace, monospace"> try:</font></div><div><font face="monospace, monospace"> value = func(tbl, name)</font></div><div><font face="monospace, monospace"> except:</font></div><div><font face="monospace, monospace"> # If something went wrong just ignore (could not perform</font></div><div><font face="monospace, monospace"> # operation on this column).</font></div><div><font face="monospace, monospace"> pass</font></div><div><font face="monospace, monospace"> else:</font></div><div><font face="monospace, monospace"> out[name] = value</font></div><div><font face="monospace, monospace"> return out</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace">out_rows = []</font></div><div><font face="monospace, monospace">for group in grouped.groups:</font></div><div><font face="monospace, monospace"> out_rows.append(transform_table_to_row(group, average_weighted))</font></div><div><font face="monospace, monospace">result = Table(rows=out_rows)</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace">print('transform_table_to_row')</font></div><div><font face="monospace, monospace">print(result)</font></div><div><br></div></div><div>Code also at <a href="https://gist.github.com/taldcroft/12249ad7eeacbec12f44">https://gist.github.com/taldcroft/12249ad7eeacbec12f44</a>.</div><div><br></div><div>Cheers,</div><div>Tom</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Jan 20, 2016 at 7:47 AM, Andrew Hearin <span dir="ltr"><<a href="mailto:andrew.hearin@yale.edu" target="_blank">andrew.hearin@yale.edu</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi Evert,<div><br></div><div>Great question, I'm also really interested to hear the answer to this. I always use the built-in table aggregation functions when possible, but sometimes end up writing my own Numpy calculation for more complicated examples (using np.unique and/or np.searchsorted). </div><div><br></div><div>For computing a group-wise weighted average, there is a way you can recast your problem that allows you to use the existing astropy built-in: just create a new column that is the product of your second and third columns, ' and then use aggregate(average) in the normal way on this new column. </div><div><br></div><div>So I *think* that gives an answer to the specific example you gave, but it dodges the real question, which I am also interested to hear the experts weigh in on. </div><div><br></div><div>Andrew</div></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div class="h5">On Tue, Jan 19, 2016 at 11:52 PM, Evert Rol <span dir="ltr"><<a href="mailto:evert.rol@gmail.com" target="_blank">evert.rol@gmail.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5">Is there a way in an astropy table to run the TableGroups aggregate function on multiple columns at once?<br>
<br>
In this specific case, I'd like to group by names in one column, and then average the second column weighted by values in the third column.<br>
An example would be:<br>
<br>
from astropy.table import Table<br>
<br>
def average(col):<br>
# Manipulate multiple columns at once?<br>
return col.mean()<br>
<br>
t = Table([['a', 'a', 'a', 'b', 'b', 'c'],<br>
[1, 2, 3, 4, 5, 6],<br>
[2, 2, 1, 2, 1, 1]],<br>
names=('name', 'value', 'weight'))<br>
group = t.group_by('name')<br>
result = group.groups.aggregate(average)<br>
print(result)<br>
<br>
which gives<br>
<br>
name value weight<br>
---- ----- -------------<br>
a 2.0 1.66666666667<br>
b 4.5 1.5<br>
c 6.0 1.0<br>
<br>
which is not what I want.<br>
<br>
<br>
In Pandas, this can be done with apply() on a groupby object, since that passes the relevant subsection the dataframe as input to the function.<br>
So I can write:<br>
<br>
def average_pd(df):<br>
weight = df['weight']<br>
total = weight.sum()<br>
df['value'] *= weight / total<br>
df['value'] = df['value'].sum()<br>
df['weight'] = total # for info; not necessary<br>
return df.iloc[0] # ignore other rows: they are the same anyway<br>
<br>
df = t.to_pandas()<br>
result = df.groupby('name')[['value', 'weight']].apply(average_pd)<br>
print(result)<br>
<br>
which gives:<br>
<br>
value weight<br>
name<br>
a 1.800000 5<br>
b 4.333333 3<br>
c 6.000000 1<br>
<br>
and 'value' consists of weighted averages.<br>
<br></div></div>
(code also on <a href="https://urldefense.proofpoint.com/v2/url?u=https-3A__gist.github.com_evertrol_12955a5d98edf055a2f4&d=AwICAg&c=-dg2m7zWuuDZ0MUcV7Sdqw&r=AHkQ8HPUDwzl0x62ybAnwN_OEebPRGDtcjUPBcnLYw4&m=-BKjmG3hTRkdOfmFIKI5e3myB8cKiFHeJbTAhi3Zg5U&s=ix-QzeHis8ltaMFyVo3QvHpnQYri_s75MpTGsufcbqM&e=" rel="noreferrer" target="_blank">https://urldefense.proofpoint.com/v2/url?u=https-3A__gist.github.com_evertrol_12955a5d98edf055a2f4&d=AwICAg&c=-dg2m7zWuuDZ0MUcV7Sdqw&r=AHkQ8HPUDwzl0x62ybAnwN_OEebPRGDtcjUPBcnLYw4&m=-BKjmG3hTRkdOfmFIKI5e3myB8cKiFHeJbTAhi3Zg5U&s=ix-QzeHis8ltaMFyVo3QvHpnQYri_s75MpTGsufcbqM&e=</a> )<span class=""><br>
<br>
<br>
Perhaps I overlooked some documentation, but I can't find if this can be done in astropy.table. Or do I just need to approach this differently?<br>
Alternatively, should I convert & stick to Pandas for this type of functionality?<br>
<br>
<br>
Evert<br>
<br>
<br>
<br>
<br>
<br>
<br>
_______________________________________________<br>
AstroPy mailing list<br>
<a href="mailto:AstroPy@scipy.org" target="_blank">AstroPy@scipy.org</a><br>
</span><a href="https://urldefense.proofpoint.com/v2/url?u=https-3A__mail.scipy.org_mailman_listinfo_astropy&d=AwICAg&c=-dg2m7zWuuDZ0MUcV7Sdqw&r=AHkQ8HPUDwzl0x62ybAnwN_OEebPRGDtcjUPBcnLYw4&m=-BKjmG3hTRkdOfmFIKI5e3myB8cKiFHeJbTAhi3Zg5U&s=XZ616g8wR7LBzFglTQ8J2F-bDe6rE-HuXIrePKntv6w&e=" rel="noreferrer" target="_blank">https://urldefense.proofpoint.com/v2/url?u=https-3A__mail.scipy.org_mailman_listinfo_astropy&d=AwICAg&c=-dg2m7zWuuDZ0MUcV7Sdqw&r=AHkQ8HPUDwzl0x62ybAnwN_OEebPRGDtcjUPBcnLYw4&m=-BKjmG3hTRkdOfmFIKI5e3myB8cKiFHeJbTAhi3Zg5U&s=XZ616g8wR7LBzFglTQ8J2F-bDe6rE-HuXIrePKntv6w&e=</a><br>
</blockquote></div><br></div>
<br>_______________________________________________<br>
AstroPy mailing list<br>
<a href="mailto:AstroPy@scipy.org">AstroPy@scipy.org</a><br>
<a href="https://mail.scipy.org/mailman/listinfo/astropy" rel="noreferrer" target="_blank">https://mail.scipy.org/mailman/listinfo/astropy</a><br>
<br></blockquote></div><br></div>