What is the most efficient way to switch the locations of two columns in python datatable? I wrote the below function that does what I want, but this may not be the best way, especially if my actual table is big. Is it possible to do this in place? Am I missing something obvious?
from datatable import Frame
dat = Frame(a=[1,2,3],b=[4,5,6],c=[7,8,9])
def switch_cols(data,col1,col2):
data_n = list(data.names)
data_n[data.colindex(col1)], data_n[data.colindex(col2)] = data_n[data.colindex(col2)], data_n[data.colindex(col1)]
return data[:, data_n]
dat = switch_cols(dat, "c","a")
| c b a
| int32 int32 int32
-- + ----- ----- -----
0 | 7 4 1
1 | 8 5 2
2 | 9 6 3
[3 rows x 3 columns]
For comparison in R, we can do this
dat = data.table(a=c(1,2,3), b=c(4,5,6), c=c(7,8,9))
switch_cols <- function(data,col1,col2) {
indexes = which(names(dat) %in% c(col1,col2))
datn = names(dat)
datn[indexes] <- datn[c(indexes[2], indexes[1])]
return(datn)
}
Then, we can change the order of two columns in-place like this
setcolorder(dat, switch_cols(dat,"a","c"))
Please note that assigning the values to each column is not what I'm after here. Consider this example, in R. I construct a large data.table like this:
dat = data.table(
x = rnorm(10000000),
y = sample(letters, 10000000, replace = T)
)
I make two copies of this data.table d
and e
e = copy(dat)
d = copy(dat)
I then compare these two in-place operations
:=
re-assignment of the two columnsmicrobenchmark::microbenchmark(
list=alist("setcolorder" = setcolorder(d, c("y", "x")),
"`:=`" = e[,`:=`(x=y, y=x)]),
times=1)
Unit: microseconds
expr min lq mean median uq max neval
setcolorder 81.5 81.5 81.5 81.5 81.5 81.5 1
`:=` 53691.1 53691.1 53691.1 53691.1 53691.1 53691.1 1
As expected, setcolorder
is the right way to switch column locations in R data.table
. I'm looking for a similar approach in python.
After some consideration and timings, I'm finding that the best approach is this:
from datatable import Frame
dat = Frame(a=[1,2,3],b=[4,5,6],c=[7,8,9])
| a b c
| int32 int32 int32
-- + ----- ----- -----
0 | 1 4 7
1 | 2 5 8
2 | 3 6 9
[3 rows x 3 columns]
def switch_cols(data,col1,col2):
return data[:, [col1 if c==col2 else col2 if c==col1 else c for c in data.names]]
switch_cols(dat, "a","c")
| c b a
| int32 int32 int32
-- + ----- ----- -----
0 | 7 4 1
1 | 8 5 2
2 | 9 6 3
[3 rows x 3 columns]