transpose data frame in R

How to transpose data frame in R

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





Posted

in

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *