Switching values of columns by condition

Question

I would like to rearrange the positions of certain cells in my Excel spreadsheet using R. There are two columns, place_x and place_y, where the coordinates are not correct. I intend to swap some values in these two columns to ensure cleanliness. I want to swatch, line by line, the value of the cell of column"place_x" with the cell of "place_y" when the cell of "place_x" is above 30.

Below is an example of my Excel spreadsheet that I want to change : enter image description here

I tried this...

#######DEFINITION DU WORKING DIRECTORY#####

# Installer la bibliothèque readxl si ce n'est pas déjà fait
install.packages("Rtools")
install.packages("readxl")
install.packages("openxlsx")


# Charger la bibliothèque openxlsx
library(openxlsx)

# Charger la bibliothèque readxl
library(readxl)

# Spécifier le chemin du fichier Excel
data <- read_excel("Example.xlsx")

# Condition to check if place_x is greater than 30
condition <- data$place_x > 30

# Swap values of place_x and place_y where the condition is met
temp <- data[condition, "place_x"]
data[condition, "place_x"] <- data[condition, "place_y"]
data[condition, "place_y"] <- temp

# Write the result to a new Excel file
output_file <- "INVERSE.xlsx"
write.xlsx(data, file = output_file)

And I would like to have this at the end :

enter image description here

So what exactly is your question here? It's easier to help you if you include a simple reproducible example with sample input and desired output that can be used to test and verify possible solutions. — MrFlick
– MrFlick, Commented Mar 13, 2024 at 14:10
I think the code you provided should be working. What result do you get? — ihecker
– ihecker, Commented Mar 13, 2024 at 14:36
What's the problem with your current code? It seems to do the correct thing. Note when sharing data, please do not use images. Share data in a reproducible format such as a dput(). Also only include packages that are required. install.packages("Rtools") should throw an error. That's not a real package. — MrFlick
– MrFlick, Commented Mar 13, 2024 at 14:39
That sounds like your condition has a problem. Since you haven't shared any data reproducibly (like with dput(data[1:6, ])) we can't test for ourselves. Have you examined condition to see if it look right--a mixture of TRUE and FALSE values, with the TRUEs in the places that need switching? — Gregor Thomas
– Gregor Thomas, Commented Mar 13, 2024 at 14:59

Jan Marvin · Accepted Answer · 2024-03-14 20:32:39Z

For some reason I toyed around with this a bit. Below are a few variations of possible answers:

library(openxlsx2)

# read data as numerics
df <- wb_to_df("/tmp/so.xlsx", types = c(place_x = 1, place_y = 1))
constructive::construct(df)
#> data.frame(
#>   place_x = c(43.2169125, 43.2169125, 6.758107, 43.504441, 43.504441, 6.2203264),
#>   place_y = c(6.2203264, 6.2203264, 43.502058, 6.757837, 6.757837, 43.2169125),
#>   row.names = 2:7
#> )

## going step by step - be careful with place_x/place_y ----
df[c("low", "high")] <- NA

df$low[df$place_x < 30] <- df$place_x[df$place_x < 30]
df$low[df$place_y < 30] <- df$place_y[df$place_y < 30]

df$high[df$place_x >= 30] <- df$place_x[df$place_x >= 30]
df$high[df$place_y >= 30] <- df$place_y[df$place_y >= 30]

df <- df[c("low", "high")]
names(df) <- c("place_x", "place_y")

df
#>    place_x  place_y
#> 2 6.220326 43.21691
#> 3 6.220326 43.21691
#> 4 6.758107 43.50206
#> 5 6.757837 43.50444
#> 6 6.757837 43.50444
#> 7 6.220326 43.21691

## with selections ----
df <- wb_to_df("/tmp/so.xlsx", types = c(place_x = 1, place_y = 1))
df[c("low", "high")] <- NA

sel <- df$place_x < 30
df$low[sel]   <- df$place_x[sel]
df$low[!sel]  <- df$place_y[!sel]

df$high[!sel] <- df$place_x[!sel]
df$high[sel]  <- df$place_y[sel]

df <- df[c("low", "high")]
names(df) <- c("place_x", "place_y")

df
#>    place_x  place_y
#> 2 6.220326 43.21691
#> 3 6.220326 43.21691
#> 4 6.758107 43.50206
#> 5 6.757837 43.50444
#> 6 6.757837 43.50444
#> 7 6.220326 43.21691

## mapply and if ----
df <- wb_to_df("/tmp/so.xlsx", types = c(place_x = 1, place_y = 1))

tdf <- mapply(df$place_x, df$place_y, FUN = function(x, y) if (x < 30) c(x, y) else c(y, x))
df <- as.data.frame(t(tdf))
names(df) <- c("place_x", "place_y")

df
#>    place_x  place_y
#> 1 6.220326 43.21691
#> 2 6.220326 43.21691
#> 3 6.758107 43.50206
#> 4 6.757837 43.50444
#> 5 6.757837 43.50444
#> 6 6.220326 43.21691

## apply and order ----
df <- wb_to_df("/tmp/so.xlsx", types = c(place_x = 1, place_y = 1))
ordr <- apply(df, 1, FUN = function(x) x[order(x)])
df <- as.data.frame(t(ordr))
names(df) <- c("place_x", "place_y")

df
#>    place_x  place_y
#> 2 6.220326 43.21691
#> 3 6.220326 43.21691
#> 4 6.758107 43.50206
#> 5 6.757837 43.50444
#> 6 6.757837 43.50444
#> 7 6.220326 43.21691

## OPs answer ----
df <- wb_to_df("/tmp/so.xlsx", types = c(place_x = 1, place_y = 1))

condition <- df$place_x > 30

temp <- df[condition, "place_x"]
df[condition, "place_x"] <- df[condition, "place_y"]
df[condition, "place_y"] <- temp

df
#>    place_x  place_y
#> 2 6.220326 43.21691
#> 3 6.220326 43.21691
#> 4 6.758107 43.50206
#> 5 6.757837 43.50444
#> 6 6.757837 43.50444
#> 7 6.220326 43.21691

## write data as xlsx ----
write_xlsx(df, file = "my_file.xlsx")

Collectives™ on Stack Overflow

Switching values of columns by condition

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related