Graphically reorganizing columns in DataFrame

Question

I'm currently in the process of cleaning up a large questionnaire data base.

I wanted to know, if in R or pandas, there was a way to graphically change the order of columns.

I mostly used RStudio and because I did a lot of data manipulation, now I have to reorder everything to make it logical to the human mind.

Is there a more intuitive way to do this than with select() or by dragging everything into excel?

Perfection would be to have a function like irec() or iorder() (from questionr) that launches a window in which you can click and drag columns into place. I'm tired of re-writing these long-ass variable names in select functions.

Answer 1

You can use a text editor to cut and paste column names to avoid re-typing column names. These are all very simple involving just 1 or 2 lines of R code and do not depend on R Studio.

1) edit Use edit to put the names vector of the data frame, here mtcars, into a text editor, edit the names to be in the order you want and then exit the editor.

mtcars2 <- mtcars[edit(names(mtcars))]

The names will appear like this in the text editor:

c("mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", 
"gear", "carb")

2) cat/readLines A variation of that is the following. names.dat will contain one name per line and those lines can be rearranged in a text editor. This makes the editing slightly easier than in (1) at the expense of an extra line of R code to invoke.

cat(names(mtcars), file = "names.dat", sep = "\n")
# in a text editor edit names.dat rearranging the names
mtcars2 <- mtcars[readLines("names.dat")]

The names will appear like this in the text editor making them even easier to cut and paste

mpg
cyl
disp
hp
drat
wt
qsec
vs
am
gear
carb

3) Programmatic rearrangement In some cases it may be possible to sort the columns programmatrically. For example if we have dat shown below and want to sort the columns so that all the a's sort together and similarly for the b's.

library(gtools)

dat <- data.frame(a1 = 1, b1 = 2, a2 = 3, b2 = 4, a3 = 5, b3 = 6)

dat[mixedsort(names(dat))]

##   a1 a2 a3 b1 b2 b3
## 1  1  3  5  2  4  6

or this which has the advantage that it keeps other columns in place

dat2 <- data.frame(id = 0, a1 = 1, b1 = 2, a2 = 3, b2 = 4, a3 = 5, b3 = 6)
root <- sub("\\d+$", "", names(dat2))
dat2[order(match(root, root))]

##   id a1 a2 a3 b1 b2 b3
## 1  0  1  3  5  2  4  6

Answer 2

You can do it using a Shiny App and DT. The resulting column order will be shown on top with column indexes. I used mtcars, but you can do use your own data. The columns are drag & dropable. Right now, I put the max drag-n-drop time to 900 ms.

library(shiny)
library(DT)

callback <- JS("
  var finalOrder;
  
  table.on('column-reorder.dt', function(e, settings, details) {
    clearTimeout(finalOrder);
    
    // Wait for all reordering to finish before sending the final order
    finalOrder = setTimeout(function() {
      var order = table.colReorder.order();
      Shiny.setInputValue('columnsOrder', order);
    }, 900); // update to time you need for dragging
  });
")

df <- mtcars

shinyApp(
  ui = fluidPage(
    p('Use this command to reorder your data.frame:'),
    fluidRow(column(12, verbatimTextOutput('reorder'))),
    p('Drag and Drop your columns in the correct order'),
    fluidRow(column(12, DTOutput('table')))
  ),
  
  server = function(input, output) {
    tableData <- reactiveVal(df)
    lastOrder <- reactiveVal(NULL)
    
    output$table <- renderDT({
      datatable(
        tableData(),
        extensions = 'ColReorder',
        options = list(
          dom = 'Rlfrtip',
          pageLength = nrow(tableData()),
          colReorder = TRUE
        ),
        callback = callback,
        rownames = FALSE
      )
    })
    
    observeEvent(input$columnsOrder, {
      req(input$columnsOrder)
      
      # Get the new order
      new_order <- as.numeric(input$columnsOrder) + 1
      
      # Only update if the order is different from the last one
      if (!identical(new_order, lastOrder())) {
        lastOrder(new_order)
        current_data <- tableData()
        tableData(current_data[, new_order])
      }
    })
    
    output$reorder <- renderPrint({
      current_cols <- colnames(tableData())
      paste0(
        "df <- df[, c(",
        paste0("'", current_cols, "'", collapse = ", "),
        ")]"
      )
    })
  }
)

Answer 3

A solution is to create a simple Shiny app that uses the sortable R package to enable you to reorder the columns of tables you upload using a drag and drop interface:

library(shiny)
library(sortable)

ui <- fluidPage(
  titlePanel("Drag-and-Drop Column Reordering"),
  fileInput("target_upload", h5(strong("Click to Upload CSV File"), style = "color:#007acc;"),
            accept = c("text/csv"),
            buttonLabel = strong("Select File", style = "color:#007acc;"),
            placeholder = "No file selected"),
  
  fluidRow(
    column(
      width = 4,
      h4("Reorder Columns:"),
      uiOutput("rank_list_ui")
    ),
    column(
      width = 8,
      h4("Data Frame Preview:"),
      tableOutput("table"),
      downloadButton("download", strong("Download"), icon = icon("download"))
    )
  )
)

server <- function(input, output, session) {
  
  
  file_upload <- reactive({
    inFile <- input$target_upload
    if (is.null(inFile)) {
      return(NULL)
    }
    data <- read.csv(inFile$datapath, header = TRUE, sep = ",")
    return(data)
  })
  
  output$rank_list_ui <- renderUI({
    data <- file_upload()
    if (is.null(data)) {
      return(NULL)
    }
    column_names <- colnames(data)
    rank_list(
      input_id = "column_order",
      labels = column_names,
      options = sortable_options(multiDrag = TRUE)
    )
  })
  
  reordered_df <- reactive({
    data <- file_upload()
    order <- input$column_order
    if (is.null(data) || is.null(order)) {
      return(NULL)
    }
    data[, order, drop = FALSE]
  })
  
  
  output$table <- renderTable({
    reordered_df()
  })
  
  
  output$download <- downloadHandler(
    filename = function() {
      paste("data", ".csv", sep = "")
    },
    content = function(file) {
      write.csv(reordered_df(), file, row.names = FALSE)
    }
  )
}

shinyApp(ui, server)