sorting row values in dataframe by column values

Question

I have difficulty sorting row values by particular column. The values have different order, for example,

 METHOD  VAL1  VAL2  VAL3
    1-A     10    2     15
    10-B    11    5     15
    11-c    23    45    65
    2-F     4     65    67
    3-T     4     56    11

and I need like this,

 METHOD  VAL1  VAL2  VAL3
    1-A     10    2     15
    2-F     4     65    67
    3-T     4     56    11 
    10-B    11    5     15
    11-c    23    45    65

The sorting order is based on METHOD column. I've tried to arrange it in many ways but without success.

I have solved this issue but there is an another issue on the same code. Individually, the following code works but when applied to function - creates an issue.

a1 <-  a1[order(as.numeric(gsub("-.*", "", a1$varname))),]

My function as follows,

t1<- doTable1(AE_subset$Disp_code,AE_subset$FY,"DisposalMethod",thresh = 0.02,testvar = AE_subset$Attendance,fun="sum")


doTable1<- function(var1,var2,varname,testvar=NULL,fun=NULL,inc=TRUE,thresh=0.02) {

  if (is.null(fun)) {
    a1<- as.data.frame.matrix(table(var1,var2))
  } else {
    a1<- as.data.frame.matrix(tapply(testvar,list(var1,var2),FUN=fun,na.rm=TRUE))
  }

  a1<- rownames_to_column(a1,var=varname)

  a1$FY3PR<- a1$FY3*proRata



  if (!is.null(fun))
    if (fun=="mean")
      a1$FY3PR<- a1$FY3


  a1 <-  a1[order(as.numeric(gsub("-.*", "", a1$varname))),]  # dataframe is not updating here

  a1 <- a1 %>% replace(., is.na(.), 0)
  a1 <- rbind(a1,c("Total",as.numeric(colSums(a1[,2:4]))))


  return(a1)

}

Simple it returns NULL data frame. Can anyone identify why this function fails when it comes to order() command?

some sort of dupe of [this](https://stackoverflow.com/questions/39270292/mixedorder-data-frame-in-r) unless it has to be dplyr — David Arenburg, Sep 13 '18 at 10:38
@SalmanLashkarara sorry how it works? its juts created a list with my dataframe. — Sharmi, Sep 13 '18 at 10:51

score 0 · Accepted Answer · answered Sep 13 '18 at 10:27

0

You can use gsub to split the numbers from the characters and order them:

df[order(as.numeric(gsub("-.*", "", df$METHOD))),]

  METHOD VAL1 VAL2 VAL3
1    1-A   10    2   15
4    2-F    4   65   67
5    3-T    4   56   11
2   10-B   11    5   15
3   11-c   23   45   65

answered Sep 13 '18 at 10:27

LAP

6,605
2
15
28

Thank you this works fine but what if I have numerical categorical values, for example 1-2,3-5,11-20? – Sharmi Sep 13 '18 at 10:38
As long as the first numbers, so the ones before the hyphen, are increasing, it should work the same. – LAP Sep 13 '18 at 10:40
Thank you!!! Because I have 10 big tables that was generated through my function so I have to apply this formula globally to work. All the tables have different categorical values. – Sharmi Sep 13 '18 at 10:43

score 0 · Answer 2 · answered Sep 13 '18 at 10:30

With dplyr you can do:

library(dplyr)
dat %>% # we create a new column based on METHOD
  mutate(met_num =as.numeric(gsub("\\D", "", METHOD)) ) %>% # gets only the number part 
  arrange(met_num) %>%  # we arrange just by the number part of METHOD
  select(-met_num) # removes that new column

  METHOD VAL1 VAL2 VAL3
1    1-A   10    2   15
2    2-F    4   65   67
3    3-T    4   56   11
4   10-B   11    5   15
5   11-c   23   45   65

Data used:

tt <- "METHOD  VAL1  VAL2  VAL3
    1-A     10    2     15
10-B    11    5     15
11-c    23    45    65
2-F     4     65    67
3-T     4     56    11"

dat <- read.table(text = tt, header = T)

Thank you, but I have many tables that was generated from function so one line code works fine. I can use your idea differently. — Sharmi, Sep 13 '18 at 10:55

sorting row values in dataframe by column values

2 Answers2