Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Swapping values between two columns using data.table

Tags:

r

data.table

swap

I have been breaking my head over translating this question to a data.table solution. (to keep it simple I'll use the same data set)
When V2 == "b I want to swap the columns between V1 <-> V3.

dt <- data.table(V1=c(1,2,4), V2=c("a","a","b"), V3=c(2,3,1))
#V1 V2 V3
#1:  1  a  2
#2:  2  a  3
#3:  4  b  1

The code below would be the working solution for data.frame, however because of the amount of frustration this has given me because I was using a data.table without realising I'm now determined to find a solution for data.table.

dt <- data.table(V1=c(1,2,4), V2=c("a","a","b"), V3=c(2,3,1))
df <- as.data.frame(dt)
df[df$V2 == "b", c("V1", "V3")] <- df[df$V2 == "b", c("V3", "V1")] 
#  V1 V2 V3
#1  1  a  2
#2  2  a  3
#3  1  b  4

I have tried writing a lapply function looping through my target swapping list, tried to narrow down the problem to only replace one value, attempted to call the column names in different ways but all without success.
This was the closest attempt I've managed to get:

> dt[dt$V2 == "b", c("V1", "V3")] <- dt[dt$V2 == "b", c(V3, V1)]
#Warning messages:
#1: In `[<-.data.table`(`*tmp*`, dt$V2 == "b", c("V1", "V3"), value = c(1,  :
#  Supplied 2 items to be assigned to 1 items of column 'V1' (1 unused)
#2: In `[<-.data.table`(`*tmp*`, dt$V2 == "b", c("V1", "V3"), value = c(1,  :
#  Supplied 2 items to be assigned to 1 items of column 'V3' (1 unused)

How can we get the data.table solution?

like image 533
Bas Avatar asked Apr 25 '16 06:04

Bas


People also ask

How do you swap two columns in a table?

Move the mouse to the right edge of the column until your cursor changes to four arrows pointing in all directions. Left click on the edge of the column and hold the Shift key. Drag the column to the one you want to swap it with. You should see a '|' line indicating where the next column will be inserted.

How do I swap data between two columns in SQL?

SET Col1 = Col2, Col2 = Col1; When you run above update statement, the values of the columns will be swapped in SQL Server. There is no need for temporary column, variable or storage location in SQL Server.

How do I swap two columns in Excel?

Move or copy rows or columnsDrag the rows or columns to another location. Hold down OPTION and drag the rows or columns to another location. Hold down SHIFT and drag your row or column between existing rows or columns. Excel makes space for the new row or column.


1 Answers

We can try

dt[V2=="b", c("V3", "V1") := .(V1, V3)]
like image 119
akrun Avatar answered Oct 21 '22 08:10

akrun