Home > OS >  From wide format data to trend lines
From wide format data to trend lines

Time:07-23

I have this set with data in wide format, where each row should be a line in a trend-like type of plot. I am trying to reproduce a graph done in Excel with ggplot. Below is the Excel plot:

enter image description here

And for the MWE, please consider the data:


structure(list(`0.1` = c(0.98, 0.6, 0.34, 0.22, 0.17, 0.14, 0.12,
0.11, 0.11), `0.11` = c(0.99, 0.67, 0.39, 0.26, 0.19, 0.16, 0.14,
0.13, NA), `0.12` = c(1, 0.74, 0.45, 0.3, 0.22, 0.18, 0.15, 0.14,
NA), `0.13` = c(1, 0.8, 0.5, 0.34, 0.25, 0.2, 0.17, 0.16, NA),
    `0.14` = c(1, 0.84, 0.56, 0.38, 0.28, 0.22, 0.19, 0.17, NA
    ), `0.15` = c(1, 0.88, 0.61, 0.42, 0.31, 0.25, 0.21, 0.19,
    NA), `0.16` = c(1, 0.91, 0.66, 0.46, 0.34, 0.27, 0.23, 0.21,
    NA), `0.17` = c(1, 0.94, 0.71, 0.5, 0.38, 0.3, 0.26, 0.23,
    NA), `0.18` = c(1, 0.96, 0.75, 0.54, 0.41, 0.33, 0.28, 0.25,
    NA), `0.19` = c(1, 0.97, 0.79, 0.59, 0.45, 0.36, 0.31, 0.28,
    NA), `0.2` = c(1, 0.98, 0.82, 0.63, 0.48, 0.39, 0.33, 0.3,
    NA), `0.21` = c(1, 0.99, 0.85, 0.66, 0.52, 0.42, 0.36, NA,
    NA), `0.22` = c(1, 0.99, 0.88, 0.7, 0.55, 0.45, 0.39, NA,
    NA), `0.23` = c(1, 0.99, 0.9, 0.73, 0.59, 0.48, 0.42, NA,
    NA), `0.24` = c(1, 1, 0.92, 0.77, 0.62, 0.51, 0.45, NA, NA
    ), `0.25` = c(1, 1, 0.94, 0.8, 0.65, 0.54, 0.47, NA, NA),
    `0.26` = c(1, 1, 0.95, 0.82, 0.68, 0.58, 0.5, NA, NA), `0.27` = c(1,
    1, 0.96, 0.85, 0.71, 0.61, 0.53, NA, NA), `0.28` = c(1, 1,
    0.97, 0.87, 0.74, 0.63, 0.56, NA, NA), `0.29` = c(1, 1, 0.98,
    0.89, 0.77, 0.66, 0.59, NA, NA), `0.3` = c(1, 1, 0.98, 0.9,
    0.79, 0.69, 0.62, NA, NA), `0.31` = c(1, 1, 0.99, 0.92, 0.82,
    0.72, NA, NA, NA), `0.32` = c(1, 1, 0.99, 0.93, 0.84, 0.74,
    NA, NA, NA), `0.33` = c(1, 1, 0.99, 0.94, 0.86, 0.77, NA,
    NA, NA), `0.34` = c(1, 1, 0.99, 0.95, 0.87, 0.79, NA, NA,
    NA), `0.35` = c(1, 1, 1, 0.96, 0.89, 0.81, NA, NA, NA), `0.36` = c(1,
    1, 1, 0.97, 0.9, 0.83, NA, NA, NA), `0.37` = c(1, 1, 1, 0.97,
    0.92, 0.85, NA, NA, NA), `0.38` = c(1, 1, 1, 0.98, 0.93,
    0.86, NA, NA, NA), `0.39` = c(1, 1, 1, 0.98, 0.94, 0.88,
    NA, NA, NA), `0.4` = c(1, 1, 1, 0.99, 0.95, 0.89, NA, NA,
    NA), `0.41` = c(1, 1, 1, 0.99, 0.96, NA, NA, NA, NA), `0.42` = c(1,
    1, 1, 0.99, 0.96, NA, NA, NA, NA), `0.43` = c(1, 1, 1, 0.99,
    0.97, NA, NA, NA, NA), `0.44` = c(1, 1, 1, 0.99, 0.97, NA,
    NA, NA, NA), `0.45` = c(1, 1, 1, 1, 0.98, NA, NA, NA, NA),
    `0.46` = c(1, 1, 1, 1, 0.98, NA, NA, NA, NA), `0.47` = c(1,
    1, 1, 1, 0.98, NA, NA, NA, NA), `0.48` = c(1, 1, 1, 1, 0.99,
    NA, NA, NA, NA), `0.49` = c(1, 1, 1, 1, 0.99, NA, NA, NA,
    NA), `0.5` = c(1, 1, 1, 1, 0.99, NA, NA, NA, NA), `0.51` = c(1L,
    1L, 1L, 1L, NA, NA, NA, NA, NA), `0.52` = c(1L, 1L, 1L, 1L,
    NA, NA, NA, NA, NA), `0.53` = c(1L, 1L, 1L, 1L, NA, NA, NA,
    NA, NA), `0.54` = c(1L, 1L, 1L, 1L, NA, NA, NA, NA, NA),
    `0.55` = c(1L, 1L, 1L, 1L, NA, NA, NA, NA, NA), `0.56` = c(1L,
    1L, 1L, 1L, NA, NA, NA, NA, NA), `0.57` = c(1L, 1L, 1L, 1L,
    NA, NA, NA, NA, NA), `0.58` = c(1L, 1L, 1L, 1L, NA, NA, NA,
    NA, NA), `0.59` = c(1L, 1L, 1L, 1L, NA, NA, NA, NA, NA),
    `0.6` = c(1L, 1L, 1L, 1L, NA, NA, NA, NA, NA), `0.61` = c(1L,
    1L, 1L, NA, NA, NA, NA, NA, NA), `0.62` = c(1L, 1L, 1L, NA,
    NA, NA, NA, NA, NA), `0.63` = c(1L, 1L, 1L, NA, NA, NA, NA,
    NA, NA), `0.64` = c(1L, 1L, 1L, NA, NA, NA, NA, NA, NA),
    `0.65` = c(1L, 1L, 1L, NA, NA, NA, NA, NA, NA), `0.66` = c(1L,
    1L, 1L, NA, NA, NA, NA, NA, NA), `0.67` = c(1L, 1L, 1L, NA,
    NA, NA, NA, NA, NA), `0.68` = c(1L, 1L, 1L, NA, NA, NA, NA,
    NA, NA), `0.69` = c(1L, 1L, 1L, NA, NA, NA, NA, NA, NA),
    `0.7` = c(1L, 1L, 1L, NA, NA, NA, NA, NA, NA), `0.71` = c(1L,
    1L, NA, NA, NA, NA, NA, NA, NA), `0.72` = c(1L, 1L, NA, NA,
    NA, NA, NA, NA, NA), `0.73` = c(1L, 1L, NA, NA, NA, NA, NA,
    NA, NA), `0.74` = c(1L, 1L, NA, NA, NA, NA, NA, NA, NA),
    `0.75` = c(1L, 1L, NA, NA, NA, NA, NA, NA, NA), `0.76` = c(1L,
    1L, NA, NA, NA, NA, NA, NA, NA), `0.77` = c(1L, 1L, NA, NA,
    NA, NA, NA, NA, NA), `0.78` = c(1L, 1L, NA, NA, NA, NA, NA,
    NA, NA), `0.79` = c(1L, 1L, NA, NA, NA, NA, NA, NA, NA),
    `0.8` = c(1L, 1L, NA, NA, NA, NA, NA, NA, NA), `0.81` = c(1L,
    NA, NA, NA, NA, NA, NA, NA, NA), `0.82` = c(1L, NA, NA, NA,
    NA, NA, NA, NA, NA), `0.83` = c(1L, NA, NA, NA, NA, NA, NA,
    NA, NA), `0.84` = c(1L, NA, NA, NA, NA, NA, NA, NA, NA),
    `0.85` = c(1L, NA, NA, NA, NA, NA, NA, NA, NA), `0.86` = c(1L,
    NA, NA, NA, NA, NA, NA, NA, NA), `0.87` = c(1L, NA, NA, NA,
    NA, NA, NA, NA, NA), `0.88` = c(1L, NA, NA, NA, NA, NA, NA,
    NA, NA), `0.89` = c(1L, NA, NA, NA, NA, NA, NA, NA, NA),
    `0.9` = c(1L, NA, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names =
 c("0.1",
"0.2", "0.3", "0.4", "0.5", "0.6", "0.7", "0.8", "0.9"))

Note that first column are row names.

My attempts just got me so far as:

enter image description here

With the code:

tableA <- t(tableA)

df <- tibble::rownames_to_column(as.data.frame(tableA), "value")


df |>
  pivot_longer(cols = c("0.1" , "0.2",  "0.3",  "0.4",  "0.5",  "0.6",  "0.7",  "0.8" , "0.9"),
    names_to = "elevels",
    values_to = "evalues") |>
  mutate(evalues = as.numeric(evalues),
          elevels = as.numeric(elevels)) |>
  ggplot()  
    geom_density(aes(x = as.factor(elevels), y = value, group = elevels), stat = "summary")
  • What am I doing wrong here?

CodePudding user response:

Bonus Update:

library(ggthemes)
library(tidyverse)
df %>% 
  rownames_to_column("E") %>% 
  pivot_longer(-E) %>%  
  ggplot(aes(x=as.numeric(name), y=value, group=E, color=E)) 
  scale_y_continuous(breaks = seq(0, 1, by = 0.1)) 
  scale_x_continuous(breaks = seq(0, 1, 0.1)) 
  scale_color_manual(values= c("maroon", "red","orange", "gold",
                               "cadetblue2","dodgerblue",
                               "blue", "black", "green")
                     ) 
  geom_line(size = 1)  
  labs(x="", y="") 
  theme_hc() 
  theme(legend.position = "right")

enter image description here

First answer:

Here is a basic version how we could plot a line with the provided values:

library(tidyverse)
df %>% 
  rownames_to_column("id") %>% 
  pivot_longer(-id) %>%  
  ggplot(aes(x=name, y=value, group=id, color=id)) 
  geom_line()

enter image description here

  • Related