Search code examples
rtidyrreshapemelt

Reshape data table from wide to long with transpose

I have a dataset (Sample_pH):

Head(Sample_pH)

Mill   Acid `1_day`          `3_days` `1_week` `2_weeks` `4_weeks` `2_months` `3_months` `6-7_months`
  <chr> <dbl> <chr>               <dbl>    <dbl>     <dbl>     <dbl>      <dbl>      <dbl> <chr>       
1 Gävle  0    10.5                12.0     10.9      10.7      10.6       10.1       10    9.81        
2 Gävle  0.5  8.7899999999999~    10        9.29      9.08      9.39       9.13       9.14 8.86        
3 Gävle  0.75 8.0500000000000~     8.95     8.33      8.26      8.24       8.22       8.25 7.44        
4 Gävle  1    6.7                  7.82     7.77      8.02      8.19       7.79       7.97 6.99        
5 Gävle  1.25 6.52                 7.43     7.33      7.11      7.72       7.88       7.91 6.96        
6 Gävle  1.5  6.41                 7.25     7.28      6.92      7.63       7.01       7.64 6.7

The Mill column actually have 338 rows and several different sample names (Gävle, Obbola, Aspa etc). I use tis data set to make fawcet_wraped plots:

Time_plots

I would like to reshape this data set so that in the plots each panel is a Mill instead of a time_stamp with the Time stamp being the group plotted instead of Mill while keeping the x and y as is. In order to do this I have to reshape the data set, but the way I understand it I have both transpose and change from wide to long format, getting something like this:

Acid    Time        Gävle   Obbola  Munksund            
0       1_day       10.5    13      15.3
0.5     1_day       8.79    11.03   12.82
0.75    1_day       8.05    10.59   12.27
1       1_day       6.7     9.72    11.6
1.25    1_day       6.52    9.06    10.41
1.5     1_day       6.41    8.01    10.3
1.75    1_day       6.24    8.48    5.78
2       1_day       6.37    8.49    9.94
2.25    1_day       5.08    8.03    7.13

I tried using melt (from both reshape2 and data.table) but since the patterns call is not recognised by my version despite reloading both of the librarys I think I have to use reshape. I have looked at several other examples but none seems to have the transpose problem I have, and I can´t seem to figure it out by myself. Help please?

Edit: As per request: dput(Sample_pH): https://docs.google.com/document/d/1AfJWPL1GjzAskRKzXWr8MDEvlHBrY-SxjKDZ7LY7lNY/edit?usp=sharing

And the ggplot code I use for making the graphs:

ggplot(gather(Sample_pH, key=Time, value="value", -"Mill", -"Acid"),
          aes(x=Acid, y=as.numeric(value), colour=Mill, group=Mill) )  +
              geom_line() +
               scale_color_ucscgb() +
              facet_wrap(~ Time, scales = 'free', ncol =4) +
               ylab("pH") +
              xlab("ml 12.1 M HCl") +
              theme(plot.title = element_text(hjust = 0.5),
                    legend.title = element_blank())
Solution

  • A better approach is to use the new pivot_londer and pivot_wider functions from the tidyr package.

    Easier convention to use and has convenient text manipulation options built in. In this case removing the "X." that was added to column names.

    df <- read.table(header=TRUE, text="Mill   Acid `1_day`  `3_days` `1_week` `2_weeks` `4_weeks` `2_months` `3_months` `6-7_months`
Gävle  0    10.5      12.0     10.9      10.7      10.6       10.1       10    9.81        
Gävle  0.5  8.79    10        9.29      9.08      9.39       9.13       9.14 8.86        
Gävle  0.75 8.05     8.95     8.33      8.26      8.24       8.22       8.25 7.44        
Gävle  1    6.7       7.82     7.77      8.02      8.19       7.79       7.97 6.99        
Gävle  1.25 6.52     7.43     7.33      7.11      7.72       7.88       7.91 6.96        
Gävle  1.5  6.41     7.25     7.28      6.92      7.63       7.01       7.64 6.7   
Obbola  0    10.5    12.0     10.9      10.7      10.6       10.1       10    9.81        
Obbola  0.5  8.79    10        9.29      9.08      9.39       9.13       9.14 8.86        
Obbola  0.75 8.05     8.95     8.33      8.26      8.24       8.22       8.25 7.44        
Obbola  1    6.7     7.82     7.77      8.02      8.19       7.79       7.97 6.99        
Obbola  1.25 6.52   7.43     7.33      7.11      7.72       7.88       7.91 6.96        
Obbola  1.5  6.41   7.25     7.28      6.92      7.63       7.01       7.64 6.7   ")

library(tidyr)

longdf <- df %>% pivot_longer(-c("Mill", "Acid"), names_to="Time", values_to = "value", names_prefix="X.")

answer <-longdf %>% pivot_wider(id_cols= c("Time", "Acid" ), names_from = "Mill" )