How do I select all columns excluding one (or two) from a datatable in python

user_2146894 0
Viewed 465

In R data.table, I can exclude columns like so

library(data.table)

foo <- data.table(x = c(1,2,3), y = c(4, 5, 6), z = c(7, 8, 9))
print(foo)
   x y z
1: 1 4 7
2: 2 5 8
3: 3 6 9

# exclude one column
foo[, !"x"]
   y z
1: 4 7
2: 5 8
3: 6 9

# exclude two columns
foo[, !c("x", "y")]
   z
1: 7
2: 8
3: 9

How do I do the same thing in Python datatable?

import datatable as dt # v 1.0.0

foo = dt.Frame({'x': [1,2,3], 'y': [4,5,6], 'z': [7,8,9]})
print(foo)
   |     x      y      z
   | int32  int32  int32
-- + -----  -----  -----
 0 |     1      4      7
 1 |     2      5      8
 2 |     3      6      9
[3 rows x 3 columns]

# exclude one column
foo[:, !"x"]    # error
foo[:, !["x"]]  # error
foo[:, !f.x]    # error

# exclude two columns

EDIT

Apologies for not explicitly stating this, but I'm aware of the obvious solution - do an inclusion statement, whereby I build a list of all the columns I want to include and use that as opposed to an exclusion statement whereby I use a list of columns to exclude. However, I find the inclusion technique awkward / cumbersome and less natural to read and write. As such, I'm specifically seeking an exclusion solution, like the one implemented in data.table.

py-datatable data.table r python
3 Answers

These methods are listed in the documentation on how to Deselect rows/columns.

List Building Methods

A comprehension on names to filter out values:

cols = ["x"]
foo_filtered = foo[:, [name for name in foo.names if name not in cols]]

Or the filter equivalent:

cols = ["x"]
foo_filtered = foo[:, list(filter(lambda n: n not in cols, foo.names))]

With a list of booleans:

cols = ["x"]
foo_filtered = foo[:, [n not in cols for n in foo.names]]

or the map equivalent:

cols = ["x"]
foo_filtered = foo[:, list(map(lambda n: n not in cols, foo.names))]

foo_filtered:

   |     y      z
   | int32  int32
-- + -----  -----
 0 |     4      7
 1 |     5      8
 2 |     6      9
[3 rows x 2 columns]

Exclusion Method

remove can also be used, however, this is limited to column selection:

from datatable import f


foo[:, f[:].remove(f['x', 'y'])]

   |     z
   | int32
-- + -----
 0 |     7
 1 |     8
 2 |     9
[3 rows x 1 column]

This method does not come from the documentation.

Sub-Class Method

If obscuring the list comprehension is the goal, we can make a wrapper for tuple that can be used to add additional behaviour to names. Namely, using xor (or other desired operation) to become the "exclusion operator".

(I've chosen xor because pandas columns used to allow this operator to exclude columns.)

This custom tuple can then be used with a sub-class of dt.Frame to wrap the property names:

from __future__ import annotations

import datatable as dt  # v 1.0.0


class TableColumns(tuple):
    def __xor__(self, other) -> TableColumns:
        if isinstance(other, str):
            output = [n != other for n in self]
        elif any(isinstance(other, i) for i in [tuple, list, set]):
            output = [n not in other for n in self]
        else:
            raise TypeError(
                f'Unsupported type {type(other)} used to filter names'
            )
        return TableColumns(output)


class MyFrame(dt.Frame):
    @property
    def names(self) -> TableColumns:
        return TableColumns(super(MyFrame, self).names)

Then filtering can be done naturally:

Frame Constructor (Using Sub-Class):

foo = MyFrame({'x': [1, 2, 3], 'y': [4, 5, 6], 'z': [7, 8, 9]})

Filtering With List:

foo_filtered = foo[:, foo.names ^ ['x', 'z']]

Tuple:

foo_filtered = foo[:, foo.names ^ ('x', 'z')]

Set:

foo_filtered = foo[:, foo.names ^ {'x', 'z'}]

foo_filtered:

   |     y
   | int32
-- + -----
 0 |     4
 1 |     5
 2 |     6
[3 rows x 1 column]

Excluding a single column with str:

foo_filtered = foo[:, foo.names ^ 'x']

   |     y      z
   | int32  int32
-- + -----  -----
 0 |     4      7
 1 |     5      8
 2 |     6      9
[3 rows x 2 columns]
user_154978880

A possible solution using list comprehensions:

>>> cols_to_exclude = ['x']
>>> print(foo[:, [col for col in foo.names if col not in cols_to_exclude]])
   |     z      y
   | int32  int32
-- + -----  -----
 0 |     7      4
 1 |     8      5
 2 |     9      6
[3 rows x 2 columns]
user_62751030

You can select data using booleans in datatable, so a list comprehension to generate the desired booleans for the column slection can be done.

In this case that would be foo[:, ["x" not in name for name in foo.names]]

>>> import datatable as dt
>>> foo = dt.Frame({'x': [1,2,3], 'y': [4,5,6], 'z': [7,8,9]})
>>> foo[:, ["x" not in name for name in foo.names]]
   |     y      z
   | int32  int32
-- + -----  -----
 0 |     4      7
 1 |     5      8
 2 |     6      9
[3 rows x 2 columns]
user_143549780
Related