columns to rows in r

Question

I have a dataframe like this:

DATE	CZK	EUR	USD
2021-07-25	25	15,5555684	4

And I want to turn it into this table:

DATE	CP	mnozstvi
2021-07-25	CZK	25
2021-07-25	EUR	15,5555684
2021-07-25	DOL	4

My data.frame is much larger, this is just minimal example. I seek some versatile solution. I managed to do that by function gather() like this:

data.frame %>% gather(CP,,CZK,EUR,USD) %>% rename(mnozstvi = value)

But it changed the numbers as some of them vere decimal adn I dnt know why. Any ideae how to do that easily? thank you.

The numbers were probably changed because you have `,` instead of `.` as a decimal separator I suspect — GuedesBF, Aug 01 '21 at 16:30

score 2 · Accepted Answer · answered Aug 01 '21 at 15:45

You can try melt within data.table package

> melt(setDT(df), id.vars = "DATE",variable.name = "CP",value.name = "mnozstvi")
         DATE  CP mnozstvi
1: 2021-07-25 CZK 25.00000
2: 2021-07-25 EUR 15.55557
3: 2021-07-25 USD  4.00000

or using stack

> setDT(df)[,setNames(rev(stack(.SD)),c("CP","mnozstvi")),DATE]
         DATE  CP mnozstvi
1: 2021-07-25 CZK 25.00000
2: 2021-07-25 EUR 15.55557
3: 2021-07-25 USD  4.00000

GuedesBF · Answer 2 · 2021-08-01T16:53:51.790

A dplyr solution:
This question is most suited for an answer with pivot_longer(), which is pretty much the modern version of gather.

library(dplyr)

df%>%pivot_longer(cols=c(CZK, EUR, USD), names_to="CP", values_to = 'mnozstvi')

# A tibble: 3 x 3
  DATE       CP    mnozstvi
  <chr>      <chr>    <dbl>
1 2021-07-25 CZK       25  
2 2021-07-25 EUR       15.6
3 2021-07-25 USD        4

data

df<-data.frame(DATE=c('2021-07-25'), CZK=25, EUR=15.5555684, USD=4)

If your data has characters with comas,as separators, like this,

data2

df<-data.frame(DATE=c('2021-07-25'), CZK=25, EUR='15,5555684', USD=4)

You may have to do some transformation before the pivot_longer operation:

df%>%mutate(across(CZK:USD, ~as.numeric(str_replace_all(.x, ',', '.'))))%>%
        pivot_longer(cols=c(CZK, EUR, USD), names_to="CP", values_to = 'mnozstvi')

columns to rows in r

2 Answers2