Data manipulation in R: Starting a new row if i i-1

Question

I have a long (one row) data file with many values. It needs to be broken up into multiple rows. While the specifics of why I need to do this isn't important, the logic is that column i should always be bigger than column i+1. I.e. The values along a row should be decreasing.

The best way I can think to do this is to break up the data frame into multiple rows with an 'if then' style of function: If column i > i-1, start a new row. If i < i-1, keep this value in the row.

#Example data but with similar format to my real data

df <- data.frame(matrix(ncol = 9, nrow = 1))
df[1,] <- c(3, 2, 1, 2, 1, 1, 3, 2, 1)

I would like it to end up looking like this.

I'm not very proficient with functions referring to i position in a data frame and the kind of data manipulation this needs. Any advice would be appreciated.

user_1580645 · Answer

Splitting the vector into groups is simple, but how the data are finally stored depends on what you are trying to do with the result. Here is a simple way to split the data:

vect <- unname(unlist(df))    # Convert the data to a simple vector
cut <- which(diff(vect) >= 0) # Find the points for splitting the vector
grps <- rep(1:4, diff(c(0, cut, length(vect))))  # Define the groups created
groups <- split(vect, grps)   # Create a list containing the groups
groups
# $`1`
# [1] 3 2 1
# 
# $`2`
# [1] 2 1
# 
# $`3`
# [1] 1
# 
# $`4`
# [1] 3 2 1

A data frame and a matrix requires that all of the columns are the same length so those are not structures that you can use to save the result. To convert to a matrix we need to pad with missing values:

maxno <- max(sapply(groups, length))  # How long is the longest run?
t(sapply(groups, function(x) c(x, rep(NA, maxno - length(x)))))
#   [,1] [,2] [,3]
# 1    3    2    1
# 2    2    1   NA
# 3    1   NA   NA
# 4    3    2    1

user_19512611 · Answer

Here is a tidy solution. Please let me know if this solves your question:

library(tidyverse)

df <- data.frame(matrix(ncol = 9, nrow = 1))
df[1,] <- c(3, 2, 1, 2, 1, 1, 3, 2, 1) 

df %>%
  pivot_longer(cols = everything(), names_to = "vars") %>%
  mutate(smaller_than_prev = value < lag(value) | is.na(lag(value)),
         num_falses = cumsum(smaller_than_prev == FALSE)) %>%
  group_by(num_falses) %>%
  mutate(row_num = row_number()) %>%
  pivot_wider(names_from = row_num, values_from = value, values_fill = NA, names_prefix = "var") %>%
  fill(c(`var1`, `var2`, `var3`), .direction = "downup") %>%
  slice_head(n = 1) %>%
  ungroup() %>%
  select(`var1`, `var2`, `var3`)

user_6574038 · Answer

We may cumsum the differences and split where non-negative, i.e. i > i - 1.

x <- df[1, ] |> unname()
r <- split(x, cumsum(c(1, diff(x)) >= 0))
r
# $`1`
# X1 X2 X3 
#  3  2  1 
# 
# $`2`
# X4 X5 
#  2  1 
# 
# $`3`
# X6 
#  1 
# 
# $`4`
# X7 X8 X9 
#  3  2  1

To create a data frame, we harmonize lengths and rbind.

do.call(rbind, lapply(r, `length<-`, max(lengths(r))))
#   X1 X2 X3
# 1  3  2  1
# 2  2  1 NA
# 3  1 NA NA
# 4  3  2  1

This also works out of the box with the "small increases" i > i - 1 ± tol., OP talks about,

set.seed(424643)
(x2 <- x + rnorm(length(x), 0, .02))
#        X1        X2        X3        X4        X5        X6        X7        X8        X9 
# 2.9989375 1.9675093 0.9695195 2.0286091 0.9860200 0.9867120 3.0126058 2.0082577 1.0027076 

split(x2, cumsum(c(1, diff(x2)) >= 0))
# $`1`
#        X1        X2        X3 
# 2.9989375 1.9675093 0.9695195 
# 
# $`2`
#       X4       X5 
# 2.028609 0.986020 
# 
# $`3`
#       X6 
# 0.986712 
# 
# $`4`
#       X7       X8       X9 
# 3.012606 2.008258 1.002708

for small decreases, we may adjust the zero comparison by a small tolerance value, -.02 in this case.

set.seed(219291)
(x2 <- x + rnorm(length(x), 0, .02))
#        X1        X2        X3        X4        X5        X6        X7        X8        X9 
# 2.9866361 2.0236431 1.0053049 2.0061573 1.0348428 1.0008761 3.0145685 2.0016665 0.9719804 

split(x2, cumsum(c(1, diff(x2)) >= 0 + -.02))
# $`1`
#        X1        X2        X3 
# 3.0109922 2.0061321 0.9900378 
# 
# $`2`
#        X4        X5 
# 1.9728080 0.9973932 
# 
# $`3`
#        X6 
# 0.9829894 
# 
# $`4`
#       X7       X8       X9 
# 3.003697 1.997184 0.984649

Data:

df <- structure(list(X1 = 3, X2 = 2, X3 = 1, X4 = 2, X5 = 1, X6 = 1, 
    X7 = 3, X8 = 2, X9 = 1), row.names = c(NA, -1L), class = "data.frame")

Data manipulation in R: Starting a new row if i > i-1

3 Answers