CODEWITHSUNDEEP
rdataframe
Klaas Wouters
Klaas Wouters

Reputation: 11

Wide table to long table

I have a dataframe called "results" that look like this

my dataframe:

result <- structure(list(D = c(2986.286, 2842.54, 2921), E = c(3020.458, 
2943.926, 2860.763), F = c(3008.644, 3142.134, 3002.515), G = c(2782.983, 
3135.148, 2873.025), H = c(2874.082, 3066.655, 2778.107), I = c(2592.377, 
3017.99, 2859.603), J = c(3051.184, 3011.467, 3007.769)), class = "data.frame", row.names = c("Above average", 
"Below average", "very Good"))

I have tried the following:

result_long <- pivot_longer(result, 1:7, names_to="combination", values_to ="price.m" )

But like this I loose my "index": "Above average",...,"Very good" As my result is:

# A tibble: 21 x 2
   letter value
   <chr>  <dbl>
 1 D      2986.
 2 E      3020.
 3 F      3009.
 4 G      2783.
 5 H      2874.
 6 I      2592.
 7 J      3051.
 8 D      2843.
 9 E      2944.
10 F      3142.
# ... with 11 more rows

Anyone know how I can achieve the same result but keep the column/index "Above average", "Very good", "Below average"?

Upvotes: 0

Views: 190

Answers (2)

akrun
akrun

Reputation: 887048

In base R, we may convert to table and wrap with as.data.frame

as.data.frame.table(as.matrix(result))

-output

            Var1 Var2     Freq
1  Above average    D 2986.286
2  Below average    D 2842.540
3      very Good    D 2921.000
4  Above average    E 3020.458
5  Below average    E 2943.926
6      very Good    E 2860.763
7  Above average    F 3008.644
8  Below average    F 3142.134
...

Upvotes: 1

TarJae
TarJae

Reputation: 78927

While a tibble can have row names (e.g., when converting from a regular data frame), they are removed when subsetting with the [ operator. A warning will be raised when attempting to assign non-NULL row names to a tibble.

Generally, it is best to avoid row names, because they are basically a character column with different semantics than every other column. https://tibble.tidyverse.org/reference/rownames.html

In your case pivot_longer is removing the rownames, but you could save the rownames as column with rownames_to_column from tibble package before transforming with pivot_longer like this:

library(tibble)
library(tidyr)
library(dplyr)

result_long <- result %>% 
  rownames_to_column("id") %>% 
  pivot_longer(
    -id, 
    names_to="combination", 
    values_to ="price.m" 
  )

A tibble: 21 x 3
   id            combination price.m
   <chr>         <chr>         <dbl>
 1 Above average D             2986.
 2 Above average E             3020.
 3 Above average F             3009.
 4 Above average G             2783.
 5 Above average H             2874.
 6 Above average I             2592.
 7 Above average J             3051.
 8 Below average D             2843.
 9 Below average E             2944.
10 Below average F             3142.
# ... with 11 more rows

Upvotes: 3

Related Questions