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?
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.
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.
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.
We can try
dt[V2=="b", c("V3", "V1") := .(V1, V3)]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With