How to Flip Rows and Columns in R

How do I flip rows and columns in R

Assuming you have this data frame df, see data below.

  Country.Name 1997 1998 1999 2000
1     Country1    1    1    1    1
2     Country2    2    4    7   10
3     Country3    4    2    1    5

First you have to transpose all data frame except the first column. The result being a matrix that we need to convert to a data frame. Finally, we assign as column names of df2the first column of the original data frame df.

df2 <- data.frame(t(df[-1]))
colnames(df2) <- df[, 1]

Output:

     Country1 Country2 Country3
1997        1        2        4
1998        1        4        2
1999        1        7        1
2000        1       10        5

Data:

df <- structure(list(Country.Name = c("Country1", "Country2", "Country3"
), `1997` = c(1L, 2L, 4L), `1998` = c(1L, 4L, 2L), `1999` = c(1L, 
7L, 1L), `2000` = c(1L, 10L, 5L)), .Names = c("Country.Name", 
"1997", "1998", "1999", "2000"), class = "data.frame", row.names = c(NA, 
-3L))

How can I transpose my dataframe so that the rows and columns switch in r?

There are two ways you can do this. The first one is using t to just transpose the dataframe as if it would be a matrix (indeed the result of t is a matrix, not a dataframe).

The other option is to take the tidy data approach and use tidyr::spread along with tidyr::gather. Both have similar results although the second one is more versatile as it can be applied partially. Also notice that when using t the first column (which has type chr) will become the first row in the new matrix and therefore the entire matrix will be converted into a chr matrix, while gather+spread keeps the columns numeric.

library(tidyr)

df <- read.table(text = "Age Kodiak Banana Banff Montreal Fairfax
Age_1 5 6 7 9 2 
Age_2 7 6 4 3 2
Age_3 5 3 8 5 9", header = T)

t(df)
#>          [,1]    [,2]    [,3]   
#> Age      "Age_1" "Age_2" "Age_3"
#> Kodiak   "5"     "7"     "5"    
#> Banana   "6"     "6"     "3"    
#> Banff    "7"     "4"     "8"    
#> Montreal "9"     "3"     "5"    
#> Fairfax  "2"     "2"     "9"

df %>%
  gather("location", "value", 2:ncol(df)) %>%
  spread(Age, value)
#>   location Age_1 Age_2 Age_3
#> 1   Banana     6     6     3
#> 2    Banff     7     4     8
#> 3  Fairfax     2     2     9
#> 4   Kodiak     5     7     5
#> 5 Montreal     9     3     5

how do you flip rows to columns in r?

With tidyverse, we can use pivot_wider to get the data into the wide format.

library(tidyverse)

df %>% 
  pivot_wider(names_from = "Market", values_from = "N")

Output

  Date     First Second Third
  <chr>    <int>  <int> <int>
1 11/21/21     2      3     4
2 11/22/21     9      6     7

Data

df <- structure(list(Market = c("First", "Second", "Third", "First", 
"Second", "Third"), Date = c("11/21/21", "11/21/21", "11/21/21", 
"11/22/21", "11/22/21", "11/22/21"), N = c(2L, 3L, 4L, 9L, 6L, 
7L)), class = "data.frame", row.names = c(NA, -6L))

How to flip column variable names to row labels?

You can use pivot_longer and pivot_wider -

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = -Trial_Type) %>%
  pivot_wider(names_from = Trial_Type, values_from = value)

#  name          Pre  Post
#  <chr>       <dbl> <dbl>
#1 CT_tib_all  0.244 0.254
#2 CT_lum_all  0.209 0.211
#3 CT_tho_all  0.309 0.302
#4 CT_gps_all  0.315 0.313
#5 CT_vest_all 0.31  0.316

In data.table -

library(data.table)

dcast(melt(setDT(df), id.vars  = 'Trial_Type'), 
      variable~Trial_Type, vvalue.var = 'value')

Summarize and Transpose rows to columns in R

You can take the following approach:

library(dplyr)

df %>%
  group_by(Program) %>%
  summarise(
    `Mean Age` = mean(Age),
    ENG = sum(Language=="Eng"),
    KOR = sum(Language=="Kor"),
    Female = sum(Gender=="F"),
    Male = sum(Gender=="M"),
    .groups="drop"
  )

Output:

# A tibble: 3 x 6
  Program `Mean Age`   ENG   KOR Female  Male
  <chr>        <dbl> <int> <int>  <int> <int>
1 A             23.3     2     1      2     1
2 B             22       0     1      0     2
3 C             53       0     0      1     0

Note: .groups is a special variable for dplyr functions. The way it's used here is equivalent to using %>% ungroup() after the calculation. If you type any other name in the summarise function, it will assume it's a column name.

Transpose columns to rows in R dataframe

We may use pivot_longer

library(tidyr)
pivot_longer(df, cols = starts_with('cheese'), 
   names_to = c("product", ".value"), names_sep = "\\.", 
     values_transform = list(kg = as.integer))

-output

# A tibble: 6 × 4
  level1 column.other product    kg
  <chr>  <chr>        <chr>   <int>
1 A      yes          cheese1    58
2 A      yes          cheese2    11
3 B      yes          cheese1    63
4 B      yes          cheese2    22
5 C      yes          cheese1    33
6 C      yes          cheese2    20

How to transpose a dataframe in tidyverse?

Try with add_rownames

add_rownames(mtcars) %>% 
         gather(var, value, -rowname) %>% 
         spread(rowname, value)

In the newer version, rownames_to_column replaces add_rownames

mtcars %>%
   rownames_to_column %>% 
   gather(var, value, -rowname) %>% 
   spread(rowname, value)

In the even newer version, pivot_wider replaces spread:

mtcars %>%
   tibble::rownames_to_column() %>%  
   pivot_longer(-rowname) %>% 
   pivot_wider(names_from=rowname, values_from=value)