Transposition of a Tibble Using Pivot_Longer() and Pivot_Wider (Tidyverse)

Transposition of a Tibble Using Pivot_Longer() and Pivot_Wider (Tidyverse)

It is easier with data.table::transpose

library(data.table)
data.table::transpose(setDT(df1), make.names = 'people')[, People := .I][]

---

or with tidyverse, the transpose can be done in two steps, 1), reshape to long format with pivot_longer, 2) reshape back to wide with a different column with pivot_wider

library(dplyr)
library(tidyr)
df1 %>%
   pivot_longer(cols = -people, names_to = 'People') %>% 
   pivot_wider(names_from = people, values_from = value)
# A tibble: 5 x 5
#  People person1 person2 person3 person4
#  <chr>    <int>   <int>   <int>   <int>
#1 1        27000   27000   27000   27000
#2 2        30000   30000   30000   30000
#3 3        40000   40000   40000   40000
#4 4        50000   50000   50000   50000
#5 5        60000   60000   60000   60000

data

df1 <- structure(list(people = c("person1", "person2", "person3", "person4"
), `1` = c(27000L, 27000L, 27000L, 27000L), `2` = c(30000L, 30000L, 
30000L, 30000L), `3` = c(40000L, 40000L, 40000L, 40000L), `4` = c(50000L, 
50000L, 50000L, 50000L), `5` = c(60000L, 60000L, 60000L, 60000L
)), class = "data.frame", row.names = c(NA, -4L))

How to flip column variable names to row labels?

You can use pivot_longer and pivot_wider -

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = -Trial_Type) %>%
  pivot_wider(names_from = Trial_Type, values_from = value)

#  name          Pre  Post
#  <chr>       <dbl> <dbl>
#1 CT_tib_all  0.244 0.254
#2 CT_lum_all  0.209 0.211
#3 CT_tho_all  0.309 0.302
#4 CT_gps_all  0.315 0.313
#5 CT_vest_all 0.31  0.316

In data.table -

library(data.table)

dcast(melt(setDT(df), id.vars  = 'Trial_Type'), 
      variable~Trial_Type, vvalue.var = 'value')

How to transpose a dataframe in tidyverse?

Try with add_rownames

add_rownames(mtcars) %>% 
         gather(var, value, -rowname) %>% 
         spread(rowname, value) 

In the newer version, rownames_to_column replaces add_rownames

mtcars %>%
   rownames_to_column %>% 
   gather(var, value, -rowname) %>% 
   spread(rowname, value) 

In the even newer version, pivot_wider replaces spread:

mtcars %>%
   tibble::rownames_to_column() %>%  
   pivot_longer(-rowname) %>% 
   pivot_wider(names_from=rowname, values_from=value) 

How to Transpose (t) in the Tidyverse Using Tidyr

The general idiom in the tidyverse is to gather() your data to the maximal extent, forming a "long" data frame with one measurement per row. Then, spread() can revert this long data frame into whichever "wide" format that you like best. This procedure can effectively transpose the data: just gather() all the identifier columns except the row names, and then spread() the row names.

For example, here is how to effectively transpose mtcars:

require(tidyverse)

mtcars %>% 
    rownames_to_column %>%
    gather(variable, value, -rowname) %>% 
    spread(rowname, value)

Your data does not have "row names" as understood in R, but Code1 effectively serves as a row name because it uniquely identifies each (original) row of your data.

Df1 <- Df %>% 
    group_by(Code1, Code2, Level) %>%
    summarise_all(funs(count = sum(!is.na(.)))) %>%
    gather(column, value, -Code1) %>%
    spread(Code1, value)

UPDATE for tidyr 1.0 or higher (late 2019 onwards)

The new pivot_wider() and pivot_longer() functions are now preferred over the older (but still supported) gather() and spread(). Thus the preferred way to transpose mtcars is probably

require(tidyverse)

mtcars %>% 
    rownames_to_column() %>%
    pivot_longer(-rowname, 'variable', 'value') %>%
    pivot_wider(variable, rowname)

Transpose a tibble

We can use data.table::transpose

as_tibble(data.table::transpose(dat, make.names = 'Cor', keep.names = 'Cor'))
 # A tibble: 2 × 3
  Cor   Linear Rank 
  <chr> <chr>  <chr>
1 a,b   x1     x2   
2 b,c   x3     x4   

data

dat <- tibble(Cor = c("Linear", "Rank"),
       `a,b` =  c("x1","x2"),
       `b,c` = c("x3","x4")
)

Reshape table in tidyverse, problems with pivot_wider

You can use some tibble functions:

df_initial %>%
  column_to_rownames("year_month") %>%
  t() %>%
  as_tibble(rownames = "year_month")

# # A tibble: 3 x 4
#   year_month `2021-01` `2021-02` `2021-03`
#   <chr>          <dbl>     <dbl>     <dbl>
# 1 n_unique         168       249       197
# 2 new_n            159       244       186
# 3 old_n              9         5        11

Its base equivalent:

setNames(data.frame(t(df_initial[-1])), df_initial[[1]])

R tidyverse: transposing but keeping one column intact

The issue would be that there is a clash between the name column and the default one created with pivot_longer and this would cause duplication of "name" column. Specify the names_to in pivot_longer as "Case" to avoid the duplication

library(dplyr)
library(tidyr)
test.dat %>%
  pivot_longer(cols = starts_with("Case"), names_to = "Case") %>% 
  pivot_wider(names_from = pair, values_from = value)

-output

# A tibble: 8 x 8
#  name  Case  `1 v 2` `1 v 3` `1 v 4` `2 v 3` `2 v 4` `3 v 4`
#  <chr> <chr>   <int>   <int>   <int>   <int>   <int>   <int>
#1 Louis Case1       3       3      11       4       8       2
#2 Louis Case2       5       7       4       6       4       5
#3 Louis Case3       3       1       0       2       4       2
#4 Louis Case4       1       0       0       2       1       1
#5 Paul  Case1       5       2       5       4       5       3
#6 Paul  Case2       6       6       2       1       9       4
#7 Paul  Case3       0       3       2       2       2       2
#8 Paul  Case4       0       0       1       1       0       0

Pivot longer in dplyr for mutiple value columns

How about like this:

  library(tidyverse)
  dat <- structure(list(total = c(9410, 12951.1794783802), 
                      op = c(3896.66666666667, 6976.57663230241), 
                      ox = c(2200, 4920.84776902887), 
                      ox15 = c(183.333333333333, 694.262648008611), 
                      ox30 = c(133.333333333333, 368.090117767537), 
                      hy = c(283.333333333333, 1146.14924596984),
                      hy10 = c(NA, 433.993925588459)),
                 row.names = c(NA, -2L), 
                 class = c("tbl_df", "tbl", "data.frame"))

dat %>% 
  mutate(obs = 1:n()) %>% 
  pivot_longer(-obs, names_to="var", values_to="vals") %>% 
  pivot_wider(names_from="obs", values_from="vals", names_prefix="obs_")
#> # A tibble: 7 × 3
#>   var   obs_1  obs_2
#>   <chr> <dbl>  <dbl>
#> 1 total 9410  12951.
#> 2 op    3897.  6977.
#> 3 ox    2200   4921.
#> 4 ox15   183.   694.
#> 5 ox30   133.   368.
#> 6 hy     283.  1146.
#> 7 hy10    NA    434.

^{Created on 2022-02-03 by the reprex package (v2.0.1)}

Transposing specific columns to the rows in R

Another option is to replace the summed score (i.e. in question 12 and 22) with NA. From there you can use pivot_longer with score:C and values_drop_na. Then convert the rows where name is score to NA. Finally unite the question_id and name columns.

df %>%
  mutate(score = ifelse(!is.na(A), NA, score)) %>%
  pivot_longer('score':'C', values_drop_na = TRUE) %>%
  mutate(name = na_if(name, 'score')) %>%
  unite('question_id', c(question_id, name), na.rm = T)

#------
# A tibble: 10 x 3
   student_id question_id value
        <dbl> <chr>       <dbl>
 1          1 10              2
 2          1 11              3
 3          1 12_A            1
 4          1 12_B            2
 5          1 12_C            1
 6          2 20              2
 7          2 21              1
 8          2 22_A            1
 9          2 22_B            1
10          2 22_C            3

Transposing a dataframe maintaining the first column as heading

Here is one way

tmydf = setNames(data.frame(t(mydf[,-1])), mydf[,1])

---

Related Topics