Reordering rows alphabetically with specific exception(s) in R-CodePudding

This is my first Stack Overflow question so bear with me please. I'm trying to create dataframes that are ordered alphabetically based on a "Variable" field, with exceptions made for rows of particular values (e.g. "Avg. Temp" at the top of the dataframe and "Intercept" at the bottom of the dataframe). The starting dataframe might look like this, for example:

              Variable       Model 1 Estimate


               Year=2009         0.026
               Year=2010        -0.04
               Year=2011        -0.135***
                  Age            0.033***
               Avg Temp.        -0.001***
               Intercept        -3.772***
                  Sex           -0.073***
               Year=2008         0.084***
               Year=2012        -0.237***
               Year=2013        -0.326***
               Year=2014        -0.431***
               Year=2015        -0.589***

And I want to reorder it as such:

              Variable       Model 1 Estimate


               Avg Temp.        -0.001***
                  Age            0.033***
                  Sex           -0.073***
               Year=2008         0.084***
               Year=2009         0.026
               Year=2010        -0.04
               Year=2011        -0.135***
               Year=2012        -0.237***
               Year=2013        -0.326***
               Year=2014        -0.431***
               Year=2015        -0.589***
               Intercept        -3.772***

Appreciate any help on this.

CodePudding user response：

You can use the fct_relevel() function from {forcats}. Its first call put Avg Temp., Age and Sex at the beginning (after = 0 by default). The second call will put Intercept at the end (n() refers to the numbers of line in the data frame).

library(tidyverse)

df <-
  tribble(~Variable, ~Model,
          "Year=2009", 0.026,
          "Year=2010", -0.04,
          "Year=2011", -0.135,
          "Age", 0.033,
          "Avg Temp.", -0.001,
          "Intercept", -3.772,
          "Sex", -0.073,
          "Year=2008", 0.084,
          "Year=2012", -0.237,
          "Year=2013", -0.326,
          "Year=2014", -0.431,
          "Year=2015", -0.589)

df %>% 
  mutate(Variable = as.factor(Variable),
         Variable = fct_relevel(Variable, "Avg Temp.", "Age", "Sex"),
         Variable = fct_relevel(Variable, "Intercept", after = n())) %>% 
  arrange(Variable)

# A tibble: 12 × 2
   Variable   Model
   <fct>      <dbl>
 1 Avg Temp. -0.001
 2 Age        0.033
 3 Sex       -0.073
 4 Year=2008  0.084
 5 Year=2009  0.026
 6 Year=2010 -0.04 
 7 Year=2011 -0.135
 8 Year=2012 -0.237
 9 Year=2013 -0.326
10 Year=2014 -0.431
11 Year=2015 -0.589
12 Intercept -3.77

CodePudding user response：

Another option, in case the dataframes contain a variety of different variable names besides year and intercept, is something like this:

library(tidyverse)

# Sample data
df <- tribble(
  ~variable, ~model_1_estimate,
  "Year=2009", "0.026",
  "Year=2010", "-0.04",
  "Year=2011", "-0.135***",
  "Age", "0.033***",
  "Avg Temp.", "-0.001***",
  "Intercept", "-3.772***",
  "Sex", "-0.073***",
  "Year=2008", "0.084***",
  "Year=2012", "-0.237***",
  "Year=2013", "-0.326***",
  "Year=2014", "-0.431***",
  "Year=2015", "-0.589***"
)

# Possible solution
df |> 
  separate(variable, c("term", "year"), sep = "=") |> 
  mutate(intercept = if_else(term == "Intercept", 1, 0)) |> 
  arrange(intercept, term, year) |> 
  select(-intercept)

#> # A tibble: 12 × 3
#>    term      year  model_1_estimate
#>    <chr>     <chr> <chr>           
#>  1 Age       <NA>  0.033***        
#>  2 Avg Temp. <NA>  -0.001***       
#>  3 Sex       <NA>  -0.073***       
#>  4 Year      2008  0.084***        
#>  5 Year      2009  0.026           
#>  6 Year      2010  -0.04           
#>  7 Year      2011  -0.135***       
#>  8 Year      2012  -0.237***       
#>  9 Year      2013  -0.326***       
#> 10 Year      2014  -0.431***       
#> 11 Year      2015  -0.589***       
#> 12 Intercept <NA>  -3.772***

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