Here are multiple scenarios of how to transpose data frame in R. Not the most frequently used transformation, but might be necessary, especially if you are getting your data from Excel.
transpose data frame in R easy way
It is an easy task if your data frame row names are what you want as column names.
Here is how it looks.
head(mtcars) # mpg cyl disp hp drat wt qsec vs am gear carb #Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 #Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 #Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 #Hornet 4 Drive 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1 #Hornet Sportabout 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2 #Valiant 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1
transpose by using base R
If everything looks fine, and you want to transpose the data frame in R, then here is how with the function t.
as.data.frame(t(head(mtcars))) # Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive Hornet Sportabout Valiant #mpg 21.00 21.000 22.80 21.400 18.70 18.10 #cyl 6.00 6.000 4.00 6.000 8.00 6.00 #disp 160.00 160.000 108.00 258.000 360.00 225.00 #hp 110.00 110.000 93.00 110.000 175.00 105.00 #drat 3.90 3.900 3.85 3.080 3.15 2.76 #wt 2.62 2.875 2.32 3.215 3.44 3.46 #qsec 16.46 17.020 18.61 19.440 17.02 20.22 #vs 0.00 0.000 1.00 1.000 0.00 1.00 #am 1.00 1.000 1.00 0.000 0.00 0.00 #gear 4.00 4.000 4.00 3.000 3.00 3.00 #carb 4.00 4.000 1.00 1.000 2.00 1.00
If that is not the case, look down below.
transpose by using sjmisc or tidyverse
Function rotate_df from sjmisc package allows customizing transposing process.
One of the situations might be that you transpose the data frame while keeping headers as a separate column. Here is the solution and result of that.
sjmisc::rotate_df(head(mtcars), rn = "category") # category Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive Hornet Sportabout Valiant #1 mpg 21.00 21.000 22.80 21.400 18.70 18.10 #2 cyl 6.00 6.000 4.00 6.000 8.00 6.00 #3 disp 160.00 160.000 108.00 258.000 360.00 225.00 #4 hp 110.00 110.000 93.00 110.000 175.00 105.00 #5 drat 3.90 3.900 3.85 3.080 3.15 2.76 #6 wt 2.62 2.875 2.32 3.215 3.44 3.46 #7 qsec 16.46 17.020 18.61 19.440 17.02 20.22 #8 vs 0.00 0.000 1.00 1.000 0.00 1.00 #9 am 1.00 1.000 1.00 0.000 0.00 0.00 #10 gear 4.00 4.000 4.00 3.000 3.00 3.00 #11 carb 4.00 4.000 1.00 1.000 2.00 1.00
The other way around this is by using tidyverse capabilities. It is a little bit longer but a more flexible approach that is helpful in other situations.
To transpose the data frame, you will need packages like dplyr, tidyr, and tibble.
require(dplyr) require(tidyr) require(tibble) head(mtcars) %>% rownames_to_column() %>% pivot_longer(, cols = -rowname) %>% pivot_wider(, names_from = rowname) %>% rename("category" = 1) %>% as.data.frame() # category Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive Hornet Sportabout Valiant #1 mpg 21.00 21.000 22.80 21.400 18.70 18.10 #2 cyl 6.00 6.000 4.00 6.000 8.00 6.00 #3 disp 160.00 160.000 108.00 258.000 360.00 225.00 #4 hp 110.00 110.000 93.00 110.000 175.00 105.00 #5 drat 3.90 3.900 3.85 3.080 3.15 2.76 #6 wt 2.62 2.875 2.32 3.215 3.44 3.46 #7 qsec 16.46 17.020 18.61 19.440 17.02 20.22 #8 vs 0.00 0.000 1.00 1.000 0.00 1.00 #9 am 1.00 1.000 1.00 0.000 0.00 0.00 #10 gear 4.00 4.000 4.00 3.000 3.00 3.00 #11 carb 4.00 4.000 1.00 1.000 2.00 1.00
transpose data frame in R the hard way
Let’s say that you want to transpose a data frame, but the potential headers are located in a separate column. That is the category column in the data frame below.
x <- head(mtcars) x$category <- rownames(x) rownames(x) <- NULL x # mpg cyl disp hp drat wt qsec vs am gear carb category #1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 Mazda RX4 #2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 Mazda RX4 Wag #3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 Datsun 710 #4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1 Hornet 4 Drive #5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2 Hornet Sportabout #6 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1 Valiant
One of the solutions might be that you set the necessary data frame row names as I deleted them above. But if that is not what you want, then here is a two-way how to deal with that.
A little bit longer process is by using base R capabilities. It is necessary to get data from the column that contains headers and then delete that before transposing. The column is the last one, and I used function ncol to count data frame columns.
cn <- x$category newx <- as.data.frame(t(x[, -ncol(x)])) names(newx) <- cn newx$category <- rownames(newx) rownames(newx) <- NULL cbind(category = newx$category, newx[, -ncol(newx)]) # category Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive Hornet Sportabout Valiant #1 mpg 21.00 21.000 22.80 21.400 18.70 18.10 #2 cyl 6.00 6.000 4.00 6.000 8.00 6.00 #3 disp 160.00 160.000 108.00 258.000 360.00 225.00 #4 hp 110.00 110.000 93.00 110.000 175.00 105.00 #5 drat 3.90 3.900 3.85 3.080 3.15 2.76 #6 wt 2.62 2.875 2.32 3.215 3.44 3.46 #7 qsec 16.46 17.020 18.61 19.440 17.02 20.22 #8 vs 0.00 0.000 1.00 1.000 0.00 1.00 #9 am 1.00 1.000 1.00 0.000 0.00 0.00 #10 gear 4.00 4.000 4.00 3.000 3.00 3.00 #11 carb 4.00 4.000 1.00 1.000 2.00 1.00
As I mentioned above, you can use packages from tidyverse like dplyr and tidyr to deal with the less handy situation.
require(dplyr) require(tidyr) x %>% pivot_longer(, cols = -category) %>% pivot_wider(, names_from = category) %>% rename("category" = 1) %>% as.data.frame() # category Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive Hornet Sportabout Valiant #1 mpg 21.00 21.000 22.80 21.400 18.70 18.10 #2 cyl 6.00 6.000 4.00 6.000 8.00 6.00 #3 disp 160.00 160.000 108.00 258.000 360.00 225.00 #4 hp 110.00 110.000 93.00 110.000 175.00 105.00 #5 drat 3.90 3.900 3.85 3.080 3.15 2.76 #6 wt 2.62 2.875 2.32 3.215 3.44 3.46 #7 qsec 16.46 17.020 18.61 19.440 17.02 20.22 #8 vs 0.00 0.000 1.00 1.000 0.00 1.00 #9 am 1.00 1.000 1.00 0.000 0.00 0.00 #10 gear 4.00 4.000 4.00 3.000 3.00 3.00 #11 carb 4.00 4.000 1.00 1.000 2.00 1.00
Leave a Reply