Convert columns of dataframe into rows in R

Question

I have the following dataframe available for analysis.

Sname   sid    st.sn.s1 st.sn.s2    st.sn.s3    st.sn.s1    st.sn.s2    st.sn.s3

    a       12     22       23          24          31          32          33

I want to convert it into something similar to the below one.

Sname   sid st.sn.s1    st.sn.s2    st.sn.s3            

a       12  22          23          24          
a       12  31          32          33

Can anyone direct me to relevant resources or help on this?

score 2 · Answer 1 · answered Mar 01 '20 at 08:31

2

You could the base R function rbind:

df <- structure(list(Sname = "a", sid = 12L, st.sn.s1 = 22L, st.sn.s2 = 23L, 
               st.sn.s3 = 24L, st.sn.s1 = 31L, st.sn.s2 = 32L, st.sn.s3 = 33L), row.names = c(NA, 
                                                                                              -1L), class = "data.frame")

rbind(df[, 1:5], df[, c(1:2, 6:8)])

#   Sname sid st.sn.s1 st.sn.s2 st.sn.s3
# 1     a  12       22       23       24
# 2     a  12       31       32       33

answered Mar 01 '20 at 08:31

B. Christian Kamgang

5,098
5
10

Thank you for the approach..what if i have more than 2 rows in a single line..i mean a nested list that has been flattened...I am looking for a generic solution that converts the columns into rows. – Dinesh Kumar Mar 01 '20 at 15:52

score 0 · Answer 2 · answered Mar 01 '20 at 04:46

Using pivot_longer and pivot_wider functions (https://tidyr.tidyverse.org/reference/pivot_longer.html), you can do:

library(tidyr)
library(dplyr)
df %>% pivot_longer(-c(Sname,sid), names_to = "var",values_to = "val") %>%
  pivot_wider(names_from = var, values_from = val) %>%
  dplyr::select(-.copy)

# A tibble: 2 x 5
  Sname   sid st.sn.s1 st.sn.s2 st.sn.s3
  <chr> <int>    <int>    <int>    <int>
1 a        12       22       23       24
2 a        12       31       32       33

Reproducible example

structure(list(Sname = "a", sid = 12L, st.sn.s1 = 22L, st.sn.s2 = 23L, 
    st.sn.s3 = 24L, st.sn.s1 = 31L, st.sn.s2 = 32L, st.sn.s3 = 33L), row.names = c(NA, 
-1L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x5635c38695c0>)

Thank you for the approach..what if i have more than 2 rows in a single line..i mean a nested list that has been flattened...I am looking for a generic solution that converts the columns into rows. — Dinesh Kumar, Mar 01 '20 at 15:53
What do you mean by two rows in a line ? Please provide a reproducible example of your dataset and the desired output. For now, it is difficult to understand what you are exactly looking for. https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — dc37, Mar 01 '20 at 18:31

score 0 · Answer 3 · answered Mar 01 '20 at 09:35

Generalized base R solution, that accounts for multiple rows with different "Sname"s.

res <- do.call(rbind, lapply(1:nrow(dat), function(x) {
  x <- dat[x, ];cbind(x[1:2], rbind(x[3:5], x[6:8]), row.names=NULL)
  }))
res
#   Sname sid st.sn.s1 st.sn.s2 st.sn.s3
# 1     a  12       22       23       24
# 2     a  12       31       32       33
# 3     b  12       22       23       24
# 4     b  12       31       32       33
# 5     c  12       22       23       24
# 6     c  12       31       32       33

Data:

dat <- structure(list(Sname = c("a", "b", "c"), sid = c(12L, 12L, 12L
), st.sn.s1 = c(22L, 22L, 22L), st.sn.s2 = c(23L, 23L, 23L), 
    st.sn.s3 = c(24L, 24L, 24L), st.sn.s1 = c(31L, 31L, 31L), 
    st.sn.s2 = c(32L, 32L, 32L), st.sn.s3 = c(33L, 33L, 33L)), row.names = c(NA, 
-3L), class = "data.frame")

Thank you for the approach..what if i have more than 2 rows in a single line..i mean a nested list that has been flattened...I am looking for a generic solution that converts the columns into rows. — Dinesh Kumar, Mar 01 '20 at 15:52

Convert columns of dataframe into rows in R

3 Answers3