Reputation: 887
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.
Upvotes: 5
Views: 16040
Reputation: 102920
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
Upvotes: 0
Reputation: 73842
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))
Upvotes: 7
Reputation: 161155
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 :-)
Upvotes: 5
Reputation: 887991
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))
Upvotes: 1
Reputation: 2301
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)
Upvotes: 1