How to Transpose a Dataframe in Tidyverse

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)

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))

Transposing in dplyr

I think you want tidyr rather than dplyr:

library(tidyr)
library(dplyr)
df %>% mutate(group = 1) %>%
       spread(HEADER, price)

  group AWAY_TEAM          AWAY_TRPM HOME_TEAM         HOME_TRPM
1     1       NOP -0.845186446996287       CHA 0.863104076023855

Using this, you can specify your groupings - and you can add on select(-group) to remove them later.

How do I transpose a tibble() in R

As Sotos has mentioned it, you just need to re-declare your matrix as a tibble:

as_tibble(cbind(nms = names(df), t(df)))

Transpose a data frame and add column names as variables in R

We could use pivot_longer

library(tidyr)
library(dplyr)
df %>% 
   mutate(Date = as.Date(Date)) %>%
   pivot_longer(cols = -Date, names_to = c("Ticker", ".value"), 
       names_sep = "\\.") %>% 
   rename(Data = Close)

Transpose data in R multiple variables (preferrably with tidyverse or stargazer)

Get data in long format and then wide :

library(tidyr)
df %>%
  pivot_longer(cols = -locationAndYear, names_to = 'variable') %>%
  pivot_wider(names_from = locationAndYear, values_from = value)

# variable  City_2015 City_2016 City_2018 City_2019
#  <chr>     <dbl>     <dbl>     <dbl>     <dbl>
#1 var1      0.425     0.27      0.468     0.336
#2 var2      0.412     0.259     0.454     0.604
#3 var3      0.854     0.842     0.816     0.842
#4 var4      0.844     0.834     0.833     0.852
#5 var5      0.853     0.856     0.848     0.887
#6 var6      0.368     0.456     0.43      0.525

Using data.table :

library(data.table)
dcast(melt(setDT(df), id.vars = 'locationAndYear'), 
      variable~locationAndYear, value.var = 'value')

data

df <- structure(list(locationAndYear = c("City_2015", "City_2016", 
"City_2018", "City_2019"), var1 = c(0.425, 0.27, 0.468, 0.336
 ), var2 = c(0.412, 0.259, 0.454, 0.604), var3 = c(0.854, 0.842, 
0.816, 0.842), var4 = c(0.844, 0.834, 0.833, 0.852), var5 = c(0.853, 
0.856, 0.848, 0.887), var6 = c(0.368, 0.456, 0.43, 0.525)), 
class = "data.frame", row.names = c(NA, -4L))

---

Related Topics