Converting Rows into columns in R

Question

I have a data table like the following:

ID    Date      parameter1 parameter2  parameter3 
1     01/01/20  10         11          12
1     03/01/20  12         13          14
1     05/01/20  11         15          14
2     02/01/20  17         16          15
2     07/01/20  14         12          18
3     04/02/20  11         12          13
3     06/02/20  12         14          16

Now if I have to make a ggplot graph as trends for these values which is tagged to unique ID, what code do I write

Hello and welcome to SO :) In order for us to help you, please provide a [reproducible](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) example. For example, to produce a minimal data set, you can use `head()`, `subset()`. **Then use `dput()` to give us something that can be put in R immediately.** Alternatively, you can use base R datasets such as `mtcars`, `iris`, *etc*. Also, could you send what you have already tried? — Paul, Aug 20 '20 at 07:47
it seems, at least from your headline, that you want to use a pivot table. maybe this can help you: https://rstudio-conf-2020.github.io/r-for-excel/pivot-tables.html — D.J, Aug 20 '20 at 07:51

score 0 · Answer 1 · answered Aug 20 '20 at 08:29

There are many ways to plot this data as you describe. This may not be what you're looking for, so please be more specific if you need something else.

First we start with your data in the format it was presented:

df <- structure(list(ID = c(1L, 1L, 1L, 2L, 2L, 3L, 3L), Date = structure(c(1L, 
3L, 5L, 2L, 7L, 4L, 6L), .Label = c("01/01/20", "02/01/20", "03/01/20", 
"04/02/20", "05/01/20", "06/02/20", "07/01/20"), class = "factor"), 
    parameter1 = c(10L, 12L, 11L, 17L, 14L, 11L, 12L), parameter2 = c(11L, 
    13L, 15L, 16L, 12L, 12L, 14L), parameter3 = c(12L, 14L, 14L, 
    15L, 18L, 13L, 16L)), class = "data.frame", row.names = c(NA, 
-7L))

df
#>   ID     Date parameter1 parameter2 parameter3
#> 1  1 01/01/20         10         11         12
#> 2  1 03/01/20         12         13         14
#> 3  1 05/01/20         11         15         14
#> 4  2 02/01/20         17         16         15
#> 5  2 07/01/20         14         12         18
#> 6  3 04/02/20         11         12         13
#> 7  3 06/02/20         12         14         16

We want the Date column to represent actual dates (at the moment it's a character or factor vector), so we convert it with strptime:

df$Date <- as.POSIXct(strptime(df$Date, format = "%d/%m/%y", tz = "GMT"))

To plot all your data in a single chart, it will make it easier if we pivot it from wide format to long format. We can do this with tidyr::pivot_longer:

df <- tidyr::pivot_longer(df, c("parameter1", "parameter2", "parameter3"))
df
#> # A tibble: 21 x 4
#>       ID Date                name       value
#>    <int> <dttm>              <chr>      <int>
#>  1     1 2020-01-01 00:00:00 parameter1    10
#>  2     1 2020-01-01 00:00:00 parameter2    11
#>  3     1 2020-01-01 00:00:00 parameter3    12
#>  4     1 2020-01-03 00:00:00 parameter1    12
#>  5     1 2020-01-03 00:00:00 parameter2    13
#>  6     1 2020-01-03 00:00:00 parameter3    14
#>  7     1 2020-01-05 00:00:00 parameter1    11
#>  8     1 2020-01-05 00:00:00 parameter2    15
#>  9     1 2020-01-05 00:00:00 parameter3    14
#> 10     2 2020-01-02 00:00:00 parameter1    17
#> # ... with 11 more rows

Now we are ready to plot. We will produce 3 facets, one for each ID. These will contain the data points from each ID, with each parameter coloured differently. We will then draw on a best fitting straight line using geom_smooth:

library(ggplot2)

ggplot(df, aes(Date, value, colour = name)) + 
  geom_point() +
  geom_smooth(method = lm, formula = y ~ x, se = FALSE, linetype = 2) +
  scale_x_datetime(date_labels = "%d-%b") +
  facet_wrap(ID~., scales = "free_x") +
  theme(axis.text.x.bottom = element_text(angle = 45, hjust = 1))

^{Created on 2020-08-20 by the reprex package (v0.3.0)}

Converting Rows into columns in R

1 Answers1