Sorting, Reordering and splitting dataframe

[cook675]

I have a data frame which I would like to sort by Column A largest to smallest, then cut and paste all rows that are negative to identical columns side beside the original, leaving 1 column gap in between, and then sorting the negative values smallest to largest.

Basically I would like to display the positive and negative values side by side for ease of reading so the user doesn't have to manually sort or scroll:

df <- data.frame(
  A = c("2","-4","5","-9", NULL ),
  B = c(1,3,2,3))
df
   A B
1  2 1
2 -4 3
3  5 2
4 -9 3

The new data frame would look like this, with a blank space in between, and columns A and B would have the exact same names:

  A B     A  B
1 5 2    -9  3
2 2 1    -4  3

any ideas?? Thanks!

[StatSteph]

How are you sharing the data with the user? How will they access this constructed table?

[cook675]

Im going to write to an excel or CSV and they'll view it that way.

I think R can not have the same column names within a data frame correct? Im not sure how to work around this either

[oli.m]

I guess StatSteph asked, because it is much easier to report the splitted dataframes seperately.

If you insert an empty row (with colname ".") at the end of the first splitted dataframe:

dataset[ , "."] <- ""

and then join them again?

Before you could use:

data %>% 
     arrange(desc(columnname)) %>% 
     View()

and then split it in dplyr with separate() at that point where your threshold is?

Good luck!

[nirgrahamuk]

I think that your example was potentially an 'easy case' because there were as many negatives as positives, so the equal length would mean you could trivially cbind the two parts together.
However, with unequal parts as in my example, you would need a cbind that pads, I modified such a function to operate by converting to character and padding with blank string.

library(dplyr)
#credit https://gist.github.com/abelsonlive/4112423

cbind.fill<-function(...){
  nm <- list(...) 
  nm<-lapply(nm, as.matrix)
  n <- max(sapply(nm, nrow)) 
  r1<- do.call(cbind, lapply(nm, function (x) 
     rbind(x, matrix("", n-nrow(x), ncol(x))))) 
  r2 <-as.data.frame(r1)
}




df <- data.frame(
  A = c("2","-4","5","-9", -2 ),
  B = c(1,3,2,3,1))

df <- mutate_all(df,
                 as.numeric) %>%
             arrange(desc(A))


(r1 <- cbind.fill(filter(df,A>0)
      ,filter(df,A<=0)))