How to transpose a data frame in R using dplyr?

Question

I'm trying to figure this out but I cannot get anything to work.

Here is my data.frame.

Type   Claims   Adds   Family   Individual   ES     SO
A       0         10     1          9         6      4         
B       0         17     1          9         2      6      
C       2         16     2          5         6      4
D       6         15     1          6         6      4
E       7         12     3          9         8      6

My goal is to turn into this

Type             A      B    C     D    E
Claims           0      0    2     6    7
Adds             10     17   16    15   12
Family           1      1    2     1    3
Individuals      9      9    5     6    9
ES               6      2    6     6    8
SO               4      6    4     4    6

I've been trying to use transpose(data) but it comes out wrong.

Answer 1

Here is a base R option using read.table

read.table(
  text = paste0(sapply(as.list(rbind(names(dat), dat)), toString), collapse = " \n"),
  header = TRUE,
  sep = ","
)

which gives

        Type  A  B  C  D  E
1     Claims  0  0  2  6  7
2       Adds 10 17 16 15 12
3     Family  1  1  2  1  3
4 Individual  9  9  5  6  9
5         ES  6  2  6  6  8
6         SO  4  6  4  4  6

Data

> dput(dat)
structure(list(Type = c("A", "B", "C", "D", "E"), Claims = c(0L, 
0L, 2L, 6L, 7L), Adds = c(10L, 17L, 16L, 15L, 12L), Family = c(1L,
1L, 2L, 1L, 3L), Individual = c(9L, 9L, 5L, 6L, 9L), ES = c(6L,
2L, 6L, 6L, 8L), SO = c(4L, 6L, 4L, 4L, 6L)), class = "data.frame", row.names = c(NA,
-5L))

---

A data.table option is using fread

> fread(text = paste0(sapply(as.list(rbind(names(dat), dat)), toString), collapse = " \n"))
         Type  A  B  C  D  E
1:     Claims  0  0  2  6  7
2:       Adds 10 17 16 15 12
3:     Family  1  1  2  1  3
4: Individual  9  9  5  6  9
5:         ES  6  2  6  6  8
6:         SO  4  6  4  4  6

Answer 2

Using t, gives a matrix, thus as.data.frame.

library(dplyr)
dat[-1] %>% t() %>% as.data.frame() %>% setNames(dat[,1])
#             A  B  C  D  E
# Claims      0  0  2  6  7
# Adds       10 17 16 15 12
# Family      1  1  2  1  3
# Individual  9  9  5  6  9
# ES          6  2  6  6  8
# SO          4  6  4  4  6

or without dplyr:

setNames(as.data.frame(t(dat[-1])), dat[,1])
#             A  B  C  D  E
# Claims      0  0  2  6  7
# Adds       10 17 16 15 12
# Family      1  1  2  1  3
# Individual  9  9  5  6  9
# ES          6  2  6  6  8
# SO          4  6  4  4  6

Edit

To get the rownames as column there's tibble::rownames_to_column

dat[-1] %>% t() %>% as.data.frame() %>% setNames(dat[,1]) %>% 
  tibble::rownames_to_column("xyz")
#          xyz  A  B  C  D  E
# 1     Claims  0  0  2  6  7
# 2       Adds 10 17 16 15 12
# 3     Family  1  1  2  1  3
# 4 Individual  9  9  5  6  9
# 5         ES  6  2  6  6  8
# 6         SO  4  6  4  4  6

or without packages:

setNames(data.frame(names(dat)[-1], unname(t(dat[-1]))), c("xyz", dat[,1]))
#          xyz  A  B  C  D  E
# 1     Claims  0  0  2  6  7
# 2       Adds 10 17 16 15 12
# 3     Family  1  1  2  1  3
# 4 Individual  9  9  5  6  9
# 5         ES  6  2  6  6  8
# 6         SO  4  6  4  4  6

---

Data:

dat <- structure(list(Type = c("A", "B", "C", "D", "E"), Claims = c(0L, 
0L, 2L, 6L, 7L), Adds = c(10L, 17L, 16L, 15L, 12L), Family = c(1L, 
1L, 2L, 1L, 3L), Individual = c(9L, 9L, 5L, 6L, 9L), ES = c(6L, 
2L, 6L, 6L, 8L), SO = c(4L, 6L, 4L, 4L, 6L)), class = "data.frame", row.names = c(NA, 
-5L))

Answer 3

Yet another option, using tidyr::pivot_*:

library(dplyr)    # rename, %>%
library(tidyr)    # pivot_*
pivot_longer(dat, -Type) %>%
  pivot_wider(name, names_from="Type", values_from="value") %>%
  rename(Type=name)
# # A tibble: 6 x 6
#   Type           A     B     C     D     E
#   <chr>      <int> <int> <int> <int> <int>
# 1 Claims         0     0     2     6     7
# 2 Adds          10    17    16    15    12
# 3 Family         1     1     2     1     3
# 4 Individual     9     9     5     6     9
# 5 ES             6     2     6     6     8
# 6 SO             4     6     4     4     6

I agree with the notion that if not everything is the same class (integer here), then columns will be cast into another class. For instance, if we add a string column:

dat$QX <- "A"

then our efforts to pivot/transpose are thwarted, for good reason:

pivot_longer(dat, -Type) %>%
  pivot_wider(name, names_from="Type", values_from="value") %>%
  rename(Type=name)
# Error: Can't combine `Claims` <integer> and `QX` <character>.
# Run `rlang::last_error()` to see where the error occurred.

If this is a known thing for you, consider pre-converting to the "higher" class (string here) before pivoting.

dat %>%
  mutate_if(Negate(is.character), as.character) %>%
  pivot_longer(-Type) %>%
  pivot_wider(name, names_from="Type", values_from="value") %>%
  rename(Type=name)
# # A tibble: 7 x 6
#   Type       A     B     C     D     E    
#   <chr>      <chr> <chr> <chr> <chr> <chr>
# 1 Claims     0     0     2     6     7    
# 2 Adds       10    17    16    15    12   
# 3 Family     1     1     2     1     3    
# 4 Individual 9     9     5     6     9    
# 5 ES         6     2     6     6     8    
# 6 SO         4     6     4     4     6    
# 7 QX         A     A     A     A     A    

and then optionally convert the values back to integer or whatever ... recognizing that the QX row will certainly be problematic :-)

Answer 4

Here is an option with transpose from data.table

library(data.table)
out <- data.table::transpose(setDT(dat), make.names = 'Type', keep.names = 'Type')

-output

out
#         Type  A  B  C  D  E
#1:     Claims  0  0  2  6  7
#2:       Adds 10 17 16 15 12
#3:     Family  1  1  2  1  3
#4: Individual  9  9  5  6  9
#5:         ES  6  2  6  6  8
#6:         SO  4  6  4  4  6

It automatically changes the type as in the original data

str(out)
#Classes ‘data.table’ and 'data.frame': 6 obs. of  6 variables:
# $ Type: chr  "Claims" "Adds" "Family" "Individual" ...
# $ A   : int  0 10 1 9 6 4
# $ B   : int  0 17 1 9 2 6
# $ C   : int  2 16 2 5 6 4
# $ D   : int  6 15 1 6 6 4
# $ E   : int  7 12 3 9 8 6

---

If we want to use pipes

library(magrittr)
dat %>% 
    data.table::transpose(make.names = 'Type', keep.names = 'Type')

data

dat <- structure(list(Type = c("A", "B", "C", "D", "E"), Claims = c(0L, 
0L, 2L, 6L, 7L), Adds = c(10L, 17L, 16L, 15L, 12L), Family = c(1L, 
1L, 2L, 1L, 3L), Individual = c(9L, 9L, 5L, 6L, 9L), ES = c(6L, 
2L, 6L, 6L, 8L), SO = c(4L, 6L, 4L, 4L, 6L)), class = "data.frame",
row.names = c(NA, 
-5L))

Answer 5

If Type is a column rather than rownames the transpose will coerce everything to character. You can create rownames and transpose in base R with:

rownames(df) <- df$Type
df <- df[ , -1]
df <- t(df)
df <- as.data.frame(df)