How to filter NA values of group of columns in pydatatable?

user_9479925 0
Viewed 85

I have created a datatable with 3 different group of observations as,

DT_EX= dt.Frame({
    'country':['a','a','a','a','b','b','c','c'],
    'id':[3,3,3,3,4,4,4,4],
    'shop':['dmart','dmart','dmart','dmart','amzn','amzn','amzn','amzn'],
    'beef':[23,None,None,None,93,None,None,None],
    'eggs':[None,33,None,None,None,103,None,None],
    'fork':[None,None,10,None,None,None,210,None],
    'veg':[None,None,None,40,None,None,None,340]
})

It's output as,

   | country  id  shop   beef  eggs  fork  veg
-- + -------  --  -----  ----  ----  ----  ---
 0 | a         3  dmart    23    NA    NA   NA
 1 | a         3  dmart    NA    33    NA   NA
 2 | a         3  dmart    NA    NA    10   NA
 3 | a         3  dmart    NA    NA    NA   40
 4 | b         4  amzn     93    NA    NA   NA
 5 | b         4  amzn     NA   103    NA   NA
 6 | c         4  amzn     NA    NA   210   NA
 7 | c         4  amzn     NA    NA    NA  340

[8 rows x 7 columns]

Now i have applied a recommended grouping on first 3 fields of datatable, aggregated(sum) of other remaining columns(beef,eggs,fork,veg) as

In [5]: DT_EX[:,first(f[:3]).extend(sum(f[3:])),by(f[:3])]

It's output as-

Out[5]: 
   | country  id  shop   country.0  id.0  shop.0  beef  eggs  fork  veg
-- + -------  --  -----  ---------  ----  ------  ----  ----  ----  ---
 0 | a         3  dmart  a             3  dmart     23    33    10   40
 1 | b         4  amzn   b             4  amzn      93   103     0    0
 2 | c         4  amzn   c             4  amzn       0     0   210  340

[3 rows x 10 columns]

Here it gives a correct output, but it's adding duplicate columns, and another observation is that its filling NA values with 0, it can be found on C observation.

Here i did some workaround as

In [7]: DT_EX[:,first(f[:3]).extend(sum(f[3:])),by(f[:3])][:,f[:].remove(f[3:6])]

and its output as,

Out[7]: 
   | country  id  shop   beef  eggs  fork  veg
-- + -------  --  -----  ----  ----  ----  ---
 0 | a         3  dmart    23    33    10   40
 1 | b         4  amzn     93   103     0    0
 2 | c         4  amzn      0     0   210  340

But i think it's not a feasible solution as i always have to specify which columns to be hidden in .remove function,

Would you have any other ideas/suggestions on it ?.

py-datatable python
1 Answers

You can use:

DT_EX[:, dt.sum(f[:].remove(f[:3])), by(f[:3])]

However it still feels kind of awkward. I would expect to just use dt.sum(f[:]) and have the columns in by removed automatically. Not sure if there's a different syntax.

Edit:

As Pasha pointed out in the comments, in this case it is simpler to use:

DT_E[:, dt.sum(f[3:]), by=f([:3])]
user_89983100
Related