How to reorder dataframe rows in based on conditions in more than 1 column in R?

user_13645106 0
Viewed 219

The Problem

I am trying to reorder rows based on the conditions in 2 other columns. Specifically, I have a sequential ID for hundreds of randomly generated sampling transects called "ID_First" and then for each transect there is a corresponding "ID_Next" that represents the next transect that should be sampled. I am trying to reorder the rows such that the sampling transects are in order of execution rather than the original order based on "ID_First"

I know that data frames can be arranged based on one or more columns for numerical variables in either an ascending or descending way and, for factors, in an "ordered" way (e.g., high, medium, low). Is it possible to arrange the order of the rows based on the sequence of ID_first and then ID_Next? I have not been able to figure out how to do this so I have been doing it manually.

Simplified Reproducible Example

Data

# sequential ID for a small number of randomly generated transects 
ID_First <- seq(1,10,1)

# represents the next transect that should be sampled following ID_First
ID_Next <- c(4,5,8,7,10,2,9,6,3,NA)


# make a dataframe
df <- cbind.data.frame(ID_First, ID_Next)

# look at the df
df

>    ID_First ID_Next
> 1         1       4
> 2         2       5
> 3         3       8
> 4         4       7
> 5         5      10
> 6         6       2
> 7         7       9
> 8         8       6
> 9         9       3
> 10       10      NA

So, if you start with ID_First equal to 1 and then look at the corresponding ID_Next this would indicate that the next transect to sample is 4. Then you go to ID_First equal to 4 and the corresponding ID_Next to sample next would be 7, and so on. For this example, the order of sampling would progress as follows: 1,4,7,9,3,8,6,2,5,10.

Ideal Outcome

Here is what I am trying to accomplish:

>    ID_First ID_Next
> 1         1       4
> 4         4       7
> 7         7       9
> 9         9       3
> 3         3       8
> 8         8       6
> 6         6       2
> 2         2       5
> 5         5      10
> 10       10      NA

Now the transects are following the order needed for sampling (e.g., 1 to 4, 4 to 7, 7 to 9, 9 to 3, etc. through 10) rather than the ascending ID_First.

Question

Is there an easy way to reorder the original data frame using ID_First equal to 1 as the standpoint and then, following the progression of ID_Next to ID_Tirst to ID_Next to arrange the remainder of the transects?

dataframe sequence r
2 Answers

You can accomplish this for your specific example using a while loop and the match() function in R. I also used list.append() from the rlist package.

library(rlist)

# sequential ID for a small number of randomly generated transects 
ID_First <- seq(1,10,1)

# represents the next transect that should be sampled following ID_First
ID_Next <- c(4,5,8,7,10,2,9,6,3,NA)


# make a dataframe
df <- cbind.data.frame(ID_First, ID_Next)

#create while loop to define target order
i = 1
order = c(i)

n = 1
while (n < length(df$ID_Next)){
  j = df[df$ID_First == i, 2]
  order = list.append(order, j)
  i = j
  n = n+1
}

#match df order to target order
df2 = df[match(order, df$ID_First),]
user_96805480

You can use Reduce with match to find the chain from ID_First to ID_Next.

df[Reduce(function(i,j) match(df$ID_Next[i], df$ID_First)
 , seq_len(nrow(df)), accumulate = TRUE),]
#   ID_First ID_Next
#1         1       4
#4         4       7
#7         7       9
#9         9       3
#3         3       8
#8         8       6
#6         6       2
#2         2       5
#5         5      10
#10       10      NA

Data:

df <- data.frame(ID_First = 1:10, ID_Next = c(4,5,8,7,10,2,9,6,3,NA))
df
#   ID_First ID_Next
#1         1       4
#2         2       5
#3         3       8
#4         4       7
#5         5      10
#6         6       2
#7         7       9
#8         8       6
#9         9       3
#10       10      NA
user_104885040
Related