Search code examples
rdataframepivot-tabletranspose

permute the values ​of a dataframe diagonally

How can i transform the dataframe data into new_data ? I used pivot_longer and pivot_wider but i don't get what I want.

data <- data.frame(
  ind = c("la", "lb", "lc"),
  c1 = c(1, 2, 3),
  c2 = c(4, 5, 6),
  c3 = c(7, 8, 9),
  c4 = c(10, 11, 12)
)

Desired result

new_data <- data.frame(
  ind = c("c1", "c2", "c3", "c4"),
  la = c(1, 4, 7, 10),
  lb = c(2, 5, 8, 11),
  lc = c(3, 6, 9, 12)
)
Solution

  • R base

    As far as I know, there is no t()-like function for data frames in base R. An R-base approach:

    x <- t(as.matrix(data))
colnames(x) <- x[1L, ]
x <- x[-1L, ]
x <- as.data.frame(cbind("ind" = rownames(x), x))
rownames(x) <- seq(nrow(x))
x

    wrapped in a small function:

    transpose_df <- \(df) {
  stopifnot(is.data.frame(df))
  x <- t(as.matrix(df))
  colnames(x) <- x[1, ]
  x <- x[-1, ]
  x <- as.data.frame(cbind("ind" = rownames(x), x))
  rownames(x) <- seq(nrow(x))
  x
}

    gives

    data <- data.frame(
  ind = c("la", "lb", "lc"),
  c1 = c(1, 2, 3),
  c2 = c(4, 5, 6),
  c3 = c(7, 8, 9),
  c4 = c(10, 11, 12)
)

transpose_df(data)
  ind la lb lc
1  c1  1  2  3
2  c2  4  5  6
3  c3  7  8  9
4  c4 10 11 12