Melt a frequency table in order to use ggplot2

Question

I have a "frequency table" similar to this one

# Create a matrix
  df<- matrix(sample(0:10,35,replace=T),nrow=5, ncol=7)
# Rename columns and rows
  colnames(df) <- c("Monday","Tuesday", "Wednesday", "Thursday", "Friday",
                    "Saturday", "Sunday")
  row.names(df) <- c(2015, 2016, 2017, 2018, 2019)

I want to use ggplot2 in order to represent the information, but I need to transform this information into something like this:

Day       Year  Frequency
Monday    2015  10
Monday    2016  7
Monday    2017  13

I tried to use melt() function from reshape2 package but I did not get what I wanted, because the second column does not appear (probably because it corresponds to the row names of the table).

Any help would be appreciated,

score 2 · Answer 1 · answered Apr 29 '22 at 08:01

Your first column is actually your row names, which is not counted as a column when you convert to dataframe. So, first, convert your matrix to a dataframe, then add the row names to the dataframe (rownames_to_column), and then use melt:

reshape2::melt(tibble::rownames_to_column(data.frame(df)))

score 2 · Accepted Answer · answered Apr 29 '22 at 08:01

library(tidyverse)

# Create a matrix
df <- matrix(sample(0:10, 35, replace = T), nrow = 5, ncol = 7)
# Rename columns and rows
colnames(df) <- c(
  "Monday", "Tuesday", "Wednesday", "Thursday", "Friday",
  "Saturday", "Sunday"
)
row.names(df) <- c(2015, 2016, 2017, 2018, 2019)

df %>%
  as_tibble(rownames = "year") %>%
  pivot_longer(-year)
#> # A tibble: 35 × 3
#>    year  name      value
#>    <chr> <chr>     <int>
#>  1 2015  Monday        9
#>  2 2015  Tuesday       5
#>  3 2015  Wednesday     6
#>  4 2015  Thursday      9
#>  5 2015  Friday        6
#>  6 2015  Saturday     10
#>  7 2015  Sunday        3
#>  8 2016  Monday       10
#>  9 2016  Tuesday       2
#> 10 2016  Wednesday     6
#> # … with 25 more rows

^{Created on 2022-04-29 by the reprex package (v2.0.0)}

Melt a frequency table in order to use ggplot2

2 Answers2