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.
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 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)) 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 :-)
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) 
as.data.frame(t(df)) the row names and column names are transposed and the data remains numeric.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') 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)) 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 