Converting multiple column dataframe to long 3 column dataframe with R

Question

I want to convert this

> tab2
      X         ONE           TWO         THREE        FOUR
1   ONE  1.00000000  0.7182953806 -0.0360738799  0.05865445
2   TWO  0.71829538  1.0000000000 -0.0004615852 -0.05088079
3 THREE -0.03607388 -0.0004615852  1.0000000000  0.72551871
4  FOUR  0.05865445 -0.0508807908  0.7255187058  1.00000000

to csv file with such data

ONE, ONE, 1.00000000
ONE, TWO, 0.7182953806
ONE, THREE, -0.0360738799
ONE, FOUR, 0.05865445
TWO, ONE, 0.71829538
TWO, TWO, 1.0000000000
TWO, TREE, -0.0004615852
...

How to do this with R?

P.S. Real data contains more than 100 columns and strings.

Try `df %>% pivot_wider(names_from = V2, values_from=V3)` from `tidyverse` package — Duck, Aug 26 '20 at 13:04

score 1 · Answer 1 · answered Aug 26 '20 at 13:09

You can use pivot_longer() from tidyr (part of the tidyverse) to convert your data to the desired format, and then save it as a csv using either base r write.csv() or readr (tidyverse) write_csv().

library(tidyverse)

tab2_newformat <- tab2 %>%
     pivot_longer(cols = -1)

write_csv(tab2_newformat, "tab2.csv", col_names = F)

ThomasIsCoding · Accepted Answer · 2020-08-26T13:28:21.530

Some base R options:

Using expand.grid

setNames(
  cbind(
    rev(expand.grid(df$X, names(df[-1]))),
    c(t(df[-1]))
  ), c("X1", "X2", "Val")
)

Using stack

setNames(
  cbind(
    rep(df$X, ncol(df) - 1),
    rev(stack(df[-1]))
  )[c(2,1,3)],
  c("X1", "X2", "Val")
)

which gives

      X1    X2           Val
1    ONE   ONE  1.0000000000
2    ONE   TWO  0.7182953800
3    ONE THREE -0.0360738800
4    ONE  FOUR  0.0586544500
5    TWO   ONE  0.7182953806
6    TWO   TWO  1.0000000000
7    TWO THREE -0.0004615852
8    TWO  FOUR -0.0508807908
9  THREE   ONE -0.0360738799
10 THREE   TWO -0.0004615852
11 THREE THREE  1.0000000000
12 THREE  FOUR  0.7255187058
13  FOUR   ONE  0.0586544500
14  FOUR   TWO -0.0508807900
15  FOUR THREE  0.7255187100
16  FOUR  FOUR  1.0000000000

Converting multiple column dataframe to long 3 column dataframe with R

2 Answers2