How to apply aggregations(sum,mean,max,min etc ) across columns in pydatatable?

user_9479925 0
Viewed 137

I have a datatable as,


DT_X = dt.Frame({
    
    'issue':['cs-1','cs-2','cs-3','cs-1','cs-3','cs-2'],
    
    'speech':[1,1,1,0,1,1],
    
    'narrative':[1,0,1,1,1,0],
    
    'thought':[0,1,1,0,1,1]
})

it can be viewed as,

Out[5]: 
   | issue  speech  narrative  thought
-- + -----  ------  ---------  -------
 0 | cs-1        1          1        0
 1 | cs-2        1          0        1
 2 | cs-3        1          1        1
 3 | cs-1        0          1        0
 4 | cs-3        1          1        1
 5 | cs-2        1          0        1

[6 rows x 4 columns]

I'm now applying a group operation summing of all the values across 3 columns as,

DT_X[:,{'speech': dt.sum(f.speech),
        'narrative': dt.sum(f.narrative),
        'thought': dt.sum(f.thought)},
        by(f.issue)]

It produces an output as,

Out[6]: 
   | issue  speech  narrative  thought
-- + -----  ------  ---------  -------
 0 | cs-1        1          2        0
 1 | cs-2        2          0        2
 2 | cs-3        2          2        2

[3 rows x 4 columns]

Here I'm manually giving the each field name and the aggregation function (dt.sum),as it is required only 3 columns i can easily carry this task out, but what if i have to work on more than 10, 20, etc etc fields?.

Would you have any other solution for it?.

Reference: we have a same kind of functionality in Rdatatable as :

DT[,lapply(.SD,sum),by=.(issue),.SDcols=c('speech','narrative','thought')]
py-datatable python
2 Answers

Most functions in datatable, including sum(), will automatically apply across all columns if given a multi-column set as an argument. Thus, R's lapply(.SD, sum) becomes simply sum(.SD), except that there is no .SD in python, instead we use the f symbol and combinations. In your case, f[:] will select all columns other than the groupby, so it is basically equivalent to .SD.

Secondly, all unary functions (i.e. functions that act on a single column, as opposed to binary functions like + or corr) pass-through the names of their columns. Thus, sum(f[:]) will produce a set of columns with the same names as in f[:].

Putting this all together:

>>> from datatable import by, sum, f, dt

>>> DT_X[:, sum(f[:]), by(f.issue)]
   | issue  speech  narrative  thought
-- + -----  ------  ---------  -------
 0 | cs-1        1          2        0
 1 | cs-2        2          0        2
 2 | cs-3        2          2        2

[3 rows x 4 columns]
user_9586240

Here is a one of recommended solution by @Erez.

DT_X[:,{name: dt.sum(getattr(f, name)) for name in ['speech', 'narrative', 'thought']},
by(f.issue)]

and output:-

Out[7]: 
   | issue  speech  narrative  thought
-- + -----  ------  ---------  -------
 0 | cs-1        1          2        0
 1 | cs-2        2          0        2
 2 | cs-3        2          2        2

[3 rows x 4 columns]
user_94799250
Related