-1

I'm turning some long-form data into wide-form. When I transform my rows to columns, they appear in the order 1, 10, 100 rather than 1,2,3, etc. How do I fix this? I have 100s of rows so I'd rather not manually type out the order.

I know this is an issue with the column names being strings and I attempted to simply use select(), however, this removes my cluster column. I also have tried the standard way of renaming my columns (data <- data[c("A", "B", "C")]).

I've also taken a look at the following threads and can't seem to parse it out. Reordering columns in a large dataframe Preserve order of columns when going from wide to long format R: Reorder columns from dcast output numerically instead of lexicographically

Here is my code:

library(reshape2)
library(data.table)
library(tidyverse)
library(tidyr)
library(gtools)
library(stringr)

rf_83_88 <- read.csv('Google Drive File Stream/My Drive/Bang_RIA/bang_83_05_rainfall_avg/Bangladesh-precipitation-decadal-83-88.csv')


groupdata_1 <- dcast(setDT(rf_83_88), cluster ~
                     paste0("precipitation", rowid(cluster)), value.var = "precipitation")

Here is the df sample it produces:

cluster        precipitation1  precipitation10 precipitation100
Akhai Bari _ 1   0               11.730278        11.12267
Akhai Bari _ 2   0               10.130148        12.53500

When I try:

test_select <- select(groupdata_1, num_range("precipitation", 0:nrow(groupdata_1))

, the df becomes ordered, however it drops cluster.

I'm relatively new to R (and stack) and tried reading the documentation to no avail. Any help would be appreciated. Thanks!

selfteachshiny
  • 11
  • 1
  • `DF[,-1] <- DF[,-1][, order(as.numeric(gsub("precipitation", "", names(DF[, -1]))))]`? – Roland Jul 15 '19 at 10:35
  • I am getting this error when I try your solution ``` Error in `[<-.data.table`(`*tmp*`, , -1, value = c(1L, 112L, 144L, 155L, : Item 1 of column numbers in j is -1 which is outside range [1,ncol=221]. Use column names instead in j to add new columns.``` I tried replacing ```-1``` with ```"cluster"``` and get this message: ```Warning message: In order(as.numeric(gsub("precipitation", "", names(groupdata_1[, : NAs introduced by coercion ``` but there is no change in column order – selfteachshiny Jul 15 '19 at 11:15
  • Well, you didn't say you have a data.table. I'd assumed you have a regular data.frame. Maybe `setcolorder(DT, c(1, order(as.numeric(gsub("precipitation", "", names(DT)[-1]))) + 1))`. – Roland Jul 15 '19 at 11:24

1 Answers1

1

OP has clarified in comments that they actually have a data.table and not a regular data.frame.

  1. Extract the column names and remove the first column name:

    names(DT)[-1]

  2. Extract the numbers from the column names (or easier, remove the word "precipitation"):

    gsub("precipitation", "", names(DT)[-1])

  3. Now find the sorting order of these numbers (after turning them into numeric values):

    order(as.numeric(gsub("precipitation", "", names(DT)[-1])))

  4. Now we only have to add the first column to this order:

    c(1, order(as.numeric(gsub("precipitation", "", names(DT)[-1]))) + 1)

  5. and pass the order to setcolorder:

    setcolorder(DT, c(1, order(as.numeric(gsub("precipitation", "", names(DT)[-1]))) + 1))

Roland
  • 127,288
  • 10
  • 191
  • 288