swap rows of a dataframe based on condition

[nithinmkp]

#Package
library(tidyverse)

Example Data ------------------------------------------------------------

df<-tribble(
~id, ~sex, ~educ, ~income, ~job,
1, "H1", 7 , 35, "Delhi",
2, "H2", 12, 67, "Delhi",
3, "W1", 9, 42, "Kochi",
4 , "H3",15, 78, "Mumbai",
5, "W3", 6, 25, "Kolkata",
6, "W2", 13, 70, "Jaipur"
)

#Problem
df %>%
mutate(
pe= case_when(
sex=="H1"~educ[sex=="W1"],
sex=="H2"~educ[sex=="W2"],
sex=="H3"~educ[sex=="W3"]
)
)
#> # A tibble: 6 x 6
#> id sex educ income job pe
#>
#> 1 1 H1 7 35 Delhi 9
#> 2 2 H2 12 67 Delhi 13
#> 3 3 W1 9 42 Kochi NA
#> 4 4 H3 15 78 Mumbai 6
#> 5 5 W3 6 25 Kolkata NA
#> 6 6 W2 13 70 Jaipur NA

The problem is I want the output in "pe" column for "W1", "W2", "W3" also where "pe" for "W1" is educ of "H1", "pe" for "W2" is educ of "H2" etc. I do not want to copy paste the code within case_when. is there any way to do this in a better way?

[DavoWW]

Hi @nithinmkp,
If I understand your question correctly then I think this code will achieve what you want:

suppressPackageStartupMessages(library(tidyverse))

df <- tribble(
~id, ~sex, ~educ, ~income, ~job,
1, "H1", 7 , 35, "Delhi",
2, "H2", 12, 67, "Delhi",
3, "W1", 9, 42, "Kochi",
4 , "H3",15, 78, "Mumbai",
5, "W3", 6, 25, "Kolkata",
6, "W2", 13, 70, "Jaipur"
)

df
#> # A tibble: 6 × 5
#>      id sex    educ income job    
#>   <dbl> <chr> <dbl>  <dbl> <chr>  
#> 1     1 H1        7     35 Delhi  
#> 2     2 H2       12     67 Delhi  
#> 3     3 W1        9     42 Kochi  
#> 4     4 H3       15     78 Mumbai 
#> 5     5 W3        6     25 Kolkata
#> 6     6 W2       13     70 Jaipur

df %>% 
  mutate(index = str_sub(sex, start=2, end=2)) %>% 
  arrange(index, sex) %>% 
  group_by(index) %>% 
  mutate(pe = rev(educ)) %>% 
  arrange(id)
#> # A tibble: 6 × 7
#> # Groups:   index [3]
#>      id sex    educ income job     index    pe
#>   <dbl> <chr> <dbl>  <dbl> <chr>   <chr> <dbl>
#> 1     1 H1        7     35 Delhi   1         9
#> 2     2 H2       12     67 Delhi   2        13
#> 3     3 W1        9     42 Kochi   1         7
#> 4     4 H3       15     78 Mumbai  3         6
#> 5     5 W3        6     25 Kolkata 3        15
#> 6     6 W2       13     70 Jaipur  2        12

^{Created on 2022-06-09 by the reprex package (v2.0.1)}

Hope this helps.

[nirgrahamuk]

I would do it the following way

df %>%
  separate(sex, into = c("g", "num"), sep = 1) %>%
  pivot_wider(names_from = "g", values_from = "educ") %>%
  group_by(num) %>%
  summarise(across(c(H, W), ~ max(., na.rm = TRUE))) %>%
  mutate(sex = paste0("H", num)) %>%
  select(sex, Pe = W) %>%
  right_join(df) %>%
  relocate(Pe, .after = everything())

[nithinmkp]

This is wonderful DavoWW.. Bravo!!

[nithinmkp]

Thanks for chipping in. This solution however returns some NA values

[nirgrahamuk]

it seems I didnt understand your requirement. I'll just leave it at that.